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.
1 2 3 4 5 6 7 | $ cd ${ORACLE_HOME}/rdbms/lib$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=${ORACLE_HOME}SQL> show parameter audit_trailNAME TYPE VALUE------------- ----------- ----------audit_trail string NONE |
I disabled all the Oracle built-in audit policies and created my own to record LOGON / LOGOFF actions.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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 YESSQL> 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 NONEAUDPOL_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).
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 6 | 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.
1 2 3 4 5 6 7 8 | $ sqlplus system/manager@dev1SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 21 17:39:02 2014Copyright (c) 1982, 2014, Oracle. All rights reserved.Last Successful login time: Tue Oct 21 2014 17:38:49 +02:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith 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.
1 2 3 4 5 6 | 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.
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 5 | SQL> show parameter audit_trailNAME 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.
1 2 3 4 5 6 7 8 | $ sqlplus system/manager@dev1SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 21 17:39:02 2014Copyright (c) 1982, 2014, Oracle. All rights reserved.Last Successful login time: Tue Oct 21 2014 17:38:49 +02:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options |
Check the unified audit trail – and there it is.
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 5 6 7 8 9 | 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 0Standard 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
Pingback: Mashing up audit parameters | Spot on Oracle