Oracle 19c — Audit Only Top-Level SQL Statements

 If the volume of logs generated by the unified auditing feature is relatively high, referring to and maintaining the audit trail may lead to performance issues and storage shortages. Therefore, auditing should be configured in a way that avoids storing unnecessary information in the related table.

The new Oracle 19c feature called Auditing Only Top-Level SQL Statements can be effective in this regard.

With this feature, you can enable auditing only for statements that are executed directly by the user (so-called Top-Level statements) and ignore auditing statements that are executed as part of a procedure call.

To use this feature, you simply need to add the clause ONLY TOPLEVEL to the CREATE AUDIT POLICY statement. Additionally, by checking the column AUDIT_ONLY_TOPLEVEL in the view AUDIT_UNIFIED_POLICIES, you can see which audit policies are using this feature. See the example below:

Example: Consider the following procedure:

create or replace procedure myproc1  as

begin

insert into  mytbl select * from v$datafile;

insert into  mytbl select * from v$datafile;

insert into  mytbl select * from v$datafile;

insert into  mytbl select * from v$datafile;

insert into  mytbl select * from v$datafile;

insert into  mytbl select * from v$datafile;

insert into  mytbl select * from v$datafile;

insert into  mytbl select * from v$datafile;

insert into  mytbl select * from v$datafile;

commit;

end;

Suppose we want to audit some of the actions performed by user usef. For this purpose, we create an audit policy with the following command:

SQL> create audit policy actions_all_pol actions all;
Audit policy created.

Then we enable the created audit policy for user usef:

SQL> audit policy actions_all_pol by usef;
Audit succeeded.

User usef executes the procedure myproc1:

SQL> exec myproc1;
PL/SQL procedure successfully completed

By checking the unified_audit_trail view, we will see that 11 records were added to the audit trail for this procedure execution:

SQL>select count(*) from unified_audit_trail f where f.unified_audit_policies=’ACTIONS_ALL_POL’;

11

We can display the recorded information with the following command:

SQL> select f.sessionid,f.action_name,f.object_schema,f.object_name from unified_audit_trail f where f.unified_audit_policies=’ACTIONS_ALL_POL’;

As shown, the text of the INSERT statements inside the myproc1 procedure were recorded in the audit trail.

Now, we want to use the Auditing Only Top-Level SQL Statements feature to prevent logging extra information. So, we repeat the process again:

— Delete existing records from the aud$unified table and disable the created audit policy:

SQL> begin
       dbms_audit_mgmt.clean_audit_trail(
         audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
         use_last_arch_timestamp => false
       );
     end;
/

SQL> noaudit policy actions_all_pol by usef;
Noaudit succeeded.

— Create a new audit policy with the ONLY TOPLEVEL clause:

SQL> create audit policy TOPLEVEL_on actions all ONLY TOPLEVEL;
Audit policy created.

— Enable the created audit policy for user usef:

SQL> audit policy TOPLEVEL_on by usef;
Audit succeeded.

— Execute the procedure by user usef:

SQL> exec myproc1;
PL/SQL procedure successfully completed

— With this procedure execution, only 1 record will be added to the audit trail:

SQL> select f.sessionid, f.action_name, f.sql_text 
     from unified_audit_trail f 
     where f.unified_audit_policies = 'TOPLEVEL_ON';

As you can see, with the help of the Auditing Only Top-Level SQL Statements feature, only the statement that was executed directly by the user is recorded in the audit trail.

Written by Vahid Yousefzadeh

I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com