Managing the Unified Audit Trail

Unified auditing is enabled by default, and audit trail management ensures audit configuration is efficient for your needs.

Related Topics

How and Where Unified Audit Records Are Created

Auditing is always enabled. Oracle Database generates audit records during or after the execution phase of the audited SQL statements.

The unified audit records are written immediately to disk to an internal relational table in the AUDSYS schema. In the previous release, the unified audit records were written to SecureFile LOBs.The partitioned version of this table is based on the EVENT_TIMESTAMP timestamp as a partition key with a default partition interval of once a day. If the database version does not support partitioning, then the internal table is a regular, non-partitioned table. Note: If you had migrated to unified auditing in Oracle Database 12c release 1 (12.1), then you can manually transfer the unified audit records from the SecureFile LOBS to this internal table. If the version of the database that you are using supports partitioned tables, then this internal table is a partitioned table. In this case, you can modify the partition interval of the table by using the DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL procedure.

The generation and insertion of an audit trail record is independent of the user transaction being committed. That is, even if a user transaction is rolled back, the audit trail record remains committed.

Statement and privilege audit options from unified audit policies that are in effect at the time a database user connects to the database remain in effect for the duration of the session. When an unified audit policy is created and enabled, it will take effect immediately in the on-going session of the user on whom that policy is enabled without requiring that user to restart the database session. This holds true even when the unified audit policy gets disabled as well. However, any modifications (with respect to the statement audit option, privilege audit option, and audit conditions) to the existing unified audit policy definition using ALTER AUDIT POLICY statement will take effect in the subsequent sessions of the users on whom that policy is enabled.

In contrast, changes to schema object audit options become immediately effective for current sessions.

By default, audit trail records are written to the AUDSYS schema in the SYSAUX tablespace. Oracle recommends that you designate a different tablespace, including the one that is encrypted, by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure.

The following example shows how to designate a different tablespace by using DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.

  1. Create a dedicated auto segment space managed (ASSM) tablespace for unifited auditing:

    CREATE TABLESPACE auto_seg_audit_tablespace DATAFILE 'DiskGroup_name' SIZE 1M
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;

  2. Designate the tablespace for unified auditing:

    BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_location_value  =>  'auto_seg_audit_tablespace');
END;

Related Topics

Sizing Recommendations for Unified Auditing

Unified audit trail records require at least 50 percent more disk space than traditional audit records.

As a best practice, Oracle recommends that you archive and purge unified audit trail records on a regular basis.

Related Topics

Managing Potential Sensitive Data Visibility in the Audit Trail

By setting the UNIFIED_AUDIT_TRAIL_EXCLUDE_COLUMNS parameter, you can specify columns that should be excluded from the UNIFIED_AUDIT_TRAIL view. This can help prevent sensitive information from being visible in the UNIFIED_AUDIT_TRAIL view.

How Audit Trail Records Are Written to the AUDSYS Schema

Oracle Database automatically writes audit records to an internal relational table in the AUDSYS schema.

Writing audit records to a relational table in the AUDSYS schema prevents the risk of audit records being lost in the event of an instance crash or during a SHUTDOWN ABORT operation. By default, the AUDSYS schema is dictionary protected, which means that other users cannot use system privileges (including ANY privileges) to modify or tamper with its data.

Note: In Oracle Database 12c release 1 (12.1), you had the option of queuing the audit records in memory (queued-write mode) and be written periodically to the AUDSYS schema audit table. However, starting with Oracle Database 12c release 2 (12.2), immediate-write mode and queued-write mode are deprecated. The parameters that controlled them (DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE and DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE), while still viewable, no longer have any functionality. If you have upgraded from Oracle Database 12c release 1 (12.1) and migrated to unified auditing in that release, then Oracle recommends that you use the DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS procedure to transfer the audit records as generated in the previous release to the AUDSYS audit internal table. Oracle Database Upgrade Guide provides information about transferring unified audit records after an upgrade.

Related Topics

