Auditing Enhancements (Audit Policies and Unified Audit Trail) in Oracle Database 12c Release 1 (12.1)

Creating Audit Policies

Just like standard auditing that came before it, unified auditing can be used to create extremely complex auditing rules. The documentation for managing audit policies is very good, so rather than trying to duplicate that, I will just show some simple examples to give a flavour of the functionality.

An audit policy is made up of several distinct clauses, some of which are optional.

CREATE AUDIT POLICY policy_name
    { {privilege_audit_clause [action_audit_clause ] [role_audit_clause ]}
        | { action_audit_clause  [role_audit_clause ] } 
        | { role_audit_clause }
     }        
    [WHEN audit_condition EVALUATE PER {STATEMENT|SESSION|INSTANCE}] 
    [CONTAINER = {CURRENT | ALL}];

Examples of the usages are given in the sections below, but here is a quick summary of them.

• privilege_audit_clause : Used to specify a list of system privileges to be audited.
• action_audit_clause : Defines the actions that need to be audited. These can be standard_actions, like DELETE, or object-specific, like DELETE ON schema.table. They can also be component_actions that target specific features like data pump or SQL*Loader.
• role_audit_clause : Specifies a list of roles. All system privileges granted via those roles are audited.
• WHEN ... EVALUATE PER : Allows you to define an audit_condition to determine when the auditing should take place. The condition can be evaluated for eachSTATEMENT, SESSION or INSTANCE, depending on the level of granularity the condition requires.
• CONTAINER : Determines if an audit policy is specific to an individual PDB (CURRENT) or common to all PDBs (ALL).

This might sound a little confusing, but if you've ever used database auditing in previous releases, it will quickly look quite familiar. The main thing to remember is rather than issuing the AUDIT/NOAUDIT commands directly, you create an audit policy containing the relevant pieces, then enable and disable it using the AUDIT/NOAUDIT commands.

Some of the following examples require these three test users.

CONN sys@pdb1 AS SYSDBA

CREATE USER test IDENTIFIED BY test QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO test;

CREATE USER test2 IDENTIFIED BY test2 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO test2;

CREATE USER test3 IDENTIFIED BY test3 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO test3;

 In some cases the contents of the unified audit trail has been purged between tests to keep the output simple and specific to the functionality being tested.

Privilege Auditing

As the name suggests, privilege auditing allows you to audit the use of system privileges. The SYSTEM_PRIVILEGE_MAP view identifies the system privileges that can be audited.

SELECT name
FROM   system_privilege_map
ORDER BY name;

NAME
----------------------------------------
ADMINISTER ANY SQL TUNING SET
ADMINISTER DATABASE TRIGGER
.
.
.
UPDATE ANY TABLE
USE ANY SQL TRANSLATION PROFILE

237 rows selected.

SQL>

If we want to audit the creation of tables and sequences by the TEST user, we might do something like the following.

CONN sys@pdb1 AS SYSDBA

CREATE AUDIT POLICY test_audit_policy
  PRIVILEGES CREATE TABLE, CREATE SEQUENCE
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''TEST'''
  EVALUATE PER SESSION
  CONTAINER = CURRENT;

AUDIT POLICY test_audit_policy;

Display the configuration of the policy.

SET LINESIZE 200
COLUMN audit_option FORMAT A15
COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50

SELECT audit_option,
       condition_eval_opt,
       audit_condition
FROM   audit_unified_policies
WHERE  policy_name = 'TEST_AUDIT_POLICY';

AUDIT_OPTION    CONDITION_ AUDIT_CONDITION
--------------- ---------- --------------------------------------------------
CREATE SEQUENCE SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST'
CREATE TABLE    SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST'

SQL>

Connect to the TEST user and create some objects.

CONN test/test@pdb1

CREATE TABLE tab1 (id NUMBER);
CREATE SEQUENCE tab1_seq;

Check the audit trail. If you are in delayed-write mode, you may need to flush the audit trail before you can see the audit records.

CONN sys@pdb1 AS SYSDBA

-- You might need to flush the audit information before it is visible.
-- EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

COLUMN event_timestamp FORMAT A30
COLUMN dbusername FORMAT A10
COLUMN action_name FORMAT A20
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A20

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
FROM   unified_audit_trail
WHERE  dbusername = 'TEST'
ORDER BY event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- --------------------
27-JUN-2015 10:25:37.359596    TEST       CREATE TABLE         TEST       TAB1
27-JUN-2015 10:25:37.363989    TEST       CREATE SEQUENCE      TEST       TAB1_SEQ

SQL>

Disable the policy and drop it.

NOAUDIT POLICY test_audit_policy;

DROP AUDIT POLICY test_audit_policy;

Action Auditing

Actions against all objects, specific objects or even those actions performed by specific utilities can be audited quite easily in Oracle 12c.

Create some schema objects to audit and grant access to them to the second user.

CONN test/test@pdb1

DROP TABLE tab1 PURGE;
DROP SEQUENCE tab1_seq;
DROP TABLE tab2 PURGE;
DROP SEQUENCE tab2_seq;

CREATE TABLE tab1 (
  id NUMBER,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
);

CREATE SEQUENCE tab1_seq;

CREATE TABLE tab2 (
  id NUMBER,
  CONSTRAINT tab2_pk PRIMARY KEY (id)
);

CREATE SEQUENCE tab2_seq;

GRANT SELECT, INSERT, UPDATE, DELETE ON tab1 TO test2;
GRANT SELECT ON tab1_seq TO test2;
GRANT SELECT, INSERT, UPDATE, DELETE ON tab2 TO test2;
GRANT SELECT ON tab2_seq TO test2;

Create and enable an audit policy that audits some actions against those objects, when performed by the TEST2 user.

CONN sys@pdb1 AS SYSDBA

CREATE AUDIT POLICY test_audit_policy
  ACTIONS DELETE ON test.tab1,
          INSERT ON test.tab1,
          UPDATE ON test.tab1,
          SELECT ON test.tab1_seq,
          ALL ON test.tab2,
          SELECT ON test.tab2_seq
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''TEST2'''
  EVALUATE PER SESSION
  CONTAINER = CURRENT;

