Unified Auditing – is it ON or OFF in Oracle Database 12.2.0.1?

Just a quick update to my blog post from September 2014:

Unified Auditing – is it ON or OFF in Oracle 12c?

Any changes in Oracle Database 12.2.0.1?

Yes. Significant changes happen to Unified Auditing in Oracle Database 12.2.0.1. Mostly internally as the mechanism used to dump the audit records in Oracle 12.1.0.x when Unified Auditing was on turned out to be very good for write performance, but not so good when you tried to read data. See my blog post from a few weeks ago how to deal with this performance implication and a potential patch:

Unified Auditing – Performance Improvements in Oracle 12.1.0.2

But I’d like to understand if the so called “Mixed Mode” is still existent in Oracle Database 12.2.0.1, meaning you can have the old audit trail enabled but the database is still auditing some activities via the newer Unified Auditing policies.

Comparison Oracle 12.1.0.2 vs Oracle 12.2.0.1

Quick check in Oracle 12.1.0.2:

SQL> column policy_name format a25
SQL> column user_name format a14
SQL> column enabled format a7
SQL> set line 200
SQL> set pages 1000
SQL> SELECT policy_name, enabled_opt, user_name FROM audit_unified_enabled_policies;

POLICY_NAME		  ENABLED_ USER_NAME
------------------------- -------- --------------
ORA_SECURECONFIG	  BY	   ALL USERS
ORA_LOGON_FAILURES	  BY	   ALL USERS

And now the same query in Oracle 12.2.0.1:

SQL> column policy_name format a25
SQL> column user_name format a14
SQL> column enabled format a7
SQL> set line 200
SQL> set pages 0
SQL> SELECT policy_name, enabled_opt, user_name
  FROM audit_unified_enabled_policies  2  ;
ORA_SECURECONFIG	  BY	   ALL USERS
ORA_LOGON_FAILURES	  BY	   ALL USERS

SQL> set pages 1000
SQL> r
  1  SELECT policy_name, enabled_opt, user_name
  2*   FROM audit_unified_enabled_policies

POLICY_NAME		  ENABLED_ USER_NAME
------------------------- -------- --------------
ORA_SECURECONFIG	  BY	   ALL USERS
ORA_LOGON_FAILURES	  BY	   ALL USERS

No change.

As in Oracle 12.1.0.x, in Oracle Database 12.2.0.1 two default Unified Auditing Policies are enabled. And still I’d recommend to turn them off if you are either going to use the old auditing via audit_trail or don’t want to have auditing at all.

SQL> noaudit policy ORA_SECURECONFIG;
Noaudit succeeded.

SQL> noaudit policy ORA_LOGON_FAILURES;
Noaudit succeeded.

SQL> SELECT policy_name, enabled_opt, user_name FROM audit_unified_enabled_policies;
no rows selected

Again, to be clear, I’m not saying that you shouldn’t use the new Unified Auditing. But disable the Mixed Mode. Use the real (and enabled, i.e. linked into your kernel) Unified Auditing instead if you would like to audit in Oracle Database 12.2.0.1. Or stay with the old auditing if it does what you want and expect.

–Mike

Unified Auditing – Performance Improvements in Oracle 12.1.0.2

Unified Auditing got introduced in Oracle Database 12.1.

The downsides of the “old” auditing facilities became obvious when too many users had activities or transactions at the same time leading to audit records being written into AUD$. Contention was a typical issue. The same thing happened when too many users tried to login at the same time. Furthermore protecting the auditing information required Database Vault as there was no default protection available.

This – and some other things – should be remedied by Unified Auditing which is available since Oracle Database 12c. It gets enabled in sort of a “mixed mode” where Unified Auditing is not linked into the Oracle kernel by default. The same applied to Oracle Database 12.2.

At last year’s DOAG Conference in November in Nürnberg a customer approached me being really angry about Unified Auditing performance in Oracle 12.1.0.2.

How does Unified Auditing write audit records?

