Oracle Auditing Part 3: Unified Auditing

This is the third, and last, article on the topic of Oracle auditing. It is relevant to Oracle 12c only. With Unified Auditing, Oracle simplified the task of auditing activities in a modern database environment, and rather than having to learn multiple methods, patterns, and techniques for both different tasks and different Oracle modules, all audit trails go to the same place and use the same definitions. There are however some downsides – some related to previous investments that you may have already done and some related to deprecated features – mainly the inability to use syslog auditing any more. This is more
serious than it sounds, because pretty much all SIEM integrations have been based on syslog auditing in the past.

The main benefit of Unified Auditing is that it consistently supports all types of auditing including Standard Auditing introduced in Part 1 and both Mandatory and Fine Grained Auditing introduced in Part 2 of this series. The second main benefit is that Unified Auditing supports all the modules consistently – in addition to the database modules such as RMAN, Data Pump, SQL*Loader, and security modules such as Database Vault and Oracle Label Security.

There are two Unified Auditing modes. Mixed Mode allows you to use both Unified Auditing but also all the auditing features that existed before Unified Auditing as described in Part 1 and Part 2 of this series. This is a default mode and allows you to “flow into” Unified Auditing without breaking any existing investment. The audit records go into the standard locations as well as into the SYS.UNIFIED_AUDIT_TRAIL location. The second mode, PURE mode, disables all the existing methods and only writes audit records to the Unified Auditing location. It is likely that while the current default is Mixed Mode, in future major versions Oracle will make PURE mode the default.

To see whether Unified Auditing is enabled in your database do:

select PARAMETER, VALUE from V$OPTION where PARAMETER = ‘Unified Auditing’;
The new fundamental building block of Unified Auditing is the audit policy. When using mixed-mode you create a new audit policy using CREATE AUDIT POLICY, enable it using the AUDIT command, and can view the content of existing policies by selecting from AUDIT_UNIFIED_POLICIES, which maps the policy to the audit options. There are also a few built-in audit policies included with the database such as ORA_SECURECONFIG and ORA_ACCOUNT_MGMT. To see which policies are enabled by default select from AUDIT_UNIFIED_ENABLED_POLICIES.

To exit mixed-mode and enable PURE mode you need to stop all services and relink the database with the uniaud_on option using (on *nix):

Cd $ORACLE_HOME/rdbms/lib
make –f ins_rdbms.mk uniaud_on ioracle

While Unified Auditing does have a new syntax, if you have already learned how to use Standard Auditing, the learning curve will be short. For example, to create a policy that will audit selects that are using the SELECT ANY privilege performed by the user SCOTT, do:

create audit policy aud_scott 
  PRIVILEGES select any table
  WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')=''SCOTT'''
  EVALUATE PER STATEMENT;

and then enable the policy using:

audit policy aud_scott;

To create rules based on activity performed use the ACTIONS modifier – e.g.:

create audit policy aud_scott 
  ACTIONS select, insert, update, delete
  WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')=''SCOTT'''
  EVALUATE PER STATEMENT;

Fine-grained auditing still uses the FGA packages, but the audit records will also be inserted into the Unified Audit trail.

Architectural Changes

When you use Unified Auditing the new schema AUDSYS is used for storage of all audit data, rather than keeping the data in the SYS schema (in mixed modes the previous locations in SYS are still populated in parallel).

Two new database roles are added for better separation of duties: AUDIT_ADMIN for policy configuration and audit administration and AUDIT_VIEWER for viewing and reading audit data. But perhaps the most important architectural change is that Unified Auditing is directly linked into the SGA and provides better performance and lower overhead when doing a lot of auditing.

Summary

Unified Auditing was introduced in Oracle 12c as a single unified platform for all types of auditing and is consistent for all modules that make up a modern database environment. It simplifies the task of auditing, but since it still is very robust and full-featured, it takes a while to get used to it. This is made even more complex with all the various Database as a Service offerings since the audit records “go a different way” (e.g., to CloudWatch in Amazon RDS). This complexity at two dimensions is what prompted us at Imperva to provide a comprehensive, yet simple database security solution to cover both on-premise and cloud deployments of Oracle and other databases. Talk to us to learn more.