Understanding Unified Auditing in Oracle Database

Auditing is a crucial component of any enterprise database security strategy. It helps organizations track user activity, detect suspicious behaviour, and comply with regulatory requirements such as GDPR, SOX, HIPAA, and PCI‑DSS.

With Oracle Database 12c, Oracle introduced Unified Auditing, a major enhancement that consolidates auditing configuration and improves performance. If you're still relying on traditional auditing, now is the perfect time to understand what Unified Auditing offers and how it can simplify audit management.

In this blog, we will explore:

• What Unified Auditing is
• Benefits over traditional auditing
• Modes of unified auditing
• Key components
• How to configure and manage unified auditing
• Best practices for Oracle DBAs

What Is Unified Auditing?

Unified Auditing is Oracle’s auditing framework that centralizes all auditing configurations into a single unified audit trail. Instead of storing audit records in multiple locations (DBA_AUDIT_TRAIL, SYS.AUD$, FGA_LOG$, etc.), Oracle now stores them in one optimized table: UNIFIED_AUDIT_TRAIL.

This makes audit management simpler, faster, and more secure.

Consolidation of Views

In traditional auditing (pre-12c), you had to query different views depending on the type of audit:

• SYS.AUD$: Standard database auditing.
• SYS.FGA_LOG$: Fine-Grained Auditing (FGA).
• DVSYS.AUDIT_TRAIL$: Database Vault auditing.
• Operating System files: SYS user auditing (often stored as .aud files in adump).

In Unified Auditing, all of these sources are piped into a single destination.

Table vs View

• UNIFIED_AUDIT_TRAIL is the read-only view that users/auditors query.
• AUDSYS.AUD$UNIFIED is the actual underlying table where the data is physically stored.

Why Unified Auditing? (Advantages)

 1. Single Audit Trail

All audit records—standard, FGA, RMAN, Data Pump, Label Security, etc. are stored in one place.

 2. Better Performance

Unified auditing uses memory-based caching and dedicated audit writing, reducing overhead on the database.

 3. More Secure

Audit records are tamper‑resistant because they are stored in a secure, write-only internal table.

 4. Policy-Based Control

Instead of enabling audits with multiple parameters, unified auditing allows centralized policy creation.

 5. Support for Conditional Auditing

Audit only when specific conditions are met, such as:

WHENEVER NOT SUCCESSFUL

Unified Auditing Modes

Oracle supports two modes of Unified Auditing:

 1. Mixed Mode (Default)

• Enabled automatically when the database is installed.
• Traditional auditing + unified auditing coexist.
• Ideal for upgrading environments.

2. Pure Mode

• Only unified auditing is allowed.
• Traditional auditing is disabled.
• Requires relinking Oracle binaries:

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_on ioracle

To disable:

make -f ins_rdbms.mk uniaud_off ioracle

Unified Audit Trail Views

View Name	Description
UNIFIED_AUDIT_TRAIL	Main audit trail table
AUDIT_UNIFIED_POLICIES	Lists unified audit policies
DBA_AUDIT_MGMT_CONFIG_PARAMS	Audit configuration details

How to Create Unified Audit Policies

Unified auditing works on policies, making auditing more modular and easier to manage.

 Example 1: Audit All Logins

CREATE AUDIT POLICY audit_logins ACTIONS LOGON;

AUDIT POLICY audit_logins;

Example 2: Audit All SELECTs on SCOTT.EMP

CREATE AUDIT POLICY audit_emp_sel ACTIONS SELECT ON SCOTT.EMP;

AUDIT POLICY audit_emp_sel;

Example 3: Conditional Audit

CREATE AUDIT POLICY audit_failed_logins ACTIONS LOGON;

AUDIT POLICY audit_failed_logins WHENEVER NOT SUCCESSFUL;

 Viewing Audit Records

SELECT event_timestamp, Os_username, action_name, return_code FROM unified_audit_trail ORDER BY event_timestamp DESC;

Automating Housekeeping

You should never let your audit trail grow indefinitely. Oracle provides the DBMS_AUDIT_MGMT package to automate purging.

Example: Creating a Purge Job

BEGIN

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (

     audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

    audit_trail_purge_interval  => 48, -- 48 hours = 2 days

    audit_trail_purge_name      => 'Daily_Unified_Audit_Purge',

    use_last_arch_timestamp     => TRUE,

    container                   => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);

end;

/

Best Practices for Oracle DBAs

 1. Move to Pure Unified Auditing

 It improves performance and simplifies management.

 2. Audit Only What You Need

 Avoid unnecessary performance overhead.

 3. Regularly Purge and Archive Audit Records

 Unified audit trail can grow quickly.

 4. Protect Audit Trail with RBAC

 Restrict access to:

• AUDIT_ADMIN
• AUDIT_VIEWER

 5. Integrate with SIEM Tools

 Forward unified audit logs to Splunk, QRadar, or ELK for real-time monitoring.

Conclusion

Unified Auditing is a powerful and streamlined way to manage auditing in Oracle databases. Whether you're looking to improve performance, enhance security, or meet compliance needs, transitioning to unified auditing brings significant benefits.

As an Oracle DBA, understanding unified auditing is essential for maintaining secure and efficient database environments.