AUDIT POLICY test_audit_policy;

Display the configuration of the policy.

SET LINESIZE 200
COLUMN object_schema FORMAT A15
COLUMN object_name FORMAT A15
COLUMN object_type FORMAT A12
COLUMN audit_option FORMAT A15
COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50

SELECT object_schema,
       object_name,
       object_type,
       audit_option,
       condition_eval_opt,
       audit_condition
FROM   audit_unified_policies
WHERE  policy_name = 'TEST_AUDIT_POLICY';


OBJECT_SCHEMA   OBJECT_NAME     OBJECT_TYPE  AUDIT_OPTION    CONDITION_ AUDIT_CONDITION
--------------- --------------- ------------ --------------- ---------- --------------------------------------------------
TEST            TAB1            TABLE        DELETE          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1            TABLE        INSERT          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1            TABLE        UPDATE          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1_SEQ        SEQUENCE     SELECT          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB2            TABLE        ALL             SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB2_SEQ        SEQUENCE     SELECT          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'

SQL>

Perform some actions that won't match the policy condition.

CONN test/test@pdb1

INSERT INTO tab1 (id) VALUES (tab1_seq.NEXTVAL);
INSERT INTO tab2 (id) VALUES (tab2_seq.NEXTVAL);
COMMIT;

Perform some actions that will match the policy condition.

CONN test2/test2@pdb1

UPDATE test.tab1 SET id = test.tab1_seq.NEXTVAL;
UPDATE test.tab2 SET id = test.tab2_seq.NEXTVAL;
DELETE FROM test.tab1;
DELETE FROM test.tab2;
COMMIT;

Check the audit trail. If you are in delayed-write mode, you may need to flush the audit trail before you can see the audit records.

CONN sys@pdb1 AS SYSDBA

-- You might need to flush the audit information before it is visible.
-- EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

COLUMN event_timestamp FORMAT A30
COLUMN dbusername FORMAT A10
COLUMN action_name FORMAT A20
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A20

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
FROM   unified_audit_trail
WHERE  dbusername LIKE 'TEST%'
ORDER BY event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- --------------------
27-JUN-2015 10:41:40.070965    TEST2      SELECT               TEST       TAB1_SEQ
27-JUN-2015 10:41:40.071033    TEST2      UPDATE               TEST       TAB1
27-JUN-2015 10:41:40.073326    TEST2      SELECT               TEST       TAB2_SEQ
27-JUN-2015 10:41:40.073347    TEST2      UPDATE               TEST       TAB2
27-JUN-2015 10:41:40.074657    TEST2      DELETE               TEST       TAB1
27-JUN-2015 10:41:40.076132    TEST2      DELETE               TEST       TAB2

SQL>

Amend the audit policy and check the changes have been reflected in the configuration.

ALTER AUDIT POLICY test_audit_policy
  DROP ACTIONS ALL ON test.tab2,
               SELECT ON test.tab2_seq;

