Unified Auditing

Maja Veselica

December 2015

In this article by Zoran Pavlovic and Maja Veselica, authors of the book Oracle Database 12c Security Cookbook, we will be introduced to a new feature in Oracle Database 12c, unified auditing, which brings forth a new auditing architecture.

In this article, we will cover the following tasks:

  • Enabling unified auditing mode
  • Configuring whether loss of audit data is acceptable
  • Which roles do you need to have to be able to create audit policies and to view audit data?
  • Auditing RMAN operations
  • Auditing Data Pump operations
  • Auditing Database Vault operations

(For more resources related to this topic, see here.)

Enabling unified auditing mode

In Oracle Database 12c, unified auditing is not enabled by default. The process of enabling it is simple and equivalent to enabling of other database options.

Getting ready

To complete this recipe, you'll need to shut down the database.

How to do it...

The process of enabling unified auditing is depicted in Figure 1:


Figure 1

  1. In our case, there is only one database instance. Connect to the instance as sysoper and shut it down. Also, stop the listener:
    $ sqlplus / as sysoper
    
    SQL> shutdown immediate
    
    SQL> exit

    $ lsnrctl stop
  2. Relink Oracle binaries with the uniaud_on option:
    $ cd $ORACLE_HOME/rdbms/lib
    $ make -f ins_rdbms.mk uniaud_on ioracle
  3. Start the listener and the database instance:
    $ lsnrctl start
    
    $ sqlplus / as sysoper
    
    SQL> startup

To verify that unified auditing is enabled, issue the following SQL statement:

SQL> SELECT PARAMETER, VALUE

  2  from v$option

  3  where PARAMETER = 'Unified Auditing';

You should see that value for unified auditing parameter is TRUE:

PARAMETER               VALUE

-----------------     --------

Unified Auditing        TRUE

How it works...

When database is upgraded to 12c, by default, it uses traditional way of auditing (everything like it was in previous versions). However, when you directly install a new database 12c, default auditing is set to mixed auditing mode. In both cases, the procedure to enable unified auditing mode is the same.

After you enable unified auditing mode, traditional auditing doesn't work anymore. Old audit instance parameters (AUDIT_TRAIL, AUDIT_FILE_DEST, AUDIT_SYSLOG_LEVEL, and AUDIT_SYS_OPERATIONS) are disregarded. Also, using syslog and writing audit records to OS are not supported. Predefined unified audit policies that are enabled by default:

  • ORA_SECURECONFIG (database versions: 12.1.0.1, 12.1.0.2)
  • ORA_LOGON_FAILURES (Oracle Database 12.1.0.2)

Predefined unified audit policies

A predefined unified audit policy is a named set of commonly used and recommended audit settings, which already exists in Oracle Database 12c. In Oracle Database 12.1.0.1, there are five predefined unified audit policies whereas there are eight predefined audit policies in Oracle Database 12.1.0.2. Table 1 lists predefined audit policies:

Predefined audit policy

Oracle Database 12.1.0.1

Oracle Database 12.1.0.2

ORA_RAS_POLICY_MGMT

Yes

Yes

ORA_DATABASE_PARAMETER

Yes

Yes

ORA_RAS_SESSION_MGMT

Yes

Yes

ORA_ACCOUNT_MGMT

Yes

Yes

ORA_SECURECONFIG

Yes

Yes

ORA_LOGON_FAILURES

No

Yes

ORA_CIS_RECOMMENDATIONS

No

Yes

ORA_DV_AUDPOL

No

Yes

Table 1: The list of predefined unified audit policies

Even though predefined audit policies have the same name in different versions of Oracle Database, it doesn't necessarily mean that they are always identical.

You may execute the following statement in both 12.1.0.1 and 12.1.0.2 database versions, as a user who has audit_admin or dba (short for database administrator) role:

SQL> select audit_option from audit_unified_policies

     where policy_name='ORA_SECURECONFIG'

     order by 1;

