Thanks for the question, Paul.
Asked: December 01, 2017 - 7:42 pm UTC
Last updated: December 08, 2017 - 6:06 am UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
We are running on 11.2.0.4, 12.1 and 12.2.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SQL>
Some of the application groups we support want to review what they requested to be audited.
There are 3 basic audit options - statement, object, user and privilege.
To check the enabled audit options by each category I use below views,
DBA_STMT_AUDIT_OPTS
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
SQL> audit select, insert, update, delete on HR.COUNTRIES by access;
Audit succeeded.
SQL> select * from DBA_OBJ_AUDIT_OPTS;
OWNER OBJECT_NAM OBJECT_TYP ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
------------------------------ ---------- ---------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --- --------- --------- --------- --------- ---------
HR COUNTRIES TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
Is there a mechanism in place that can reconstruct the output of the all 3 views and produce commands that were used to enable auditing for stmt,obj and priv.
and Connor said...
DBMS_METADATA lets you get DDL for an object using DBMS_METADATA.GET_DDL, but you can also get what it called "dependent" DDL, ie, the DDL that "references" a base object. For that you need DBMS_METADATA.GET_DEPENDENT_DDL
SQL> audit select on hr.employees;
Audit succeeded.
SQL> set long 5000
SQL> select dbms_metadata.GET_DEPENDENT_DDL('AUDIT_OBJ','EMPLOYEES','HR') from dual;
DBMS_METADATA.GET_DEPENDENT_DDL('AUDIT_OBJ','EMPLOYEES','HR')
--------------------------------------------------------------------------------
AUDIT SELECT ON "HR"."EMPLOYEES" BY ACCESS WHENEVER SUCCESSFUL
AUDIT SELECT ON "HR"."EMPLOYEES" BY ACCESS WHENEVER NOT SUCCESSFUL