SET LINESIZE 200
COLUMN object_schema FORMAT A15
COLUMN object_name FORMAT A15
COLUMN object_type FORMAT A12
COLUMN audit_option FORMAT A15
COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50

SELECT object_schema,
       object_name,
       object_type,
       audit_option,
       condition_eval_opt,
       audit_condition
FROM   audit_unified_policies
WHERE  policy_name = 'TEST_AUDIT_POLICY';

OBJECT_SCHEMA   OBJECT_NAME     OBJECT_TYPE  AUDIT_OPTION    CONDITION_ AUDIT_CONDITION
--------------- --------------- ------------ --------------- ---------- --------------------------------------------------
TEST            TAB1            TABLE        DELETE          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1            TABLE        INSERT          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1            TABLE        UPDATE          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1_SEQ        SEQUENCE     SELECT          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'

SQL>

Disable and drop the audit policy, then check the changes have been reflected in the configuration.

NOAUDIT POLICY test_audit_policy;

DROP AUDIT POLICY test_audit_policy;

SET LINESIZE 200
COLUMN object_schema FORMAT A15
COLUMN object_name FORMAT A15
COLUMN object_type FORMAT A12
COLUMN audit_option FORMAT A15
COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50

SELECT object_schema,
       object_name,
       object_type,
       audit_option,
       condition_eval_opt,
       audit_condition
FROM   audit_unified_policies
WHERE  policy_name = 'TEST_AUDIT_POLICY';

no rows selected

SQL>

In the above examples the audit policies were object specific. We could have omitted the ON object-name part of the action to make it apply to all objects. If we wanted to audit all DML and queries issued by the TEST2 user, we might create a policy like the following.

CREATE AUDIT POLICY test_audit_policy
  ACTIONS DELETE, INSERT, UPDATE, SELECT
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''TEST2'''
  EVALUATE PER SESSION
  CONTAINER = CURRENT;

--DROP AUDIT POLICY test_audit_policy;

Component Action Auditing

Rather than auditing actions on specific objects, you can instead audit actions relevant to specific functionality or utilities, such as Oracle Label Security (OLS), Real Application Security, Database Vault, Data Pump or SQL*Loader. There are two examples of component_action auditing linked below.

• Auditing Data Pump Operations
• Auditing SQL*Loader Direct Path Loads

Role Auditing

It is possible to audit all system privileges granted via a built-in or custom role using an audit policy.

Create a new role, then grant privileges on the TEST objects via the new role.

CONN sys@pdb1 AS SYSDBA

CREATE ROLE create_table_role;
GRANT CREATE TABLE TO create_table_role;

GRANT create_table_role TO test3;

Create an audit policy based on all the system privileges associated with the role. Enable the auditing policy and check the configuration.

CREATE AUDIT POLICY create_table_role_policy
  ROLES create_table_role
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''TEST3'''
  EVALUATE PER SESSION
  CONTAINER = CURRENT;

AUDIT POLICY create_table_role_policy;

SET LINESIZE 200
COLUMN audit_option FORMAT A20
COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50

SELECT audit_option,
       audit_option_type,
       condition_eval_opt,
       audit_condition
FROM   audit_unified_policies
WHERE  policy_name = 'CREATE_TABLE_ROLE_POLICY';

AUDIT_OPTION         AUDIT_OPTION_TYPE  CONDITION_ AUDIT_CONDITION
-------------------- ------------------ ---------- --------------------------------------------------
CREATE_TABLE_ROLE    ROLE PRIVILEGE     SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST3'

SQL>

Perform some actions that will match the policy condition.

CONN test3/test3@pdb1

CREATE TABLE tab1 (id NUMBER);

Check the audit trail. If you are in delayed-write mode, you may need to flush the audit trail before you can see the audit records.

CONN sys@pdb1 AS SYSDBA

-- You might need to flush the audit information before it is visible.
-- EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

COLUMN event_timestamp FORMAT A30
COLUMN dbusername FORMAT A10
COLUMN action_name FORMAT A20
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A20

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
FROM   unified_audit_trail
WHERE  dbusername = 'TEST3'
ORDER BY event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- --------------------
27-JUN-2015 10:48:40.744492    TEST3      CREATE TABLE         TEST3      TAB1

SQL>

Disable and drop the policy.

NOAUDIT POLICY create_table_role_policy;

DROP AUDIT POLICY create_table_role_policy;

Unified Audit Trail Administration

Administration of the unified audit trail can seem a little complicated at first, but there are a few things to keep in mind.

• Unified auditing works by default, so you don't need to do anything to get started.
• The default settings are OK. You will probably only have to focus on your specific audit policies.
• Setting up an archival and purging process will need some thought, but you will probably only do this once in the lifetime of your database, so don't get scared off by this aspect of auditing.

