Unified Auditing with Configurable Conditions

You can use the CREATE AUDIT POLICY statement to create conditions for a unified audit policy.

About Conditions in Unified Audit Policies

You can use conditions in unified audit policies to create focused and selective audit policies.

You can use the CREATE AUDIT POLICY statement to create conditions for a unified audit policy. For example, you can create policy that audits only when access is from a specific host or IP address. If the audit condition is satisfied, then only then the audit record is generated for the event. As part of the condition definition, you must specify whether the audited condition is evaluated per statement occurrence, session, or database instance.

Note: Audit conditions can use attributes from the USERENV namespace, or from named application contexts (both secure and insecure).

Configuring a Unified Audit Policy with a Condition

The WHEN clause in the CREATE AUDIT POLICY statement defines the condition in the audit policy.

Use the following syntax to create a unified audit policy that uses a condition:

CREATE AUDIT POLICY policy_name
 action_privilege_role_audit_option
[WHEN function_operation_value_list_1 [[AND | OR] function_operation_value_list_n]
 EVALUATE PER STATEMENT | SESSION | INSTANCE];

In this specification:

For example:

CREATE AUDIT POLICY oe_orders_pol
 ACTIONS UPDATE ON OE.ORDERS
 WHEN 'SYS_CONTEXT(''USERENV'', ''IDENTIFICATION_TYPE'') = ''EXTERNAL'''
 EVALUATE PER STATEMENT;

Remember that after you create the policy, you must use the AUDIT statement to enable it.

Related Topics

Example: Auditing Access to SQL*Plus

The CREATE AUDIT POLICY statement can audit access to SQLPlus. The following example shows how to audit access to the database with SQLPlus by users who have been directly granted the roles emp_admin and sales_admin.

CREATE AUDIT POLICY logon_pol
 ACTIONS LOGON
 WHEN 'INSTR(UPPER(SYS_CONTEXT(''USERENV'', ''CLIENT_PROGRAM_NAME'')), ''SQLPLUS'') > 0'
 EVALUATE PER SESSION;

AUDIT POLICY logon_pol BY USERS WITH GRANTED ROLES emp_admin, sales_admin;

Example: Auditing Actions Not in Specific Hosts

The CREATE AUDIT POLICY statement can audit actions that are not in specific hosts. The following example shows how to audit two actions (UPDATE and DELETE statements) on the OE.ORDERS table, but excludes the host names sales_24 and sales_12 from the audit. It performs the audit on a per session basis and writes audit records for failed attempts only.

CREATE AUDIT POLICY oe_table_audit1
 ACTIONS UPDATE ON OE.ORDERS, DELETE ON OE.ORDERS
 WHEN 'SYS_CONTEXT (''USERENV'', ''HOST'') NOT IN (''sales_24'',''sales_12'')'
 EVALUATE PER SESSION;

AUDIT POLICY oe_table_audit1 WHENEVER NOT SUCCESSFUL;

Example: Auditing Both a System-Wide and a Schema-Specific Action

The CREATE AUDIT POLICY statement can audit both system-wide and schema-specific actions. The following example shows a variation of the preceding example in which the UPDATE statement is audited system wide. The DELETE statement audit is still specific to the OE.ORDERS table.

CREATE AUDIT POLICY oe_table_audit2
 ACTIONS UPDATE, DELETE ON OE.ORDERS
 WHEN 'SYS_CONTEXT (''USERENV'', ''HOST'') NOT IN (''sales_24'',''sales_12'')'
 EVALUATE PER SESSION;

AUDIT POLICY oe_table_audit2;

Example: Auditing a Condition Per Statement Occurrence

The CREATE AUDIT POLICY statement can audit conditions. The following example shows how to audit a condition based on each occurrence of the DELETE statement on the OE.ORDERS table and exclude user jmartin from the audit.

CREATE AUDIT POLICY sales_clerk_pol
 ACTIONS DELETE ON OE.ORDERS
 WHEN 'SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'') = ''sales_clerk'''
 EVALUATE PER STATEMENT;

AUDIT POLICY sales_clerk_pol EXCEPT jmartin;

Example: Unified Audit Session ID of a Current Administrative User Session

The SYS_CONTEXT function can be used to find session IDs. The following example shows how to find the unified audit session ID of current user session for an administrative user.

CONNECT SYS AS SYSDBA
Enter password: password

SELECT SYS_CONTEXT('USERENV', 'UNIFIED_AUDIT_SESSIONID') FROM DUAL;

Output similar to the following appears:

SYS_CONTEXT('USERENV','UNIFIED_AUDIT_SESSIONID')
--------------------------------------------------------------------------------
6094699107174170004

Example: Unified Audit Session ID of a Current Non-Administrative User Session

The SYS_CONTEXT function can find the session ID of a current non-administrative user session. The following example shows how to find the unified audit session ID of a current user session for a non-administrative user.

CONNECT mblake@pdb_name
Enter password: password

SELECT SYS_CONTEXT('USERENV', 'UNIFIED_AUDIT_SESSIONID') FROM DUAL;

Output similar to the following appears:

SYS_CONTEXT('USERENV','UNIFIED_AUDIT_SESSIONID')
--------------------------------------------------------------------------------
6094699107174170004

How Audit Records from Conditions Appear in the Audit Trail

The audit record conditions from a unified audit policy do not appear in the audit trail.

If the condition evaluates to true and the record is written, then the record appears in the audit trail. You can check the audit trail by querying the UNIFIED_AUDIT_TRAIL data dictionary view.

Related Topics

Auditing for Multitier or Multitenant Configurations

You can create unified audit policies using conditions and application contexts, and in multitier and multitenant environments.