Introduction
In previous releases of Oracle Database prior to Oracle 12c Release 1, there were separate audit trails for individual components.
For example:
- SYS.AUD$ for the database audit trail,
- SYS.FGA_LOG$ for fine-grained auditing,
- DVSYS.AUDIT_TRAIL$ for Oracle Database Vault, Oracle Label Security,
Starting with 12c Release 1, Oracle has unified all of the auditing types into one single unit called Unified Auditing where the AUD$ and FGA$ tables have been replaced with one single audit trail table.
We no longer need to set any auditing related parameters in the database like AUDIT_SYS_OPERATIONS and AUDIT_FILE_DEST. Once unified auditing is enabled, it does not depend on the initialization parameters that were used in previous releases.
By default, audit trail records are written to the AUDSYS schema in the SYSAUX tablespace. We can designate a different tablespace, including one that is encrypted, by using the DBMS_AUDIT_MGMT package.
In Oracle Database 12c release 1 (12.1), we had the option of queuing the audit records in memory (queued-write mode) which would be written then periodically to the AUDSYS schema audit table.
However, starting with Oracle Database 12c release 2 (12.2) the queued-write mode is deprecated and the unified audit records are written immediately to disk to a range-partitioned table in the AUDSYS schema called AUD$UNIFIED.
Access to the AUDSYS. AUD$UNIFIED table is provided via the view SYS.UNIFIED_AUDIT_TRAIL.
Audit administration duties have been separated with two audit administration roles: AUDIT_ADMIN and AUDIT_VIEWER.
Unified auditing can be implemented in one of the following ways:
1) Mixed mode auditing: allows both the new audit engine and the traditional pre-Oracle 12c audit engine to work simultaneously. This is the default on upgrade to 12c.
2) Pure unified auditing: allows only the unified auditing
To enable unified auditing relink Oracle binaries with the new uniaud_on option after shutting down all Oracle processes running out of the Oracle Home like database instance and listener.
After unified auditing is enabled none of the old audit related database parameters like audit_trail, audit_file_dest,audit_sys_operations,audit_syslog_level are applicable.
When
the database is not open in read-write mode, then audit records are
written to new format operating system files located in the
$ORACLE_BASE/audit/$ORACLE_SID directory.
Enabling Unified Auditing
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Unified Auditing’;
VALUE
—————————————————————-
FALSE
SQL> show parameter AUDIT_TRAIL
NAME TYPE VALUE
———————————— ———– ——————————
audit_trail string DB
SQL> alter system set audit_trail=’NONE’ scope=spfile;
System altered.
SQL> shutdown immediate;
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle
SQL> startup;
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Unified Auditing’;
VALUE
—————————————————————-
TRUE
Changing the default tablespace for the Unified Auditing
Create a new tablespace called AUDIT_DATA
SQL> BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
audit_trail_location_value => ‘AUDIT_DATA’);
END;
/
PL/SQL procedure successfully completed.
SQL> col owner format a10
SQL> col table_name format a15
SQL> col tablespace_name format a12
SQL> col interval format a20
SQL> set lines 200
SQL> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner=’AUDSYS’;
OWNER TABLE_NAME INTERVAL PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
———- ————— ——————– ——— ————— ——————————
AUDSYS AUD$UNIFIED INTERVAL ‘1’ MONTH RANGE 1048575 AUDIT_DATA
No DDL or DML permitted on AUD$UNIFIED Table
SQL> truncate table audsys.AUD$UNIFIED;
truncate table audsys.AUD$UNIFIED
*
ERROR at line 1:
ORA-46385: DML and DDL operations are not allowed on table
“AUDSYS”.”AUD$UNIFIED”.
Create an Auditing user with the AUDIT_VIEWER Role
SQL> create user auditor identified by Dreamliner787##;
User created.
SQL> grant create session, select any dictionary, select any table to auditor;
Grant succeeded.
SQL> conn auditor/Dreamliner787##@pdb1
Connected.
SQL> select count(*) from audsys.unified_audit_trail;
select count(*) from audsys.unified_audit_trail
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb1;
Session altered.
SQL> grant audit_viewer to auditor;
Grant succeeded.
SQL> conn auditor/Dreamliner787##@pdb1
Connected.
SQL> select count(*) from audsys.unified_audit_trail;
COUNT(*)
———-
278
Purge Audit Trail is not possible using AUDIT_VIEWER role – requires AUDIT_ADMIN role
SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => TRUE,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT );
END;
/
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
*
ERROR at line 3:
ORA-06550: line 3, column 35:
PLS-00904: insufficient privilege to access object AUDSYS.DBMS_AUDIT_MGMT
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
Manually Purging the Unified Audit Trail
SQL> select count(*) from audsys.unified_audit_trail;
COUNT(*)
———-
289
SQL> BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
LAST_ARCHIVE_TIME => ’13-APR-2020 06:30:00.00′,
RAC_INSTANCE_NUMBER => 1,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => TRUE,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT );
END;
/
PL/SQL procedure successfully completed.
SQL> select count(*) from audsys.unified_audit_trail;
COUNT(*)
———-
18
Purging the Unified Audit Trail via a job
SQL> BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => ‘Unified_Audit_Trail_Purge_Job’,
USE_LAST_ARCH_TIMESTAMP => TRUE,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/
PL/SQL procedure successfully completed.
**View Purge Job Details – DBA_AUDIT_MGMT_CLEANUP_JOBS
Create an Unified Audit Policy – audit CREATE TABLE for the user AUDITOR
SQL> CREATE AUDIT POLICY policy_for_auditor
PRIVILEGES CREATE TABLE
WHEN ‘SYS_CONTEXT(”USERENV”, ”SESSION_USER”) = ”AUDITOR”’
EVALUATE PER SESSION
CONTAINER = CURRENT;
Audit policy created.
SQL> AUDIT POLICY policy_for_auditor;
Audit succeeded.
SQL> conn auditor/Dreamliner787##@pdb1
Connected.
SQL> create table auditors_table as select * from dba_objects;
Table created.
View the Unified Audit Trail
SQL> conn / as sysdba
Connected.
SQL> alter session set container=PDB1;
Session altered.
SQL> COLUMN event_timestamp FORMAT A30
SQL> COLUMN dbusername FORMAT A10
SQL> COLUMN action_name FORMAT A20
SQL> COLUMN object_schema FORMAT A10
SQL>COLUMN object_name FORMAT A20
SQL> SELECT event_timestamp,
dbusername,
action_name,
object_schema,
object_name
FROM unified_audit_trail
WHERE dbusername = ‘AUDITOR’;
EVENT_TIMESTAMP DBUSERNAME ACTION_NAME OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- --------------------
13-APR-20 04.27.26.721127 PM AUDITOR CREATE TABLE AUDITOR AUDITORS_TABLE
13-APR-20 04.27.26.741823 PM AUDITOR SELECT SYS DBA_OBJECTS
13-APR-20 04.03.54.117862 PM AUDITOR EXECUTE AUDSYS DBMS_AUDIT_MGMT
**Note: EXECUTE of the DBMS_AUDIT_MGMT PL/SQL package is audited by default – the attempt to execute DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL by the AUDITOR user was audited
Using the predefined ORA_* Unified Audit Policies
SQL> select distinct policy_name from audit_unified_policies order by 1;
POLICY_NAME
——————————————————————————————————————————–
ORA_ACCOUNT_MGMT
ORA_CIS_RECOMMENDATIONS
ORA_DATABASE_PARAMETER
ORA_DV_AUDPOL
ORA_DV_AUDPOL2
ORA_LOGON_FAILURES
ORA_RAS_POLICY_MGMT
ORA_RAS_SESSION_MGMT
ORA_SECURECONFIG
POLICY_FOR_AUDITOR
**Note: For new databases, all the ORA_* policy is enabled by default for both pure unified auditing and mixed-mode auditing environments.
SQL> conn auditor/DReamliner787##@pdb1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> SELECT event_timestamp,
dbusername,
action_name,
object_schema,
object_name
FROM unified_audit_trail
WHERE dbusername = ‘AUDITOR’;
EVENT_TIMESTAMP DBUSERNAME ACTION_NAME OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- --------------------
13-APR-20 04.27.26.721127 PM AUDITOR CREATE TABLE AUDITOR AUDITORS_TABLE
13-APR-20 04.27.26.741823 PM AUDITOR SELECT SYS DBA_OBJECTS
13-APR-20 04.58.25.980623 PM AUDITOR LOGON
13-APR-20 04.03.54.117862 PM AUDITOR EXECUTE AUDSYS DBMS_AUDIT_MGMT
SQL> SELECT
event_timestamp,
dbusername,
object_schema,
object_name
FROM unified_audit_trail
WHERE action_name=’CREATE USER’;
EVENT_TIMESTAMP DBUSERNAME OBJECT_SCH OBJECT_NAME
------------------------------ ---------- ---------- --------------------
13-APR-20 03.41.02.108624 PM SYS AUDITOR
Disabling a predefined ORA_* Unified Audit Policy
Privileges like ALTER ANY TABLE, CREATE ANY TABLE, DROP ANY TABLE, ALTER USER, CREATE USER, DROP USER etc are part of predefined ORA_SECURECONFIG unified audit policy definition.
**Note:The CREATE USER statement we had issued earlier has been audited by default.
SQL> SELECT
event_timestamp,
dbusername,
object_schema,
object_name
FROM unified_audit_trail
WHERE action_name=’CREATE USER’;
EVENT_TIMESTAMP DBUSERNAME OBJECT_SCH OBJECT_NAME
------------------------------ ---------- ---------- --------------------
13-APR-20 03.41.02.108624 PM SYS AUDITOR
SQL> noaudit policy ORA_SECURECONFIG;
Noaudit succeeded.
Last Update: June 8, 2020