With that said, the remaining parts of this article will explain how to perform some of the basic administration tasks.

 For brevity, I will avoid repetition of functionality explained in previous articles, but links will be provided.

Available By Default

The unified audit trail and audit policy functionality is available by default in all editions, but it can run in two modes. By default it runs in mixed mode, which means you can combine traditional auditing with unified auditing. The following query shows that "pure" unified auditing is not enabled, which means we are running in mixed mode.

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE

SQL>

All the functionality of unified auditing is still available, but you may still need to consider the AUDIT_TRAIL parameter, as it still controls how the traditional auditing is performed.

Enable/Disable Pure Unified Auditing

Running in mixed mode is perfectly acceptable, but you can choose to switch to pure unified auditing, so the traditional auditing functionality is no longer available. This is done by relinking the Oracle binaries as follows.

sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
EXIT
EOF

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

sqlplus / as sysdba <<EOF
STARTUP;
EXIT
EOF

After restarting, the banner will include the "Unified Auditing" option and we can see the unified auditing option is enabled.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 1 11:35:52 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

SQL>

Switching back to mixed mode involves a relink again.

sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
EXIT
EOF

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

sqlplus / as sysdba <<EOF
STARTUP;
EXIT
EOF

After restarting, the banner no longer includes the "Unified Auditing" option and we can see the unified auditing option is disabled.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 1 11:35:52 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE

SQL>

For Windows, the documentation suggests it is just a case of turning the services off and moving the relevant DLL. I've not tried this, so I can't guarantee it works.

Rem Enable
%ORACLE_HOME%/bin/orauniaud12.dll.dbl file to %ORACLE_HOME%/bin/orauniaud12.dll

Rem Disable
%ORACLE_HOME%/bin/orauniaud12.dll file to %ORACLE_HOME%/bin/orauniaud12.dll.dbl

Audit Trail Security

Maintenance of the audit trail and audit policies is limited to those users granted the AUDIT_ADMIN role.

The AUDIT_VIEWER role can be granted to users who need to view the audit information, but not manage the audit trail or audit policies.

Under unified auditing, users are no longer able to create auditing policies against their own objects. For backwards compatibility, this is still possible for traditional auditing. This is possibly a reason to move away from mixed-mode auditing.

Write Mode

For performance reasons unified auditing uses a queued-write mechanism, so audited actions are written to the SGA and periodically pushed out to disk. This can result in loss of audit records during an instance crash, since the records may not be pushed out to disk before the instance fails. If this represents a problem, the write mode can be altered to immediate-write, so the audit actions are pushed directly to disk, but this will result in auditing having a greater overhead. The following code shows how to switch between these two modes. In a multitenant environment, these setting are container-specific.

-- Switch to immediate-write.
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.audit_trail_unified,
    audit_trail_property       => DBMS_AUDIT_MGMT.audit_trail_write_mode, 
    audit_trail_property_value => DBMS_AUDIT_MGMT.audit_trail_immediate_write
  );
END;
/

-- Switched to queued-write.
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.audit_trail_unified,
    audit_trail_property       => DBMS_AUDIT_MGMT.audit_trail_write_mode, 
    audit_trail_property_value => DBMS_AUDIT_MGMT.audit_trail_queued_write
  );
END;
/

The size of the queue in the SGA is controlled by the UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter, which can be set to values between 1-30MB, with the default being 1MB. Each RAC instance can have a different value.

As a consequence of running in queued-write mode, in some cases, especially while testing or before maintenance, it may be necessary to flush audit records to the audit trail. This can be done using the FLUSH_UNIFIED_AUDIT_TRAIL procedure. It accepts the FLUSH_TYPE and CONTAINER parameters, which allow the following variations.

-- Flush records to audit trail for the current container in the current instance.
EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail(flush_type => DBMS_AUDIT_MGMT.flush_current_instance);

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail(container => DBMS_AUDIT_MGMT.container_current);

BEGIN
  DBMS_AUDIT_MGMT.flush_unified_audit_trail(
    flush_type => DBMS_AUDIT_MGMT.flush_current_instance,
    container  => DBMS_AUDIT_MGMT.container_current);
END;
/

-- Flush records to audit trail for all containers in the current instance.
-- Must be run from root container.
EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail(container => DBMS_AUDIT_MGMT.container_all);

BEGIN
  DBMS_AUDIT_MGMT.flush_unified_audit_trail(
    flush_type => DBMS_AUDIT_MGMT.flush_current_instance,
    container  => DBMS_AUDIT_MGMT.container_all);
