Unified Auditing: some gotchas to be aware of!

  • Database should be offline when you are enabling pure Unified Auditing or you will get this error:
    ORA-00600: internal error code, arguments: [krsh_fsga_sgaq.ds_not_found], [], [], [], [], [], [], [], [], [], [], []
    
  • By default, in 12c version the auditing runs in mixed-mode, which means you can combine traditional auditing with unified auditing. There is one gotcha to be aware of:
    – if the database has been upgraded, then there will be no Unified Policy enabled!
    – if the database has been newly created, then there will be 2 Unified Policies enabled: ORA_SECURECONFIG and ORA_LOGON_FAILURES. The second one audits only failed logons, so if you want to audit also the successful logons, then you have to adapt that policy.That’s the reason, why many DBAs are asking themselves why the view UNIFIED_AUDIT_TRAIL is getting populated even if (pure!) Unified Auditing is not explicitly enabled! There is also a MOS Note covering that “problem”: “The UNIFIED_AUDIT_TRAIL is Getting Populated even if Unified Auditing was not explicitly enabled in 12c (Doc ID 1624051.1)“.
    If you want to keep the old traditional auditing, then you should disable Unified Policies:

    NOAUDIT POLICY ORA_SECURECONFIG;
    NOAUDIT POLICY ORA_LOGON_FAILURES;
  • Unified Auditing: Unable To Clean Audit Trail Based On Last Archive Timestamp (Doc ID 1956200.1)
    There is a Bug 18743542 – ORA-8180 from DBMS_AUDIT_MGMT.clean_audit_trail deleting 12c unified audit based on a last archive timestamp (Doc ID 18743542.8), and it is solved with the Patch PSUOct2015.
  • Unified Audit Trail – LOGON Action Not Captured (Doc ID 1940793.1)
    BUG:19383839 – UNIFIED AUDIT – NO LOGON OR FAILED LOGON ACTION CAPTURED
    There is a patch 19383839 for it.
  • Wrong examples on some webpages, for example this one: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/security/sec_uni_audit/sec_uni_audit.html
    There is following example:

    create audit policy aud_action_pol 
      ACTIONS select, update, delete on hr.employees;

    But, in this case, not only the select and update statements for the hr.employees table will be audited – moreover ALL tables will be audited, so you can end up with unwanted audit records!!! The correct example would be:

    create audit policy aud_action_pol 
      ACTIONS select on hr.employees, update on hr.employees, delete on hr.employees;
  • Poor performance on UNIFIED_AUDIT_TRAIL: There is a known issue with read performance on the unified audit trail queries, and I can confirm this bug (with ca. 500 000 records in UNIFIED_AUDIT_TRAIL):
    BUG 21119008 - POOR QUERY PERFORMANCE ON UNIFIED_AUDIT_TRAIL
    

    As of now, there is no fix for this Bug. Next week, I’ll try to figure it out, why is it so slow…

  • Up to 12.2 base release, there is a bug  “Bug 19383839 – “Logon” and “Failed Logon” not recorded by Unified Audit if AUDIT_TRAIL=NONE (Doc ID 19383839.8)”. Additional MOS note: “UNIFIED_AUDIT_TRAIL View Does Not Display LOGGON FAILURES (Doc ID 2089107.1)”.
    Fortunately, there is an interim patch already to be applied onto PSU 12.0.1.2.170117 .

 

If you’re also aware of some gotcha regarding to the Unified Auditing, please inform me!

In the meantime, you can find more details about Unified Auditing here:
http://uhesse.com/2015/07/31/less-performance-impact-with-unified-auditing-in-oracle-12c/
https://blogs.oracle.com/UPGRADE/entry/how_to_migrate_to_unified
https://www.integrigy.com/oracle-security-blog/what-oracle-release-12c-unified-auditing
http://www.muniqsoft.de/tipps/oracle-neuerungen-zu/12c-release-1/unified-auditing.htm   <- in German
https://oracle-base.com/articles/12c/auditing-enhancements-12cr1 <- Auditing Enhancements (Audit Policies and Unified Audit Trail) in Oracle Database 12c Release 1 (12.1)
https://db-blog.web.cern.ch/blog/szymon-skorupinski/2014-06-unified-auditing-performance
http://docs.oracle.com/database/121/DBSEG/audit_config.htm#DBSEG888 <- DB Security Guide: Configuring Audit Policies
http://docs.oracle.com/database/121/DBSEG/audit_admin.htm#DBSEG370  <- DB Security Guide: Administering the Audit Trail
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/security/sec_uni_audit/sec_uni_audit.html

Known Issues When Using: DBMS_AUDIT_MGMT (Doc ID 804624.1)

 


5 thoughts on “Unified Auditing: some gotchas to be aware of!”

  • 1
    Michael Seberg on July 22, 2016 Reply

    Nice!

    I noticed another small gotcha. If ORA_CIS_RECOMMENDATIONS and ORA_SECURECONFIG are both enabled the UNIFIED_AUDIT_POLICIES column in the UNIFIED_AUDIT_TRAIL table gets populated by both policies.

    This could trip a WHERE clause like this:
    WHERE UNIFIED_AUDIT_POLICIES=’ORA_CIS_RECOMMENDATIONS’;

    Returning zero rows.

  • 2
    McLarenfan on August 23, 2017 Reply

    Hello and thank you for this post.
    I stuck into the following problem when using Unified Audit.

    DB Version – 12.1.0.2.0
    OS: AIX 7.1 (64)

    I’ve created policy ON SELECT like this:
    CREATE AUDIT POLICY pol1 ACTIONS SELECT ON user1.tab1;
    AUDIT POLICY pol1 EXCEPT user2,user3;

    Audit works some time, but on next day it doesn’t generate any audit records in unified_audit_trail view for any users, except user2,user3…

    If I recreate audit policy, it begin work again…

    Maybe bug or some error from me?

  • 3
    Lasse Jenssen on December 1, 2018 Reply

    Very nice article 🙂

    I have tested a little (using Oracle 12.1 and Oracle 12.2), and observed that the “ACTIONS” auditing do not write anything to the UNIFIED_AUDIT_TRAIL in Mixed Mode (even if specific user har reconnected). If I relink with uniaud_on the ACTION audit starts to appear. If I relink back to “uniaud_off” the ACTION is also written to the UNIFIED_AUDIT_TRAIL.

  • 4
    William Laverde on April 6, 2020 Reply

    Hi, Oracle Ninja.
    Nice Blogs. Here´s my question.
    I have RAC Primary (2 nodes), I know, need to enable on both nodes unified audit trail, now my dubt is on standy side (2 nodes too) Do I need to enable too on both node Unified Audit Trail for normal operations or in case o SwitchOver/Failover?

    • 5
      oracleninja on April 16, 2020 Reply

      Yes, you need to set the init parameter also on the standby database.

Leave a Reply to Michael Seberg Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.