Phase 1 Implementing Oracle Fine-Grained Auditing (FGA)

In a previous lab, you have created DBSEC user and CUSTOMER table.

In this lab, you will implement a FGA case. The CUSTOMER table will be the one you created on the previous lab. Your task is to design and implement the following auditing functions on the CUSTOMER table.

Track all "SELECT" activities on the CUSTOMER table, including database user, operating system user, and time when the operation is performed.

Track the changes when CREDIT_LIMIT is set to a value above $5,000.

Track the CUSTOMER table when a customer record was deleted.

You need to generate one SQL script file to perform all the following tasks:

Add a policy to implement audit task 1 (SELECT). The policy will be named as AUDIT_POLICY_1_SELECT.

Add a policy to implement audit task 2 (CREDIT_LIMIT). The policy will be named as AUDIT_POLICY_2_CREDIT.

Add a policy to implement audit task 3 (DELETE on CUSTOMER table). The policy will be named as AUDIT_POLICY_3_DELETE.

Turn on the audit function.

Connect to user DBSEC, and perform the following DML functions for testing purpose:

SELECT CUSTOMER_ID, FIRST_NAME,LAST_NAME,CREDIT_LIMIT FROM customer WHERE CUSTOMER_ID=201340;

UPDATE customer SET CREDIT_LIMIT=2500000 WHERE CUSTOMER_ID=201340;

DELETE from CUSTOMER WHERE CUSTOMER_ID=201340;

Show the audit trail including when and who has used/modified the CUSTOMER table.

Roll back the above change.

Tips:

You may use a drop user statement to refresh your database and enable testing. Then, you need to recreate your CUSTOMER table. But, it is not required as a part of this project.

When you need to connect to SYSTEM user, please use a syntax so that it will ask the user to type the password. A typical example will be: "connect system;"

Phase 2

Using an Oracle select an existing user and audit all its activities.

Using Oracle:

Step #1: Connect as SYSTEM and make sure that the following parameters are set:

SQL> show parameter aud

NAME TYPE VALUE

------------------------------------ ----------- -----

audit_sys_operations boolean TRUE

audit_trail string DB

Step #2: Issue audit statement as follows:

AUDIT ALL BY DBSEC_CLERK

Step #3: Connect as DBSEC_CLERK and perform one DDL and DML statement.

Step #4: Connect as SYSTEM and retrieve the content of DBA_AUDIT_TRAIL.

CUSTOMER TABLE:

Column Name	Data Type
SALES_REP_ID	NUMBER(4)
CUSTOMER_ID	NUMBER(8) NOT NULL
CUSTOMER_SSN	VARCHAR2(9)
FIRST_NAME	VARCHAR2(20)
LAST_NAME	VARCHAR2(20)
ADDR_LINE	VARCHAR2(80)
CITY	VARCHAR2(30)
STATE	VARCHAR2(30)
ZIP_CODE	VARCHAR2(9)
PHONE	VARCHAR2(15)
EMAIL	VARCHAR2(80)
CC_NUMBER	VARCHAR2(20)
CREDIT_LIMIT	NUMBER
GENDER	CHAR(1)
STATUS	CHAR(1)
COMMENTS	VARCHAR2(1024)
USER_NAME	VARCHAR2(30)