Writing the Unified Audit Trail Records to SYSLOG or the Windows Event Viewer

You can write the unified audit trail records to SYSLOG or the Windows Event Viewer by setting an initialization parameter.

About Writing the Unified Audit Trail Records to SYSLOG or the Windows Event Viewer

With this feature, you can copy some of the key unified audit fields to SYSLOG or the Windows Event Viewer.

Only key fields of unified audit records in the UNIFIED_AUDIT_TRAIL data dictionary view are copied to SYSLOG. SYSLOG records in a unified audit environment provide proof of operational integrity.

You can configure this feature on both UNIX and Microsoft Windows systems. On Windows systems, you either enable it or disable it. If enabled, it writes the records to the Windows Event Viewer.

On UNIX systems, you can fine-tune the capture of unified audit trail records for SYSLOG to specify the facility where the SYSLOG records are sent and the severity level of the records (for example, DEBUG if it is capturing debugging-related messages). The following table maps the names given to the unified audit records fields that are written to SYSLOG and the Windows Event Viewer to the corresponding column names in the UNIFIED_AUDIT_TRAIL view.

Field Name Column Name in UNIFIED_AUDIT_TRAIL Column Type Column Description
TYPE AUDIT_TYPE NUMBER Type of the audit record
DBID DBID NUMBER Database identifier
SESID SESSION_ID NUMBER Session identifier
CLIENTID CLIENT_IDENTIFIER VARCHAR2 Client identifier in the session
STMTID STATEMENT_ID NUMBER Identifier for each statement run in the system
DBUSER DB_USERNAME VARCHAR2 Session user
CURUSER CURRENT_USER VARCHAR2 Effective user for the audited event
ACTION ACTION NUMBER Action code of the audited event
RETCODE RETURN_CODE NUMBER Return code for the audited event
SCHEMA OBJECT_SCHEMA VARCHAR2 Schema name of the object
OBJNAME OBJECT_NAME VARCHAR2 Name of the object
PDB_GUID NULL (there are no columns in UNIFIED_AUDIT_TRAIL for this field) VARCHAR2 GUID of the container in which the unified audit record is generated

Enabling SYSLOG and Windows Event Viewer Captures for the Unified Audit Trail

You can write a subset of unified audit trail records to the UNIX SYSLOG or to the Windows Event Viewer.

  1. Locate the init.ora initialization file, which by default is in the $ORACLE_HOME/dbs directory.

  2. Edit the init.ora file to include the UNIFIED_AUDIT_SYSTEMLOG parameter.

    You can set UNIFIED_AUDIT_SYSTEMLOG in either the CDB root or in a PDB.

    In an Oracle Database Real Application Clusters (Oracle RAC) environment, set UNIFIED_AUDIT_SYSTEMLOG to the same value on each Oracle RAC instance.

    • On Windows, set UNIFIED_AUDIT_SYSTEMLOG to either TRUE or FALSE. TRUE writes the SYSLOG values to the Windows Event Viewer; FALSE disables the parameter. On Windows, the default is FALSE. For example:

      UNIFIED_AUDIT_SYSTEMLOG = TRUE

    • On UNIX systems, use the following syntax:

      UNIFIED_AUDIT_SYSTEMLOG = 'facility_clause.priority_clause'

      There is no default setting for UNIFIED_AUDIT_SYSTEMLOG on UNIX systems.

      In this specification:

      • facility_clause refers to the facility to which you will write the audit trail records. Valid choices are USER and LOCAL. If you enter LOCAL, then optionally append 0–7 to designate a local custom facility for the SYSLOG records.

      • priority_clause refers to the type of warning in which to categorize the record. Valid choices are NOTICE, INFO, DEBUG, WARNING, ERR, CRIT, ALERT, and EMERG.

    For example:

    UNIFIED_AUDIT_SYSTEMLOG = 'LOCAL7.EMERG'

  3. On UNIX platforms, to write unified audit records to SYSLOG set the UNIFIED_AUDIT_COMMON_SYSTEMLOG parameter to either TRUE or FALSE in the init.ora file in the root.

    Setting UNIFIED_AUDIT_COMMON_SYSTEMLOG to TRUE writes predefined columns of unified audit records from common unified audit policies to SYSLOG. FALSE disables these columns from being written to SYSLOG.

    You cannot set this parameter in a pluggable database (PDB). There is no Windows equivalent of the UNIFIED_AUDIT_COMMON_SYSTEMLOG parameter.

  4. Add the audit file destination to the SYSLOG configuration file /etc/syslog.conf.

    For example, assuming you had set the UNIFIED_AUDIT_SYSTEMLOG to LOCAL7.EMERG, enter the following:

    local7.emerg /var/log/audit.log

    This setting logs all emergency messages to the /var/log/audit.log file.

  5. Restart the SYSLOG logger.

    $/etc/rc.d/init.d/syslog restart

    Now, all audit records will be captured in the file /var/log/audit.log through the syslog daemon.

  6. Log back in to the database instance.

  7. Restart the database.

    For example:

    SHUTDOWN IMMEDIATE
