Oracle | Audit Policies

Oracle Unified Audit Policies Version 19c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Like traditional auditing which is covered on a different library page, see link at page bottom, Audit Policies aka Unified Audit Policies are new to Database 12c and make possible substantial improvements in the way auditing is defined of great value when deploying a container database.

Page Sections

CREATE	DROP	Demo
ALTER

Dependencies

ALL_AUDITED_SYSTEM_ACTIONS	CDB_AUDIT_POLICY_COLUMNS	GV$UNIFIED_AUDIT_TRAIL
ALL_AUDIT_POLICIES	CDB_AUDIT_SESSION	INT$AUDIT_UNIFIED_ENABLED_POL
ALL_AUDIT_POLICY_COLUMNS	CDB_AUDIT_STATEMENT	INT$AUDIT_UNIFIED_POLICIES
ALL_DEF_AUDIT_OPTS	CDB_AUDIT_TRAIL	I_UNIFIED_AUDIT_ACTIONS
ALL_POLICIES	CDB_COMMON_AUDIT_TRAIL	KU$_12AUDIT_POLICY_ENABLE_VIEW
ALL_UNIFIED_AUDIT_ACTIONS	CDB_OBJ_AUDIT_OPTS	KU$_AUDIT_POLICY_ENABLE_T
AUDITABLE_SYSTEM_ACTIONS	CDB_POLICIES	KU$_AUDIT_POLICY_ENABLE_VIEW
AUDIT_ACTIONS AUDIT_UNIFIED_CONTEXTS	CDB_XS_AUDIT_POLICY_OPTIONS	KU$_AUDIT_POLICY_T
AUDIT_UNIFIED_ENABLED_POLICIES	CDB_XS_ENABLED_AUDIT_POLICIES	KU$_AUDIT_POLICY_VIEW
AUDIT_UNIFIED_POLICIES	CDB_XS_ENB_AUDIT_POLICIES	KU$_AUDIT_POL_ROLE_LIST_T
AUDIT_UNIFIED_POLICY_COMMENTS	CDB_STMT_AUDIT_OPTS	KU$_AUDIT_POL_ROLE_T
AUD_POLICY$	CDB_UNIFIED_AUDIT_TRAIL	RLS$
AUD_OBJECT_OPT$	DBA_AUDIT_POLICIES	UNIFIED_AUDIT_TRAIL
CDB_AUDIT_EXISTS	DBA_AUDIT_POLICY_COLUMNS	UNIFIED_MISC_AUDITED_ACTIONS
CDB_AUDIT_MGMT_CLEAN_EVENTS	DBA_POLICIES	USER_AUDIT_POLICIES
CDB_AUDIT_MGMT_CLEANUP_JOBS	DBA_XS_AUDIT_POLICY_OPTIONS	USER_AUDIT_POLICY_COLUMNS
CDB_AUDIT_MGMT_CONFIG_PARAMS	DBA_XS_ENABLED_AUDIT_POLICIES	USER_POLICIES
CDB_AUDIT_MGMT_LAST_ARCH_TS	DBA_XS_ENB_AUDIT_POLICIES	V$UNIFIED_AUDIT_RECORD_FORMAT
CDB_AUDIT_OBJECT	DBMS_FEATURE_UNIFIED_AUDIT	V$UNIFIED_AUDIT_TRAIL
CDB_AUDIT_POLICIES	FGA$	_CURRENT_EDITION_OBJ

System Privileges

Exempt Access Policy	Exempt DML Redaction Policy	Exempt Reaction Policy
Exempt DDL Redaction Policy	Exempt Identity Policy

CREATE

Create a new audit policy

Note: The ACTION_AUDIT clause has substantial flexibility so it is essential to read the docs.

Note: the list of component actions can be found in AUDITABLE_SYSTEM_ACTIONS CREATE AUDIT POLICY <policy_name> [PRIVILEGES <comma_delimited_system_privileges_list>] [<standard_actions | component_actions>] [ROLES <comma_delimited_roles_list>] [WHEN '<audit_condition>' EVALUATE PER <STATEMENT | SESSION | INSTANCE>] [ONLY TOPLEVEL] [CONTAINER = <ALL | CURRENT>];

