Oracle Audit Policies
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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 allow make possible substantial improvements in the way auditing is defined of special value when deploying a container database.
Page Sections
Dependencies
ALL_AUDITED_SYSTEM_ACTIONS AUD_OBJECT_OPT$  
ALL_AUDIT_POLICIES CDB_AUDIT_MGMT_CLEAN_EVENTS CDB_STMT_AUDIT_OPTS
ALL_AUDIT_POLICY_COLUMNS CDB_AUDIT_MGMT_CONFIG_PARAMS CDB_UNIFIED_AUDIT_TRAIL
ALL_DEF_AUDIT_OPTS CDB_AUDIT_MGMT_LAST_ARCH_TS DBA_POLICIES
ALL_POLICIES CDB_AUDIT_OBJECT FGA$
AUDITABLE_SYSTEM_ACTIONS CDB_AUDIT_POLICIES GV$UNIFIED_AUDIT_TRAIL
AUDIT_UNIFIED_CONTEXTS CDB_AUDIT_POLICY_COLUMNS KU$_AUDIT_POLICY_VIEW
AUDIT_UNIFIED_ENABLED_POLICIES CDB_AUDIT_SESSION RLS$
AUDIT_UNIFIED_POLICIES CDB_AUDIT_STATEMENT USER_POLICIES
AUDIT_UNIFIED_POLICY_COMMENTS CDB_AUDIT_TRAIL V$UNIFIED_AUDIT_RECORD_FORMAT
AUD_OBJECT_OPT$ CDB_COMMON_AUDIT_TRAIL V$UNIFIED_AUDIT_TRAIL
CDB_AUDIT_EXISTS CDB_OBJ_AUDIT_OPTS _CURRENT_EDITION_OBJ
CDB_AUDIT_MGMT_CLEANUP_JOBS CDB_POLICIES  
Role Privileges AUD_POLICY$
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.
CREATE AUDIT POLICY <policy_name>
POLICY [<privilege_audit_clause>][<action_audit_clause>][<role_audit_clause>]
[WHEN '<audit_condition>' EVALUATE PER <STATEMENT | SESSION | INSTANCE>]
[CONTAINER = <ALL | CURRENT>];
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 12c 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
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';

  -- Audit policy to audit Database parameter settings
  EXECUTE IMMEDIATE
    'CREATE AUDIT POLICY ORA_DATABASE_PARAMETER '||
    'ACTIONS ALTER DATABASE, ALTER SYSTEM, CREATE SPFILE';

  -- Audit Logon by failures
  EXECUTE IMMEDIATE
    'CREATE AUDIT POLICY ORA_LOGON_FAILURES ACTIONS LOGON';

  -- Audit policy containing all Secure Configuration audit-options
  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, ' ||
    '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 ' ||
    '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';

  -- Bug 17299076: audit policy with CIS recommended audit options
  EXECUTE IMMEDIATE
    'CREATE AUDIT POLICY ORA_CIS_RECOMMENDATIONS '||
    'PRIVILEGES SELECT ANY DICTIONARY, CREATE ANY LIBRARY, '||
    'DROP ANY LIBRARY, CREATE ANY TRIGGER, '||
    'ALTER ANY TRIGGER, DROP ANY TRIGGER, '||
    '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';

  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
    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 EXECUTE ON DBMS_RLS BY ACCESS';
  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;
/

Related Topics
Auditing
Security

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved