SDEV350-Lab 5 Security Auditing in Oracle Solved

In this lab, you will demonstrate to set-up and monitor audits using the Auditing features within Oracle.

Scenario: 

A new manager at your company has some growing concerns that private customer data stored in an AWS Oracle instance is being leaked or at least is being accessed by those who might not need to view the data. You have been tasked with using Auditing features with Oracle to monitor, track and identify any user who reads, inserts, updates or deletes data in the following tables:

•        Sales2019

•        Projections2020

•        Customers

The Tables have the following composition

Sales2019

•        CustomerID – Not Null, Integer, References Customers. The ID of the customer.

•        TransactionDate – Not Null, Date, Composite Primary Key with CustomerID. The date and time of the sale.

•        SalesAmount – not null, number(10,2). The amount of the sale.

•        ProfitAmount - not null, number(10,2). The amount of profit for this sale.

 

Projections2020

•        CustomerID – Not Null, Integer, References Customers. The ID of the customer.

•        QuarterlyPurchaseAmount – Not Null, number(10,2). Projected Quarterly purchases for this customer.

•        QuarterlyProfitAmount – not null, number(10,2). The projected quarterly Profit from purchases from this customer

•        Confidence – not null, number (4,2). The confidence (range 0.00 – 1.00) of this projection. Higher numbers indicate more confidence

Customers

•        CustomerID - Not Null, Integer, Primary Key. The ID of the customer

•        CustomerLastName – Not Null, varchar2(40), Lastname of the customer

•        CustomerFirstName – Not Null, varchar2(40). Firstname of the customer   CustomerEmail – Not Null, varchar2(80). Email address of the customer   CustomerPhone – varchar2(12), Phone number of the customer.

•        CustomerCellPhone - varchar2(12), Cell Phone number of the customer

Here are some suggestions that might help you get started:

1.      You will need to create the tables and at least 3 users to test the audit functionality. Note, your developer/DBA just left the company and put this application together quickly. Essentially, he gave all 3 users read, insert, update and delete privileges on all 3 tables. You should do the same for this analysis.

2.      You will need to populate the tables with real-looking data.

3.      You will need to create Audit statements, as appropriate for each table and/or user.

4.      You will need to login as each user to generate some data in the dba_audit_trail. To generate interesting results, I recommend conducting at least 10 different transactions for each user on each of the tables.

5.      Users should be created using the similar nomenclature as before:

Lab5_1FirstnameLastname, Lab5_2FirstnameLastname, Lab5_3FirstnameLastname. You should also create a role (e.g. R5FirstnameLastname) for the privileges.

6.      As you are building your audit results, be sure to look in to the dba_audit_trail data dictionary view to watch the data grow.