-- standard actions

      

      ACTIONS <object_actions | ALL> ON DIRECTORY <directory_name>

      

      ACTIONS <object_actions | ALL> ON MINING MODEL [schema_name.] <object_name>

      

      ACTIONS <object_actions | ALL> [schema_name.] <object_name>

      

      ACTIONS <system_action | ALL>

-- component actions

      

      ACTION COMPONENT = <DATAPUMP | DIRECT_LOAD | OLS | XS> <component_action_comma_delimited_list>

      

      ACTION COMPONENT = DV <comma_delimited_list <component_action> ON <object_name>>

CREATE AUDIT POLICY uw_priv_clause PRIVILEGES ALTER ANY TABLE;

      

      CREATE AUDIT POLICY uw_actions_clause ACTIONS LOGOFF, ALL ON sys.user$;

      

      CREATE AUDIT POLICY uw_actions_component ACTIONS COMPONENT = datapump EXPORT;

      

      CREATE AUDIT POLICY uw_role_clause ROLES DBA;

      

      CREATE AUDIT POLICY uw_multi_clause PRIVILEGES ALTER ANY TABLE

      ACTIONS LOGOFF ROLES DBA;

      

      CREATE AUDIT POLICY uw_full_clause PRIVILEGES ALTER ANY TABLE

      ACTIONS LOGOFF ROLES DBA

      WHEN 'SYS_CONTEXT(''USERENV'', ''ISDBA'') = ''TRUE'''

      EVALUATE PER STATEMENT

      CONTAINER = ALL;

ALTER

Add to an existing audit policy ALTER AUDIT POLICY <policy_name> ADD [<privilege_audit_clause>][<action_audit_clause>][<role_audit_clause>];

CREATE AUDIT POLICY uw_priv_clause PRIVILEGES ALTER ANY TABLE;

      

      ALTER AUDIT POLICY uw_priv_clause ADD PRIVILEGES ALTER ANY PROCEDURE;

Drop part of an existing audit policy ALTER AUDIT POLICY <policy_name> DROP [<privilege_audit_clause>][<action_audit_clause>][<role_audit_clause>];

CREATE AUDIT POLICY uw_multi_clause PRIVILEGES ALTER ANY TABLE 

      ACTIONS LOGOFF ROLES DBA;

      

      ALTER AUDIT POLICY uw_multi_clause DROP PRIVILEGES ALTER ANY TABLE;

Drop the policy condition ALTER AUDIT POLICY <policy_name> CONDITION <DROP;

CREATE AUDIT POLICY uw_full_clause PRIVILEGES ALTER ANY TABLE 

      ACTIONS LOGOFF ROLES DBA

      WHEN 'SYS_CONTEXT(''USERENV'', ''ISDBA'') = ''TRUE'''

      EVALUATE PER STATEMENT

      CONTAINER = ALL;

      

      ALTER AUDIT POLICY uw_multi_clause CONDITION DROP;

DROP

Drop an existing audit policy DROP AUDIT POLICY <policy_name>;

DROP AUDIT POLICY uw_secureconfig;

Demo

Configure a 19c Unified Audit Policy

The code at right is copied from rdbms/admin/secconf.sql with formatting to improve readability

