Review of Oracle 12c Unified Auditing

Introduction

Prior to Oracle 12c there were several tables in the Database that stored audit trails:

1. SYS.AUD$ (also accessible through DBA_AUDIT_TRAIL view) which is the main database audit trail 
2. SYS.FGA_LOG$ (also accessible through DBA_FGA_AUDIT_TRAIL view)  for the Fine-Grained auditing records
3. DVSYS.AUDIT_TRAIL$ for the Label security and DB Vault

In Oracle Database version 12c, Oracle introduced a new feature named "Unified Auditing" which basically consolidates all the audit trails into one single view - UNIFIED_AUDIT_TRAIL. There is no option to write audit records to the operating system.

This feature is also introducing a new set of predefined audit policies and you can also create your own audit policies - they could be either simple or more complex depends on what you need. 

For example, you can create audit policy that audits delete statements on specific tables (ORDERS and ORDER_LINE tables for example) by a specific user (user SALES for example) on a specific PDB (PDB named PROD for example).

You can choose whether you want to have one policy that will contain all the audit settings for your database or several audit policies. Personally, I believe that having less policies and even one single policy enabled for your database is better because it has lower overhead on your Oracle instance.

You can view all the audit policies that are configured in your database using AUDIT_UNIFIED_POLICIES dictionary view as you can see in the following example:

SQL> select distinct policy_name
     from audit_unified_policies;

POLICY_NAME
-----------------------------------
ORA_CIS_RECOMMENDATIONS
ORA_LOGON_FAILURES
ORA_RAS_POLICY_MGMT
ORA_DATABASE_PARAMETER
ORA_RAS_SESSION_MGMT
ORA_ACCOUNT_MGMT
ORA_DV_AUDPOL
ORA_SECURECONFIG

8 rows selected.

Each policy contains one or more auditing options. Each auditing option has its type. Let's view all the auditing option types that the "ORA_SECURECONFIG" policy contains:

SQL> select distinct audit_option_type
     from audit_unified_policies
     where policy_name ='ORA_SECURECONFIG';

AUDIT_OPTION_TYPE
------------------
SYSTEM PRIVILEGE
STANDARD ACTION
OBJECT ACTION

Now, let's view all the audit options that defined in the "ORA_SECURECONFIG" policy and their type is "SYSTEM PRIVILEGE".

SQL> select policy_name, audit_option
     from audit_unified_policies
     where policy_name = 'ORA_SECURECONFIG' AND audit_option_type = 'SYSTEM PRIVILEGE';

POLICY_NAME          AUDIT_OPTION
-------------------- ----------------------------------------
ORA_SECURECONFIG     LOGMINING
ORA_SECURECONFIG     TRANSLATE ANY SQL
ORA_SECURECONFIG     EXEMPT REDACTION POLICY
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE EXTERNAL JOB
ORA_SECURECONFIG     CREATE ANY JOB

POLICY_NAME          AUDIT_OPTION
-------------------- ----------------------------------------
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE
ORA_SECURECONFIG     EXEMPT ACCESS POLICY
ORA_SECURECONFIG     CREATE ANY LIBRARY
ORA_SECURECONFIG     GRANT ANY PRIVILEGE
ORA_SECURECONFIG     DROP ANY PROCEDURE
ORA_SECURECONFIG     ALTER ANY PROCEDURE
ORA_SECURECONFIG     CREATE ANY PROCEDURE
ORA_SECURECONFIG     ALTER DATABASE
ORA_SECURECONFIG     GRANT ANY ROLE
ORA_SECURECONFIG     DROP PUBLIC SYNONYM
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM

POLICY_NAME          AUDIT_OPTION
-------------------- ----------------------------------------
ORA_SECURECONFIG     DROP ANY TABLE
ORA_SECURECONFIG     ALTER ANY TABLE
ORA_SECURECONFIG     CREATE ANY TABLE
ORA_SECURECONFIG     DROP USER
ORA_SECURECONFIG     CREATE USER
ORA_SECURECONFIG     AUDIT SYSTEM
ORA_SECURECONFIG     ALTER SYSTEM

29 rows selected.

Is this feature enabled by default?

I have a new Oracle 12c instance so let's check if this feature is enabled using V$OPTION :

SQL> select value
  2  from V$OPTION
  3  where parameter = 'Unified Auditing';

VALUE
-------------------------------------------
FALSE

Does this mean that the feature is disabled?

Well, not Exactly. 

