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

How to enable extended data types in Oracle 12c?

Introduction

One of the coolest features that were introduced in Oracle 12c (in version 12.1.0.1) is the Extended Data Types which makes the maximum size limit of VARCHAR2, NVARCHAR2 and RAW data types to be 32767 bytes.

In earlier releases (i.e. prior Oracle 12c) the maximum size limit for VARCHAR2, NVARCHAR2 data types was 4000 bytes and for RAW data types was 2000 bytes.

How to enable extended data types in Oracle 12c?

In order to enable support for extended data types, you will have to perform the following steps:

1. SHUTDOWN IMMEDIATE;
2. STARTUP UPGRADE;
3. ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED; (instead of STANDARD)
4. @$ORACLE_HOME/rdbms/admin/utl32k.​sql (execute it as SYSDBA)
5. Restart the Oracle instance

Conclusion

Before Oracle 12c in order to support strings longer than 4000 bytes we mainly used LOB. If you decide to enable this feature you will need to take into account that internally Oracle is storing these extended types in out-of-line LOB segments but you can't manage these lob segments using DBMS_LOB as Oracle is managing them internally.

From my point of view I think this feature can be useful although its biggest disadvantage is that it requires downtime to enable it which can be problematic for many Oracle customers.

Another 12c new feature - Invisible Columns

Introduction

In my previous post I've demonstrated the In-Database archiving feature that was introduced in Oracle 12c (in version 12.1.0.1 to be more specific) and enables you to mark some rows as invisible to the application.
In this post, I'd like to demonstrate the invisible columns feature which allows you to mark a specific column or set of columns as invisible. You can either mark the columns as invisible as part of the CREATE TABLE command, or as part of the ALTER TABLE command.

If you will execute a SELECT * from the table, the invisible columns will not be displayed in the output of the select statement; however, if you will explicitly specify these columns as part of the SELECT statement they will be displayed in the output of the select statement.
If you will execute an INSERT statement and you don't specify the columns, the INSERT will work; however, you can insert value to the invisible columns if you will explicitly specify the invisible columns as part of the INSERT statement.

Demonstration

Let's see a quick demonstration of this feature. In this demonstration I will perform the following steps:

1. create a new table named "TEST" with 2 columns:
• ID - VISIBLE (default)
• NAME - INVISIBLE
2. insert a row to the table without specifying the column - the insert statement will fail because it expects only 1 value for the ID column 
3. insert a row to the tame - this time I explicitly specify the columns and and the insert statement works
4. execute SELECT * from the table and see that only the ID column is displayed
5. execute the select query again - but this time explicitly specify the columns - now both the ID and NAME columns are displayed
6. execute the DESC command on the table - the NAME column is not being displayed as part of the DESC command output
7. Changing the column to be visible using the ALTER TABLE command - this time the DESC command displays the NAME column

Invisible columns and constraints

Oracle enforce integrity constraints for columns that are configured to be invisible like any other column. To demonstrate this, I will make the "NAME" column invisible again and then I will create the NOT NULL constraint and I will try to insert a value to the ID column only. This attempt will fail with ORA-01400 because it's impossible to insert a NULL value to the NAME column:

Conclusions

The Invisible columns is a very simple and straight-forward feature which can be useful if you would like to add a new column to a table but at the first stage you don't want that the application will be affected by the new column. SELECT * and INSERT statements will continue to function normal (unless an integrity constraint on the invisible column was violated), and in the moment that you decide that you want to make the column visible, you can manage to do so in a single command (ALTER TABLE table_name MODIFY column_name VISIBLE).

12c New feature - Invisible Rows A.K.A In-Database Archiving

Introduction

In Oracle Database 12c (12.1.0.1 to be more specific) Oracle introduced a new feature named "In-Database Archiving" which allows to mark specific rows as invisible (archived) so they will not be visible. For example, if someone queries a table that is configured with this feature enabled then all the rows that are marked as archived will be invisible unless the session has enabled to see archived data. The rows that are archived can be compressed in order to reduce the storage of the database and also improve backup performance.

So how does it work?

In order to configure a table with the In-Database Archiving feature enable, you will need to use the ROW ARCHIVAL clause during the CREATE TABLE command, or if the table already exists then you can use the ROW ARCHIVAL clause using the ALTER TABLE command.

