OCP 12C – Auditing

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

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);

Unified Auditing

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
  • 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:

  • You can audit actions based on their success:

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

  •  To disable an audit policy

  • To drop an audit policy:

  •   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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.