Oracle 12c introduces “Unified Audit Trail” a faster, easier to access and more secure audit system.

It optionally allows to stage the audit records in a dedicated memory buffer (UNIFIED_AUDIT_SGA_QUEUE_SIZE), where they are temporarily grouped before being written into the audit table via batch transactions.

This new audit configuration substantially reduces the transactional overhead generated by the auditing.

Important improvements have also done to simplify the utilization:

– One single audit trail for any audit data, in fact  UNIFIED_AUDIT_TRAIL view replaces SYS.AUD$/DBA_AUDIT_TRAIL, SYS.FGA_LOGS$/DBA_FGA_AUDIT_TRAIL, DVSYS.AUDIT_TRAIL$, V$XML_AUDIT_TRAIL and the OS audit files in adump.

– All audit data stored in Oracle secure files.

– Role segregation between:

• DBA responsible to maintain free space and backup.
•  AUDIT_ADMIN responsible to manage the audit policies and define the data retention.
• AUDIT_VIEWER in charge of the reports.

Unified Audit Trail introduces also new security options important to mention:

– It is activated with a kernel relink and it doesn’t require additional steps or parameters.

– The new AUDSYS table has a Read-Only Protection for all users. Even the DBA privilege can’t manipulate the audit records!

How to activate Unified Audit Trail

--Stop all Oracle processes: databases, listener and Enterprise Manager agent.

--Relink Oracle with the uniaud_on option.
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle

--Restart all Oracle processes: databases, listener and Enterprise Manager agent.

--Check is Unified Audit Trail is active
SQL> select * from v$option where PARAMETER='Unified Auditing';

INST_ID PARAMETER                       VALUE              CON_ID
------- ------------------------------ ------------------- ----------
 1      Unified Auditing                TRUE                0

Optional, but strongly recommended it is possible to relocate the AUDIT segments  from SYSAUX Tablespace to a dedicated one.

SQL> Create tablespace TBS_AUDIT datafile SIZE 2G AUTOEXTEND ON;

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
 audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
 audit_trail_location_value => 'TBS_AUDIT');
END;
/


SQL> select OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, BYTES from dba_segments where TABLESPACE_NAME='TBS_AUDIT';

OWNER           SEGMENT_NAME                    PARTITION_NAME                SEGMENT_TYPE       BYTES
--------------- ------------------------------ ------------------------------ ------------------ ----------
AUDSYS           CLI_SWP$1b2a49f1$1$1           HIGH_PART                      TABLE PARTITION   65536
AUDSYS           CLI_SWP$1b2a49f1$1$1           PART_2                         TABLE PARTITION   65536
AUDSYS           CLI_LOB$1b2a49f1$1$1           HIGH_PART                      INDEX PARTITION   65536
AUDSYS           CLI_TIME$1b2a49f1$1$1          HIGH_PART                      INDEX PARTITION   65536
AUDSYS           CLI_LOB$1b2a49f1$1$1           PART_2                         INDEX PARTITION   65536
AUDSYS           CLI_TIME$1b2a49f1$1$1          PART_2                         INDEX PARTITION   65536
AUDSYS           CLI_SCN$1b2a49f1$1$1           PART_2                         INDEX PARTITION   65536
AUDSYS           SYS_IL0000091784C00014$$       SYS_IL_P241                    INDEX PARTITION   65536
AUDSYS           CLI_SCN$1b2a49f1$1$1           HIGH_PART                      INDEX PARTITION   65536
AUDSYS           SYS_IL0000091784C00014$$       SYS_IL_P246                    INDEX PARTITION   65536
AUDSYS           SYS_LOB0000091784C00014$$      SYS_LOB_P244                   LOB PARTITION     131072
AUDSYS           SYS_LOB0000091784C00014$$      SYS_LOB_P239                   LOB PARTITION     131072

12 rows selected.

The introduction of Audit Policies have brought flexibility and granularity on what it is possible to audit, here an example using Oracle sys_context function.

CREATE AUDIT POLICY hr_employees
 PRIVILEGES CREATE TABLE
 ACTIONS UPDATE ON HR.EMPLOYEES
 WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''HR_ADMIN'''
 EVALUATE PER STATEMENT;

AUDIT POLICY hr_employees;