You will note that the ORA_SECURECONFIG-predefined unified audit policy is slightly different (for example, audit_options: LOGON, LOGOFF that exist in 12.1.0.1 are removed from the policy in 12.1.0.2 and LOGON is part of ORA_LOGON_FAILURES policy; also some audit options are added in ORA_SECURECONFIG in 12.1.0.2, such as ALTER PLUGGABLE DATABASE).

There's more...

In Oracle Database 12cR1 Standard Edition, when you enable unified auditing mode and query v$option view to verify that it's enabled, you see the following result:

PARAMETER              VALUE
-----------------    --------
Unified Auditing       FALSE

This bug is reported in My Oracle Support Community (Bug 17466854) and should be fixed in product version 12.2.

For customers who have Standard Edition (SE), we don't recommend using unified auditing mode on a production system before this bug is fixed. We haven't noted any other difference when using this feature in the SE environment, but we haven't tested it thoroughly.

Configuring whether loss of audit data is acceptable

In this recipe, you'll learn to set whether audit data is queued in memory or is immediately written to audit trail.

Getting ready

To complete this recipe, you'll need an existing user who has the audit_admin role (for example, jack).

How to do it...

  1. Connect to the database as user who has the audit_admin role (for example, jack):
    SQL> connect jack
  2. If you want audit records to be immediately written to unified audit trail, set it to immediate-write mode:
    SQL> EXEC 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);
  3. Check that the mode is set to immediate-write:
    SQL> select * from dba_audit_mgmt_config_params
    
    where parameter_name='AUDIT WRITE MODE';

You should see that value for the AUDIT WRITE MODE parameter is IMMEDIATE WRITE MODE:

 PARAMETER_NAME                 PARAMETER_VALUE                          AUDIT_TRAIL
  ---------------------         -------------------------                            ---------------------
AUDIT WRITE MODE               IMMEDIATE WRITE MODE         UNIFIED AUDIT TRAIL

If you want audit records to be queued in memory and at later time persisted, then set queued-write mode. Instead of step 2 execute:

SQL> EXEC 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_QUEUED_WRITE);

How it works...

Default value for write mode is queued-write mode. In this mode, audit data is stored in System Global Area (SGA) queues and later automatically persisted in read-only table in the AUDSYS schema in the SYSAUX tablespace. You can also manually flush content of memory queues to the disk:

SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

You'll achieve better performance by using queued-write mode, but in an event of instance crash, you may lose some audit records.

It is recommended that you use queued-write mode in case that possibility of some audit data loss is acceptable.

Which roles do you need to have to be able to create audit policies and to view audit data?

In this recipe, you're going to create two users (for example, jack and jill). Jack's job is to implement auditing requirements and to make sure that auditing is functioning properly. Jill is an auditor and her job is to analyze audit data.

Getting ready

To complete this recipe, you'll need an existing user who has dba role (for example, maja).

How to do it...

  1. Connect to the database as a user who has dba role (for example, maja):
    $ sqlplus maja
  2. Create user jack and grant him create session privilege and audit_admin role:
    SQL> create user jack identified by pQ3s7a4w2;
    SQL> grant create session, audit_admin to jack;
  3. Create user jill and grant her create session privilege and audit_viewer role:
    SQL> create user jill identified by t1m5_R2f3;
    SQL> grant create session, audit_viewer to jill;

How it works...

In Oracle Database 12c, there are two new roles: audit_admin and audit_viewer. They enable separation of duties in the auditing process. To configure auditing, you no longer need to have the dba role or connect as sysdba. From security perspective, that is a significant improvement.

In step 2, you granted audit_admin role to newly created user jack because that role enables him to create, alter, enable, disable, and drop audit policies, view audit data, and manage the unified audit trail. In step 3, you granted audit_viewer role to user jill because it enables her to view audit data. You may wonder why audit_admin role is designed in such a way that it enables a user to view audit data. One of the reasons could be that when you configure auditing (for example, create and enable audit policies), you have to be able to verify audit records that are generated in a way you expected they would.

There's more...

To test what can and can't be done as a user who has audit_viewer role, connect to the database as Jill and try to create unified audit policy jill_policy:

