Oracle Audit Policies
Version 12.1.0.1

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  
Page Sections
Dependencies
ALL_POLICIES CDB_POLICIES USER$
AUDITABLE_SYSTEM_ACTIONS
AUDIT_UNIFIED_POLICIES
DBA_POLICIES USER_POLICIES
AUD_OBJECT_OPT$ FGA$ _CURRENT_EDITION_OBJ
AUD_POLICY$ RLS$  
Role Privileges AUDIT SYSTEM or AUDIT ADMIN
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 a slightly rewritten version of Oracle's secconf.sql
BEGIN
  -- 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 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, '||
            'LOGON, LOGOFF, CREATE DIRECTORY, DROP DIRECTORY, '||
            'CREATE PLUGGABLE DATABASE, ' ||
            'DROP PLUGGABLE DATABASE, '||
            'ALTER PLUGGABLE DATABASE ';

  -- Enable ORA_SECURECONFIG for all users
  EXECUTE IMMEDIATE 'AUDIT POLICY ORA_SECURECONFIG';
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