Monday, November 23, 2015

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:

3 comments:

  1. Can we actually create a test case when some audit records are getting lost in case of database crash?
    How can we prove this scenario?
    Thank you,
    vr

    ReplyDelete
    Replies
    1. Hello,

      I’ve never tested a scenario of losing audit records due to an instance crash, but the following is a quote from Oracle official documentation:

      “This design greatly improves the performance of the audit trail processes and the database as a whole. In the event of an instance crash or during SHUTDOWN ABORT operations, there is a chance that some audit records may be lost. If this is a concern, then you can configure the audit trail to immediately write audit records to the AUDSYS schema audit table. The following modes are available:
      • Immediate-write mode. This setting writes all audit records to the audit trail immediately. However, be aware that database performance may be affected.
      • Queued-write mode. This setting, which is the default write mode, queues the audit records in memory to be written periodically to the AUDSYS schema audit table. To set the size of the SGA, set the UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter. The default size is 1 MB, and you can enter a range of 1 through 30.“

      If you would like to make sure that you will never lose any audit record, you can simply switch to “Immediate-write mode” by executing the following code:

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

      Delete
  2. Prudent Chartered Accountants is a leading provider of accounting, auditing and management consultancy in the Dubai, UAE. We provide accountancy and auditing services through Middle East. Our professional and qualified team has a wealth of knowledge and experience in areas such as taxation, corporate finance, litigation support, audit assurance and accounting.

    ReplyDelete