SQL> connect jill

SQL> create audit policy jill_policy

     actions delete on oe.orders;

 

actions delete on oe.orders

                     *

ERROR at line 2:

ORA-00942: table or view does not exist

Even if you grant object privileges on oe.orders table to Jill, she won't be able to create unified audit policy, because she doesn't have the audit_admin role or AUDIT SYSTEM privilege:

SQL> conn maja

SQL> grant select, delete on oe.orders to jill;

 

SQL> connect jill

 

SQL> create audit policy jill_policy

     actions delete on oe.orders;

actions delete on oe.orders

                     *

ERROR at line 2:

ORA-01031: insufficient privileges

Revoke select and delete on oe.orders table from Jill:

SQL> connect maja

SQL> revoke select, delete on oe.orders from jill;

 

Revoke succeeded.

Grant AUDIT SYSTEM privilege to jill and again try to create audit policy jill_policy:

SQL> grant audit system to jill;

SQL> connect jill

SQL> create audit policy jill_policy

     actions delete on oe.orders;

 

Audit policy created.

Drop unified audit policy jill_policy and revoke AUDIT SYSTEM privilege from Jill:

SQL> drop audit policy jill_policy;

 

Audit Policy dropped.

 

SQL> connect maja

SQL> revoke audit system from jill;

View audit data:

SQL> connect jill

SQL> select dbusername, action_name from unified_audit_trail

     where unified_audit_policies='ORA_SECURECONFIG';

Also, user who has the audit_viewer role can access information about defined and enabled unified audit policies.

Throughout this article, you'll use a user who has the audit_admin role (for example, jack). So, only test you'll do right now is to enable predefined audit policy ORA_ACCOUNT_MGMT and then to disable it:

SQL> connect jack

SQL> audit policy ora_account_mgmt;

 

Audit succeeded.

SQL> noaudit policy ora_account_mgmt;

 

Noaudit succeeded.

Auditing RMAN operations

In this recipe, you'll see that RMAN operations are audited by default.

Getting ready

In this recipe, we assume that database is in ARCHIVELOG mode. To complete this recipe, you'll need an existing user who has SYSBACKUP privilege (for example, tom) and an existing user who has the dba role (for example, maja).

How to do it...

  1. Connect to the target database as a user who has SYSBACKUP privilege (for example, tom):
    $ rman target '"tom@ora12cR1 AS SYSBACKUP"'
  2. Back up tablespace EXAMPLE and view information about backups, as follows:
    RMAN> backup tablespace EXAMPLE;
    RMAN> list backup;
    RMAN> exit
  3. Connect to the database as a user who has a dba role (for example, maja).
    $ sqlplus maja
  4. Find the location of data file for the example tablespace:
    SQL> select file_name from dba_data_files where tablespace_name='EXAMPLE';
    
    FILE_NAME
    ----------------------------------------------------------
    /u01/app/oracle/oradata/ORA12CR1/datafile/o1_mf_example_9z79vpcj_.dbf
  5. Remove the example tablespace datafile:
    SQL> !rm /u01/app/oracle/oradata/ORA12CR1/datafile/o1_mf_example_9z79vpcj_.dbf
  6. Put the example tablespace offline:
    SQL> alter tablespace example offline immediate;
    SQL> exit
  7. Restore the example tablespace datafile:
    $ rman target '"tom@ora12cR1 AS SYSBACKUP"'
    RMAN> restore tablespace EXAMPLE;
  8. Recover the example tablespace datafile:
    RMAN> recover tablespace EXAMPLE;
    RMAN> exit
  9. Put the tablespace back online:
    $ sqlplus maja
    SQL> alter tablespace EXAMPLE online;
  10. To verify that RMAN operations were successfully audited, execute the following statements:
    SQL> connect jack
    
    SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
    
    SQL> select dbusername, rman_operation
    
    from unified_audit_trail
    
    where rman_operation is not null;

How it works...

When mixed or unified auditing mode is enabled, RMAN operations are automatically audited. That means that you don't create audit policies, but you view and manage audit data in the same way as for other components.

