How can I audit all DDL for a specific schema in Oracle with the Unified Audit Trail?

Question

I'm running Oracle 19C and looking for a way to audit all DDL for a given schema. Specifically, I want to be able to audit all changes to the schema itself. Things like creating, dropping, and altering tables and views, modifying and/or compiling functions and packages, and even truncate. I'm not interested in any DML such as inserts, updates, or selects. I know I can do something like:

CREATE AUDIT POLICY ALTER_TABLE_POLICY
ACTIONS ALTER ON MYSCHEMA.TABLE1,
ACTIONS ALTER ON MYSCHEMA.TABLE2,
ACTIONS ALTER ON MYSCHEMA.TABLE3;
 
AUDIT POLICY ALTER_TABLE_POLICY;

But this seems very tedious for what I want to do. Not to mention, it doesn't account for creating new tables. Surely there is a better way to get all the changes I want.

I did see this topic here: Oracle - Audit Trail for a specific user

But that seems to be more for following all the actions of a specific user. I want to track the actions ON a specific user/schema. Not to mention, a lot of the results I see when I look for this seem to focus more on solutions prior to 12c and above.

How can I audit the DDL for a given schema utilizing the unified audit trail?

Paul W · Answer 1 · 2023-03-07T01:00:25.300

There is enable_ddl_logging, but it has some problems:

It only gives you the SQL, not identifying information.
It can fill up an alert log if you have application processes that do a lot of truncates and temp table work.
The alert log is not (easily) programmatically accessible.
Only the DBAs can look at it.
It's all-or-nothing. The whole DB, every DDL.

A better alternative is to create a system trigger (CREATE TRIGGER myschema.tr_audit_ddl AFTER DDL ON SCHEMA) and populate your own custom audit table. Within this trigger you have predefined variables available:

           ora_sysevent
           ora_dict_obj_owner
           ora_dict_obj_name
           ora_dict_obj_type
           original_sql_txt
           ora_revokee
           ora_grantee

You could pull in additional fields from SYS_CONTEXT/USERENV and v$session of course. You can decide what DDLs to ignore because they're too frequent. You can control who can access this information, how long you keep it, etc... you can even use table-drive rules. This is much more powerful than other options.

Just be sure, as with any system trigger, that you trap all exceptions (WHEN OTHERS THEN NULL) in the outermost block to avoid disrupting an application process if you have a problem in your trigger code. It's also wise to break the dependency chain to your logging table and any other objects you reference by using EXECUTE IMMEDIATE so you can trap missing object references or other parse problems at run time rather than have an invalid trigger that prevents application DDL from succeeding.

How can I audit all DDL for a specific schema in Oracle with the Unified Audit Trail?

1 Answers1