Unified Audit Data Trail

• Unifed Auditing offers a consolidated approach, all the audit data is consolidated in a single place.
• Unified Auditing consolidate audit records for the following sources :
  • Standard Auditing
  • Fine-grained auditing (DBMS_FGA)
  • RAC security auditing
  • RMAN auditing
  • Database Vault auditing
  • Oracle Label Security auditing
  • Oracle Data Mining
  • Oracle Data Pump
  • Oracle SQL*Loader
• In addition to the consolidation, the unified auditing adds security, all audit data consolidated is read-only.
• Unified auditing stores the consolidated data into the SYS.AUDSYS table.
• The unified auditing is activated by default in Oracle 12c but the Old auditing system is still functionning too, you are in mixed-mode. You have to set up explicitly the Unified Auditing to disable the old auditing system.
• You can query audit data by using the view : SYS.UNIFIED_AUDIT_TRAIL

How the new Unified Auditing works

• All audit data is at first written in a queue inside the SGA, this guarantees a minimal impact on the performances.
• When the queue fills-up into the SGA, audit data is written to the audit tables
• During the writting operation, Oracle creates a new queue into the SGA, this allow audit data to continue to be written even when a queue fills-up.
• Audit data is by defaullt written to the SYSAUX tablespace but Oracle recommends the creation of a dedicated tablespace, you can change the audit destination with the following command:
  • DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
• You can also force Oracle to write the audit data located into the SGA to the tables by using :
  • DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL
• The background process responsible for writting audit data to tables is GEN0
• If you want to increase the size of the queue into the SGA you can use the parameter : UNIFIED_AUDIT_SGA_QUEUE_SIZE. This will allow to write to disk less often.
• If at contrary you want to write to disk directly to avoid the loss of audit data in case of crash tou can set this using :
  • 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);

Create, Enable and Alter Audit Policies

•  When you create a policy it can be a :
  • LOCAL Policy, local to a PDB
  • COMMON Policy, available to all PDBs
• By default when you create a policy, It’s a LOCAL policy, if you want to make it COMMON you have to use the CONTAINER=ALL keyword at the end of the create statement.
• To view audit data you need the AUDIT_USER privilege
• To create and enable policies you need the AUDIT_ADMIN privilege.
• Here the the create policy syntaxe

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

• A policy can be created with three options :
  • A privilege
  • An action
  • A role
• These options can be combined with each other.
• To enable a policy you can use the following syntax:

SQL> AUDIT POLICY POLICY_NAME;

• You can audit actions based on their success:

SQL> AUDIT POLICY POLICY_NAME WHENEVER SUCCESSFULL;
SQL> AUDIT POLICY POLICY_NAME WHENEVER NOT SUCCESSFULL;

• You can alter an existing POLICY with the following syntax:

ALTER AUDIT POLICY policy
  [ ADD [ privilege_audit_clause ] [ action_audit_clause ] [ role_audit_clause ] ]
  [ DROP [ privilege_audit_clause ] [ action_audit_clause ] [ role_audit_clause ] ]
  [ CONDITION { DROP | 'audit_condition' EVALUATE PER { STATEMENT | SESSION | INSTANCE } } ] ;

•  To disable an audit policy

SQL> NOAUDIT POLICY POLICY_NAME;

• To drop an audit policy:

SQL> DROP AUDIT POLICY POLICY_NAME;

•   You can query the AUDIT_UNIFIED_POLICIES view to list the Unified Policies

Enabling the Unified Auditing

• By default the Mixed Mode is activated
• To enable the Unified Auditing you need to relink the Oracle binaries :
  • Shutdown all databases and processes using the binaries
  • Relink the binaries using the uniaud_on option:
    • cd $ORACLE_HOME/rdbms/lib
    • make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
• Once you activated the unified auditing the old auditing system becomes inoperative
• To disable the Unified Auditing follow the same procedure with the uniaud_off option