Skip to Main Content
  • Questions

  • Reconstruct sale from audit commands

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

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



We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.