END;
/

-- Flush records to audit trail for the all RAC instances. Current Container.
EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail(flush_type => DBMS_AUDIT_MGMT.flush_all_instances);

BEGIN
  DBMS_AUDIT_MGMT.flush_unified_audit_trail(
    flush_type => DBMS_AUDIT_MGMT.flush_all_instances,
    container  => DBMS_AUDIT_MGMT.container_current);
END;
/

-- Flush records to audit trail for the all RAC instances. All Containers.
-- Must be run from root container.
BEGIN
  DBMS_AUDIT_MGMT.flush_unified_audit_trail(
    flush_type => DBMS_AUDIT_MGMT.flush_all_instances,
    container  => DBMS_AUDIT_MGMT.container_all);
END;
/

Purging the Unified Audit Trail

Oracle 11g Release 2 introduced the DBMS_AUDIT_MGMT package to manage the audit trail. Oracle 12c uses the same functionality to archive and purge the unified audit trail, described here, with the addition of constants to identify the unified audit trail. For mixed mode auditing environments, remember to manage both the conventional audit trails as well as the unified audit trail.

The following example shows how to set the last archived time for the unified audit trail and manually purge audit records older than that time. Remember to archive records before purging them if you need to store them long term.

-- Set the last archive timestamp.
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type     => DBMS_AUDIT_MGMT.audit_trail_unified,
    last_archive_time    => SYSTIMESTAMP-5,
    --rac_instance_number  =>  1,
    container            => DBMS_AUDIT_MGMT.container_current
  );
END;
/

-- Check the new setting.
COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

SELECT audit_trail,
       last_archive_ts
FROM   dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          LAST_ARCHIVE_TS
-------------------- ----------------------------------------
UNIFIED AUDIT TRAIL  10-JUN-15 13.48.14.000000 +00:00

SQL>

-- Manually purge the audit trail to the last archive timestamp.
SELECT COUNT(*) FROM unified_audit_trail;

  COUNT(*)
----------
        49

SQL>

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.audit_trail_unified,
   use_last_arch_timestamp => TRUE);
END;
/

SELECT COUNT(*) FROM unified_audit_trail;

  COUNT(*)
----------
         4

SQL>

You can automate purging by creating a purge job

Managing Audit Trails

Relocate the audit trail to a different tablespace and set up an automatic purge process to keep its size under control.

One of the most significant aspects of database security involves setting up auditing to record user activities. The very knowledge that a user’s actions are being recorded can act as a significant deterrent to prevent wrongdoers from committing malicious acts.

When auditing is enabled, the audit output is recorded in an audit trail, which is usually stored in the database in a table under the SYS schema called AUD$. It can also reside as files in the file system, and the files can optionally be stored in XML format. For more-precise control, the Fine Grained Auditing feature of Oracle Database 11g provides granular control of what to audit, based on a more detailed set of policies. Fine Grained Auditing audits are usually stored in another table, FGA_LOG$, under the SYS schema.

These various audit trails can quickly grow out of control when database activity increases. As audit trails grow, two main challenges must be addressed: 

1. Trails need to be kept to a manageable size (and old records purged) if they are to be used effectively in forensic analysis.
2. Because database-resident trails are typically stored in the SYSTEM tablespace, they can potentially fill it up—bringing the database to a halt. 

Fortunately, the new auditing features in Oracle Database 11g Release 2 can help address these challenges. These capabilities, implemented in a package called DBMS_AUDIT_MGMT, enable you to move audit trails from the SYSTEM tablespace to one of your choice.

The new auditing features also let you set up one-time and automated purge processes for each of your audit trail types. Historically, to purge an audit trail, you were generally forced to stop auditing (which may have required bouncing the database), truncate, and then restart auditing (and bouncing the database again).

In this article, you will learn how to use the new features in Oracle Database 11g Release 2 to manage your audit trails. 

Relocating the Audit Trail Tables

Let’s first examine how to relocate an audit trail from the default SYSTEM tablespace to a new one. In case you don’t already have a suitable target tablespace, the code below shows how to create one: 

create tablespace audit_trail_ts
datafile '+DATA'
size 500M
segment space management auto
/
 

For moving an audit trail to the new tablespace, Oracle Database 11g Release 2 provides a procedure in DBMS_AUDIT_MGMT called SET_AUDIT_TRAIL_LOCATION. Listing 1 shows how to move a “standard” audit trail, which is the Oracle Database audit recorded in the AUD$ table.

Code Listing 1: Relocating a standard audit trail 

