Unify Auditing

Combine disparate audit trails to a secure, high-performance single view.

September/October 2015

Database auditing involves systematically recording user activities in a designated location for future examination, generally for security-related objectives such as identifying malicious actions. Oracle Database has provided an auditing mechanism since Oracle6 Database and enhanced it over time with additional targeted auditing features such as fine-grained auditing (FGA), which was introduced in Oracle Database 10g.

Although it is immensely useful for performing forensic analysis on database activities, there are a couple of common issues with traditional auditing that cause many DBAs to skip it altogether:

1. There are various types of auditing—audit trails written to different locations. Traditional Oracle Database auditing is written to AUD$, FGA is written to an FGA_LOG$ table, SYS log auditing is written to the Oracle Home location, and so on. The dispersed nature of this important database activity information makes it very difficult to correlate for meaningful analysis. For example, FGA may disclose SELECT statements, but much of the SELECT activity may come from a SYS user whose activities are audited in operating-system–based files. You can’t effectively combine the sources unless you load all the files to the database. Additionally, the formats of these audit trails can vary widely, making comparative analysis even more difficult.
2. Audit records are written immediately at the time of the activity in the database, so they can negatively affect the performance of that activity. For example, if you audit INSERT, all INSERT statements will kick off an additional statement to write to the audit trail, which can cause latency and slow the INSERT process.

These two limitations can now be overcome. Unified auditing, introduced in Oracle Database 12c, not only fixes these problems but also provides enhancements not found in traditional auditing. In this article, you will learn how to easily set up and effectively use this handy new feature.

To set up unified auditing, first check whether it is enabled in your database by issuing the following query:

select value
from v$option
where parameter = 'Unified Auditing';
VALUE
—————
TRUE

If the value returned is FALSE, you will have to enable unified auditing. Because it is an integral part of the database software, you’ll need to relink the option to make a new version of the Oracle Database executable.

To do this, first shut down the database and the listener. Next, relink the executable with the UNIAUD_ON parameter:

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

This creates a new version of the Oracle Database executable with unified auditing enabled. Now you can bring the database and the listener back up. (Query the V$OPTION view again, and you should get a TRUE value.)

Next, designate the users who will be allowed to set up and (optionally) view the audit. The SYS user has these privileges by default, but you should not employ the SYS user for unified auditing activities—dedicated audit users should perform these tasks. Oracle Database provides two roles—AUDIT_ADMIN and AUDIT_VIEWER—to manage and view the audit settings, respectively. The following SQL statements grant these roles to the AUDITMAN and AUDITVIEWER users, respectively:

grant audit_admin to auditman;
grant audit_viewer to auditviewer;

After executing these statements, the AUDITMAN user can manage audit settings as well as the audit trails, but the AUDITVIEWER user can only see the audit trails, not modify or manage them. This allows for a separation of duties where a single privileged user (usually someone on the security team) sets up what needs to be audited, but multiple reviewers can analyze these trails without the risk of contaminating them.

An audit policy specifies the type of activity (SELECT, DELETE, and so on) to be audited and the object on which the activity is audited. For example, if you want to audit all the SELECT activities on the EMP table in the Oracle-supplied SCOTT sample schema, issue the following SQL statement as the AUDITMAN user to define the SEL_EMP audit policy:

create audit policy sel_emp actions select on scott.emp;

However, this will not start the audit unless you enable the policy with the AUDIT POLICY command:

audit policy sel_emp;

With this separation of policy and process, you can create and save as many policies as you want but enable only those that interest you. If you want to stop auditing, use the noaudit policy <policyName> command. This will stop the auditing, but the original policy will be left intact for later use. With traditional Oracle Database auditing, you can use the noaudit command to stop auditing, but that command also removes all information about what was being audited. Unified auditing separates the setup and enablement of the audit, so you can stop and restart auditing without redefining a policy.

With the SEL_EMP audit policy and auditing of SELECT against SCOTT.EMP in place, let’s perform a query on the EMP table by two different users: SH and SCOTT (both are created as a part of the sample schema installation). Issue this statement:

select * from scott.emp;

This SELECT statement will cause unified audit records to be generated. You can look at these audit records from the UNIFIED_AUDIT_TRAIL view by using the query shown in Listing 1. The view includes many columns, but I will list only a few here due to space constraints. Let’s focus on these columns:

Code Listing 1: Checking the unified audit trail

select
        to_char(event_timestamp, 'mm/dd/yy hh24:mi') audit_time,
        dbusername,
        userhost,
        action_name,
        return_code,
        SCN,
        object_schema,
        object_name
from unified_audit_trail
where unified_audit_policies='SEL_EMP';
AUDIT_TIME      DBUSERNAME USERHOST   Action   RC       SCN Schema   Object
--------------- ---------- ---------- ------- --- --------- -------- ------
06/05/15 08:05  SCOTT      prolap1    SELECT    0  26066039 SCOTT    EMP
06/04/15 15:13  SH         prolap2    SELECT    0  26011905 SCOTT    EMP