Note that "CIS" is the Center for Internet Security whose standards are the basis for basic US Federal Database Security PROMPT Do you wish to configure 11g style Audit Configuration OR PROMPT Do you wish to configure 12c Unified Audit Policies? PROMPT Enter RDBMS_11G for former or RDBMS_UNIAUD for latter DECLARE USER_CHOICE VARCHAR2(100); RDBMS11_CHOICE CONSTANT VARCHAR2(20) := 'RDBMS_11G'; UNIAUD_CHOICE CONSTANT VARCHAR2(20) := 'RDBMS_UNIAUD'; BEGIN USER_CHOICE := '&1'; -- Audit policy to audit user account and privilege management EXECUTE IMMEDIATE 'CREATE AUDIT POLICY ORA_ACCOUNT_MGMT ' || 'ACTIONS CREATE USER, ALTER USER, DROP USER, ' || 'CREATE ROLE, DROP ROLE, ALTER ROLE, ' || 'SET ROLE, GRANT, REVOKE'; EXECUTE IMMEDIATE 'COMMENT ON AUDIT POLICY ORA_ACCOUNT_MGMT IS '|| '''Audit policy containing audit options for auditing account ' || 'management actions '''; -- Audit policy to audit Database parameter settings EXECUTE IMMEDIATE 'CREATE AUDIT POLICY ORA_DATABASE_PARAMETER '|| 'ACTIONS ALTER DATABASE, ALTER SYSTEM, CREATE SPFILE'; EXECUTE IMMEDIATE 'COMMENT ON AUDIT POLICY ORA_DATABASE_PARAMETER IS '|| ''' Audit policy containing audit options to audit changes '|| ' in database parameters'''; -- Audit Logon by failures EXECUTE IMMEDIATE 'CREATE AUDIT POLICY ORA_LOGON_FAILURES ACTIONS LOGON'; EXECUTE IMMEDIATE 'COMMENT ON AUDIT POLICY ORA_LOGON_FAILURES IS '|| '''Audit policy containing audit options to capture logon failures'''; -- Audit policy containing all Secure Configuration audit-options -- Bug 20383779: audit BECOME USER by default in Unified Audit EXECUTE IMMEDIATE 'CREATE AUDIT POLICY ORA_SECURECONFIG ' || 'PRIVILEGES ALTER ANY TABLE, CREATE ANY TABLE, ' || 'DROP ANY TABLE, CREATE ANY PROCEDURE, ' || 'DROP ANY PROCEDURE, ALTER ANY PROCEDURE, '|| 'GRANT ANY PRIVILEGE, ' || 'GRANT ANY OBJECT PRIVILEGE, GRANT ANY ROLE, '|| 'AUDIT SYSTEM, CREATE EXTERNAL JOB, ' || 'CREATE ANY JOB, CREATE ANY LIBRARY, ' || 'EXEMPT ACCESS POLICY, CREATE USER, ' || 'DROP USER, ALTER DATABASE, ALTER SYSTEM, '|| 'CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM, ' || 'CREATE SQL TRANSLATION PROFILE, ' || 'CREATE ANY SQL TRANSLATION PROFILE, ' || 'DROP ANY SQL TRANSLATION PROFILE, ' || 'ALTER ANY SQL TRANSLATION PROFILE, ' || 'TRANSLATE ANY SQL, EXEMPT REDACTION POLICY, ' || 'PURGE DBA_RECYCLEBIN, LOGMINING, ' || 'ADMINISTER KEY MANAGEMENT, BECOME USER ' || 'ACTIONS ALTER USER, CREATE ROLE, ALTER ROLE, DROP ROLE, '|| 'SET ROLE, CREATE PROFILE, ALTER PROFILE, ' || 'DROP PROFILE, CREATE DATABASE LINK, ' || 'ALTER DATABASE LINK, DROP DATABASE LINK, '|| 'CREATE DIRECTORY, DROP DIRECTORY, '|| 'CREATE PLUGGABLE DATABASE, ' || 'DROP PLUGGABLE DATABASE, '|| 'ALTER PLUGGABLE DATABASE, '|| 'EXECUTE ON DBMS_RLS, '|| 'ALTER DATABASE DICTIONARY'; EXECUTE IMMEDIATE 'COMMENT ON AUDIT POLICY ORA_SECURECONFIG IS '|| '''Audit policy containing audit options as per database '|| 'security best practices'''; -- Bug 17299076: audit policy with CIS recommended audit options -- Bug 26040105: Update ORA_CIS_RECOMMENDATIONS policy per V2.0.0 -- (12-28-2016) FOR CIS BENCHMARK EXECUTE IMMEDIATE 'CREATE AUDIT POLICY ORA_CIS_RECOMMENDATIONS '|| 'PRIVILEGES SELECT ANY DICTIONARY, ALTER SYSTEM '|| 'ACTIONS CREATE USER, ALTER USER, DROP USER, ' || 'CREATE ROLE, DROP ROLE, ALTER ROLE, ' || 'GRANT, REVOKE, CREATE DATABASE LINK, '|| 'ALTER DATABASE LINK, DROP DATABASE LINK, '|| 'CREATE PROFILE, ALTER PROFILE, DROP PROFILE, '|| 'CREATE SYNONYM, DROP SYNONYM, '|| 'CREATE PROCEDURE, DROP PROCEDURE, '|| 'ALTER PROCEDURE, ALTER SYNONYM, CREATE FUNCTION, '|| 'CREATE PACKAGE, CREATE PACKAGE BODY, '|| 'ALTER FUNCTION, ALTER PACKAGE, ALTER SYSTEM, '|| 'ALTER PACKAGE BODY, DROP FUNCTION, '|| 'DROP PACKAGE, DROP PACKAGE BODY, '|| 'CREATE TRIGGER, ALTER TRIGGER, '|| 'DROP TRIGGER'; EXECUTE IMMEDIATE 'COMMENT ON AUDIT POLICY ORA_CIS_RECOMMENDATIONS IS '|| '''Audit policy containing audit options as per CIS recommendations'''; IF USER_CHOICE = RDBMS11_CHOICE THEN -- 11g Secure Audit Configuration -- Bug 20383779: audit BECOME USER by default in Traditional Audit EXECUTE IMMEDIATE 'AUDIT ALTER ANY TABLE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT CREATE ANY TABLE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT DROP ANY TABLE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT CREATE ANY PROCEDURE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT DROP ANY PROCEDURE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT ALTER ANY PROCEDURE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT GRANT ANY PRIVILEGE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT GRANT ANY OBJECT PRIVILEGE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT GRANT ANY ROLE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT AUDIT SYSTEM BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT CREATE EXTERNAL JOB BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT CREATE ANY JOB BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT CREATE ANY LIBRARY BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT CREATE PUBLIC DATABASE LINK BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT EXEMPT ACCESS POLICY BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT ALTER USER BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT CREATE USER BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT ROLE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT CREATE SESSION BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT DROP USER BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT ALTER DATABASE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT ALTER SYSTEM BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT ALTER PROFILE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT DROP PROFILE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT DATABASE LINK BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT SYSTEM AUDIT BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT PROFILE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT PUBLIC SYNONYM BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT SYSTEM GRANT BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT CREATE SQL TRANSLATION PROFILE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT CREATE ANY SQL TRANSLATION PROFILE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT DROP ANY SQL TRANSLATION PROFILE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT ALTER ANY SQL TRANSLATION PROFILE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT TRANSLATE ANY SQL BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT PURGE DBA_RECYCLEBIN BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT LOGMINING BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT EXEMPT REDACTION POLICY BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT ADMINISTER KEY MANAGEMENT BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT DIRECTORY BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT PLUGGABLE DATABASE BY ACCESS'; EXECUTE IMMEDIATE 'AUDIT BECOME USER BY ACCESS'; -- Audit configurarion on Common object in PDB is not supported. -- Hence execute AUDIT on DBMS_RLS in non-CDB and CDB$ROOT. IF (SYS_CONTEXT('USERENV', 'CON_ID') in (0,1)) THEN EXECUTE IMMEDIATE 'AUDIT EXECUTE ON DBMS_RLS BY ACCESS'; END IF; ELSIF USER_CHOICE = UNIAUD_CHOICE THEN -- 12c Secure Audit Configuration -- Enable ORA_SECURECONFIG for all users EXECUTE IMMEDIATE 'AUDIT POLICY ORA_SECURECONFIG'; -- Also enable Logon failures. Bug 18174384 EXECUTE IMMEDIATE 'AUDIT POLICY ORA_LOGON_FAILURES WHENEVER NOT SUCCESSFUL'; ELSE DBMS_OUTPUT.PUT_LINE('Invalid Input "' || USER_CHOICE || '". Please try again'); END IF; END; /