Fine Grained Auditing (FGA) routine using DBMS_FGA

STEP 1

Create a policy on a table & column to be audited

BEGIN
dbms_fga.add_policy
(
object_schema=>’SALES’,
object_name=>’PAYMENT’,
policy_name=>’PAYMENT_ACCESS’,
audit_column => ‘CARD_NUM’,
statement_types => ‘UPDATE, DELETE, SELECT’,
audit_condition => ‘CARD_NUM IS NOT NULL’
);
END;

Here we are asking to audit CARD_NUM column in PAYMENT table for SELECT, UPDATE & DELETE statements.

It will not audit operations, where CARD_NUM is NULL.

Policy name for this access control is PAYMENT_ACCESS.

STEP 2

Check the policy details from

SQL> select policy_name, object_name, object_schema, policy_text, policy_column from dba_audit_policies
/

STEP 3

Now if anyone uses statement containing CARD_NUM column

SQL> select * from payment
/

SQL> select card_num from payment
/

It will be logged in sys.fga_log$ OR dba_fga_audit_trail views

SQL> select timestamp, db_user, os_user,object_schema, object_name,sql_text from dba_fga_audit_trail
/

SAMPLE OUTPUT

TIMESTAMP,DB_USER,OS_USER,OBJECT_SCHEMA,OBJECT_NAME,SQL_TEXT

05/02/2008 19:15:20,SALES,USER1,SALES,PAYMENT,select * from payment
05/02/2008 19:43:19,SALES,USER1,SALES,PAYMENT,select card_num from payment

STEP 4

To delete the audit log

SQL> delete from sys.fga_log$

OR

SQL> delete from dba_fga_audit_trail

STEP 5

To delete the policy

begin
DBMS_FGA.DROP_POLICY
(
object_schema => ‘SALES’,
object_name => ‘PAYMENT’,
policy_name => ‘PAYMENT_ACCESS’
);
end;

Advertisements
This entry was posted in Oracle Auditing, Oracle Security and tagged . Bookmark the permalink.

2 Responses to Fine Grained Auditing (FGA) routine using DBMS_FGA

  1. preethi says:

    SQL> select timestamp, db_user, os_user,object_schema, object_name,sql_text from dba_fga_audit_trail
    2 ;
    select timestamp, db_user, os_user,object_schema, object_name,sql_text from dba_fga_audit_trail
    *
    ERROR at line 1:
    ORA-04063: view “SYS.DBA_FGA_AUDIT_TRAIL” has errors

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s