Exploring SYS.AUD$ and DBMS_AUDIT

In the intricate landscape of Oracle 19c database administration, managing audit trail data efficiently is paramount for maintaining data integrity and ensuring compliance with security standards. While SYS.AUD$ serves as the cornerstone for storing audit data, Oracle provides advanced functionalities through the DBMS_AUDIT_MGMT package, empowering administrators with greater control over audit trail management. In this technical discourse, we’ll delve into the capabilities of DBMS_AUDIT_MGMT, including its usage, an illustrative example, and the possibility of relocating the audit trail table to a user tablespace to mitigate SYSTEM tablespace constraints.

Enabling auditing

By default the audit_trail parameter is set to “DB”. This value directs audit records to the database audit trail (the SYS.AUD$ table), except for records that are always written to the operating system audit trail. Oracle recommends to use this setting for a general database for manageability.

show parameter audit_trail;

As this is an initialisation parameter, it must be changed in SPFILE and requires a database restart.

alter system set audit_trail=db scope=spfile;
startup force;

Now, let’s select some types of events to be audited.

audit create user by access;
audit create session by access;
audit drop user by access;
select * from DBA_STMT_AUDIT_OPTS;

It’s worth noting that the AUD$ table can grow very rapidly, and if it fills up the tablespace, the database will not allow new connections as they cannot be recorded in the AUD$ table. For this reason, it is advisable to perform periodic cleaning of the table and even move it to a user tablespace. Here is where the DBMS_AUDIT_MGMT package becomes so handy.”

Understanding DBMS_AUDIT_MGMT

DBMS_AUDIT_MGMT is a powerful PL/SQL package introduced by Oracle to facilitate the management of audit trail data. It offers a suite of procedures and functions designed to streamline audit trail administration tasks, such as purging obsolete audit data, managing audit policies, and configuring audit settings.

Example Usage of DBMS_AUDIT_MGMT

Let’s illustrate the usage of DBMS_AUDIT_MGMT with a practical scenario:

First of all the audit trail must be initialised using the INIT_CLEANUP procedure.

BEGIN
 IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
 THEN
    DBMS_OUTPUT.PUT_LINE('Already initialised');
 ELSE
   DBMS_AUDIT_MGMT.INIT_CLEANUP(
      audit_trail_type          =>; DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      default_cleanup_interval  =>; 12 /* hours */);
 END IF;
END;
/

Once initialised, we can call the CLEAN_AUDIT_TRAIL anytime.

BEGIN
    DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
        audit_trail_type    =>; DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
        use_last_arch_timestamp =>; TRUE);
END;
/

To display the currently configured audit trail properties we can query the view DBA_AUDIT_MGMT_CONFIG_PARAMS.

select * from DBA_AUDIT_MGMT_CONFIG_PARAMS;

We can also create a purge job for periodically deleting the audit trail records.

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
  audit_trail_type             =>;  DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  audit_trail_purge_interval   =>;  100 /* hours */,  
  audit_trail_purge_name       =>;  'CLEANUP',
  use_last_arch_timestamp      =>;  TRUE);
END;
/

The view DBA_AUDIT_MGMT_CLEANUP_JOBS shows the currently configured audit trail purge jobs.

select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;

All in all, by leveraging DBMS_AUDIT_MGMT, administrators can automate routine audit maintenance tasks, ensuring optimal performance and compliance adherence.

Moving SYS.AUD$ to a User Tablespace

To alleviate potential space constraints in the SYSTEM tablespace caused by the accumulation of audit data, administrators have the flexibility to relocate the SYS.AUD$ table to a user tablespace. This approach not only segregates audit data from system-related objects but also enables better management of tablespace resources.

-- Create a new tablespace with autoextend.
create tablespace TBS_AUDIT datafile '/opt/oracle/oradata/XE/audit.dbf' size 200M AUTOEXTEND on;

BEGIN
-- Move SYS.AUD$ to the new tablespace
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
       audit_trail_type            =>; DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
       audit_trail_location_value  =>;  'TBS_AUDIT');
END;
/

-- Check the new location.
select tablespace_name from all_tables where table_name = 'AUD$';

Executing the above SQL statement relocates the SYS.AUD$ table to a new tablespace, thereby mitigating the risk of SYSTEM tablespace saturation and ensuring uninterrupted audit trail functionality.

Conclusion

By harnessing the capabilities of DBMS_AUDIT_MGMT and strategically relocating the SYS.AUD$ table to a user tablespace, Oracle 19c administrators can optimise audit trail management, uphold database integrity, and mitigate potential performance bottlenecks. Embracing these advanced techniques empowers administrators to navigate the complexities of database security and compliance with confidence.

For further insights into audit trail management and advanced Oracle database administration techniques, consult the official Oracle documentation: