Oracle 11gR2 Fine Grained Auditing (FGA) Policy

Example of adding a FGA policy to Oracle 11gR2 that will be collected by Audit Vault. This will create a policy on the SCHEMA.SCHEMA_TBL table called "QRY_TBL" for any select statement on COL1. The audited event will be written to the extended DB audit trail. This will provide the full sql command run, the column that triggered the event, various session data and will include any bind variable values used in the query.

Multiple columns and statement types can be provided in one statement. It is usual to have one policy statement to cover all columns that need to be audited on a table. The audit trail will show details of which columns caused the event to be audited.

BEGIN

  DBMS_FGA.ADD_POLICY (object_schema=>'"SCHEMA"',      object_name=>'"SCHEMA_TBL"',                policy_name=>'QRY_TBL’,

                audit_condition=>NULL,

                statement_types=>'SELECT', audit_column=>'COL1',

                audit_column_opts=>DBMS_FGA.ANY_COLUMNS,

                audit_trail=>DBMS_FGA.DB + DBMS_FGA.EXTENDED   );

END;