Create a policy on a table & column to be audited
audit_column => ‘CARD_NUM’,
statement_types => ‘UPDATE, DELETE, SELECT’,
audit_condition => ‘CARD_NUM IS NOT NULL’
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.
Check the policy details from
SQL> select policy_name, object_name, object_schema, policy_text, policy_column from dba_audit_policies
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
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
To delete the audit log
SQL> delete from sys.fga_log$
SQL> delete from dba_fga_audit_trail
To delete the policy
object_schema => ‘SALES’,
object_name => ‘PAYMENT’,
policy_name => ‘PAYMENT_ACCESS’