New Auditing Enhancements in Oracle Database 19c
Introduction
Oracle Database 19c has introduced significant advancements in auditing, offering organizations more efficient ways to handle compliance and security. These new features simplify audit configurations, enable real-time monitoring, reduce data redundancies, and enhance storage efficiency. This article explores the major auditing enhancements in Oracle Database 19c, such as unified auditing, real-time audit policies, and improvements in audit trail management.
Audit Top-Level Statements
With Oracle 19c, users can limit audit records to top-level statements, reducing the volume of audit logs and making them more manageable. For example, with PL/SQL API calls, auditing can capture only the top-level procedure calls rather than all internal actions, thereby improving performance and clarity in the audit trail.
CREATE AUDIT POLICY top_level_policy
ACTIONS ALL
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''DBDOCS_USER'''
EVALUATE PER SESSION ONLY TOPLEVEL;
Example :
We start by creating a test table named DBDOCS_T1 and a procedure INSERT_DBDOCS_T1 to add rows to the DBDOCS_T1 table. Both objects are created under the DBDOCS_USER user.
-- Create the test table
CREATE TABLE DBDOCS_USER.DBDOCS_T1 (
id NUMBER
);
-- Create the stored procedure to insert rows
CREATE OR REPLACE PROCEDURE DBDOCS_USER.insert_DBDOCS_T1 (p_id IN NUMBER)
AS
BEGIN
INSERT INTO DBDOCS_T1 VALUES (p_id);
END;
/
Creating Unified Audit Policies
Next, we create two audit policies. Both policies track all actions for the DBDOCS_USER user. However, the second policy includes the ONLY TOPLEVEL clause for a more targeted audit.
-- Create a policy to audit all actions for DBDOCS_USER
CREATE AUDIT POLICY DBDOCS_USER_pol
ACTIONS ALL
WHEN q'~ sys_context('userenv', 'session_user') = 'DBDOCS_USER' ~'
EVALUATE PER SESSION;
-- Create a top-level-only audit policy
CREATE AUDIT POLICY DBDOCS_USER_toplevel_pol
ACTIONS ALL
WHEN q'~ sys_context('userenv', 'session_user') = 'DBDOCS_USER' ~'
EVALUATE PER SESSION
ONLY TOPLEVEL;
Enable the standard audit policy:
-- Enable the standard unified audit policy
AUDIT POLICY DBDOCS_USER_pol;
Executing the Procedure and Viewing the Audit Trail
Connect to the test user and execute the procedure to add a row to the DBDOCS_T1 table. Commit the transaction afterward.
-- Connect and execute the procedure
CONN DBDOCS_USER/DBDOCS_USER@//localhost:1521/pdb1
EXEC insert_DBDOCS_T1(1);
COMMIT;
To verify the audit trail, connect as SYS and query the unified audit trail for actions related to DBDOCS_USER:
-- Connect as SYS and check the audit trail
CONN sys/SysPassword1@//localhost:1521/pdb1 AS sysdba
SET LINESIZE 200
COLUMN event_timestamp FORMAT a30
COLUMN action_name FORMAT a13
COLUMN object_schema FORMAT a15
COLUMN object_name FORMAT a25
SELECT event_timestamp,
action_name,
object_schema,
object_name
FROM unified_audit_trail
WHERE dbusername = 'DBDOCS_USER'
ORDER BY event_timestamp;
Audit Trail Output
The output should show all actions performed by DBDOCS_USER, including logon, session activity, the stored procedure call, and the row insertion.
Switching to Top-Level Only Audit Policy
To limit auditing to only top-level actions, disable the standard audit policy and enable the top-level audit policy. Clean the audit trail to clear previous entries.
-- Switch to top-level audit policy and clean audit trail
NOAUDIT POLICY DBDOCS_USER_pol;
AUDIT POLICY DBDOCS_USER_toplevel_pol;
EXEC dbms_audit_mgmt.clean_audit_trail(dbms_audit_mgmt.audit_trail_unified, FALSE);
Reconnect as DBDOCS_USER, execute the procedure, and check the audit trail again.
-- Connect and execute the procedure
CONN DBDOCS_USER/DBDOCS_USER@//localhost:1521/pdb1
EXEC insert_DBDOCS_T1(1);
COMMIT;
-- Check the audit trail for top-level actions only
CONN sys/SysPassword1@//localhost:1521/pdb1 AS sysdba
SELECT event_timestamp,
action_name,
object_schema,
object_name
FROM unified_audit_trail
WHERE dbusername = 'DBDOCS_USER'
ORDER BY event_timestamp;
Top-Level Audit Output
Only top-level calls, such as the execution of the stored procedure, will appear in the audit trail, without recording the individual row insertion.
Disabling and Dropping Audit Policies
To complete the audit configuration, disable and remove both audit policies when done.
-- Disable and drop the audit policies
NOAUDIT POLICY DBDOCS_USER_pol;
NOAUDIT POLICY DBDOCS_USER_toplevel_pol;
DROP AUDIT POLICY DBDOCS_USER_pol;
DROP AUDIT POLICY DBDOCS_USER_toplevel_pol;
Unified Auditing
Oracle’s Unified Auditing in 19c consolidates various auditing options into a single framework, providing a centralized view of all audit data. This unification allows administrators to create flexible and detailed policies, simplifying audit management and reducing overhead. Unified auditing supports both standard and fine-grained policies, and you can enable it with:
ALTER SYSTEM SET audit_trail = 'DB, EXTENDED' SCOPE = SPFILE;
Real-Time Audit Policies
Oracle 19c introduces the ability to audit in real time, offering immediate detection and alerting of policy breaches. Real-time audit policies empower organizations to respond faster to security incidents. Here’s an example of creating a real-time audit policy:
CREATE AUDIT POLICY sensitive_data_access
ACTIONS SELECT ON hr.employees
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SCOTT'''
EVALUATE ON ACCESS;
Audit Trail Improvements
In Oracle 19c, improvements to audit trails help organizations optimize storage and manage audit logs more efficiently. Features like data compression reduce storage requirements while maintaining access speed, and administrators now have more granular options for audit data retention.
Managing Audit Records
Oracle 19c simplifies audit record management by allowing administrators to archive, purge, and monitor logs according to customizable policies. By setting policies for records based on age, for instance, organizations can ensure that only relevant audit data is kept, aiding in compliance and database performance.
EVENT_TIMESTAMP_UTC Column
Oracle Database 19c introduces the EVENT_TIMESTAMP_UTC column in the UNIFIED_AUDIT_TRAIL view, which enhances query performance by enabling partition pruning. It’s recommended to include this column in WHERE clauses for faster filtering of audit logs.
Trace File Analyzer (TFA) and Audit
The Trace File Analyzer (TFA) tool in Oracle 19c now includes support for new Service Request Data Collections (SRDCs), such as "dbaudit." This feature provides targeted data collection for auditing issues, streamlining troubleshooting and diagnostics for audit-related events. To use TFA for DBAudit, run:
cd $ORACLE_HOME/ahf/oracle.ahf/bin
./tfactl diagcollect -srdc dbaudit
Enabling SYSLOG and Windows Event Viewer for Unified Audit Trail
Oracle 19c supports capturing audit trails through system-level logging, which can be configured for both Unix-based and Windows systems. This approach helps create a unified audit trail that simplifies monitoring and auditing across operating systems.
Configuration for Unified Audit Logging
Use the following initialization parameters to enable SYSLOG or Windows Event Viewer for audit trail capture:
- UNIFIED_AUDIT_SYSTEMLOG: Allows audit logging at an individual container level, applicable to both Unix and Windows systems. For Windows, this parameter can be set to
TRUE or FALSE to enable or disable logging.
- UNIFIED_AUDIT_COMMON_SYSTEMLOG: Available only on Unix-based systems, this parameter enables a common SYSLOG configuration for all containers from the root container.
Setting Parameters in Oracle
-- Enable unified audit logging in SYSLOG (Unix) or Windows Event Viewer
ALTER SYSTEM SET unified_audit_systemlog = 'local0.notice' SCOPE=SPFILE;
ALTER SYSTEM SET unified_audit_common_systemlog = 'local0.notice' SCOPE=SPFILE;
On Unix systems, the configuration can be customized further with facility and priority clauses:
-- Unix-specific options for SYSLOG
UNIFIED_AUDIT_SYSTEMLOG = 'facility_clause.priority_clause'
UNIFIED_AUDIT_COMMON_SYSTEMLOG = 'facility_clause.priority_clause'
-- Facility and Priority Options
facility_clause ::= { USER | LOCAL[0-7] }
priority_clause ::= { NOTICE | INFO | DEBUG | WARNING | ERR | CRIT | ALERT | EMERG }
Example Unix Configuration
To capture audit logs in Unix, configure SYSLOG to store logs in a specific file, and restart the SYSLOG service. Add the following entry in the /etc/rsyslog.conf file:
# Add configuration to store logs in a specific file
local0.notice /var/log/local0.notice.audit.log
# Restart SYSLOG
sudo systemctl restart syslog
Setting Up Unified Audit Logging in Oracle
-- Enable unified audit logging at the root container
conn / as sysdba
ALTER SYSTEM SET unified_audit_common_systemlog = 'local0.notice' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
-- Enable unified audit logging for a specific container (e.g., PDB1)
conn / as sysdba
ALTER SESSION SET container = pdb1;
ALTER SYSTEM SET unified_audit_systemlog = 'local0.notice' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
After configuration, audit logs will be available in both /var/log/messages and the designated /var/log/local0.notice.audit.log file on Unix systems. Note that Oracle does not currently support excluding audit logs from /var/log/messages up to version 21.4.
Configuration on Windows
On Windows, set the UNIFIED_AUDIT_SYSTEMLOG parameter in any container to enable logging in the Windows Event Viewer. Simply set this parameter as follows:
-- Enable Windows Event Viewer logging for unified audit
ALTER SYSTEM SET unified_audit_systemlog = TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
Conclusion
Oracle Database 19c’s enhanced auditing features provide administrators with a robust, flexible suite of tools to address compliance and security challenges. By integrating unified auditing, real-time policy capabilities, and improved audit trail management, organizations can meet regulatory requirements and ensure data security more efficiently than before. These features make Oracle Database 19c a strong choice for enterprises prioritizing comprehensive security and compliance management.