How can I track related module/sql information in Oracle's Unified Audit Trail?

Question

When running a script, I like to use the SET_MODULE procedure to relate things for audit purposes. For example:

EXEC DMBS_APPLICATION_INFO.SET_MODULE('RELEASE123','My_Script.sql');

The nice thing is that subsequent DDL commands from the script will get tagged with the release and script, making it easier to identify later with the Module and Action. However, neither the Module or the Action appear in the Unified Audit Trail. I'm looking for something that we could do to pick out audit logs as it related to a release, or even a particular script associated with a release, without having to query multiple views.

What would be a good alternative option to track related audit logs in the Unified Audit Trail?

Answer 1

Oracle's built-in auditing is rather limited in the information it gathers. If you need enriched data, you need to implement your own auditing.

Simply create a database-level DDL trigger (CREATE TRIGGER xyz AFTER DDL ON DATABASE...), which you can then use to populate your own auditing table, with richer data from v$session, SYS_CONTEXT('USERENV',...), etc. And of course either of those can give you ACTION and MODULE and much more.

You can also (and should) capture further enriched info using special variables that will be available in a DDL system event trigger:

ora_dict_obj_owner
ora_dict_obj_name
ora_dict_obj_type
ora_sysevent
ora_grantee*
original_sql_txt*

The last two * items return non-scalar values (ora_name_list_t)

Note: you will need the "ADMINISTER DATABASE TRIGGER" priv to do this.