Wednesday, 10 July 2013

Querying Oracle for Fine Grained Auditing settings

An earlier post about Fine Grained Auditing shows how to enable FGA in an Oracle 11gR2 database - http://notastrophe.blogspot.com/2013/01/oracle-11gr2-fine-grained-auditing-fga.html . But how do you tell what FGA policies are applied. Typically to query audit settings you query DBA_STMT_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS, and these are shown in OEM's audit page and can be queried from there, but FGA requires a different query -

SELECT PNAME, PCOL,  FROM SYS.FGA$;
Gives the policy name and affected columns.

SELECT DBUID, CLIENTID, POLICYNAME FROM SYS.FGA_LOG$;
Gives audit trail of user, client id and audited policy. Can also get query details and object schema and name

Query against the regular audit log can be done against DBA_AUDIT_TRAIL and SYS.AUD$

No comments:

Post a Comment