Enable and configure Unified Audit Data Trail

Summary

References

Oracle® Manuals

My Oracle Support

Notes

What is Unified Auditing?

What is Unified Auditing? says:

The unified audit trail, which resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace, makes this information available in a uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view, and is available in both single-instance and Oracle Database Real Application Clusters environments. In addition to the user SYS, users who have been granted the AUDIT_ADMIN and AUDIT_VIEWER roles can query these views. If your users only need to query the views but not create audit policies, then grant them the AUDIT_VIEWER role.

When the database is writeable, audit records are written to the unified audit trail. If the database is not writable, then audit records are written to new format operating system files in the $ORACLE_BASE/audit/$ORACLE_SID directory.

Missing Procedure

Moving Operating System Audit Records into the Unified Audit Trail recommends:

From the previous release, the following modes, deprecated but retained for backward compatibility, are available. However, Oracle recommends that you use the DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS procedure instead, as described in Oracle Database Upgrade Guide.

This is no mention of DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS in either Oracle® 12.1 Database Upgrade Guide or Oracle® 12.1 Database PL/SQL Packages and Types Reference.

This procedure does not exist in Oracle RDBMS 12.1:

SQL>c DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS BEGIN DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS; END; * ERROR at line 1: ORA-06550: line 1, column 23: PLS-00302: component 'TRANSFER_UNIFIED_AUDIT_RECORDS' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

According to How To Transfer Unified Audit Records To An Internal Relational Table (Doc ID 2212196.1),

The procedure is available out of the box in the Oracle RDBMS 12.2 database and in the Oracle RDBMS 12.1.0.2 databases that have been patched with Patch 25985768.

Procedure

Enable Unified Auditing

Followed the procedure in 4.6.13.2 Migrating to Unified Auditing for Oracle Database:

[oracle@personal ~]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-APR-2018 21:30:19

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

The command completed successfully

[oracle@personal ~]$ . oraenv

ORACLE_SID = [personal] ? jar

The Oracle base remains unchanged with value /opt/app/oracle

[oracle@personal ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 15 21:31:07 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1543503872 bytes

Fixed Size 2924736 bytes

Variable Size 939528000 bytes

Database Buffers 587202560 bytes

Redo Buffers 13848576 bytes

Database mounted.

Database opened.

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE

----------------------------------------------------------------

FALSE

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@personal ~]$ cd ${ORACLE_HOME}/rdbms/lib

[oracle@personal lib]$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

/usr/bin/ar d /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a kzanang.o

/usr/bin/ar cr /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/kzaiang.o

chmod 755 /opt/app/oracle/product/12.1.0/dbhome_1/bin

- Linking Oracle

rm -f /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle

/opt/app/oracle/product/12.1.0/dbhome_1/bin/orald -o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ -L/opt/app/oracle/product/12.1.0/dbhome_1/lib/ -L/opt/app/oracle/product/12.1.0/dbhome_1/lib/stubs/ -Wl,-E /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/opimai.o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ssoraed.o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /opt/app/oracle/product/12.1.0/dbhome_1/lib/nautab.o /opt/app/oracle/product/12.1.0/dbhome_1/lib/naeet.o /opt/app/oracle/product/12.1.0/dbhome_1/lib/naect.o /opt/app/oracle/product/12.1.0/dbhome_1/lib/naedhs.o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/config.o -lserver12 -lodm12 -lcell12 -lnnet12 -lskgxp12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 -lknlopt `if /usr/bin/ar tv /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap12" ; fi` -lskjcx12 -lslax12 -lpls12 -lrt -lplp12 -lserver12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 `if [ -f /opt/app/oracle/product/12.1.0/dbhome_1/lib/libavserver12.a ] ; then echo "-lavserver12" ; else echo "-lavstub12"; fi` `if [ -f /opt/app/oracle/product/12.1.0/dbhome_1/lib/libavclient12.a ] ; then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12 -lrt -lplp12 -ljavavm12 -lserver12 -lwwg `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lztkg12 `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 `if /usr/bin/ar tv /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12 -lserver12"; fi` -L/opt/app/oracle/product/12.1.0/dbhome_1/ctx/lib/ -lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -locr12 -locrb12 -locrutl12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerged -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lsnls12 -lunls12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12 -laio -lons `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/sysliblist` -Wl,-rpath,/opt/app/oracle/product/12.1.0/dbhome_1/lib -lm `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/sysliblist` -ldl -lm -L/opt/app/oracle/product/12.1.0/dbhome_1/lib

test ! -f /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle ||\

mv -f /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracleO

mv /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle

chmod 6751 /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle

[oracle@personal lib]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-APR-2018 21:40:51

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Starting /opt/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production

System parameter file is /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/personal/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=personal)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 15-APR-2018 21:40:52

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/personal/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=personal)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

The listener supports no services

The command completed successfully

