To enable Unified Auditing in Oracle Database 12 and 18 on Oracle Linux I did this:
export ORACLE_SID=ORCLCDB cd $ORACLE_HOME/bin ./sqlplus sys as sysdba SHUTDOWN IMMEDIATE EXIT ./lsnrctl stop cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk uniaud_on ioracle cd $ORACLE_HOME/bin ./lsnrctl start ./sqlplus sys as sysdba STARTUP
After that the following query
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
returned TRUE. And the following query shows the list of enabled audit options:
col AUDIT_OPTION format A30 SELECT up.AUDIT_OPTION, uep.SUCCESS, uep.FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES uep, AUDIT_UNIFIED_POLICIES up where uep.USER_NAME = 'ALL USERS' and uep.ENABLED_OPT='BY' and uep.POLICY_NAME = up.POLICY_NAME and up.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
or in Oracle 19c (see Oracle 19c Audit changes):
col AUDIT_OPTION format A30 SELECT up.AUDIT_OPTION, uep.SUCCESS, uep.FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES uep, AUDIT_UNIFIED_POLICIES up where uep.ENTITY_NAME = 'ALL USERS' and uep.ENABLED_OPTION='BY' and uep.POLICY_NAME = up.POLICY_NAME and up.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AUDIT_OPTION SUC FAI ------------------------------ --- --- LOGON NO YES CREATE DATABASE LINK YES YES DROP DATABASE LINK YES YES ALTER USER YES YES CREATE ROLE YES YES DROP ROLE YES YES SET ROLE YES YES CREATE PROFILE YES YES DROP PROFILE YES YES ALTER PROFILE YES YES ALTER ROLE YES YES AUDIT_OPTION SUC FAI ------------------------------ --- --- CREATE DIRECTORY YES YES DROP DIRECTORY YES YES ALTER DATABASE LINK YES YES CREATE PLUGGABLE DATABASE YES YES ALTER PLUGGABLE DATABASE YES YES DROP PLUGGABLE DATABASE YES YES CREATE TABLE YES YES DROP TABLE YES YES ALTER TABLE YES YES CREATE VIEW YES YES DROP VIEW YES YES AUDIT_OPTION SUC FAI ------------------------------ --- --- CREATE PROCEDURE YES YES ALTER PROCEDURE YES YES RENAME YES YES ALTER DATABASE YES YES ALTER USER YES YES ALTER SYSTEM YES YES CREATE USER YES YES CREATE ROLE YES YES DROP USER YES YES DROP ROLE YES YES SET ROLE YES YES AUDIT_OPTION SUC FAI ------------------------------ --- --- CREATE TRIGGER YES YES ALTER TRIGGER YES YES DROP TRIGGER YES YES CREATE PROFILE YES YES DROP PROFILE YES YES ALTER PROFILE YES YES DROP PROCEDURE YES YES CREATE MATERIALIZED VIEW YES YES DROP MATERIALIZED VIEW YES YES ALTER ROLE YES YES TRUNCATE TABLE YES YES AUDIT_OPTION SUC FAI ------------------------------ --- --- CREATE FUNCTION YES YES ALTER FUNCTION YES YES DROP FUNCTION YES YES CREATE PACKAGE YES YES ALTER PACKAGE YES YES DROP PACKAGE YES YES CREATE PACKAGE BODY YES YES ALTER PACKAGE BODY YES YES DROP PACKAGE BODY YES YES CREATE DIRECTORY YES YES DROP DIRECTORY YES YES AUDIT_OPTION SUC FAI ------------------------------ --- --- CREATE JAVA YES YES ALTER JAVA YES YES DROP JAVA YES YES PURGE TABLE YES YES CREATE FLASHBACK ARCHIVE YES YES ALTER FLASHBACK ARCHIVE YES YES DROP FLASHBACK ARCHIVE YES YES CREATE PLUGGABLE DATABASE YES YES ALTER PLUGGABLE DATABASE YES YES DROP PLUGGABLE DATABASE YES YES CREATE AUDIT POLICY YES YES AUDIT_OPTION SUC FAI ------------------------------ --- --- ALTER AUDIT POLICY YES YES DROP AUDIT POLICY YES YES GRANT YES YES REVOKE YES YES AUDIT YES YES NOAUDIT YES YES LOGON YES YES LOGOFF YES YES 74 rows selected.
Disabling Unified Auditing:
make -f ins_rdbms.mk uniaud_off ioracle
To determine specific policies the following can be added to the query, for example:
... and up.AUDIT_OPTION in ('ALL', 'GRANT', 'REVOKE')
Also I enabled old auditing with the following commands:
export ORACLE_SID=ORCLCDB cd $ORACLE_HOME/bin ./sqlplus sys as sysdba ALTER SYSTEM SET audit_sys_operations=true SCOPE=spfile; ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE; commit; SHUTDOWN IMMEDIATE STARTUP
Checking if old audit has been enabled:
SELECT VALUE FROM V$PARAMETER WHERE NAME='audit_trail'; show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/oracle/admin/ORCLCDB/adum
p
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
Switching old auditing off:
ALTER SYSTEM SET audit_sys_operations=false SCOPE=spfile; ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE;
If audit_trail is set to XML or XML,EXTENDED the audit records are written in XML format to $ORACLE_BASE/admin/$ORACLE_SID/adump directory where $ORACLE_SID is “ORCLCDB”.
Links:
Oracle Database Sample Schemas: https://github.com/oracle/db-sample-schemas