When you install a new Oracle Database 12c or upgrade to Oracle Database 12c then by default it's configured to work in a "Mixed Mode". Mixed Mode allows you to run the traditional auditing (like you used prior to Oracle 12c) and also benefit the new 12c audit policies. It even has the ORA_SECURECONFIG and ORA_LOGON_FAILURES audit policies enabled by default (so don't be surprised that you see some records in the new UNIFIED_AUDIT_TRAIL view). You can verify which audit policies are enabled in your database using AUDIT_UNIFIED_ENABLED_POLICIES dictionary view. See the following screenshot as an example:

SQL> select *
     FROM audit_unified_enabled_policies;

USER_NAME            POLICY_NAME          ENABLED_ SUCCESS    FAILURE
-------------------- -------------------- -------- ---------- ----------
ALL USERS            ORA_SECURECONFIG     BY       YES        YES
ALL USERS            ORA_LOGON_FAILURES   BY       NO         YES

As I've mentioned, in this configuration Oracle will support both the traditional auditing and the new 12c audit policies so you may find new records written into the pre-12c audit trail - DBA_AUDIT_TRAIL dictionary view and also into the new UNIFIED_AUDIT_TRAIL dictionary view.

Once you will decide that you want to enable the "Pure" unified auditing, Oracle will ignore the "old" AUDIT_TRAIL parameter and will also stop writing records to the old audit trails and will write trail records only to the new UNIFIED_AUDIT_TRAIL dictionary view.

What other benefits the Unified Auditing feature provides?

Performance

Prior to Oracle 12c, if you configures auditing per execution (per "Action"), for example, let's say you configured auditing for every INSERT command by users SALES; in this case, every insert statement by user SALES will insert a row to the SYS.AUD$ table as an autonomous transaction which will obviously generate redo, undo, and perform a commit at the end of the transaction. Until this autonomous transaction is completed, the session will have to wait. In Oracle 12c Unified Auditing, the audit records will be written into an SGA queue and periodically, a dedicated background process will write the records to the AUDSYS schema in the SYSAUX tablespace in order to ensure that the data is persistent.

Note:
The SGA queue for the unified auditing default size is 1 MB, but can be adjusted to a value of [1,30] via the UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter.

You may now ask yourself - does that mean that in a crash scenario soe audits records may be lost? The answer is yes; some audit records may be lost. There's always a trade-off between protection and performance. However, if you prefer to have 100% protection for the audit records in the cost of impacting your database performance you can change this default behavior and tell Oracle to write the audit trails records into the AUDSYS schema immediately (see the "Managing the Unified Audit Trail" section).

Security

Prior to Oracle 12c, if you chose to write the audit trail to the DB (audit_tail='db') then all the records will be written to SYS.AUD$ table, which means that the DBA can modify this table. In Oracle 12c Unified Auditing the DBA can't modify the audited information because the base X$ table behind the UNIFIED_AUDIT_TRAIL dictionary view is decrypted.

How to enable the Pure Unified Auditing

In order to enable to Pure Unified Auditing you will need to perform the following steps:

1. Shutdown your Oracle Databases and listeners that are associated to the Oracle Home
2. Relink the Oracle executable to support the Unified Auditing
3. Start your Oracle instances and listener

In order to relink the Oracle executable you will need to execute the "make" command from the $ORACLE_HOME/rdbms/lib directory:

make -f ins_rdbms.mk uniaud_on ioracle

Now, after the relink has been completed, let's query V$OPTION again to verify that the Unified Auditing feature is enabled:

SQL> select value
     from V$OPTION
     where parameter = 'Unified Auditing';

VALUE
-------------------------------------------
TRUE

Changing the default audit trail write mode 

As I've mentioned earlier in this article, this feature offers a great performance improvement by not writing the audit trails immediately, but rather writing into a dedicated SGA queue and periodically writing the audit trails persistenly.

You can change the default write mode (Queued-write mode) to be Immediate-write mode using the DBMS_AUDIT_MGMT package, as follows:

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

Summary

The Unified Auditing is a great security feature that was introduced in Oracle 12c. It offers a simple management for unified (consolidated) auditing that can be accessed via a single dictionary view - UNIFIED_AUDIT_TRAIL. It also provides major security and performance enhancements.

Useful Links

More information and details are available in the Oracle documentation:

• Oracle 12c Documentation - Introduction to Auditing
• Oracle 12c Documentation - Administering the Audit Trail