[oracle@personal lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 15 21:41:02 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1543503872 bytes

Fixed Size 2924736 bytes

Variable Size 939528000 bytes

Database Buffers 587202560 bytes

Redo Buffers 13848576 bytes

Database mounted.

Database opened.

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE

----------------------------------------------------------------

TRUE

SQL>

Summary:

    1. Stop all listeners and database instances

    2. Rebuild the kernel using the uniaud_on option

    3. Start all listeners and database instances

Configure Unified Auditing

Generate OS Audit Records and Load Them into Audit Table

This scenario is based upon Moving Operating System Audit Records into the Unified Audit Trail.

Open Database as Read-Only

First, I open the database as read-only:

[oracle@personal ~]$ sqlplus / as sysdba


SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 19 12:23:38 2018


Copyright (c) 1982, 2014, Oracle. All rights reserved.



Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options


SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup open read only

ORACLE instance started.


Total System Global Area 1543503872 bytes

Fixed Size 2924736 bytes

Variable Size 939528000 bytes

Database Buffers 587202560 bytes

Redo Buffers 13848576 bytes

Database mounted.

Database opened.

SQL> select open_mode from v$database;


OPEN_MODE

--------------------

READ ONLY

Generate OS Audit Records

Next, I generate some audit records:

SQL> connect system

Enter password:

ERROR:

ORA-28032: Your password has expired and the database is set to read-only



Warning: You are no longer connected to ORACLE.

SQL> connect / as sysdba

Connected.

SQL> connect demo

Enter password:

Connected.

Next, I check for the generation of OS Audit records:

SQL> !ls -lth /opt/app/oracle/audit/personal/

total 104K

-rw-r-----. 1 oracle oinstall 10K Apr 19 12:24 ora_audit_11.bin

-rw-r-----. 1 oracle oinstall 6.0K Apr 19 12:24 ora_audit_00.bin

-rw-r-----. 1 oracle oinstall 11K Apr 19 12:24 ora_audit_10.bin

-rw-r-----. 1 oracle oinstall 1.5K Apr 19 12:24 ora_audit_092.bin

-rw-r-----. 1 oracle oinstall 1.5K Apr 19 12:24 ora_audit_192.bin

-rw-r-----. 1 oracle oinstall 8.5K Apr 19 08:44 ora_audit_141.bin

-rw-r-----. 1 oracle oinstall 1.5K Apr 18 23:12 ora_audit_082.bin

-rw-r-----. 1 oracle oinstall 1.5K Apr 18 23:12 ora_audit_182.bin

-rw-r-----. 1 oracle oinstall 2.0K Apr 17 21:28 ora_audit_087.bin

-rw-r-----. 1 oracle oinstall 2.5K Apr 17 21:28 ora_audit_187.bin

-rw-r-----. 1 oracle oinstall 3.5K Apr 17 18:13 ora_audit_973707194_3058604836.bin

-rw-r-----. 1 oracle oinstall 1.5K Apr 17 12:46 ora_audit_070.bin

-rw-r-----. 1 oracle oinstall 1.5K Apr 17 12:45 ora_audit_170.bin

-rw-r-----. 1 oracle oinstall 3.5K Apr 17 07:27 ora_audit_973668475_2422469920.bin

-rw-r-----. 1 oracle oinstall 4.0K Apr 16 19:22 ora_audit_143.bin

-rw-r-----. 1 oracle oinstall 8.5K Apr 16 19:21 ora_audit_973624908_1453341488.bin

-rw-r-----. 1 oracle oinstall 1.5K Oct 4 2015 ora_audit_159.bin

SQL> !file /opt/app/oracle/audit/personal/ora_audit_11.bin

/opt/app/oracle/audit/personal/ora_audit_11.bin: data

Open Database as Read-Write

Next, open the database as read-write:

SQL> connect / as sysdba

Connected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup open read write

ORACLE instance started.


Total System Global Area 1543503872 bytes

Fixed Size 2924736 bytes

Variable Size 939528000 bytes

Database Buffers 587202560 bytes

Redo Buffers 13848576 bytes

Database mounted.

Database opened.

SQL> select open_mode from v$database;


OPEN_MODE

--------------------

READ WRITE

Load OS Audit Records into Audit Table

Now, load the OS audit records into the audit table using the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure:

SQL> select count(*) from unified_audit_trail;


COUNT(*)

----------

26393


SQL> exec DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES


PL/SQL procedure successfully completed.


SQL> select count(*) from unified_audit_trail;


COUNT(*)

----------

26410


SQL> !ls -lth /opt/app/oracle/audit/personal/

total 0

Display Audit Log

I ran the following SQL to get the audit entries for the above scenario:

SELECT event_timestamp,

dbusername,

audit_type,

action_name,

sql_text,

unified_audit_policies

FROM unified_audit_trail

WHERE event_timestamp > systimestamp - interval '6' hour

ORDER BY event_timestamp

The result was:

Manually Flushing Audit Records to the Audit Trail in Queued-Write Mode

Followed the procedure in Manually Flushing Audit Records to the Audit Trail in Queued-Write Mode.

Flushing audit records is only applicable if the Audit Write Mode is set to Queued.

Check Audit Write Mode

The following snippet of code shows that the Audit Write Mode is set to Queued:

COLUMN parameter_name FORMAT A30

COLUMN parameter_value FORMAT A30

SELECT parameter_name, parameter_value

FROM DBA_AUDIT_MGMT_CONFIG_PARAMS

WHERE parameter_name = 'AUDIT WRITE MODE'

AND audit_trail = 'UNIFIED AUDIT TRAIL'

/

The output is:

PARAMETER_NAME PARAMETER_VALUE

------------------------------ ------------------------------

AUDIT WRITE MODE QUEUED WRITE MODE

Manually Flush Audit Records

The DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL procedure is called to manually flush the audit records to disk:

EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

The output is simply:

PL/SQL procedure successfully completed.

Change Audit Write Mode

Check Audit Write Mode

The following snippet of code shows that the Audit Write Mode is set to Queued:

COLUMN parameter_name FORMAT A30

COLUMN parameter_value FORMAT A30

SELECT parameter_name, parameter_value

FROM DBA_AUDIT_MGMT_CONFIG_PARAMS

WHERE parameter_name = 'AUDIT WRITE MODE'

AND audit_trail = 'UNIFIED AUDIT TRAIL'

/

The output is:

PARAMETER_NAME PARAMETER_VALUE

------------------------------ ------------------------------

AUDIT WRITE MODE QUEUED WRITE MODE

Switch Audit Write Mode to Immediate

The DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure is called to set the Audit Write Mode to Immediate:

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(

DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,

DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);

END;

/

The output is simply:

PL/SQL procedure successfully completed.

Check Audit Write Mode

The following snippet of code shows that the Audit Write Mode is now set to Immediate:

COLUMN parameter_name FORMAT A30

COLUMN parameter_value FORMAT A30

SELECT parameter_name, parameter_value

FROM DBA_AUDIT_MGMT_CONFIG_PARAMS

WHERE parameter_name = 'AUDIT WRITE MODE'

AND audit_trail = 'UNIFIED AUDIT TRAIL'

/

The output is:

PARAMETER_NAME PARAMETER_VALUE

------------------------------ ------------------------------

AUDIT WRITE MODE IMMEDIATE WRITE MODE

Switch Audit Write Mode to Queued

The DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure is called to set the Audit Write Mode to Queued:

COLUMN parameter_name FORMAT A30

COLUMN parameter_value FORMAT A30

SELECT parameter_name, parameter_value

FROM DBA_AUDIT_MGMT_CONFIG_PARAMS

WHERE parameter_name = 'AUDIT WRITE MODE'

AND audit_trail = 'UNIFIED AUDIT TRAIL'

/

The output is simply:

PL/SQL procedure successfully completed.

Check Audit Write Mode

The following snippet of code shows that the Audit Write Mode is now set to Queued:

COLUMN parameter_name FORMAT A30

COLUMN parameter_value FORMAT A30

SELECT parameter_name, parameter_value

FROM DBA_AUDIT_MGMT_CONFIG_PARAMS

WHERE parameter_name = 'AUDIT WRITE MODE'

AND audit_trail = 'UNIFIED AUDIT TRAIL'

/

The output is:

PARAMETER_NAME PARAMETER_VALUE

------------------------------ ------------------------------

AUDIT WRITE MODE QUEUED WRITE MODE

Alternative Method to Get Audit Write Mode

The following snippet of code shows that the Audit Write Mode can be gotten by a PL/SQL procedure call to DBMS_AUDIT_MGMT.GET_AUDIT_TRAIL_PROPERTY_VALUE:

VARIABLE write_mode NUMBER
BEGIN
:write_mode
:=
dbms_audit_mgmt.get_audit_trail_property_value(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
audit_trail_property => dbms_audit_mgmt.audit_trail_write_mode
);
END;
/
PRINT write_mode

The output is:

PL/SQL procedure successfully completed.


WRITE_MODE

----------

1

These values are not in the online documentation. However, by examining the package definition, one finds the following snippet of code:

--

-- NG Audit Trail write mode configuration

AUDIT_TRAIL_WRITE_MODE CONSTANT NUMBER := 33;

-- Values for Write mode

AUDIT_TRAIL_QUEUED_WRITE CONSTANT NUMBER := 1;

AUDIT_TRAIL_IMMEDIATE_WRITE CONSTANT NUMBER := 2;

--AUDIT_TRAIL_COMMIT_WRITE CONSTANT NUMBER := 3;

Thus, 1 corresponds to Queued Write.