Fine-Grained Auditing (FGA)

Fine-Grained Auditing (FGA).

Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAILparameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used.

FGA does not record the statement when no rows matched the user’s query, or if the query was not successful due to some reason.

FGA can handle only four types of statements: SELECT, INSERT, UPDATE, and DELETE.

FGA works on PL*SQL package called “DBMS_FGA”.

The DBMS_FGA package contains the following procedures.

  1. ADD_POLICY
  2. DROP_POLICY
  3. ENABLE_POLICY
  4. DISABLE_POLICY

In detailed…

In Oracle 9i fine grained auditing was limited to queries, but in Oracle 10g it has been extended to include DML statements, as shown by the following example.

Now we will create user called aud_test with required privileges.

Give permissions to audit all operations to the VINOD user.

SYS>> grant connect,resource,unlimited tablespace to vinod identified by vinod;

Connect to VINOD user  and create a table object.

Add policies using dbms_fga  packages.

Now will do few transaction into SEP_AUD table.

Check the audit trail using dba_fga_audit_trail.

Perform UPDATE statement on same column which we enabled the auditing.

Connect to SYS user and check audit log.

In audit log we have two inset operations and one update .

All three operations we performed on STOCK_LEVEL column.

Now  i will perform another update operation on different column.

In above result the new update  operation is not audited

The thumb rule is on what columns we add policy those columns modification only audited.

How to enable & disable auditing??

By using same package but procedure is different.

DBMS_FGA.ENABLE_POILCY

DBMS_FGA.DISABLE_POILCY

 

THANK YOU ………..

 

 

 

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 5.00 out of 5)
Loading...

Add Comment