How to migrate to Unified Auditing?

What is Unified Auditing and is it on by default?

Unified Auditing is the new auditing facility since Oracle Database 12c. But the “old” auditing is still working. And there are a few things to mention if you’d like to make the right choice. I have written some things about it a while ago but as I discovered yesterday my previous blog post (https://blogs.oracle.com/UPGRADE/entry/unified_auditing_is_it_on)  doesn’t satisfy all my needs.

The initial motivation to move towards the new Unified Audit trail is audit performance. The audit records will be written into the read-only table AUDSYS in SYSAUX tablespace. But there are other benefits such as no dependency on init.ora parameters, one location – one format, and close interaction with Oracle Audit Vault and Database Firewall. And of course tiny things such as the immediate write, which avoids losing any audit records during an instance crash.

Audit records are coming from those sources:

• Audit records (including SYS audit records) from unified audit policies and AUDIT settings
• Fine-grained audit records from the DBMS_FGA PL/SQL package
• Oracle Database Real Application Security audit records
• Oracle Recovery Manager audit records
• Oracle Database Vault audit records
• Oracle Label Security audit records
• Oracle Data Mining records
• Oracle Data Pump
• Oracle SQL*Loader Direct Load

In addition to user SYS all users having the roles AUDIT_ADMIN and AUDIT_VIEWER can query the AUDSYS table.

After upgrade to Oracle Database 12c Unified Auditing is not enabled by default in order to prevent customers having “old” auditing on already from enabling both auditing facilities at the same time. This is something you need to be aware of: Unified Auditing can be on together with the “old” auditing at the same time.

Check if Unified Auditing is linked into the oracle kernel;

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
PARAMETER         VALUE
----------------  ---------- 
Unified Auditing  FALSE

To link it into the kernel or enable it use the following commands/actions – and the documentation states that you’ll have to shut down the listener and restart it again afterwards:

• UNIX
  • cd $ORACLE_HOME/rdbms/lib
    make -f ins_rdbms.mk uniaud_on ioracle
• Windows
  • Rename the file %ORACLE_HOME%/bin/orauniaud12.dll.option to %ORACLE_HOME%/bin/orauniaud12.dll

The tricky part is now that – even though Unified Auditing is not enabled by default – Unified Auditing is enabled in a Mixed Mode, i.e. there are two auditing policies enabled – but the option is not linked into the kernel.

To disable these policies you’ll execute:

SQL> noaudit policy ORA_SECURECONFIG;
SQL> noaudit policy ORA_LOGON_FAILURES;

Don’t get me wrong: This is not a recommendation to disable Unified Auditing. I just would like to explain what’s on and the possibilities to turn things into the desired direction. The documentation says about Mixed Mode:

Mixed mode is intended to introduce unified auditing, so that you can have a feel of how it works and what its nuances and benefits are. Mixed mode enables you to migrate your existing applications and scripts to use unified auditing. Once you have decided to use pure unified auditing, you can relink the oracle binary with the unified audit option turned on and thereby enable it as the one and only audit facility the Oracle database runs. 

How do you enable a Unified Auditing Policy?

The documentation offers a straight forward tutorial (which is a bit EM driven):
http://docs.oracle.com/database/121/TDPSG/GUID-BF747771-01D1-4BFB-8489-08988E1181F6.htm#TDPSG50000

How to change between IMMEDIATE and QUEUED WRITE mode?

For a performance evaluation please see Szymon’s blog post at the CERN blogs. To switch between the different modes please see the Oracle Documentation:

• To use immediate write mode use this procedure:

• 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;
  /
  

• To use queued write mode run this procedure:

• 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_QUEUED_WRITE);
  END;
  /

The size of the queue by default is 1MB. If you’d like to change it (maximum: 30MB) the initialization parameter UNIFIED_AUDIT_SGA_QUEUE_SIZE has to be changed.

What happens now to the traditional AUDIT_TRAIL parameter and what effect does it have?

AUDIT_TAIL will still trigger and direct the “old” auditing facilitiy (SYS.AUD$ for the database audit trail, SYS.FGA_LOG$ for fine-grained auditing, DVSYS.AUDIT_TRAIL$ for Oracle Database Vault, Oracle Label Security, and so on). So be aware to have both auditing facilities on at the same time as this won’t make much sense. Our recommendation since Oracle Database 11g is generally to set AUDIT_TRAIL in every 11g/12c database explicitly to the value you want. Otherwise it could always happen (and happens many times) that your database accidentally writes audit records into AUD$. Reason why this happens so often: the default setting for AUDIT_TRAIL since Oracle Database 11g is “DB” unless you change this via the non-standard parameter listening in the DBCA (Database Configuration Assistant).

Therefore always set AUDIT_TRAIL explicitly to the value you want to prevent the database from accidental auditing.

Summary – Steps to migrate to Unified Auditing?

1. Turn off traditional auditing with AUDIT_TRAIL=NONE
2. Link Unified Auditing into the kernel or enable it on Windows
3. Define your auditing policies
4. Monitor it with the views UNIFIED_AUDIT_TRAIL and in multitenant environments with CDB_UNIFIED_AUDIT_TRAIL

A final question remains unanswered:
What happens to your existing audit records in AUD$ and the other auditing tables?

Actually I can’t answer this question but to me there seems to be no way to migrate existing audit records into the new Unified Auditing facility. But I don’t think that this will cause any issues as you can keep and safely store the contents of the traditional auditing. They don’t get overwritten or deleted during an upgrade.

Further information required?

• Overview information:
  • https://blogs.oracle.com/imc/entry/oracle_database_12c_new_unified 
• Excellent description from CERN:
  • http://db-blog.web.cern.ch/blog/szymon-skorupinski/2014-05-migrating-oracle-database-12c-what-do-auditing
• Tutorial from Oracle:
  • http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/security/sec_uni_audit/sec_uni_audit.html
• Performance comparison between IMMEDIATE WRITE and QUEUED ASYNCH WRITE (credits go again by Szymon for this excellent write-up):
  • http://db-blog.web.cern.ch/blog/szymon-skorupinski/2014-06-unified-auditing-performance
• Admnistering the Audit Trail (Oracle 12c Doc):
  • https://docs.oracle.com/database/121/DBSEG/audit_admin.htm#DBSEG1026

Known Issues

• MOS Note:2089107.1
  UNIFIED_AUDIT_TRAIL View Does Not Display LOGGON FAILURES
  Solved with Patch 19383839

–Mike