begin
 dbms_audit_mgmt.set_audit_trail_location(
  audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
  audit_trail_location_value  => 'AUDIT_TRAIL_TS');
end;
/
 

This move operation can be performed even when the database is up and an audit trail is being written. The target tablespace (AUDIT_TRAIL_TS in this case) must be available and online. If the tablespace is not available, auditing will stop, also stopping the database in the process. You should therefore be very careful about where you create the tablespace. The location should be permanent (and not on a temporary file system such as /tmp), and the underlying hardware should be resilient against failures (using RAID-1, for example).

The procedure can also be used for Fine Grained Auditing audit trails. To move a Fine Grained Auditing audit trail, simply replace the value of the audit_trail_type parameter in Listing 1 with dbms_audit_mgmt.audit_trail_fga_std. If you want to move both the standard and Fine Grained Auditing audit trails to the new tablespace, use the dbms_audit.audit_trail_db_std value as the audit_trail_type parameter. 

Purging Old Data

Next, let’s examine how to purge audit trails. The audit management package includes a procedure that automatically performs the purge for you. But before you can actually use it, you must call a one-time initialization procedure—INIT_CLEANUP—to set up the audit management infrastructure. Listing 2 shows how to perform the initialization. 

Code Listing 2: Initializing cleanup of audit entries 

begin
  dbms_audit_mgmt.init_cleanup(
    audit_trail_type            => dbms_audit_mgmt.audit_trail_db_std,
    default_cleanup_interval    => 24 );
end;
 

The INIT_CLEANUP procedure takes two parameters, neither of which takes a default value: 

• audit_trail_type—designates the type of audit trail being initialized. For instance, audit_trail_aud_std indicates the standard database audit trail (the AUD$ table). Table 1 lists the possible values for this parameter and the audit trail types they represent.
• default_cleanup_interval—designates the default interval in hours between executions of automatic purge jobs (to be discussed later in this article).

Parameter	Description
audit_trail_aud_std	The standard AUD$ audit trail in the database
audit_trail_fga_std	The FGA_LOG$ table, for Fine Grained Auditing
audit_trail_db_std	Both standard and FGA audit trails
audit_trail_os	The OS audit trail
audit_trail_xml	The XML audit trail
audit_trail_files	Both OS and XML audit trails
audit_trail_all	All of the above

 Table 1: Types of audit trails for audit_trail_type

In addition to setting the default cleanup frequency, the INIT_CLEANUP procedure moves the audit trail out of the SYSTEM tablespace. If the FGA_LOG$ and AUD$ tables are in the SYSTEM tablespace, the procedure will move them to the SYSAUX tablespace. Needless to say, you should ensure that the SYSAUX tablespace has sufficient space to hold both of these tables. The process of moving data from one tablespace to the other can have an impact on performance, so you should avoid calling the procedure during peak hours.

If you have already relocated these two tables to another tablespace (as described in the previous section), they will stay in the new location and the procedure will execute much more quickly.

After calling the initialization procedure, you can perform the actual audit trail cleanup, but you likely wouldn’t just remove an audit trail blindly. In most cases, you would archive the trail first before performing a permanent purge. When doing so, you can call another procedure—SET_LAST_ARCHIVE_TIMESTAMP—to let the purge process know the time stamp up to which an audit trail has been archived. This procedure accepts three parameters: 

• audit_trail_type—the type of audit trail you are about to purge.
• last_archive_time—the last time the audit trail was archived for this type.
• rac_instance_number—with an Oracle Real Application Clusters (Oracle RAC) database, OS audit trail files exist on more than one server. It’s possible to archive these files at different times, so this parameter tells the purge process the archive time of each node (or instance number) of the cluster. This parameter is applicable to Oracle RAC databases only; it has no significance for single-instance databases. Furthermore, this parameter is irrelevant for database audit trails, because they are common to all Oracle RAC instances. 

After you set the archive time stamp, you can check its value from a data dictionary view, DBA_AUDIT_MGMT_LAST_ARCH_TS. Listing 3 shows how to set the cutoff time stamp to September 30, 2009 at 10 a.m. and subsequently check its value from the view.

Code Listing 3: Setting the last archived time 

begin
   dbms_audit_mgmt.set_last_archive_timestamp(
     audit_trail_type  => dbms_audit_mgmt.audit_trail_aud_std,
     last_archive_time => 
        to_timestamp('2009-09-30 10:00:00','YYYY-MM-DD HH24:MI:SS'),
     rac_instance_number  => null
   );
end;
/

SQL> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;