In step 2, you performed backup of the example tablespace. Then, in step 5, you intentionally caused a problem by removing the datafile. Afterwards, you performed restore and recover RMAN operations. The whole point of the example is to execute several RMAN operations. In unified_audit_trail data dictionary view, there are several columns that contain data pertaining to the RMAN events. Their names start with RMAN, so it's easy to find them.

In step 10, you should get similar result to this one:

DBUSERNAME            RMAN_OPERATION
-----------------          -----------------------
TOM                      Backup
TOM                      List
TOM                      Restore
TOM                      Recover

Auditing Data Pump operations

You can audit Data Pump export, import, or both export and import operations by creating audit policies.

Getting ready

To complete this recipe, you'll need an existing user who has audit_admin role (for example, jack). Also, it is assumed that directory for export operations (for example, my_dir) is created and a user (for example, maja) who is going to perform Data Pump export has read and write privileges on the directory.

SQL> CREATE DIRECTORY my_dir AS '/u01/app/oracle/oradata/export';

SQL> grant read, write ON DIRECTORY my_dir to maja;

How to do it...

  1. Connect to the database as a user who has the audit_admin role (for example, jack) as follows:
    $ sqlplus jack
  2. Create audit policy to audit Data Pump export operations as follows:
    SQL> CREATE AUDIT POLICY DP_POLICY ACTIONS
    
    COMPONENT=datapump export;
  3. Enable the audit policy as follows:
    SQL> AUDIT POLICY DP_POLICY;
  4. Export the table hr.departments as follows:
    $ expdp maja@ora12cR1 dumpfile=test tables=hr.departments DIRECTORY=my_dir
  5. Verify that export operation was successfully audited as follows:
    SQL> connect jack
    
    SQL> select DP_TEXT_PARAMETERS1,DP_BOOLEAN_PARAMETERS1
    
    from unified_audit_trail
    
    where audit_type='Datapump' and dbusername='MAJA';

How it works...

In step 2, you created audit policy dp_policy, which will audit Data Pump export operations.

In step 3, you enabled the policy.

In step 5, you saw Data Pump specific columns in the unified audit trail (DP_TEXT_PARAMETERS1 and DP_BOOLEAN_PARAMETERS1).

Auditing Database Vault operations

In this recipe, you'll learn to audit Oracle Database Vault events.

Getting ready

To complete this recipe, you'll need to use Oracle Database 12c, which has Oracle Database Vault enabled and at least some of the components configured (for example, realm HR Realm, rule set Working Hours). Also, you'll need an existing user who has audit_admin role (for example, jack).

How to do it...

  1. Connect to the database as a user who has audit_admin role (for example, jack):
    $ connect jack
  2. Create audit policy dbv_policy:
    SQL> CREATE AUDIT POLICY dbv_policy
    
    ACTIONS COMPONENT = DV Rule Set Failure on "Working Hours", realm violation on "HR Realm";
  3.      Enable audit policy dbv_policy:
    SQL> audit policy dbv_policy;
  4. Execute several statements that will cause generation of audit records:
    SQL> select * from oe.orders;
    
    SQL> update hr.employees set salary=30000 where salary=24000;

How it works...

To create audit policy that captures Oracle Database Vault events, specify ACTIONS COMPONENT = DV <action> ON <object>. In step 2, you defined audit policy dbv_policy that encapsulates following rules: audit records should be generated when somebody tries to access protected objects during nonworking hours or when unauthorized person tries to access objects secured by HR Realm.

In the unified audit trail, Oracle Database Vault-specific audit data is stored in the columns whose name starts with DV_.

There's more...

When you are using Oracle Database Vault, you can also additionally secure your auditing infrastructure by creating a realm around AUDIT_ADMIN and AUDIT_VIEWER roles. This allows you to control who can grant those roles.

Summary

In this article, we were introduced to a new feature in Oracle Database 12c, unified auditing, and the various operations in it.

Resources for Article:


Further resources on this subject:


You've been reading an excerpt of:

Oracle Database 12c Security Cookbook

Explore Title