Overview
This procedure shows how to create and enable a common auditing policy. This policy is then active for all containers. The auditing log is partitioned by container.
References
- Oracle ® Database 12.1 Reference
- Oracle ® Database SQL Language Reference 12.1
- Oracle ® Database Security Guide
Important Note
Note:
The base Oracle home must be patched before Unified Auditing becomes active. See " Apply and Review Patches " for the application of the PSU used in this procedure.
Procedure
Verify Unified Auditing is Active
One way to verify that Unified Auditing is active is review the banner at the start of a SQL*Plus session:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Another way to verify is to use the following SQL query:
select value from v$option where parameter='Unified Auditing';
The expected result is:
VALUE ---------------------------------------------------------------- TRUE
See Initial Set of Audit Policies
To see what the initial set of audit policies is, use the following SQL query:
SET PAGESIZE 150
SET LINESIZE 132
COLUMN POLICY_NAME FORMAT A30
COLUMN AUDIT_OPTION FORMAT A40
SELECT
policy_name,
audit_option,
condition_eval_opt
FROM
audit_unified_policies;
The expected result is:
POLICY_NAME AUDIT_OPTION CONDITION ------------------------------ ---------------------------------------- --------- ORA_SECURECONFIG LOGMINING NONE ORA_SECURECONFIG TRANSLATE ANY SQL NONE ORA_SECURECONFIG EXEMPT REDACTION POLICY NONE ORA_SECURECONFIG PURGE DBA_RECYCLEBIN NONE ORA_SECURECONFIG ADMINISTER KEY MANAGEMENT NONE ORA_SECURECONFIG DROP ANY SQL TRANSLATION PROFILE NONE ORA_SECURECONFIG ALTER ANY SQL TRANSLATION PROFILE NONE ORA_SECURECONFIG CREATE ANY SQL TRANSLATION PROFILE NONE ORA_SECURECONFIG CREATE SQL TRANSLATION PROFILE NONE ORA_SECURECONFIG CREATE EXTERNAL JOB NONE ORA_SECURECONFIG CREATE ANY JOB NONE ORA_SECURECONFIG GRANT ANY OBJECT PRIVILEGE NONE ORA_SECURECONFIG EXEMPT ACCESS POLICY NONE ORA_SECURECONFIG CREATE ANY LIBRARY NONE ORA_SECURECONFIG GRANT ANY PRIVILEGE NONE ORA_SECURECONFIG DROP ANY PROCEDURE NONE ORA_SECURECONFIG ALTER ANY PROCEDURE NONE ORA_SECURECONFIG CREATE ANY PROCEDURE NONE ORA_SECURECONFIG ALTER DATABASE NONE ORA_SECURECONFIG GRANT ANY ROLE NONE ORA_SECURECONFIG DROP PUBLIC SYNONYM NONE ORA_SECURECONFIG CREATE PUBLIC SYNONYM NONE ORA_SECURECONFIG DROP ANY TABLE NONE ORA_SECURECONFIG ALTER ANY TABLE NONE ORA_SECURECONFIG CREATE ANY TABLE NONE ORA_SECURECONFIG DROP USER NONE ORA_SECURECONFIG CREATE USER NONE ORA_SECURECONFIG AUDIT SYSTEM NONE ORA_SECURECONFIG ALTER SYSTEM NONE ORA_CIS_RECOMMENDATIONS SELECT ANY DICTIONARY NONE ORA_CIS_RECOMMENDATIONS DROP ANY LIBRARY NONE ORA_CIS_RECOMMENDATIONS CREATE ANY LIBRARY NONE ORA_CIS_RECOMMENDATIONS DROP ANY TRIGGER NONE ORA_CIS_RECOMMENDATIONS ALTER ANY TRIGGER NONE ORA_CIS_RECOMMENDATIONS CREATE ANY TRIGGER NONE ORA_CIS_RECOMMENDATIONS ALTER SYSTEM NONE ORA_RAS_POLICY_MGMT CREATE USER NONE ORA_RAS_POLICY_MGMT UPDATE USER NONE ORA_RAS_POLICY_MGMT DELETE USER NONE ORA_RAS_POLICY_MGMT CREATE ROLE NONE ORA_RAS_POLICY_MGMT UPDATE ROLE NONE ORA_RAS_POLICY_MGMT DELETE ROLE NONE ORA_RAS_POLICY_MGMT GRANT ROLE NONE ORA_RAS_POLICY_MGMT REVOKE ROLE NONE ORA_RAS_POLICY_MGMT ADD PROXY NONE ORA_RAS_POLICY_MGMT REMOVE PROXY NONE ORA_RAS_POLICY_MGMT SET USER PASSWORD NONE ORA_RAS_POLICY_MGMT SET USER VERIFIER NONE ORA_RAS_POLICY_MGMT CREATE ROLESET NONE ORA_RAS_POLICY_MGMT UPDATE ROLESET NONE ORA_RAS_POLICY_MGMT DELETE ROLESET NONE ORA_RAS_POLICY_MGMT CREATE SECURITY CLASS NONE ORA_RAS_POLICY_MGMT UPDATE SECURITY CLASS NONE ORA_RAS_POLICY_MGMT DELETE SECURITY CLASS NONE ORA_RAS_POLICY_MGMT CREATE NAMESPACE TEMPLATE NONE ORA_RAS_POLICY_MGMT UPDATE NAMESPACE TEMPLATE NONE ORA_RAS_POLICY_MGMT DELETE NAMESPACE TEMPLATE NONE ORA_RAS_POLICY_MGMT CREATE ACL NONE ORA_RAS_POLICY_MGMT UPDATE ACL NONE ORA_RAS_POLICY_MGMT DELETE ACL NONE ORA_RAS_POLICY_MGMT CREATE DATA SECURITY NONE ORA_RAS_POLICY_MGMT UPDATE DATA SECURITY NONE ORA_RAS_POLICY_MGMT DELETE DATA SECURITY NONE ORA_RAS_POLICY_MGMT ENABLE DATA SECURITY NONE ORA_RAS_POLICY_MGMT DISABLE DATA SECURITY NONE ORA_RAS_POLICY_MGMT ADD GLOBAL CALLBACK NONE ORA_RAS_POLICY_MGMT DELETE GLOBAL CALLBACK NONE ORA_RAS_POLICY_MGMT ENABLE GLOBAL CALLBACK NONE ORA_RAS_POLICY_MGMT SET USER PROFILE NONE ORA_RAS_SESSION_MGMT ROLE NONE ORA_RAS_SESSION_MGMT DISABLE ROLE NONE ORA_RAS_SESSION_MGMT SET COOKIE NONE ORA_RAS_SESSION_MGMT SET INACTIVE TIMEOUT NONE ORA_RAS_SESSION_MGMT CREATE SESSION NONE ORA_RAS_SESSION_MGMT DESTROY SESSION NONE ORA_RAS_SESSION_MGMT SWITCH USER NONE ORA_RAS_SESSION_MGMT ASSIGN USER NONE ORA_RAS_SESSION_MGMT CREATE SESSION NAMESPACE NONE ORA_RAS_SESSION_MGMT DELETE SESSION NAMESPACE NONE ORA_RAS_SESSION_MGMT CREATE NAMESPACE ATTRIBUTE NONE ORA_RAS_SESSION_MGMT GET NAMESPACE ATTRIBUTE NONE ORA_RAS_SESSION_MGMT SET NAMESPACE ATTRIBUTE NONE ORA_RAS_SESSION_MGMT DELETE NAMESPACE ATTRIBUTE NONE ORA_ACCOUNT_MGMT ALTER USER NONE ORA_ACCOUNT_MGMT CREATE USER NONE ORA_ACCOUNT_MGMT CREATE ROLE NONE ORA_ACCOUNT_MGMT DROP USER NONE ORA_ACCOUNT_MGMT DROP ROLE NONE ORA_ACCOUNT_MGMT SET ROLE NONE ORA_ACCOUNT_MGMT ALTER ROLE NONE ORA_ACCOUNT_MGMT GRANT NONE ORA_ACCOUNT_MGMT REVOKE NONE ORA_DATABASE_PARAMETER ALTER DATABASE NONE ORA_DATABASE_PARAMETER ALTER SYSTEM NONE ORA_DATABASE_PARAMETER CREATE SPFILE NONE ORA_LOGON_FAILURES LOGON NONE ORA_SECURECONFIG CREATE DATABASE LINK NONE ORA_SECURECONFIG DROP DATABASE LINK NONE ORA_SECURECONFIG ALTER USER NONE ORA_SECURECONFIG CREATE ROLE NONE ORA_SECURECONFIG DROP ROLE NONE ORA_SECURECONFIG SET ROLE NONE ORA_SECURECONFIG CREATE PROFILE NONE ORA_SECURECONFIG DROP PROFILE NONE ORA_SECURECONFIG ALTER PROFILE NONE ORA_SECURECONFIG ALTER ROLE NONE ORA_SECURECONFIG CREATE DIRECTORY NONE ORA_SECURECONFIG DROP DIRECTORY NONE ORA_SECURECONFIG ALTER DATABASE LINK NONE ORA_SECURECONFIG CREATE PLUGGABLE DATABASE NONE ORA_SECURECONFIG ALTER PLUGGABLE DATABASE NONE ORA_SECURECONFIG DROP PLUGGABLE DATABASE NONE ORA_CIS_RECOMMENDATIONS CREATE SYNONYM NONE ORA_CIS_RECOMMENDATIONS DROP SYNONYM NONE ORA_CIS_RECOMMENDATIONS CREATE PROCEDURE NONE ORA_CIS_RECOMMENDATIONS ALTER PROCEDURE NONE ORA_CIS_RECOMMENDATIONS CREATE DATABASE LINK NONE ORA_CIS_RECOMMENDATIONS DROP DATABASE LINK NONE ORA_CIS_RECOMMENDATIONS ALTER USER NONE ORA_CIS_RECOMMENDATIONS CREATE USER NONE ORA_CIS_RECOMMENDATIONS CREATE ROLE NONE ORA_CIS_RECOMMENDATIONS DROP USER NONE ORA_CIS_RECOMMENDATIONS DROP ROLE NONE ORA_CIS_RECOMMENDATIONS CREATE PROFILE NONE ORA_CIS_RECOMMENDATIONS DROP PROFILE NONE ORA_CIS_RECOMMENDATIONS ALTER PROFILE NONE ORA_CIS_RECOMMENDATIONS DROP PROCEDURE NONE ORA_CIS_RECOMMENDATIONS ALTER ROLE NONE ORA_CIS_RECOMMENDATIONS ALTER DATABASE LINK NONE ORA_CIS_RECOMMENDATIONS GRANT NONE ORA_CIS_RECOMMENDATIONS REVOKE NONE ORA_SECURECONFIG EXECUTE NONE 132 rows selected.
Find Currently Enabled Policies
To see what the initial set of enabled audit policies is, use the following SQL query:
SELECT policy_name FROM audit_unified_enabled_policies;
The expected result is:
POLICY_NAME -------------------------------------------------------------------------------- ORA_SECURECONFIG ORA_LOGON_FAILURES
Scenario
I am going to create a common user and a common audit policy on the JAR database on PADSTOW .
Set Up User
I used the following SQL on the JAR database to create the C##USER common user which can connect to all containers, and select from any table as well as the data dictionary:
prompt Connect to Root Container ALTER SESSION SET container = cdb$root; prompt Create Common User CREATE USER c##user IDENTIFIED BY "&PW." DEFAULT TABLESPACE users QUOTA UNLIMITED ON users CONTAINER = ALL; prompt Grant Privileges to Common User GRANT CREATE SESSION, ALTER SESSION, SET CONTAINER, SELECT ANY TABLE, SELECT ANY DICTIONARY TO c##user CONTAINER = ALL;
Create and Enable Global Audit Policy
I used the following SQL to create and enable a common audit policy called OCP12C_GLOBAL_SELECT_ANY which monitors users use system privileges to select from any user table or from the data dictionary:
prompt Connect to Root Container ALTER SESSION SET container = cdb$root; prompt Create Common Audit Policy CREATE AUDIT POLICY ocp12c_global_select_any PRIVILEGES SELECT ANY TABLE, SELECT ANY DICTIONARY CONTAINER = ALL; prompt Enable Common Audit Policy AUDIT POLICY ocp12c_global_select_any;
List Active Policies in Root Container
Ran the following SQL to see what Unified Auditing Policies are active in the ROOT container:
ALTER SESSION SET container = cdb$root;
SELECT
policy_name
FROM
audit_unified_enabled_policies;
The output was:
POLICY_NAME ------------------------- ORA_SECURECONFIG ORA_LOGON_FAILURES OCP12C_GLOBAL_SELECT_ANY
The OCP12C_GLOBAL_SELECT_ANY audit policy is enabled.
List Active Policies in PLUM PDB
Ran the following SQL to see what Unified Auditing Policies are active in the PLUM PDB:
ALTER SESSION SET container = plum;
SELECT
policy_name
FROM
audit_unified_enabled_policies;
The output was:
POLICY_NAME ------------------ ORA_SECURECONFIG ORA_LOGON_FAILURES
Only the default policies are seen from the PLUM PDB. Note that the common audit policy that we created is not shown here.
Test Case
I ran the following SQL to use the SELECT ANY DICTIONARY system privilege in both the root container and the PLUM PDB:
SELECT
COUNT(*)
FROM
dba_objects;
Display Audit Log Entries in Root Container
I used the following SQL to show the captured audit log entries from the root container for the common audit policy that we created:
COLUMN audit_type FORMAT A8
COLUMN action_name FORMAT A11
COLUMN sql_text FORMAT A40 TRUNC
COLUMN unified_audit_policies FORMAT A25
COLUMN system_privilege_used FORMAT A25
SET PAGESIZE 50
ALTER SESSION SET CONTAINER = cdb$root;
SELECT
event_timestamp,
audit_type,
action_name,
sql_text,
system_privilege_used,
unified_audit_policies
FROM
unified_audit_trail
WHERE
event_timestamp > systimestamp - INTERVAL '6' HOUR
AND dbusername = 'C##USER'
AND system_privilege_used IS NOT NULL
ORDER BY
event_timestamp
/
The output was:
EVENT_TIMESTAMP AUDIT_TY ACTION_NAME SQL_TEXT SYSTEM_PRIVILEGE_USED UNIFIED_AUDIT_POLICIES
------------------------------- -------- ----------- ---------------------------------------- ------------------------- -------------------------
19/MAR/20 09:26:02.312340000 PM Standard SELECT SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
select inst_cou
19/MAR/20 09:26:03.623924000 PM Standard SELECT SELECT ANY TABLE OCP12C_GLOBAL_SELECT_ANY
select tab_coun
19/MAR/20 09:26:04.122990000 PM Standard SELECT SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
select dg_count
19/MAR/20 09:26:04.988468000 PM Standard SELECT select 1 from sys.obj$ where 1=0 SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
19/MAR/20 09:27:21.889879000 PM Standard SELECT select 1 FROM sys.DBA_OBJECTS WHERE 1=0 SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
19/MAR/20 09:27:22.786376000 PM Standard SELECT SELECT object_type type, owner, object_n SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
19/MAR/20 09:27:29.060462000 PM Standard SELECT SELECT object_type type, owner, object_n SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
19/MAR/20 09:27:31.454319000 PM Standard SELECT SELECT object_type type, owner, object_n SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
19/MAR/20 09:27:32.926793000 PM Standard SELECT /* SQL Analyze(73,0) */ select count(*) SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
19/MAR/20 09:28:47.022173000 PM Standard SELECT /* SQL Analyze(73,0) */ SELECT SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
COUNT
19/MAR/20 09:29:07.942621000 PM Standard SELECT SELECT SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
COUNT(*)
FROM
dba_objects
Display Audit Log Entires in PLUM
I used the following SQL to show the captured audit log entries from the PLUM PDB for the common audit policy that we created:
COLUMN audit_type FORMAT A8
COLUMN action_name FORMAT A11
COLUMN sql_text FORMAT A40 TRUNC
COLUMN unified_audit_policies FORMAT A25
COLUMN system_privilege_used FORMAT A25
SET PAGESIZE 50
ALTER SESSION SET CONTAINER = plum;
SELECT
event_timestamp,
audit_type,
action_name,
sql_text,
system_privilege_used,
unified_audit_policies
FROM
unified_audit_trail
WHERE
event_timestamp > systimestamp - INTERVAL '6' HOUR
AND dbusername = 'C##USER'
AND system_privilege_used IS NOT NULL
ORDER BY
event_timestamp
/
The output is:
EVENT_TIMESTAMP AUDIT_TY ACTION_NAME SQL_TEXT SYSTEM_PRIVILEGE_USED UNIFIED_AUDIT_POLICIES
------------------------------- -------- ----------- ---------------------------------------- ------------------------- -------------------------
19/MAR/20 09:26:26.337470000 PM Standard SELECT SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
select inst_cou
19/MAR/20 09:26:27.152776000 PM Standard SELECT SELECT ANY TABLE OCP12C_GLOBAL_SELECT_ANY
select tab_coun
19/MAR/20 09:26:27.649471000 PM Standard SELECT SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
select dg_count
19/MAR/20 09:26:28.893543000 PM Standard SELECT select 1 from sys.obj$ where 1=0 SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
19/MAR/20 09:29:08.279910000 PM Standard SELECT SELECT SELECT ANY DICTIONARY OCP12C_GLOBAL_SELECT_ANY
COUNT(*)
FROM
dba_objects
Note that there is some similarity between the log entries captured as well as some overlap in timestamps. The root container entries may include the hard parsing for the test SQL. However when the audit log entries were examined for another PDB, there were none found.