AUDIT_TRAIL           RAC_INSTANCE
——————————————————————————————————
LAST_ARCHIVE_TS
——————————————————————————————————
STANDARD AUDIT TRAIL  0
30-SEP-09 10.00.00.000000 AM +00:00
 

Now you can execute the purge. To do so, run the code shown in Listing 4. The CLEAN_AUDIT_TRAIL procedure in the listing accepts two parameters. The first one is audit_trail_type. The second parameter—use_last_arch_timestamp—specifies whether the purge should be performed, depending on the last archive time stamp. If the parameter is set to TRUE (the default), the purge will delete the records generated before the time stamp (September 30, 2009 at 10 a.m. in this case). If it is set to FALSE, all audit trail records will be deleted.

Code Listing 4: Purging a standard database audit trail 

begin
  dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type        =>  dbms_audit_mgmt.audit_trail_aud_std,
   use_last_arch_timestamp => TRUE
  );
end;
/
 

This same procedure is also used to purge file-based audit trails such as OS file audit trails and XML trails. To purge those trails, just specify the appropriate value for the audit_trail_type parameter (as shown in Table 1). However, note that for file-based audit trails, only the files in the current audit directory (as specified by the audit_file_dest initialization parameter) will be deleted. If you have audit trail files in a different directory from the one specified in audit_file_dest, those files will not be deleted.

Note that in Microsoft Windows, audit trails are entries in Windows Event Viewer and not actual OS files. So purging OS-based audit trails on that platform will not delete the trails. 

Setting Up Automatic Purge

The foregoing process is good for a one-time purge of audit trails. To ensure that audit trails do not overwhelm their tablespace, you may want to institute an automatic purge mechanism. The DBMS_AUDIT_MGMT package has another procedure—CREATE_PURGE_JOB—to do just that. This procedure takes four parameters: 

• audit_trail_type—the type of the audit trail
• audit_trail_purge_interval—the duration, in hours, between executions of the purge process
• audit_trail_purge_name—the name you assign to this job
• use_last_arch_timestamp—an indication of whether the job should delete audit trail records marked as archived. The default is TRUE. If the parameter is set to FALSE, the procedure will delete the entire trail. 

Listing 5 shows how to create a purge job that deletes standard audit trail records every 24 hours. As with one-time purges, you can create different jobs for each type of trail—such as standard, Fine Grained Auditing, OS files, and XML—simply by specifying different values for audit_trail_type when calling CREATE_PURGE_JOB. You can even set different purge intervals for each audit trail type to suit your archival needs. For instance, you can use a simple database-link-based script to pull database audit trail records to a different database while using a third-party tool to pull the OS audit trails. The execution time of each approach may be different, causing the database records to be pulled every day while the OS files are being pulled every hour. As a result, you might schedule purge jobs with an interval of 24 hours for database-based trails and with an interval of one hour for OS-file-based trails.

Code Listing 5: Creating a purge job for a standard audit trail 

begin
   dbms_audit_mgmt.create_purge_job (
   audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
   audit_trail_purge_interval  => 24,
   audit_trail_purge_name      => 'std_audit_trail_purge_job',
   use_last_arch_timestamp     => TRUE
   );
end;
/
 

You can view information about automatic purge jobs by accessing the DBA_AUDIT_MGMT_CLEANUP_JOBS data dictionary view. It shows all the important attributes of the job, such as the name, the type of audit trail being cleaned, and the frequency. 

Setting Audit Trail Properties

Code Listing 6: Setting the deletion batch size When setting up a purge job, you should always remember one very important fact. It performs a DELETE operation—not TRUNCATE—on database-based trails, so the purge operation generates redo and undo records, which may be quite significant, depending on the number of trail records deleted. A large deletion can potentially fill up the undo tablespace. To reduce the redo size of a transaction, the purge job deletes in batches of 1,000 and performs commits between them. If the database is very large, it may be able to handle much more redo easily. You can change the delete batch size by using the SET_AUDIT_TRAIL_PROPERTY procedure. Listing 6 shows how to set the delete batch size to 100,000. 

begin
 dbms_audit_mgmt.set_audit_trail_property(
  audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
  audit_trail_property        => dbms_audit_mgmt.db_delete_batch_size,
  audit_trail_property_value  => 100000);
end;
/
 

In addition to the db_delete_batch_size property referenced in Listing 6, you can use SET_AUDIT_TRAIL_PROPERTY to set several other important properties. They include the following: 

• file_delete_batch_size specifies how many OS audit trail files will be deleted by the purge job in one batch.
• cleanup_interval specifies the default interval, in hours, between executions of a purge job.
• os_file_max_age specifies how many days an OS file or an XML file can be left open before a new file is created.
• os_file_max_size specifies the maximum size of an audit trail file (in kilobytes). 