STARTUP

    If you set UNIFIED_AUDIT_SYSTEMLOG in a PDB, then close and reopen the PDB:

    ALTER PLUGGABLE DATABASE pdb_name CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE pdb_name OPEN;

Related Topics

Configuring Auditing in Automatic Storage Management Instances

Leverage the UNIIFIED_AUDIT_SYSTEMLOG, ASM_UNIFIED_AUDIT_LEVEL, and ASM_UNIFIED_AUDIT_DESTINATION init.ora parameters to configure auditing in Automatic Storage Management (ASM) instances. The following parameters can be specified in ASM, ASM proxy, or remote ASM instances.

UNIIFIED_AUDIT_SYSTEMLOG

When UNIIFIED_AUDIT_SYSTEMLOG is set in ASM instances, audit records are redirected to SYSLOG and will not write a copy of the full audit record to the OS files.

For any changes to the UNIIFIED_AUDIT_SYSTEMLOG to be effective, you will need to restart your database instance.

ASM_UNIFIED_AUDIT_LEVEL

The ASM_UNIFIED_AUDIT_LEVEL can be set to either:

ASM_UNIFIED_AUDIT_DESTINATION

The ASM_UNIFIED_AUDIT_DESTINATION can be set to either:

Note: If SYSLOG is not configured, the audit records will be written to the spillover files regardless of the parameter.

How Unified Audit Records are Written to the Operating System

When the database cannot write audit trail records in the database itself, Oracle Database writes these records to operating system spillover audit files (.bin format).

This can happen in situations such as the following:

The default locations for unified audit spillover .bin files are as follows:

The unified audit records will continue to be written to OS spillover files until the OS disk space becomes full. At this point, when there is no room in the OS for the audit records, user auditable transactions will fail with ORA-02002 error while writing to audit trail errors. To prevent this problem, Oracle recommends that you purge the audit trail on a regular basis.

Related Topics

Moving Operating System Audit Records into the Unified Audit Trail

Audit records that have been written to the spillover audit files can be moved to the unified audit trail database table.

When the database is not writable (such as during database mounts), if the database is closed, or if it is read-only, then Oracle Database writes the audit records to these external files. The default location for these external files is the $ORACLE_BASE/audit/$ORACLE_SID directory.

You can load the files into the database by running the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure. If you are loading a large number of operating system audit records in the external files, then consider the impact on the performance.

Note: Before unplugging a PDB, Oracle recommends that you load any pending unified audit spillover records into the unified audit trail. In a multitenant environment, unified audit records for a PDB can be written to operating system spillover files in .bin format. These files are stored outside the PDB and are not included in the PDB unplug XML metadata or PDB archive.

To preserve the audit history after the unplug operation, connect to the PDB while it is open read/write and run:

EXEC DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;

Run this procedure before executing the ALTER PLUGGABLE DATABASE ... UNPLUG statement. If you do not load the spillover audit files before unplugging the PDB, some audit records may remain only in the source environment’s operating system files and may not be available after the PDB is moved.