With Unified Auditing, audit records get written into an in-memory SGA queue. The writes will be very fast. That is the good part. The not so good aspect gets visible when you compare a million audit records in the old AUD$ table vs. querying from UNIFIED_AUDIT_TRAIL (which uses the underlying (G)V$UNIFIED_AUDIT_TRAIL), a massive view with over 90 columns. Furthermore there are other issues when querying the (G)V$UNIFIED_AUDIT_TRAIL, for instance no parallelism, no indexes on fixed tables, and some others. And as the underlying data is in a SecureFile LOB the data needs to be transformed in order to query it efficiently.

This architecture got changed significantly in Oracle Database 12.2.

But part of the changes done for Oracle Database 12.2 can be made available in Oracle Database 12.1 by applying patch 22782757.

See also:

What happens when you apply patch 22782757?

With this patch the following things will be introduced into Oracle Database 12.1.0.2:

  • A new relational partitioned table AUDSYS.AUD$UNIFIED gets created. Default interval of 1 month, partition key is column EVENT_TIMESTAMP
  • A new procedure TRANSFER_UNIFIED_AUDIT_RECORDS in package SYS.DBMS_AUDIT_MGMT  gets created to transfer the unified audit records from the CLI backend table to AUDSYS.AUD$UNIFIED. Please see MOS Note: 2212196.1
    How To Transfer Unified Audit Records To An Internal Relational Table
  • The way how audit records get written in Oracle Database 12.1.0.2 does not get changed by the patch
  • A new ORA-46385 (DML and DDL operations are not allowed on table “string”.”string”.  Cause: A DML or DDL operation was attempted on a unified auditing internal table) gets introduced and raised when one attempt to modify an entry in the new AUDSYS.AUD$UNIFIED table
  • All attempts to modify entries/metadata in AUDSYS.AUD$UNIFIED will be audited as well
  • UNIFIED_AUDIT_TRAIL view is becoming a UNION ALL of existing GV$UNIFIED_AUDIT_TRAIL and new  AUDSYS.AUD$UNIFIED
  • Users wanting to get access to AUDSYS will need the SELECT ANY DICTIONARY privilege. SELECT ANY TABLE is not sufficient anymore
  • Some minor changes will happen as well

How to link Unified Auditing into the oracle kernel

Not sure why enabling/disabling Unified Auditing via chopt is not available – in fact you’ll have to link it in manually if you’d like to use Unified Auditing. See here for some information and how to link it:

How to migrate to Unified Auditing?

Basically the steps are:

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

Then check afterwards that the option is visible:

$ sqlplus / as sysdba

SQL> SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
PARAMETER         VALUE
----------------  ---------- 
Unified Auditing  TRUE