Once you enable this feature, Oracle will create additional column named ORA_ARCHIVE_STATE. By default, this column contains the value '0' for each row which means that the row is visible. If you will change to value to be '1' then the row will be invisible. If you will disable this feature using the ALTER TABLE ... NO ARCHIVAL command then Oracle will automatically drop
this column.

Demonstration

In this demonstration I will perform the following steps:

1. Create a new table named "test" and enable the ROW Archival feature for this table
2. Populate the table with 2 rows
3. Query the table including the ORA_ARCHIVE_STATE column to see that that the additional column contains only the default '0' values:
4. Mark "David" as invisible by changing the ORA_ARCHIVE_STATE  value to be '1'
5. Query again the table to verify that "David" is actually invisible
6. Allow the session to view the invisible data using the ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL command
7. Query again the table to verify that "David" is now visible
8. Prohibit the session from viewing the invisible data using the ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE command
9. Query again the table to verify that "David" is now invisible again
10. Disable the In-Database Archiving feature for the table using the ALTER TABLE ... NO ARCHIVAL command
11. Query the table to verify that the ORA_ARCHIVE_STATE column has been dropped

Useful Links:

• CREATE TABLE ... ROW ARCHIVAL clause (Oracle Documentation)
• ALTER SESSION ... ROW ARCHIVAL clause (Oracle Documentation)
• Using In-Database Archiving (Oracle Documentation)

Oracle Automatic Maintenance Tasks

Introduction

The Oracle automatic maintenance tasks were introduced in version 11g in order to automate tasks that Oracle DBAs used to do manually in the past (i.e. in earlier versions such as 9i, 10g).
This includes the following predefined automated maintenance tasks:

• Automatic Optimizer Statistics Collection - Collecting optimizer statistics for objects with stale or missing statistics
• Automatics Segment Advisor - Collecting storage-related information for segments in order to provide Segment Recommendations on those segments
• Automatic SQL Tuning Advisor - Collecting performance-related information for SQL Statements in order to provide SQL Tuning recommendations

In Oracle 12c the following maintenance task has been added:

• SQL Plan Management (SPM) Evolve Advisor - Collecting information about SQL execution plans in order to automatically evolve execution plans as part of the SQL Plan Management feature

Demonstration

You can query DBA_AUTOTASK_OERATION in order to see all the automatic maintenance tasks along with their status (whether it's enabled or disabled):

You may ask yourself when these jobs are actually running?
The answer is during the Maintenance Windows 

You can check the maintenance windows that are defined for each automatic task using DBA_AUTOTASK_WINDOW_CLIENTS

Following is an example:

The above are 7 predefined maintenance windows that are enabled by default when you install Oracle. If you'd like to see more information for each window you can query DBA_SCHEDULER_WINDOWS and it will display the exact time and duration for each window as well as the associated resource plan. You can modify the maintanence window settings using the SET_ATTRIBUTE procedure of the DBMS_SCHEDULER package. Read more here.

Please note that during the time window Oracle will create a scheduler job for the automatic task and once the job is completed Oracle will drop it, so don't be surprised that you can find the jobs in DBA_SCHEDULER_JOBS. You can find historical automated tasks information using DBA_AUTOTASK_JOB_HISTORY below is an example:

You can manually disable (or enable) all the maintenance tasks using the DBMS_AUTO_TASK_ADMIN package, for example:
EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

You can also disable or enable a particular maintenance task. Following is an example of how to disable the sql tuning advisor maintenance task
BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

If you want to disable to enable a particular maintenance task only for a particular maintenance window, you can use the "window_name" parameter. Following is an example of how to disable the sql tuning advisor maintenance task only for the SATURDAY_WINDOW:
BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => 'SATURDAY_WINDOW');
END;
/

Summary

The goal of this post was to shed light on the automatic maintenance tasks feature which is very useful but you need to aware of its behavior. In this post, I've covered the following items:

• Pre-defined automatic maintenance tasks in 11g & 12c
• Maintanence windows and how to determine their start time and duration
• Viewing historical automated tasks information (time, status, etc.)
• Disabling/enabling automated tasks