DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Reducing Unified Audit Trail Size in Oracle 23ai

#oracle #dba #database #linux

The Unified Auditing feature in Oracle Database is a powerful mechanism for capturing database activity in a centralized and consistent way. However, it may introduce challenges for DBAs, particularly when it comes to managing the size of the audit trail.
One common concern is providing sufficient storage capacity for the AUD$UNIFIED table. In some environments, DBAs may wish to reduce the amount of data stored in the audit trail by excluding specific columns from the audit records.

Oracle Database 23ai(23.7) introduces a new initialization parameter to address this need: UNIFIED_AUDIT_TRAIL_EXCLUDE_COLUMNS

This parameter allows DBAs to exclude specific columns from being populated in the unified audit trail, reducing both storage consumption and the overhead of capturing unnecessary details.

SQL> SHOW PARAMETER unified_audit_trail_exclude_columns;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
unified_audit_trail_exclude_columns  string      NONE
Enter fullscreen mode Exit fullscreen mode

By default, the value is NONE, meaning no columns are excluded from the audit trail. If you attempt to set this parameter to an invalid value, Oracle will raise an error:

SQL> ALTER SYSTEM SET unified_audit_trail_exclude_columns=test;

ORA-00096: invalid value TEST for parameter
unified_audit_trail_exclude_columns, must be from among
none, dp_clob_parameters1, rls_info, sql_binds, sql_text
Enter fullscreen mode Exit fullscreen mode

Valid values include:

  1. NONE — No columns are excluded.
  2. SQL_TEXT — Excludes the SQL statement text from the audit trail.
  3. SQL_BINDS — Excludes bind variable values.
  4. DP_CLOB_PARAMETERS1 — Excludes Data Pump parameters from DP_CLOB_PARAMETERS1 column.
  5. RLS_INFO — Excludes row-level security (RLS/VPD/FGAC) context information.

Excluding SQL_TEXT

First, create and enable a simple unified audit policy:

SQL> CREATE AUDIT POLICY pol_select_tb ACTIONS SELECT ON vahid.tb;
Audit policy created.

SQL> AUDIT POLICY pol_select_tb;
Audit succeeded.
Enter fullscreen mode Exit fullscreen mode

Run a test query and view the audit trail with SQL_TEXT included:

SQL> SHOW PARAMETER unified_audit_trail_exclude_columns

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
unified_audit_trail_exclude_columns  string      NONE

SQL> VARIABLE v_id NUMBER;

SQL> EXEC :v_id := 1;

SQL> SELECT * FROM vahid.tb WHERE id=:v_id;
        ID FIRST_NAME LAST_NAME
---------- ---------- -----------
         1 Vahid      Yousefzadeh
Enter fullscreen mode Exit fullscreen mode 
SQL> SELECT dbusername, action_name, object_name,
            TO_CHAR(sql_binds) AS sql_binds,
            TO_CHAR(sql_text)  AS sql_text
     FROM   unified_audit_trail
     WHERE  unified_audit_policies='POL_SELECT_TB';

DBUSERNAME ACTION_NAM OBJECT_NAME SQL_BINDS  SQL_TEXT
---------- ---------- ----------- ---------- --------------------------------------------
VAHID      SELECT     TB          #1(1):1    select * from vahid.tb where id=:V_ID
Enter fullscreen mode Exit fullscreen mode

Now exclude SQL_TEXT and clean the audit trail:

SQL> ALTER SYSTEM SET unified_audit_trail_exclude_columns=sql_text;
System altered.

SQL> EXEC dbms_audit_mgmt.clean_audit_trail(
       audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,
       use_last_arch_timestamp=>false);
PL/SQL procedure successfully completed.

SQL> select * from vahid.tb where id=:V_ID;
        ID FIRST_NAME LAST_NAME
---------- ---------- -----------
         1 Vahid      Yousefzadeh
Enter fullscreen mode Exit fullscreen mode 
SQL> select dbusername,action_name ,object_name,to_char(sql_binds) sql_binds ,to_char(sql_text) sql_text  from unified_audit_trail  x where  unified_audit_policies='POL_SELECT_TB';
DBUSERNAME ACTION_NAM OBJECT_NAME SQL_BINDS  SQL_TEXT
---------- ---------- ----------- ---------- -----------------------------------------
VAHID      SELECT     TB           #1(1):1
Enter fullscreen mode Exit fullscreen mode

As expected, the SQL_TEXT column is now empty.

Excluding SQL_BINDS

SQL> ALTER SYSTEM SET unified_audit_trail_exclude_columns=sql_binds;
System altered;

SQL> select * from vahid.tb where id=:V_ID;
        ID FIRST_NAME LAST_NAME
---------- ---------- -----------
         1 Vahid      Yousefzadeh

SQL> select dbusername,action_name ,object_name,to_char(sql_binds) sql_binds ,to_char(sql_text) sql_text  from unified_audit_trail  x where  unified_audit_policies='POL_SELECT_TB';
DBUSERNAME ACTION_NAM OBJECT_NAME SQL_BINDS  SQL_TEXT
---------- ---------- ----------- ---------- --------------------------------------
VAHID      SELECT     TB                     select * from vahid.tb where id=:V_ID
Enter fullscreen mode Exit fullscreen mode

The bind variable values are excluded, but SQL_TEXT remains available.

Excluding DP_CLOB_PARAMETERS1 for Data Pump

SQL> create audit policy comp_policy ACTIONS  COMPONENT=datapump export;
Audit policy created.

SQL> audit policy comp_policy;
Audit succeeded.
SQL> ALTER SYSTEM SET unified_audit_trail_exclude_columns=none;
System altered;

$ expdp directory=q dumpfile=test.dmp tables=vahid.tb
Enter fullscreen mode Exit fullscreen mode

Query the audit trail with DP_CLOB_PARAMETERS1 included:

SQL> SELECT action_name,
            TO_CHAR(dp_clob_parameters1) dp_clob_parameters1
     FROM   unified_audit_trail
     WHERE  unified_audit_policies='COMP_POLICY';

ACTION_NAM DP_CLOB_PARAMETERS1
---------- ------------------------------------------------------
EXPORT     {"parameter":"CLIENT_COMMAND","value":"vahid/********@...}
Enter fullscreen mode Exit fullscreen mode

Exclude the column and repeat the export:

SQL> ALTER SYSTEM SET unified_audit_trail_exclude_columns=dp_clob_parameters1;
System altered;

$ expdp directory=q dumpfile=test.dmp tables=vahid.tb

SQL> SELECT action_name,
            TO_CHAR(dp_clob_parameters1) dp_clob_parameters1
     FROM   unified_audit_trail
     WHERE  unified_audit_policies='COMP_POLICY';

ACTION_NAM DP_CLOB_PARAMETERS1
---------- -------------------------
EXPORT
Enter fullscreen mode Exit fullscreen mode

The DP_CLOB_PARAMETERS1 column is now empty.

Excluding RLS_INFO

For environments using RLS (Row-Level Security), VPD (Virtual Private Database), or FGAC (Fine-Grained Access Control) policies, you can also exclude the RLS_INFO column (CLOB type) from the audit trail:

SQL> ALTER SYSTEM SET unified_audit_trail_exclude_columns=rls_info;

System altered;
Enter fullscreen mode Exit fullscreen mode

When enabled, the RLS_INFO column will remain NULL in audit records.

Top comments (0)