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:
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:
where parameter = 'Unified Auditing';
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:
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
to_char(event_timestamp, 'mm/dd/yy hh24:mi') audit_time,
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
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:
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:
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:
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.
READ “Introduction to Auditing.”
Photography by Ricardo Gomez, Unsplash
Arup Nanda has been an Oracle DBA since 1993, handling all aspects of database administration, from performance tuning to security and disaster recovery. He was Oracle Magazine’s DBA of the Year in 2003 and received an Oracle Excellence Award for Technologist of the Year in 2012.