When migrating to unified auditing in Oracle 12c I faced a strange behaviour in the recording of LOGON / LOGOFF actions.

As the Oracle 12c documentation points out, the AUDIT_* parameters have no effect once you enable unified auditing. Guess what I did in the good spirit of cleaning up obsolete settings:
I reset those parameters in the spfile after enabling unified auditing.

Enable unified auditing by relinking the oracle binary.

$ cd ${ORACLE_HOME}/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=${ORACLE_HOME}

SQL> show parameter audit_trail
NAME          TYPE        VALUE
------------- ----------- ----------
audit_trail   string      NONE

I disabled all the Oracle built-in audit policies and created my own to record LOGON / LOGOFF actions.

SQL> create audit policy audpol_connection
     actions logon, logoff;

Audit policy created.

SQL> audit policy audpol_connection;

Audit succeeded.

SQL> select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME          ENABL SUC FAI
--------------- -------------------- ----- --- ---
ALL USERS       AUDPOL_CONNECTION    BY    YES YES

SQL> select * from audit_unified_policies
     where policy_name = 'AUDPOL_CONNECTION'
     and audit_option like '%LOGO%';

POLICY_NAME          AUDIT CONDI AUDIT_OPTION    AUDIT_OPTION_TY OBJEC OBJEC OBJEC COMMO
-------------------- ----- ----- --------------- --------------- ----- ----- ----- -----
AUDPOL_CONNECTION    NONE  NONE  LOGOFF          STANDARD ACTION NONE  NONE  NONE
AUDPOL_CONNECTION    NONE  NONE  LOGON           STANDARD ACTION NONE  NONE  NONE

This shows, that my audit policy is enabled and should record LOGON and LOGOFF actions.

I also want the audit trail to be written immediately so it becomes visible in UNIFIED_AUDIT_TRAIL view without waiting for the flush to happen (or explicitly flushing it every time).

SQL> exec 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
     )

PL/SQL procedure successfully completed.

For the sake of clean output let’s purge the existing audit trail.

SQL> exec dbms_audit_mgmt.clean_audit_trail(
         dbms_audit_mgmt.audit_trail_unified
       , false
     )

PL/SQL procedure successfully completed.

So far so good. Now begins the actual test case by connecting to the database from a different terminal.

$ sqlplus system/manager@dev1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 21 17:39:02 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Tue Oct 21 2014 17:38:49 +02:00
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

Note that I have not logged out yet…

Checking in my original session we cannot find any LOGON event recorded. I most definitively expected to see a LOGON event.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

no rows selected

After disconnecting from the other session the LOGOFF event becomes visible.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

AUDIT_TYPE SESSIONID    DBUSERNAME    EVENT_TIMESTAMP                 ACTION_NAME     RETURN_CODE
---------- ------------ ------------- ------------------------------- --------------- -----------
Standard   2253237941   SYSTEM        21-OCT-14 05.13.38.712564 PM    LOGOFF          0

I have run multiple different test with different clients, connection methods, etc. all ending with the same result: Oracle just wont record LOGON actions! We only get to see the LOGOFF event.

Against all odds and feeling a little desperate I eventually decided to set “AUDIT_TRAIL = DB” and give it a go.

SQL> show parameter audit_trail

NAME          TYPE        VALUE
------------- ----------- ----------
audit_trail   string      DB

I purged the audit trail and ran the same test as before again.

Connect from a differen terminal to the database.

$ sqlplus system/manager@dev1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 21 17:39:02 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Tue Oct 21 2014 17:38:49 +02:00
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

Check the unified audit trail – and there it is.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

AUDIT_TYPE SESSIONID    DBUSERNAME    EVENT_TIMESTAMP                 ACTION_NAME     RETURN_CODE
---------- ------------ ------------- ------------------------------- --------------- -----------
Standard   1706589952   SYSTEM        21-OCT-14 07.17.46.063184 PM    LOGON           0

After disconnecting from the other session I can again see the LOGOFF action.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

AUDIT_TYPE SESSIONID    DBUSERNAME    EVENT_TIMESTAMP                 ACTION_NAME     RETURN_CODE
---------- ------------ ------------- ------------------------------- --------------- -----------
Standard   1706589952   SYSTEM        21-OCT-14 07.17.46.063184 PM    LOGON           0
Standard   1706589952   SYSTEM        21-OCT-14 07.18.02.661384 PM    LOGOFF          0

In my opinion this does not work as designed as I believe Oracle actually wanted the AUDIT_* parameters to have no effect on unified auditing. I haven’t tested exhaustively but I imagine there might be other problems with unified auditing when AUDIT_TRAIL is set to NONE. So, test carefully whether all your auditing is working as expected.

Foot note: All test have been run on Oracle 12.1.0.2