In this blog post I am sharing my notes about Unified Auditing. It shows how to enable and purge Unified Audit data.
From Oracle Documents:
What Is Unified Auditing?
In unified auditing, the unified audit trail captures audit information from a variety of sources.
Unified auditing enables you to capture audit records from the following sources:
-
Audit records (including SYS audit records) from unified audit policies and AUDIT settings
-
Fine-grained audit records from the DBMS_FGA PL/SQL package
-
Oracle Database Real Application Security audit records
-
Oracle Recovery Manager audit records
-
Oracle Database Vault audit records
-
Oracle Label Security audit records
-
Oracle Data Mining records
-
Oracle Data Pump
-
Oracle SQL*Loader Direct Load
Mixed Mode or Pure Unified Auditing?
Mode Features How to Enable Mixed mode auditing Has both traditional and unified auditing Enable any unified audit policy. There is no need to restart the database. Pure unified auditing Has only unified auditing Link the oracle binary with uniaud_on, and then restart the database.Oracle Database Upgrade Guide describes how to enable pure unified auditing.
More About Unified Auditing
The unified audit trail, which resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace, makes this information available in a uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view, and is available in both single-instance and Oracle Database Real Application Clusters environments. In addition to the user SYS, users who have been granted the AUDIT_ADMIN and AUDIT_VIEWER roles can query these views. If your users only need to query the views but not create audit policies, then grant them the AUDIT_VIEWER role.
-
AUDIT_ADMIN Role. This role enables you to create unified and fine-grained audit policies, use the AUDIT and NOAUDIT SQL statements, view audit data, and manage the audit trail administration. Grant this role only to trusted users.
-
AUDIT_VIEWER Role. This role enables users to view and analyze audit data. The kind of user who needs this role is typically an external auditor.
How To Enable Audit Policies:
AUDIT POLICY ORA_LOGON_FAILURES EXCEPT DBSNMP WHENEVER NOT SUCCESSFUL; AUDIT POLICY ORA_SECURECONFIG EXCEPT DBSNMP; AUDIT POLICY ORA_DATABASE_PARAMETER;
How To Purge Audit Data
Following procedure sets the LAST_ARCHIVE_TIME (or retention to 30 days).
BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, LAST_ARCHIVE_TIME => SYSDATE - 30 ); END; /
Create a Purge Job which deletes based on LAST_ARCHIVE_TIME
BEGIN DBMS_AUDIT_MGMT.CREATE_PURGE_JOB ( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, AUDIT_TRAIL_PURGE_INTERVAL => 12, AUDIT_TRAIL_PURGE_NAME => 'Audit_Trail_PURGE', USE_LAST_ARCH_TIMESTAMP => TRUE ); END; /
How To Change The Tablespace For Unified Audit Tables:
set echo on col parameter_name format a30 col parameter_value format a20 select * from DBA_AUDIT_MGMT_CONFIG_PARAMS where AUDIT_TRAIL = 'UNIFIED AUDIT TRAIL'; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL ------------------------------ -------------------- ---------------------------- AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL DB AUDIT TABLESPACE SYSAUX UNIFIED AUDIT TRAIL AUDIT WRITE MODE QUEUED WRITE MODE UNIFIED AUDIT TRAIL 4 rows selected. --Create tablespace CREATE TABLESPACE DBAUDIT DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 512M MAXSIZE 65535M ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 16K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON; Tablespace created. --update the tablespace for Unified Audit BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, audit_trail_location_value => 'DBAUDIT'); END; / PL/SQL procedure successfully completed. select * from DBA_AUDIT_MGMT_CONFIG_PARAMS where AUDIT_TRAIL = 'UNIFIED AUDIT TRAIL'; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL ------------------------------ -------------------- ---------------------------- AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL DB AUDIT TABLESPACE DBAUDIT UNIFIED AUDIT TRAIL AUDIT WRITE MODE QUEUED WRITE MODE UNIFIED AUDIT TRAIL 4 rows selected.
Need Another Job To Advance LAST_ARCHIVE_TIMESTAMP
Create a procedure to advance LAST_ARCHIVE_TIME
create or replace procedure set_audit_archive_retention (retention in number default 30) as BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, last_archive_time => sysdate - retention); end; / --Create a database scheduler job. Change “30” to whatever your requirements are. BEGIN sys.dbms_scheduler.create_job( job_name => 'advance_archive_timestamp', job_type => 'STORED_PROCEDURE', job_action => 'set_audit_archive_retention', repeat_interval => 'FREQ=DAILY;interval=12', number_of_arguments => 1, start_date => systimestamp at time zone 'America/New_York', job_class => '"DEFAULT_JOB_CLASS"', comments => 'Advance the last archive timestamp by 30 days', auto_drop => TRUE, enabled => FALSE); dbms_scheduler.set_job_argument_value( job_name => 'advance_archive_timestamp', argument_position => 1, argument_value => 30);
sys.dbms_scheduler.enable( 'advance_archive_timestamp' ); END; /
Helpful Views/Tables:
select * from DBA_AUDIT_MGMT_CLEAN_EVENTS; select * from DBA_AUDIT_MGMT_CLEANUP_JOBS; select * from DBA_AUDIT_MGMT_CONFIG_PARAMS; select * from DBA_AUDIT_MGMT_LAST_ARCH_TS; select * from AUDIT_UNIFIED_ENABLED_POLICIES; col POLICY_NAME format A20 col AUDIT_OPTION format A40 set PAGES 100 select POLICY_NAME, AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where policy_name = 'ORA_SECURECONFIG' order by 2 ;
