\r\n$ cd ${ORACLE_HOME}\/rdbms\/lib\r\n$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=${ORACLE_HOME}\r\n\r\nSQL> show parameter audit_trail\r\nNAME TYPE VALUE\r\n------------- ----------- ----------\r\naudit_trail string NONE\r\n<\/pre>\nI disabled all the Oracle built-in audit policies and created my own to record LOGON \/ LOGOFF actions.<\/p>\n
\r\nSQL> create audit policy audpol_connection\r\n actions logon, logoff;\r\n\r\nAudit policy created.\r\n\r\nSQL> audit policy audpol_connection;\r\n\r\nAudit succeeded.\r\n\r\nSQL> select * from audit_unified_enabled_policies;\r\n\r\nUSER_NAME POLICY_NAME ENABL SUC FAI\r\n--------------- -------------------- ----- --- ---\r\nALL USERS AUDPOL_CONNECTION BY YES YES\r\n\r\nSQL> select * from audit_unified_policies\r\n where policy_name = 'AUDPOL_CONNECTION'\r\n and audit_option like '%LOGO%';\r\n\r\nPOLICY_NAME AUDIT CONDI AUDIT_OPTION AUDIT_OPTION_TY OBJEC OBJEC OBJEC COMMO\r\n-------------------- ----- ----- --------------- --------------- ----- ----- ----- -----\r\nAUDPOL_CONNECTION NONE NONE LOGOFF STANDARD ACTION NONE NONE NONE\r\nAUDPOL_CONNECTION NONE NONE LOGON STANDARD ACTION NONE NONE NONE\r\n<\/pre>\nThis shows, that my audit policy is enabled and should record LOGON and LOGOFF actions.<\/p>\n
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).<\/p>\n
\r\nSQL> exec dbms_audit_mgmt.set_audit_trail_property(\r\n dbms_audit_mgmt.audit_trail_unified\r\n , dbms_audit_mgmt.audit_trail_write_mode\r\n , dbms_audit_mgmt.audit_trail_immediate_write\r\n )\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\nFor the sake of clean output let’s purge the existing audit trail.<\/p>\n
\r\nSQL> exec dbms_audit_mgmt.clean_audit_trail(\r\n dbms_audit_mgmt.audit_trail_unified\r\n , false\r\n )\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\nSo far so good. Now begins the actual test case by connecting to the database from a different terminal.<\/p>\n
\r\n$ sqlplus system\/manager@dev1\r\n\r\nSQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 21 17:39:02 2014\r\nCopyright (c) 1982, 2014, Oracle. All rights reserved.\r\nLast Successful login time: Tue Oct 21 2014 17:38:49 +02:00\r\nConnected to:\r\nOracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\r\nWith the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options\r\n<\/pre>\nNote that I have not logged out yet…<\/p>\n
Checking in my original session we cannot find any LOGON event recorded. I most definitively expected to see a LOGON event.<\/p>\n
\r\nSQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code\r\n from unified_audit_trail\r\n where action_name like '%LOG%'\r\n order by event_timestamp;\r\n\r\nno rows selected\r\n\r\n<\/pre>\nAfter disconnecting from the other session the LOGOFF event becomes visible.<\/p>\n
\r\nSQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code\r\n from unified_audit_trail\r\n where action_name like '%LOG%'\r\n order by event_timestamp;\r\n\r\nAUDIT_TYPE SESSIONID DBUSERNAME EVENT_TIMESTAMP ACTION_NAME RETURN_CODE\r\n---------- ------------ ------------- ------------------------------- --------------- -----------\r\nStandard 2253237941 SYSTEM 21-OCT-14 05.13.38.712564 PM LOGOFF 0\r\n\r\n<\/pre>\nI 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.<\/p>\n
Against all odds and feeling a little desperate I eventually decided to set “AUDIT_TRAIL = DB” and give it a go.<\/p>\n
\r\nSQL> show parameter audit_trail\r\n\r\nNAME TYPE VALUE\r\n------------- ----------- ----------\r\naudit_trail string DB\r\n<\/pre>\nI purged the audit trail and ran the same test as before again.<\/p>\n
Connect from a differen terminal to the database.<\/p>\n
\r\n$ sqlplus system\/manager@dev1\r\n\r\nSQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 21 17:39:02 2014\r\nCopyright (c) 1982, 2014, Oracle. All rights reserved.\r\nLast Successful login time: Tue Oct 21 2014 17:38:49 +02:00\r\nConnected to:\r\nOracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\r\nWith the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options\r\n<\/pre>\nCheck the unified audit trail – and there it is.<\/p>\n
\r\nSQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code\r\n from unified_audit_trail\r\n where action_name like '%LOG%'\r\n order by event_timestamp;\r\n\r\nAUDIT_TYPE SESSIONID DBUSERNAME EVENT_TIMESTAMP ACTION_NAME RETURN_CODE\r\n---------- ------------ ------------- ------------------------------- --------------- -----------\r\nStandard 1706589952 SYSTEM 21-OCT-14 07.17.46.063184 PM LOGON 0\r\n\r\n<\/pre>\nAfter disconnecting from the other session I can again see the LOGOFF action.<\/p>\n
\r\nSQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code\r\n from unified_audit_trail\r\n where action_name like '%LOG%'\r\n order by event_timestamp;\r\n\r\nAUDIT_TYPE SESSIONID DBUSERNAME EVENT_TIMESTAMP ACTION_NAME RETURN_CODE\r\n---------- ------------ ------------- ------------------------------- --------------- -----------\r\nStandard 1706589952 SYSTEM 21-OCT-14 07.17.46.063184 PM LOGON 0\r\nStandard 1706589952 SYSTEM 21-OCT-14 07.18.02.661384 PM LOGOFF 0\r\n\r\n<\/pre>\nIn 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.<\/p>\n
Foot note: All test have been run on Oracle 12.1.0.2<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11,3],"tags":[],"class_list":["post-55","post","type-post","status-publish","format-standard","hentry","category-auditing","category-internals"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/55","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=55"}],"version-history":[{"count":2,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/55\/revisions"}],"predecessor-version":[{"id":57,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/55\/revisions\/57"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=55"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=55"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=55"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}