Follow these steps to load the audit records from operating system files to the AUDSYS schema audit table when the database is writable:

  1. Log into the database as a user who has been granted the AUDIT_ADMIN role.

    Before you can upgrade to the current release or Oracle Database, you must run the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure from the CDB root to avoid losing operating system spillover files during the upgrade process. Similarly, before unplugging a PDB, connect to the PDB and run this procedure to load any PDB-specific spillover audit records.

  2. Ensure that the database is open and writable.

    To find if the database is open and writable, query the V$DATABASE view.

    SELECT NAME, OPEN_MODE FROM V$DATABASE;

NAME            OPEN_MODE

--------------- ----------
HRPDB           READ WRITE

    You can run the show pdbs command to find information about PDBs associated with the current instance.

  3. Run the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure.

    For example:

    EXEC DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;

    If you want to load a specific batch size of spillover operating system audit files, include the load_batch_size parameter. For example, to load 10 spillover files for the current container:

    BEGIN
 DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES(
  container        => 1,
  load_batch_size  => 10);
END;
/

    If you omit the load_batch_size parameter, then the default value of load_batch_size is 3. In that case, EXEC DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES; only loads 3 files at a time.

  4. If you want to load individual PDB audit records, then log in to each PDB and run the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure again.

The audit records are loaded into the AUDSYS schema audit table immediately, and then deleted from the $ORACLE_BASE/audit/$ORACLE_SID directory.

If the session ID linked to the spillover audit files is owned by the PMON process, then the files can’t be loaded until the database is restarted.

Improving the Performance of Queries and Purge Operations

If the partition on which the AUDSYS.AUD$UNIFIED table is located is too large, then queries to and purges of the UNIFIED_AUDIT_TRAIL data dictionary view may take a long time to complete.

To improve performance, break the partition into smaller portions by using the ALTER TABLE SPLIT PARTITION statement.

For example:

ALTER TABLE "AUDSYS"."AUD$UNIFIED" SPLIT PARTITION "SYS_P1602"
 INTO
   (PARTITION SYS_P1602_1 VALUES LESS THAN (DATE '2020-08-15'),
    PARTITION SYS_P1602
    );

Related Topics

Using Oracle Data Pump to Export and Import Unified Audit Trail Records

You can include the unified audit trail in Oracle Database Pump export and import dump files.

The unified audit trail is automatically included in either full database or partial database export and import operations using Oracle Data Pump. As part of the schema level export or import operation, Oracle Database does not include the audit policy’s metadata in the SYS schema during the export or import operation. Instead, use full export (expdp) or import (impdp) for the export and import of the metadata in unified audit policies.

For example, for a partial database export operation that does not use schema level export or import, if you wanted to export only the unified audit trail tables, then you could enter the following commands:

  1. In SQL*Plus, move any operating system audit records that have been written to the spillover audit files to the unified audit trail table. Doing so ensures that all records will be exported.

  2. From the operating system prompt, run the following command:

    expdp system
full=y
directory=aud_dp_dir
logfile=audexp_log.log
dumpfile=audexp_dump.dmp
version=18.02.00.02.00
INCLUDE=AUDIT_TRAILS

Password: password

Next, you can import all the exported content by reading the export dump file. This operation imports only the unified audit trail tables.

impdp system
full=y
directory=aud_dp_dir
dumpfile=audexp_dump.dmp
logfile=audimp_log.log

Password: password

You do not need to perform any special configuration to achieve this operation. However, you must have the EXP_FULL_DATABASE role if you are performing the export operation and the IMP_FULL_DATABASE role if you are performing the import operation.

Related Topics

How Do Cursors Affect Auditing?

For each execution of an auditable operation within a cursor, Oracle Database inserts one audit record into the audit trail.

Events that cause cursors to be reused include the following:

Auditing is not affected by whether or not a cursor is shared. Each user creates their own audit trail records on first execution of the cursor.