Tag Archives: audit table and column

Fine Grained Auditing in Oracle

Fine Grained Auditing in Oracle

Fine Grained auditing is used to trace the table object with insert, update & delete operation. Without enable the System parameter AUDIT_TRAIL.
Fine grained auditing records are stored in SYS.FGA_LOG$ table and are accessible through the DBA_FGA_AUDIT_TRAIL view.

Note: DBA_COMMON_AUDIT_TRAIL view combines standard and fine-grained audit log records.

Fine Grained policy defined on the data access conditions from the objects in database.
Example you want to audit record if some one fetch the records of employee having salary greater than 5 lacs. In this you can add policy on employee table with column salary and condition > 5 lacs.

Create or ADD the Fine Grained Policy for Audit
In example, we monitor salary column of the hr.emp table of all insert,update,delete or select statements for department sales.

object_schema => 'hr',
object_name => 'emp',
policy_name => 'finegrained_hr_emp_check',
audit_condition => 'dept = ''SALES'' ',
audit_column => 'salary'
statement_types => 'insert,update,delete,select');

Note: If you execute the sql statement whichh uses salary column and in where condition dept = sales then it will generate audit.
Following are example of generate audit:

SELECT count(*) FROM hr.emp WHERE dept = 'SALES' and salary > 10000000;
SELECT salary FROM hr.emp WHERE dept = 'SALES';
DELETE from hr.emp where salary >1000000

Check the Audit record

select * from DBA_FGA_AUDIT_TRAIL;

select * from SYS.FGA_LOG$;

Example for DBMS_FGA.ADD_POLICY with all parameters

object_schema => 'scott', --schema of the object which audit
object_name=>'emp', --object name which audit
policy_name => 'mypolicy1', --define policy name for audit
audit_condition => 'sal 'comm, credit_card, expirn_date', --define column which audit
handler_schema => NULL, --schema that contains the event handler.
handler_module => NULL, -- schema that contains the event handler. Include the package the event handler is in.
enable => TRUE, -- means policy need to be enabled
statement_types=> 'INSERT, UPDATE', --Statement which need to be audit Insert,update,delete,select
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, -- includes SQL Text and SQL Bind.
audit_column_opts => DBMS_FGA.ALL_COLUMNS --audit all such columns are referenced

Enable the Policy

object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2,
enable BOOLEAN);

Disable the Policy

object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2 );

Drop the policy

object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2 );