Oracle FGA - An Introduction

Hi
I will be briefly discussing about the Fine Grained Auditing (FGA) feature of Oracle 10g.
My next posts will be a 2 part discussion around FGA.
------------------------------------------------------------------------------------------------
Simple explanation on Oracle Fine Grained Auditing

What is Oracle FGA?

A traditonal approach in Oracle Database Auditing allows the users to keep track of the User's Actions they perfom on objects e.g. who selected data, from what table. But you are unaware of what they have selected This is MACRO LEVEL Auditing. This is not the case with the statemens such as UPDATE/DELETE/INSERT. You can keep track of what changes have been made to the database by these statements. Because SELECT does not fall under the DML statement, they neither fire a trigger nor get logged into archieved log. Therefore you cannot get them from LogMiner.
In Oracle 9i, a new feature "Fine Grained Auditing" was introduced. By the help of this feature you can even track individual SELECT statements alongwith the EXACT STATEMENT issued by the users. And in Oracle 10g this facility is extended to incorporate the DML statements as well.

Lets see the following example:-

1.Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates.

2.It is independent of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table

3.You need to define the policies to make the auditing. Policies can be defined / dropped / enabled and disabled using DBMS_FGA package.
3.1 ADD_POLICY
3.2 DROP_POLICY
3.3 ENABLE_POLICY
3.4 DISABLE_POLICY

4. Example
-- Apply the policy to the SAL column of the EMP table.
SQL> CONN sys/password AS SYSDBA

-- you require to log in as SYSDBA (this could prove to be a limitation in your

-- requirements)
At the SQL prompt type the following PL/SQL code.

BEGIN
DBMS_FGA.add_policy(
object_schema => 'AUD_TEST',
object_name => 'EMP',
policy_name => 'SAL_AUD',
audit_condition => NULL, -- Equivalent to TRUE
audit_column => 'SAL',
statement_types => 'SELECT,INSERT,UPDATE,DELETE');
END;
/

-- Test the auditing.
SQL>CONN aud_test/password -- here you can provide your normal username and password

SQL>SELECT * FROM emp WHERE empno = 9999;

SQL>INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Riya', 10000);

SQL>UPDATE emp SET sal = 100000 WHERE empno = 9999;

SQL>DELETE emp WHERE empno = 9999;

SQL>ROLLBACK;

-- Check the audit trail.

SQL>CONN sys/password AS SYSDBA

-- you require to log in as SYSDBA (this could prove to be a limitation in your

-- requirements)
SQL>SELECT sql_text FROM dba_fga_audit_trail;
SQL_TEXT
--------------------------------------
SELECT * FROM emp WHERE empno = 9999

INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Riya', 10000)

UPDATE emp SET sal = 100000 WHERE empno = 9999

DELETE emp WHERE empno = 9999

4 rows selected.

Thus as you can see the DBA_FGA_AUDIT_TRAIL table is giving you the complete statement issued by the user to perform an interaction with the database.

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

Columns of DBA_FGA_AUDIT_TRAIL

Columns –

SESSION_ID
Session id of the query
TIMESTAMP
Time of the query
DB_USER
Database username who executes the query
OS_USER
OS username who executes the query
USERHOST
Numeric instance ID for the Oracle instance from which the user is accessing the
database. Used only in environments with distributed file systems and shared database
files (e.g.,clustered Oracle on DEC VAX/VMS clusters)
CLIENT_ID
Client identifier in each Oracle session
EXT_NAME
External name
OBJECT_SCHEMA
Owner of the table or view
OBJECT_NAME
Name of the table or view
POLICY_NAME
Name of Fine Grained Auditing Policy
SCN
SCN of the query
SQL_TEXT
SQL text of the query
SQL_BIND
Bind variable data of the query
COMMENT$TEXT
Comments

Himanshu Dabir
Oracle Certified Professional - DBA