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

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.