Auditing Users

Fine Grain Auditing (FGA)

Purpose: Allow Administrator to log users SQL statement that the Users issues

Environment: Oracle 9i Enterprise edition 

Scenario: Assume that you want to audit User activity against EMP table in SCOTT's Schema

The administrator can start Auditing EMP table by typing the following command

SQL>BEGIN
DBMS_FGA.ADD_POLICY (
OBJECT_SCHEMA=>'SCOTT',
OBJECT_NAME=>'EMP',
POLICY_NAME=>'EMP_ACCESS' );
END;

Then the administrator can issue the following statement to get the auditing results

SQL>SELECT TIMESTAMP, 
DB_USER,
OS_USER,
OBJECT_SCHEMA,
OBJECT_NAME,
SQL_TEXT
FROM DBA_FGA_AUDIT_TRAIL;

TIMESTAMP DB_USER OS_USER OBJECT_ OBJECT_N SQL_TEXT
--------- ------- ------- ------- -------- ----------------------
27-Jan-04 SCOTT   AMMAR   SCOTT    EMP      select * from emp

Additionally,  You may want to audit user activity for a table when the user issues restricted command, as in SAL > 4000

SQL>BEGIN
DBMS_FGA.ADD_POLICY (
OBJECT_SCHEMA=>'SCOTT',
OBJECT_NAME=>'EMP',
POLICY_NAME=>'EMP_ACCESS',

AUDIT_COLUMN => 'SAL’,

AUDIT_CONDITION => 'SAL >’4000’);

END;

/

To Drop the auditing activity, you can drop the policy

SQL>BEGIN
DBMS_FGA.Drop_POLICY (
OBJECT_SCHEMA=>'SCOTT',
OBJECT_NAME=>'EMP',
POLICY_NAME=>'EMP_ACCESS' );
END;

You can also enable or disable the policy

SQL>BEGIN
DBMS_FGA.ENABLE_POLICY (
OBJECT_SCHEMA=>'SCOTT',
OBJECT_NAME=>'EMP',
POLICY_NAME=>'EMP_ACCESS',

ENABLE=>FALSE ); -- to disable set ENABLE=>FALSE
END;

/