To find the current value of a property, you can check the data dictionary view DBA_AUDIT_MGMT_CONFIG_PARAMS. 

Conclusion

Audit trails establish accountability. In Oracle Database 11g, there are several types of audit trails—standard, fine-grained, OS-file-based, and XML. In this article, you learned how to relocate a database-based audit trail from its default tablespace—SYSTEM—to another one designated only for audit trails. You also learned how to purge audit trails of various types to keep them within a manageable limit, and you finished by establishing an automatic purge process.

Exporting Schema Statistics

While most of us are familiar with the schema statistics used by the Cost-Based Optimizer (CBO), something not so well know is the ability to export/import these statistics using the DBMS_STATS package. This can be an invaluable aid in diagnosing query plan differences as these statistics are the primary information used by CBO. It is also a way to save and restore statistics in your own Primavera database.

Exporting statistics is a three step process. The result is a single table containing schema statistics (table, index and column) and system statistics (workload and non-workload). The first step creates a physical version of a StatTable. The StatTable is a consolidated table to hold all types of statistics, so the format is very generic. First, create an instance of the table using DBMS_STATS.CREATE_STAT_TABLE:

begin

-- exec DBMS_STATS.CREATE_STAT_TABLE('<table owner>','<enter a

name for the stats ----table>','<tablespace to store the stats table');

  dbms_stats.CREATE_STAT_TABLE( ownname=>user
                             , stattab=>'MY_STATS_TABLE'
                              );
end;
/

The result is a physical table called MY_STATS_TABLE

SQL>  desc MY_STATS_TABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATID                                             VARCHAR2(30 CHAR)
 TYPE                                               CHAR(1 CHAR)
 VERSION                                            NUMBER
 FLAGS                                              NUMBER
 C1                                                 VARCHAR2(30 CHAR)
 C2                                                 VARCHAR2(30 CHAR)
 C3                                                 VARCHAR2(30 CHAR)
 C4                                                 VARCHAR2(30 CHAR)
 C5                                                 VARCHAR2(30 CHAR)
 N1                                                 NUMBER
 N2                                                 NUMBER
 N3                                                 NUMBER
 N4                                                 NUMBER
 N5                                                 NUMBER
 N6                                                 NUMBER
 N7                                                 NUMBER
 N8                                                 NUMBER
 N9                                                 NUMBER
 N10                                                NUMBER
 N11                                                NUMBER
 N12                                                NUMBER
 D1                                                 DATE
 R1                                                 RAW(32)
 R2                                                 RAW(32)
 CH1                                                VARCHAR2(1000 CHAR)
 CL1                                                CLOB

The next two steps are to export data from the current schema. The column STATID identifies a particular set of statistics within this table. It is possible to do multiple exports into a single StatTable by using a different STATID. In this case I am using "CURRENT_STATS" as the STATID.

--Export the Table, Index, and Column Statistics 
begin
  dbms_stats.export_schema_stats( ownname=>user
                                , stattab=>'MY_STATS_TABLE'
                                , statid=>'CURRENT_STATS'
                                );
end;
/

--Export system statistics (sys.aux_stats$)
begin
  dbms_stats.export_system_stats( stattab=>'MY_STATS_TABLE'
                                , statid=>'CURRENT_STATS'
                                );
end;
/

If we look at the contents of MY_STATS_TABLE, we will see rows for each different statistic type (T=Table, I=Index, C=Column, S=System).

select statid, type, count(*)
from my_stats_table
group by statid, type
/

STATID                         T   COUNT(*)
------------------------------ - ----------
CURRENT_STATS                  S          2
CURRENT_STATS                  C       4216
CURRENT_STATS                  I        884
CURRENT_STATS                  T        277

This table can be exported (Export or Datapump) and imported into another database. If the schema is the same, then the statistics can be imported. (Remember to clear the shared pool anytime statistics are updated.)

 
begin
  dbms_stats.import_schema_stats( ownname=>user
                                , stattab=>'MY_STATS_TABLE'
                                , statid=>'CURRENT_STATS'
                                );
end;
/

begin
  dbms_stats.import_system_stats( stattab=>'MY_STATS_TABLE'
                                , statid=>'CURRENT_STATS'
                                );

--database stats ;

--exec
dbms_stats.EXPORT_DATABASE_STATS('<enter the name of the stats
table>','<enter an identifier>','<enter the owner of the stats
table>');

dbms_stats.import_system_stats( stattab=>'MY_STATS_TABLE',null,schema);

end;
/