Create and enable audit policies
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 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.