Apply the patch – step-by-step

  1. Download patch 22782757 for the January 2017 Bundle Patch for Oracle Database 12.1.0.2 from Support.oracle.com and copy it to the patch directory
  2. Unzip it with:
    unzip -d $ORACLE_HOME  p22782757_12102170117ProactiveBP_Linux-x86-64.zip 
  3. Shutdown database and all services
  4. Do a conflict check:
    [DB12] oracle@localhost:/u01/app/oracle/product/12.1.0.2/22782757
    $ /u01/app/oracle/product/12.1.0.2/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
    Oracle Interim Patch Installer version 12.1.0.1.12
    Copyright (c) 2017, Oracle Corporation.  All rights reserved.
    
    PREREQ session
    
    Oracle Home       : /u01/app/oracle/product/12.1.0.2
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
    OPatch version    : 12.1.0.1.12
    OUI version       : 12.1.0.2.0
    Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2017-04-18_14-28-09PM_1.log
    
    Invoking prereq "checkconflictagainstohwithdetail"
    
    Prereq "checkConflictAgainstOHWithDetail" passed.
    
    OPatch succeeded.
    [DB12] oracle@localhost:/u01/app/oracle/product/12.1.0.2/22782757
    
  5. Apply the patch:
    $ /u01/app/oracle/product/12.1.0.2/OPatch/opatch apply
    Oracle Interim Patch Installer version 12.1.0.1.12
    Copyright (c) 2017, Oracle Corporation.  All rights reserved.
    
    
    Oracle Home       : /u01/app/oracle/product/12.1.0.2
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
    OPatch version    : 12.1.0.1.12
    OUI version       : 12.1.0.2.0
    Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2017-04-18_14-29-49PM_1.log
    
    Verifying environment and performing prerequisite checks...
    OPatch continues with these patches:   22782757  
    
    Do you want to proceed? [y|n]
    y
    User Responded with: Y
    All checks passed.
    Provide your email address to be informed of security issues, install and
    initiate Oracle Configuration Manager. Easier for you if you use your My
    Oracle Support Email address/User Name.
    Visit http://www.oracle.com/support/policies.html for details.
    Email address/User Name: 
    
    You have not provided an email address for notification of security issues.
    Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
    
    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
    (Oracle Home = '/u01/app/oracle/product/12.1.0.2')
    
    Is the local system ready for patching? [y|n]
    y
    User Responded with: Y
    Backing up files...
    Applying interim patch '22782757' to OH '/u01/app/oracle/product/12.1.0.2'
    
    Patching component oracle.rdbms, 12.1.0.2.0...
    
    Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...
    Patch 22782757 successfully applied.
    Log file location: /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2017-04-18_14-29-49PM_1.log
    
    OPatch succeeded.
    [DB12] oracle@localhost:/u01/app/oracle/product/12.1.0.2/22782757
    
  6. Start the database(s) and run datapatch to apply the required SQL changes:
    [DB12] oracle@localhost:/u01/app/oracle/product/12.1.0.2/22782757
    $ /u01/app/oracle/product/12.1.0.2/OPatch/datapatch -verbose
    SQL Patching tool version 12.1.0.2.0 Production on Tue Apr 18 14:36:13 2017
    Copyright (c) 2012, 2017, Oracle.  All rights reserved.
    
    Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_11812_2017_04_18_14_36_13/sqlpatch_invocation.log
    
    Connecting to database...OK
    Bootstrapping registry and package to current versions...done
    Determining current state...done
    
    Current state of SQL patches:
    Patch 22782757 (ER : BACKPORT PROJ #46892 TO 12.1):
      Installed in the binary registry only
    Patch 25355562 (MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170117 FOR BUGS 20602794 20807398):
      Installed in the binary registry and the SQL registry
    Bundle series DBBP:
      ID 170117 in the binary registry and ID 170117 in the SQL registry
    
    Adding patches to installation queue and performing prereq checks...
    Installation queue:
      Nothing to roll back
      The following patches will be applied:
        22782757 (ER : BACKPORT PROJ #46892 TO 12.1)
    
    Installing patches...
    Patch installation complete.  Total patches installed: 1
    
    Validating logfiles...
    Patch 22782757 apply: SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22782757/20980464/22782757_apply_DB12_2017Apr18_14_36_27.log (no errors)
    SQL Patching tool complete on Tue Apr 18 14:36:28 2017
    [DB12] oracle@localhost:/u01/app/oracle/product/12.1.0.2/22782757
    

Transfer Audit Records

Now as the patch is applied the new table does exist – but it has no rows.

SQL> select count(*) from AUDSYS.AUD$UNIFIED;

  COUNT(*)
----------
	 0

SQL> select count(*) from v$unified_audit_trail;

  COUNT(*)
----------
    726823

Therefore the transfer procedures must be initiated:

SQL> exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
SQL> exec DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS;

Once this is done (in a loop for the first time) the contents of the views above have changed – V$UNIFIED_AUDIT_TRAIL is empty whereas AUDSYS.AUD$UNIFIED has now all the information. While MOS Note: 2212196.1: How To Transfer Unified Audit Records To An Internal Relational Table recommends to do the transfer within a WHILE … LOOP I received more predictable results by doing this as a recurring job once every 5 minutes or so. And it’s important to do it only on one node on a RAC system as otherwise you may get duplicates.

Furthermore my first attempt resulted in an ORA-600 – and in an non-CDB so even though the bug indicated CDB this is independent of non-CDB/CDB setups:

  • Bug 25851605 : ORA-00600 [KZAN_SET_INTSQL] WHEN PURGING UNIFIED AUDIT DATA IN CDB

Interesting note on the side:
The DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS procedure is designed to deal with only a predefined number of rows from CLI back end table per execution. So initially you may have to run the procedure in a WHILE … LOOP to move all the records over:

 begin
    DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
    while 1=1 loop
       DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS;
    end loop;
 end;
/

But once this is completed (check with “select count(*) from v$unified_audit_trail” and then CTRL-C the program) I had a job kicking off every 5 or 10 minutes simply doing:

SQL> begin
  2   DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
  3   DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS;
  4   end;
  5  /

As a result of this action the audit information can be now queried much more efficient from AUDSYS.AUD$UNIFIED than from V$UNIFIED_AUDIT_TRAIL.

Two simple comparisons between the two tables, each of them filled with roughly 800,000 audit records. At first a simple COUNT(*):

SQL> select count(*) from v$unified_audit_trail;

  COUNT(*)
----------
    800057

Elapsed: 00:00:07.43

SQL> select count(*) from audsys.aud$unified;

  COUNT(*)
----------
    833122

Elapsed: 00:00:00.15

And then a lookup of USERID and OS_USER based on a STATEMENT_ID:

SQL> select userid, os_user from v$unified_audit_trail where statement_id=769436;

USERID	 OS_USER
-------- --------------------
SYS      oracle

Elapsed: 00:00:06.98


SQL> select userid, os_user from audsys.aud$unified where statement_id=178528;

USERID   OS_USER
-------- --------------------
SYS      oracle
SYS      oracle 

Elapsed: 00:00:00.20

–Mike

How to migrate to Unified Auditing?

Lock

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 defaultUnified 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?

Known Issues

–Mike

Unified Auditing – is it ON or OFF in Oracle 12c?

Don’t trust our slides – only believe what you’ve verified by yourself 😉

Actually one of our slides gives a parameter recommendation to set AUDIT_TRAIL since Oracle 11g explicitly to the value you want as otherwise it may switch to “DB” and you may not be aware of it. In conjunction with this setting we explain the new Oracle Database 12c feature Unified Auditing – which is not linked into the kernel and therefore should be off.

Should be … well … thanks to Marco Patzwahl who asked me why he still has over 100 audit records in V$UNIFIED_AUDIT_TRAIL? Good question – and I’ve had no answer. But Carol, my manager, knew the right person to ask. And Naveen replied within minutes (thanks!!!).

Here are the facts:

  • Unified Auditing is not linked into the Oracle 12c kernel by default to offer people the choice to use it and to avoid conflicts in case somebody has auditing ON already – so neither during an upgrade nor with a fresh 12c database you’ll see it included into the kernel. It will have to be linked in manually (see our slides)
    • Check if Unified Auditing is present in your environment:
      SQL> select VALUE from V$OPTION where
      PARAMETER=’Unified Auditing’;
    • In case you’ll link it into the kernel
      cd $ORACLE_HOME/rdbms/lib
      make -f ins_rdbms.mk
      uniaud_on ioracle
      ORACLE_HOME=$ORACLE_HOME

      make sure you set AUDIT_TRAIL=NONE afterwards as otherwise both auditing mechanisms will run concurrently
  • But even though it is not linked into the kernel a bit of Unified Auditing is ON by default in MIXED MODE when you create a fresh Oracle 12c database.
    • MIXED MODE auditing?
      • See the documentation for further information
      • Just two policies are enabled by default: ORA_SECURECONFIG and ORA_LOGON_FAILURES
  • Turn Unfiied Auditing OFF?
    • If is has been linked in into the kernel, unlink it:
      cd $ORACLE_HOME/rdbms/lib
      make -f ins_rdbms.mk 
      uniaud_off ioracle ORACLE_HOME=$ORACLE_HOME
    • Disable the two default policies – this will turn off any Unified Auditing features:
      SQL> noaudit policy ORA_SECURECONFIG;
      Noaudit succeeded.
      SQL> noaudit policy ORA_LOGON_FAILURES;
      Noaudit succeeded.

Update – 4-MAR-2016 – thanks to Marco Patzwahl, MuniQsoft:

  1. If parameter audit_trail=NONE and select VALUE from V$OPTION where PARAMETER=’Unified Auditing’ == FALSE then no Unified Audit Trail will be written
  2. If select VALUE from V$OPTION where PARAMETER=’Unified Auditing’ == TRUE then the parameter audit_trail has no meaning anymore

–Mike