• UNIFIED_AUDIT_POLICIES. The audit policy or policies that triggered this audit record (you can have multiple policies triggering the same event)
• EVENT_TIMESTAMP. The time of the activity
• DBUSERNAME. The user who performed this activity
• USERHOST. The server or machine where the user logged in (not the database server)
• ACTION-NAME. The action performed (for example, SELECT)
• RETURN_CODE. The return code of the action (value is 0 if successful; otherwise, it’s the Oracle Database error code)
• SQL_TEXT. The user’s SQL query
• SCN. The system change number at the time of the change (with SCN, you can re-execute the SQL by using a flashback query to see the value the user originally received)
• OBJECT_SCHEMA and OBJECT_NAME. The owner and name of the object on which the activity was performed, respectively

Another important property of the unified audit trail is that, by default, it is written to an area in memory called the audit queue. This makes unified auditing much faster than traditional auditing.

The UNIFIED_AUDIT_TRAIL view is based on tables in the AUDSYS schema, which is written to periodically from the in-memory audit queue. Thus, when you query the UNIFIED_AUDIT_TRAIL view, you may not see the audit trail immediately after the activity ends. To see the audit trail results immediately, you have to flush the audit trail from the audit queue to the database, using the following SQL statement:

execute dbms_audit_mgmt.flush_unified_audit_trail

After flushing the audit trail, you can select from the UNIFIED_AUDIT_TRAIL view to get up-to-date results.

Note that the AUDSYS schema does not allow direct login. If you try to connect to the AUDSYS schema, you will get an error message:

ORA-46370: cannot connect as AUDSYS user 

This restricted access makes unified audit data much more secure than traditional audit data.

Unified auditing can track more than SQL operations. With unified auditing, you can also audit many other activities, including operations of the Oracle Recovery Manager (Oracle RMAN) feature of Oracle Database, Oracle Data Pump sessions, Oracle Database Vault commands, direct path loads in the SQL*Loader feature of Oracle Database, Oracle Label Security, and the Real Application Security feature of Oracle Database—all in the same unified audit trail. Let’s look at a couple of examples.

Unified auditing of Oracle RMAN operations is enabled automatically. Oracle RMAN operations are visible in the UNIFIED_AUDIT_TRAIL view, where the AUDIT_TYPE column value is RMAN_AUDIT. Unified auditing populates some Oracle RMAN–specific columns, and Listing 2 shows the audit trail output from the UNIFIED_AUDIT_TRAIL view for these Oracle RMAN activities:

• RMAN_SESSION_RECID and RMAN_SESSION_STAMP. Columns that uniquely identify the Oracle RMAN session
• RMAN_OPERATION. The operation issued to Oracle RMAN (in the Listing 2 output, the user issued the List and Backup commands)
• RMAN_OBJECT_TYPE. The type of object these commands were issued against (in the Listing 2 output, the user issued one List command for archived logs and one for the database)
• RMAN_DEVICE_TYPE. The type of device (disk in the Listing 2 output)

Now let’s look at a unified auditing activity that is not automatically enabled. To audit Oracle Data Pump operations, you first need to create a policy:

create audit policy dp_audit actions component=datapump all;

Next, enable auditing by issuing

audit policy dp_audit;

Now, whenever an Oracle Data Pump operation is executed, it will be written to the unified audit trail. These audit records have the value DATAPUMP in the AUDIT_TYPE column in the UNIFIED_AUDIT_TRAIL view. In this view, the audit also populates two Oracle Data Pump columns—DP_TEXT_PARAMETERS1 and DP_BOOLEAN_PARAMETERS1—which show the text and Boolean parameters used in those activities, respectively. Listing 3 shows the UNIFIED_AUDIT_TRAIL view of Oracle Data Pump activities in the unified audit trail.

Oracle RMAN and Oracle Data Pump activities are just two examples of the many different operations that can be audited with unified auditing. Oracle Database 12c includes a host of predefined audit policies; for example, there is a predefined audit policy for auditing the CREATE DATABASE LINK action. The policy is not enabled at startup, so if you want to enable it, issue the ORA_SECURECONFIG SQL audit policy. You can see all the predefined policies in Oracle Database 12c in the AUDIT_UNIFIED_ENABLED_POLICIES view.

You can purge the unified audit trail by using the CLEAN_AUDIT_TRAIL procedure in the DBMS_AUDIT_MGMT supplied package. In case you want to purge the records at periodic intervals, the CREATE_PURGE_JOB procedure in the same package enables you to set up an automated purge job.

Unified auditing delivers two big advantages over traditional database auditing:

1. By consolidating all types of audit trails into a single format, it allows easier correlation between multiple action sources.
2. By writing audit trails to memory instead of tables, it removes the I/O latency incurred by audited activities.

But that’s not all. Because unified audit trails are moved from the SYS schema to a schema that does not allow direct logins, the audit records are now more secure. All this, together with coverage of additional actions such as Oracle RMAN and Oracle Data Pump operations, makes unified auditing an easy-to-employ and indispensable tool for database forensics and security.

Next Steps READ “Introduction to Auditing.” LEARN how to migrate to unified auditing.