Extending Unified Auditing to Capture Custom Attributes

You can extend the unified audit trail to capture custom attributes by auditing application context values.

About Auditing Application Context Values

In many cases, you may want to bring your custom attributes into the unified audit trail while auditing (for example, application attributes from the application session).

You can extend the unified audit trail to capture such custom attributes by auditing application context values. This feature enables you to capture any application context values set by the database applications, while executing the audited statement.

This feature enables you to capture any application context values set by the database applications, while executing the audited statement.

If you plan to audit Oracle Label Security, then this feature captures session label activity for the database audit trail. The audit trail records all the values retrieved for the specified context-attribute value pairs.

The application context audit setting or the audit policy have session static semantics. In other words, if a new policy is enabled for a user, then the subsequent user sessions will see an effect of this command. After the session is established, then the policies and contexts settings are loaded and the subsequent AUDIT statements have no effect on that session.

Note that the application context audit policy applies only to the current PDB.

Related Topics

Configuring Application Context Audit Settings

The AUDIT statement with the CONTEXT keyword configures auditing for application context values.

You do not create an unified audit policy for this type of auditing.

Use the following syntax to configure auditing for application context values:

AUDIT CONTEXT NAMESPACE context_name1 ATTRIBUTES attribute1 [, attribute2]
 [, CONTEXT NAMESPACE context_name2 ATTRIBUTES attribute1 [, attribute2]]
 [BY user_list];

In this specification:

For example:

AUDIT CONTEXT NAMESPACE clientcontext3 ATTRIBUTES module, action,
 CONTEXT NAMESPACE ols_session_labels ATTRIBUTES ols_pol1, ols_pol3
 BY appuser1, appuser2;

To find a list of currently configured application context audit settings, query the AUDIT_UNIFIED_CONTEXTS data dictionary view.

Disabling Application Context Audit Settings

The NOAUDIT statement disables application context audit settings.

To disable an application context audit setting, specify the namespace and attribute settings in the NOAUDIT statement. You can enter the attributes in any order (that is, they do not need to match the order used in the corresponding AUDIT CONTEXT statement.)

For example:

NOAUDIT CONTEXT NAMESPACE client_context ATTRIBUTES module,
CONTEXT NAMESPACE ols_session_labels ATTRIBUTES ols_pol1, ols_pol3
BY USERS WITH GRANTED ROLES emp_admin;

To find the currently audited application contexts, query the AUDIT_UNIFIED_CONTEXTS data dictionary view.

Example: Auditing Application Context Values in a Default Database

The AUDIT CONTEXT NAMESPACE statement can audit application context values. The following example shows how to audit the clientcontext application values for the module and action attributes, by the user appuser1.

AUDIT CONTEXT NAMESPACE clientcontext ATTRIBUTES module, action
BY appuser1;

Example: Auditing Application Context Values from Oracle Label Security

The AUDIT CONTEXT NAMESPACE statement can audit application context values from Oracle Label Security. The following example shows how to audit an application context for Oracle Label Security called ORA_OLS_SESSION_LABELS, for the attributes ols_pol1 and ols_pol2.

AUDIT CONTEXT NAMESPACE ORA_OLS_SESSION_LABELS ATTRIBUTES ols_pol1, ols_pol2;

How Audited Application Contexts Appear in the Audit Trail

The UNIFIED_AUDIT_POLICIES data dictionary view lists application context audit events.

The APPLICATION_CONTEXTS column of the UNIFIED_AUDIT_TRAIL data dictionary view shows application context audit data. The application contexts appear as a list of semi-colon separated values.

For example:

SELECT APPLICATION_CONTEXTS FROM UNIFIED_AUDIT_TRAIL
 WHERE UNIFIED_AUDIT_POLICIES = 'app_audit_pol';

APPLICATION_CONTEXTS
----------------------------------------------------------
CLIENT_CONTEXT.APPROLE=MANAGER;E2E_CONTEXT.USERNAME=PSMITH

Auditing Components of Other Oracle Products and Features

You can create unified audit policies for Oracle products and features such as Oracle Database Vault, Oracle Real Application Security, Oracle Data Pump, and Oracle Machine Learning for SQL events.

Auditing Oracle SQL Firewall

You can audit Oracle SQL Firewall violations with a unified audit policy.

About Auditing Oracle SQL Firewall

The occurrence of Oracle SQL Firewall violations potentially indicates abnormal database access attempts, including SQL injection and credential theft or abuse.

Auditing violations record the violation in the database audit trail, which can be protected from tampering. As an administrator with AUDIT_ADMIN role, you can create unified audit policy with the CREATE AUDIT POLICY statement and with the COMPONENT clause set to SQL Firewall.

The data dictionary views for SQL Firewall begin with the name DBA_SQL_FIREWALL_. The columns FW_ACTION_NAME and FW_RETURN_CODE in the UNIFIED_AUDIT_TRAIL data dictionary view stores the relevant information on Oracle SQL Firewall violations.

Example: Auditing Oracle SQL Firewall Violations

You can use the COMPONENT clause to set the unified audit policy to track all Oracle SQL Firewall violations. The following example shows how to create and enable this type of a unified audit policy. You can consider setting the SQL_FIREWALL component to SQL VIOLATION or CONTEXT VIOLATION to be more specific.

CREATE AUDIT POLICY sql_firewall_pol
ACTIONS COMPONENT = SQL_FIREWALL ALL
ON pfitch;

AUDIT POLICY sql_firewall_pol;

How Oracle SQL Firewall Events Appear in the Audit Trail

The UNIFIED_AUDIT_TRAIL data dictionary view lists Oracle SQL Firewall audit events.

The FW_ACTION_NAME and FW_RETURN_CODE columns of the UNIFIED_AUDIT_TRAIL data dictionary view track SQL Firewall violations. To retrieve all the audited Oracle SQL Firewall violations, consider filtering the AUDIT_TYPE component to include the Oracle SQL Firewall component from V$UNIFIED_AUDIT_RECORD_FORMAT. For example:

SELECT DBUSERNAME, ACTION_NAME, CURRENT_USER, SQL_TEXT, UNIFIED_AUDIT_POLICIES, FW_ACTION_NAME, FW_RETURN_CODE
FROM UNIFIED_AUDIT_TRAIL
WHERE AUDIT_TYPE
IN (SELECT UNIQUE COMPONENT FROM V$UNIFIED_AUDIT_RECORD_FORMAT WHERE COMPONENT = 'SQL Firewall')
AND ACTION_NAME <> 'FW ADMIN ACTION';

Output similar to the following appears:

DBUSERNAME ACTION_NAME   CURRENT_USER SQL_TEXT                        UNIFIED_AUDIT_POLICIES FW_ACTION_NAME FW_RETURN_CODE
---------- ------------- ------------ ------------------------------- ---------------------- -------------- -------------
PFITCH     SQL VIOLATION PFITCH       SELECT SALARY FROM HS.EMPLOYEES HR_FW_POL              SQL Violation 0