This section describes the CREATE
AUDIT
POLICY
statement for unified auditing. This type of auditing is new beginning with Oracle Database 12c and provides a full set of enhanced auditing features. Refer to Oracle Database Security Guide for more information on unified auditing.
Use the CREATE
AUDIT
POLICY
statement to create a unified audit policy.
You must have the AUDIT
SYSTEM
system privilege or the AUDIT_ADMIN
role.
To specify the CONTAINER
clause, you must be connected to a multitenant container database (CDB). To create a common unified audit policy, you must have the commonly granted AUDIT
SYSTEM
privilege or the AUDIT_ADMIN
common role. To create a local unified audit policy, you must have the commonly granted AUDIT
SYSTEM
privilege or the AUDIT_ADMIN
common role, or you must have the locally granted AUDIT
SYSTEM
privilege or the AUDIT_ADMIN
local role in the container to which you are connected.
Note:
You must specify at least one of the clausesprivilege_audit_clause
, action_audit_clause
, or role_audit_clause
.(privilege_audit_clause::=, action_audit_clause::=, role_audit_clause::=)
Note:
You can specify only thestandard_actions
clause, only the component_actions
clause, or both clauses in either order, but you can specify each clause only once.Specify the name of the unified audit policy to be created. The name must satisfy the requirements listed in "Database Object Naming Rules". You can find the names of all unified audit policies by querying the AUDIT_UNIFIED_POLICIES
view.
Use this clause to audit one or more system privileges. SQL statements that require the system privilege(s) in order to succeed are audited. For system_privilege
, specify a valid system privilege. To view all valid system privileges, query the NAME
column of the SYSTEM_PRIVILEGE_MAP
view.
Restriction on Auditing System Privileges You cannot audit the following system privileges: INHERIT
ANY
PRIVILEGES
, SYSASM
, SYSBACKUP
, SYSDBA
, SYSDG
, SYSKM
, SYSOPER
, and TRANSLATE
ANY
SQL
.
Use this clause to specify one or more actions to be audited. Use the standard_actions
clause to audit actions on standard RDBMS objects and to audit standard RDBMS system actions for the database. Use the component_actions
clause to audit actions for components.
Use this clause to audit actions on standard RDBMS objects and to audit standard RDBMS system actions for the database.
object_action ON Use this clause to audit an action on the specified object. For object_action
, specify the action to be audited. Table 14-1 lists the actions that can be audited on each type of object.
ALL ON Use this clause to audit all actions on the specified object. All of the actions listed in Table 14-1 for the type of object that you specify in the ON
clause will be audited.
ON Clause Use the ON
clause to specify the object to be audited. Directories and data mining models are identified separately because they reside in separate namespaces. To audit actions on a directory, specify ON
DIRECTORY
directory_name
. To audit actions on a data mining model, specify ON
MINING
MODEL
object_name
. To audit actions on the other types of objects listed in Table 14-1, specify ON
object_name
. If you do not qualify object_name
with schema
, then the database assumes the object is in your own schema.
Table 14-1 Unified Auditing Objects and Actions
Type of Object | Actions |
---|---|
Directory |
|
Function |
|
Java Schema Objects (Source, Class, Resource) |
|
Library |
|
Materialized Views |
|
Mining Model |
|
Object Type |
|
Package |
|
Procedure |
|
Sequence |
|
Table |
|
View |
|
system_action Use this clause to audit a system action for the database. To view the valid values for system_action
, query the NAME
column of the AUDITABLE_SYSTEM_ACTIONS
view where COMPONENT
is 'Standard
'.
ALL Use this clause to audit all system actions for the database.
Use this clause to audit actions for the following components: Oracle Data Pump, Oracle SQL*Loader Direct Path Load, Oracle Label Security, Oracle Database Real Application Security, and Oracle Database Vault.
DATAPUMP Use this clause to audit actions for Oracle Data Pump. For component_action
, specify the action to be audited. To view the valid actions for Oracle Data Pump, query the NAME
column of the AUDITABLE_SYSTEM_ACTIONS
view where COMPONENT
is Datapump
. For example:
SELECT name FROM auditable_system_actions WHERE component = 'Datapump';
Refer to Oracle Database Security Guide for complete information on auditing Oracle Data Pump.
DIRECT_LOAD Use this clause to audit actions for Oracle SQL*Loader Direct Path Load. For component_action
, specify the action to be audited. To view the valid actions for Oracle SQL*Loader Direct Path Load, query the NAME
column of the AUDITABLE_SYSTEM_ACTIONS
view where COMPONENT
is Direct
path
API
. For example:
SELECT name FROM auditable_system_actions WHERE component = 'Direct path API';
Refer to Oracle Database Security Guide for complete information on auditing Oracle SQL*Loader Direct Path Load.
OLS Use this clause to audit actions for Oracle Label Security. For component_action
, specify the action to be audited. To view the valid actions for Oracle Label Security, query the NAME
column of the AUDITABLE_SYSTEM_ACTIONS
view where COMPONENT
is Label
Security
. For example:
SELECT name FROM auditable_system_actions WHERE component = 'Label Security';
Refer to Oracle Database Security Guide for complete information on auditing Oracle Label Security.
XS Use this clause to audit actions for Oracle Database Real Application Security. For component_action
, specify the action to be audited. To view the valid actions for Oracle Database Real Application Security, query the NAME
column of the AUDITABLE_SYSTEM_ACTIONS
view where COMPONENT
is XS
. For example:
SELECT name FROM auditable_system_actions WHERE component = 'XS';
Refer to Oracle Database Security Guide for complete information on auditing Oracle Database Real Application Security.
DV Use this clause to audit actions for Oracle Database Vault. For component_action
, specify the action to be audited. To view the valid actions for Oracle Database Vault, query the NAME
column of the AUDITABLE_SYSTEM_ACTIONS
view where COMPONENT
is Database
Vault
. For example:
SELECT name FROM auditable_system_actions WHERE component = 'Database Vault';
For object_name
, specify the name of the Database Vault object to be audited.
Refer to Oracle Database Security Guide for complete information on auditing Oracle Database Vault.
Use this clause to specify one or more roles to be audited. When you audit a role, Oracle Database audits all system privileges that are granted directly to the role. SQL statements that require the system privileges in order to succeed are audited. For role
, specify either a user-defined (local or external) or predefined role. For a list of predefined roles, refer to Oracle Database Security Guide.
Use this clause to control when the unified audit policy is enforced.
audit_condition Specify a condition that determines if the unified audit policy is enforced. If audit_condition
evaluates to TRUE
, then the policy is enforced. If FALSE
, then the policy is not enforced.
The audit_condition
can have a maximum length of 4000 characters. It can contain expressions, as well as the following functions and conditions:
Numeric functions: BITAND
, CEIL
, FLOOR
, POWER
Character functions returning character values: CONCAT
, LOWER
, UPPER
Character functions returning number values: INSTR
, LENGTH
Environment and identifier functions: SYS_CONTEXT
, UID
Comparison conditions: =
, !=
, <>
, <
, >
, <=
, >=
Logical conditions: AND
, OR
Null conditions: IS
[NOT]
NULL
[NOT]
BETWEEN
condition
[NOT]
IN
condition
The audit_condition
must be enclosed in single quotation marks. If the audit_condition
contains a single quotation mark, then specify two single quotation marks instead. For example, to specify the following condition:
SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') = 'myclient'
Specify the following for '
audit_condition
'
:
'SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'') = ''myclient'''
EVALUATE PER STATEMENT Specify this clause to evaluate the audit_condition
for each auditable statement. If the audit_condition
evaluates to TRUE
, then the unified audit policy is enforced for the statement. If FALSE
, then the unified audit policy is not enforced for the statement.
EVALUATE PER SESSION Specify this clause to evaluate the audit_condition
once during the session. The audit_condition
is evaluated for the first auditable statement that is executed during the session. If the audit_condition
evaluates to TRUE
, then the unified audit policy is enforced for all applicable statements for the rest of the session. If FALSE
, then the unified audit policy is not enforced for all applicable statements for the rest of the session.
EVALUATE PER INSTANCE Specify this clause to evaluate the audit_condition
once during the lifetime of the instance. The audit_condition
is evaluated for the first auditable statement that is executed during the instance lifetime. If the audit_condition
evaluates to TRUE
, then the unified audit policy is enforced for all applicable statements for the rest of the lifetime of the instance. If FALSE
, then the unified audit policy is not enforced for all applicable statements for the rest of the lifetime of the instance.
Use the CONTAINER
clause to specify the scope of the unified audit policy.
Specify CONTAINER
=
ALL
to create a common unified audit policy. The current container must be the root when you specify this clause. If you specify the ACTIONS
object_action
ON
or ACTIONS
ALL
ON
clause, then you must specify a common object. If you specify the role_audit_clause
, then you must specify a common role.
Specify CONTAINER
=
CURRENT
to create a local unified audit policy in the current container.
If the current container is the root: If you specify the ACTIONS
object_action
ON
or ACTIONS
ALL
ON
clause, then you must specify a common object. If you specify the role_audit_clause
, then you must specify a common role. You cannot specify the clauses ACTIONS
standard_action
, ACTIONS
ALL
, or privilege_audit_clause
.
If you omit this clause and the current container is the root, then CONTAINER
=
ALL
is the default. If you omit the CONTAINER
clause and the current container is a pluggable database (PDB), then CONTAINER
=
CURRENT
is the default.
Note:
You cannot alter the scope of a unified audit policy after it has been created.Auditing System Privileges: Example The following statement creates unified audit policy table_pol
, which audits the system privileges CREATE
ANY
TABLE
and DROP
ANY
TABLE
:
CREATE AUDIT POLICY table_pol PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE;
The following statement verifies that table_pol
now appears in the AUDIT_UNIFIED_POLICIES
view:
SELECT * FROM audit_unified_policies WHERE policy_name = 'TABLE_POL';
Auditing Actions on Objects: Examples The following statement creates unified audit policy dml_pol
, which audits DELETE
, INSERT
, and UPDATE
actions on table hr
.employees
, and all auditable actions on table hr
.departments
:
CREATE AUDIT POLICY dml_pol ACTIONS DELETE on hr.employees, INSERT on hr.employees, UPDATE on hr.employees, ALL on hr.departments;
The following statement creates unified audit policy read_dir_pol
, which audits READ
actions on directory bfile_dir
(created in "Creating a Directory: Examples"):
CREATE AUDIT POLICY read_dir_pol ACTIONS READ ON DIRECTORY bfile_dir;
Auditing System Actions: Examples The following query displays the standard RDBMS system actions that can be audited for the database:
SELECT name FROM auditable_system_actions WHERE component = 'Standard' ORDER BY name; NAME ---- ADMINISTER KEY MANAGEMENT ALL ALTER ASSEMBLY ALTER AUDIT POLICY ALTER CLUSTER ...
The following statement creates unified audit policy security_pol
, which audits the system action ADMINISTER
KEY
MANAGEMENT
:
CREATE AUDIT POLICY security_pol ACTIONS ADMINISTER KEY MANAGEMENT;
The following statement creates unified audit policy all_actions_pol
, which audits all standard RDBMS system actions for the database:
CREATE AUDIT POLICY all_actions_pol ACTIONS ALL;
Auditing Component Actions: Example The following query displays the actions that can be audited for Oracle Data Pump:
SELECT name FROM auditable_system_actions WHERE component = 'Datapump'; NAME ---- EXPORT IMPORT ALL
The following statement creates unified audit policy dp_actions_pol
, which audits IMPORT
actions for Oracle Data Pump:
CREATE AUDIT POLICY dp_actions_pol ACTIONS COMPONENT = datapump IMPORT;
Auditing Roles: Example The following statement creates unified audit policy java_pol
, which audits the predefined roles java_admin
and java_deploy
:
CREATE AUDIT POLICY java_pol ROLES java_admin, java_deploy;
Auditing System Privileges, Actions, and Roles: Example The following statement creates unified audit policy hr_admin_pol
, which audits multiple system privileges, actions, and roles:
CREATE AUDIT POLICY hr_admin_pol PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE ACTIONS DELETE on hr.employees, INSERT on hr.employees, UPDATE on hr.employees, ALL on hr.departments, LOCK TABLE ROLES audit_admin, audit_viewer;
Controlling When a Unified Audit Policy is Enforced: Examples The following statement creates unified audit policy order_updates_pol
, which audits UPDATE
actions on table oe
.orders
. This policy is enforced only when the auditable statement is issued by an external user. The audit condition is checked once per session.
CREATE AUDIT POLICY order_updates_pol ACTIONS UPDATE ON oe.orders WHEN 'SYS_CONTEXT(''USERENV'', ''IDENTIFICATION_TYPE'') = ''EXTERNAL''' EVALUATE PER SESSION;
The following statement creates unified audit policy emp_updates_pol
, which audits DELETE
, INSERT
, and UPDATE
actions on table hr
.employees
. This policy is enforced only when the auditable statement is issued by a user who does not have a UID of 100, 105, or 107. The audit condition is checked for each auditable statement.
CREATE AUDIT POLICY emp_updates_pol ACTIONS DELETE on hr.employees, INSERT on hr.employees, UPDATE on hr.employees WHEN 'UID NOT IN (100, 105, 107)' EVALUATE PER STATEMENT;
Creating a Local Unified Audit Policy: Example The following statement creates local unified audit policy local_table_pol
, which audits the system privileges CREATE
ANY
TABLE
and DROP
ANY
TABLE
in the current container::
CREATE AUDIT POLICY local_table_pol PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE CONTAINER = CURRENT;
Creating a Common Unified Audit Policy: Example The following statement creates common unified audit policy common_role1_pol
, which audits the common role c##role1
(created in CREATE
ROLE
"Examples") across the entire CDB:
CREATE AUDIT POLICY common_role1_pol ROLES c##role1 CONTAINER = ALL;