1. What is Oracle Unified Auditing?
Oracle Unified Auditing (UA) is a centralized and comprehensive auditing framework introduced in Oracle Database 12c that simplifies and enhances database auditing. Unlike the traditional auditing mechanisms (Standard Auditing and Fine-Grained Auditing), Unified Auditing consolidates all audit logs into a single unified repository, reducing overhead and improving performance.
2. How Unified Auditing Works
Unified Auditing works by capturing audit records in a centralized, policy-based manner. It records user activity, privileged user actions, schema changes, and security events. Here’s how it operates:
1. Audit Policies:
• Admins define audit policies using SQL commands (CREATE AUDIT POLICY).
• Policies specify what to audit (e.g., login attempts, DML/DDL changes, privileged user actions).
• Policies can be enabled at system-wide or user levels.
2. Audit Data Storage:
• All audit records are stored in the UNIFIED_AUDIT_TRAIL table within the AUDSYS schema.
• Unlike traditional auditing, there are no separate audit logs/files.
3. Audit Capture Methods:
• Captures audit records for Standard Auditing, Fine-Grained Auditing (FGA), SYS operations, RMAN, Data Pump, and Label Security.
• Supports policy-based selective auditing, reducing unnecessary data collection.
4. Integration with Oracle Features:
• Works with Oracle Database Vault, Oracle Label Security, Oracle Real Application Security.
• Can be written to OS files, SYSLOG, or SIEM systems for external monitoring.
3. Benefits of Oracle Unified Auditing
• Single Audit Trail:
• Consolidates all audit records into one location, simplifying audit management.
• Performance Optimization:
• Uses memory-optimized architecture, reducing I/O impact on performance.
• Can be configured with deferred writing, improving logging efficiency.
• Policy-Based Auditing:
• Enables fine-grained control over which activities are audited.
• Reduces the volume of collected audit data, minimizing storage overhead.
• Tamper-Resistant Logs:
• Audit records are stored in SecureFiles LOBs, preventing unauthorized modifications.
• Can be protected with Oracle Database Vault.
• Integration with Security Tools:
• Can send logs to external security monitoring tools (e.g., SIEM, Splunk, ArcSight).
• Supports SYSLOG and OS Log integration.
• Mandatory Auditing for Critical Events:
• Ensures key security actions (e.g., SYSDBA logins) are always audited, even if auditing is disabled.
4. Performance Impact and Optimization
Unified Auditing significantly improves performance over traditional auditing methods:
• Optimized Storage & Processing:
• Instead of writing logs in multiple locations, it uses a single database table, reducing performance bottlenecks.
• Reduced I/O Overhead:
• Uses batch writing and compression techniques to minimize database performance impact.
• Selective Auditing:
• Admins can audit specific users, actions, or conditions to reduce unnecessary logging.
• Deferred Writing Mode:
• Allows buffering of audit records in memory before writing to disk, further optimizing performance.
5. Coverage – What Does Unified Auditing Capture?
Oracle Unified Auditing can capture a broad set of database activities, including:
Audit Category What It Covers
User Actions Logins, failed logins, user activity
DML Operations INSERT, UPDATE, DELETE actions
DDL Operations CREATE, ALTER, DROP operations
Privileged Operations SYSDBA/SYSOPER actions, grants, role changes
Data Access Auditing SELECT statements, FGA policies
Configuration Changes Parameter changes, system settings
Backup & Restore RMAN operations
Data Exports & Imports Data Pump operations
Label Security Actions Oracle Label Security modifications
6. Example: Enabling and Using Unified Auditing
Checking if Unified Auditing is Enabled
SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Unified Auditing’;
CREATE AUDIT POLICY audit_logins
ACTIONS LOGON, LOGOFF
WHEN ‘SYS_CONTEXT(”USERENV”, ”SESSION_USER”) NOT IN (”ADMIN”)’;
AUDIT POLICY audit_logins;
SELECT EVENT_TIMESTAMP, DB_USER, ACTION_NAME, OBJECT_NAME
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME = ‘LOGON’;
7. Conclusion
Oracle Unified Auditing provides a centralized, performance-optimized, and security-enhanced method for database auditing. By consolidating logs, reducing performance overhead, and enabling policy-based auditing, it simplifies compliance, enhances security monitoring, and improves overall database efficiency.
