A conditional unified audit policy applies only when its condition is true. Narrowing a policy can reduce the events it contributes, but it also changes which actions it covers. The condition's inputs, their trustworthiness and the time at which Oracle evaluates them all matter.
Oracle DBA Lesson 78 Part 2 — Conditions That Preserve Audit Coverage
This lesson uses one database user and one table. Only a session's client identifier changes.
The condition and its input
CLIENT_IDENTIFIER is application-supplied session information. A client can set it with DBMS_SESSION.SET_IDENTIFIER and clear it with DBMS_SESSION.CLEAR_IDENTIFIER. Oracle documents both procedures as executable by PUBLIC.
The value can identify application work associated with a shared database login. For example, an import operation may set the label IMPORT. SYS_CONTEXT reads it:
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') AS database_user,
SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') AS client_label
FROM dual;Changing the label does not change the authenticated database login. An application-controlled label can help correlate activity; its value alone does not authenticate an application or end user. For attribution, inspect the database/proxy identity and the authentication and context-setting mechanism. Review connection-pool reset behavior so that one request's label does not remain attached to the next request.
IMPORT here is an ordinary label chosen for this example. It does not mean that Oracle has identified a Data Pump operation.
See SET_IDENTIFIER and CLEAR_IDENTIFIER and SYS_CONTEXT and USERENV.
Choose when Oracle evaluates the condition
The evaluation clause chooses when Oracle tests the condition, and whether a later change of context can change the decision.
| Evaluation clause | When the decision is made | Consequence |
|---|---|---|
EVALUATE PER STATEMENT | For each relevant auditable statement | Later statements can get a different decision when context changes. |
EVALUATE PER SESSION | At the first relevant auditable statement, with the result reused for the session | Later changes to context do not refresh the policy's condition result. |
EVALUATE PER INSTANCE | At the first relevant evaluation in the instance lifetime, with the result reused | Appropriate only when reusing that decision for this scope is intended. It is unsuitable for tracking a changing client label. |
Oracle specifies that these evaluation scopes are per instance and per container. A decision in one container is not automatically the decision in another container in that instance. The example stays in one pluggable database.
For the condition client identifier = IMPORT, compare two reads in the same session:
| State when the read runs | Per statement | Per session, if the first read evaluated true |
|---|---|---|
First read: label is IMPORT | True: this policy applies. | True: store this decision. |
| Later read: label has been cleared | Unknown: this policy does not apply to that read. | Reuse true: this policy still applies to relevant reads. |
The reverse case also matters. If the first read has the label OTHER, equality to IMPORT is false. With per-session evaluation, changing the label to IMPORT afterward does not activate this policy for that session. With per-statement evaluation, a later relevant statement evaluates the changed value.
Evaluation frequency controls rechecking the condition. It does not mean that only one audit record can be produced per session.
See creating conditions for unified auditing and CREATE AUDIT POLICY conditions and evaluation scope.
Define and enable the local policy
In the current container:
CREATE AUDIT POLICY client_read_aud
ACTIONS SELECT ON app_data.departments
WHEN 'SYS_CONTEXT(''USERENV'',
''CLIENT_IDENTIFIER'') = ''IMPORT'''
EVALUATE PER STATEMENT;
AUDIT POLICY client_read_aud BY report_user;ACTIONS SELECT ON identifies the relevant action and qualified object. WHEN contains the condition as a string. Each single quote inside that string is doubled. EVALUATE PER STATEMENT tells Oracle to check the condition for each relevant statement.
Creating the policy stores its definition. AUDIT POLICY ... BY enables it for the reader. With no success/failure clause, applicable successful and unsuccessful outcomes are included. This does not promise a record for every malformed statement or every indirect access path. With the container clause omitted, this is a local policy in the current container.
Inspect the definition and enablement:
SELECT policy_name, audit_condition, condition_eval_opt,
audit_option, object_schema, object_name
FROM audit_unified_policies
WHERE policy_name = 'CLIENT_READ_AUD';
SELECT policy_name, enabled_option, entity_name, success, failure
FROM audit_unified_enabled_policies
WHERE policy_name = 'CLIENT_READ_AUD';Check the exact object, equality expression, STATEMENT evaluation option, reader, and enabled outcomes.
See CREATE AUDIT POLICY, AUDIT policy enablement, AUDIT_UNIFIED_POLICIES, and AUDIT_UNIFIED_ENABLED_POLICIES.
Test the true path
After enablement, open a fresh REPORT_USER session in the same pluggable database. Record a UTC interval for the read. A credential-prompted connection may use a locally configured service alias:
sqlplus -L report_user@<PDB_SERVICE>Set and inspect the label, then execute the first object read:
EXEC DBMS_SESSION.SET_IDENTIFIER('IMPORT');
SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') AS client_label
FROM dual;
SELECT COUNT(*) FROM app_data.departments;The context query confirms what the reader session currently supplies. COUNT(*) is a SELECT on the audited table and avoids displaying its row values. For this policy, the equality condition is true, and a successful read should be attributable to CLIENT_READ_AUD in the trail.
Clear the label and repeat the read
In that same reader session:
EXEC DBMS_SESSION.CLEAR_IDENTIFIER;
SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') AS client_label
FROM dual;
SELECT COUNT(*) FROM app_data.departments;The client label is now null. Equality to IMPORT evaluates to unknown, which does not satisfy the audit condition. Because the policy evaluates per statement, it checks again for the second read. This policy should not contribute its name to that second action's policy list.
Record a separate UTC interval. Keep the user, object, and query unchanged.
Match each read to the audit trail
As the audit viewer in the same container, search the records for each test interval:
SELECT event_timestamp_utc, dbusername, client_identifier,
object_schema, object_name, action_name,
unified_audit_policies, return_code
FROM unified_audit_trail
WHERE dbusername = 'REPORT_USER'
AND object_schema = 'APP_DATA'
AND object_name = 'DEPARTMENTS'
AND action_name = 'SELECT'
AND event_timestamp_utc >= :start_utc
AND event_timestamp_utc < :end_utc
ORDER BY event_timestamp_utc;:start_utc and :end_utc represent actual UTC timestamp values supplied by the client. Use timestamp-typed binds, not untyped date strings. In SQL*Plus, a simple alternative is to replace each bind with a TIMESTAMP literal for the recorded UTC boundary, for example TIMESTAMP '2026-10-22 10:00:00'. Replace this example time with the recorded boundary. The start is included and the end excluded.
| Check | Required interpretation |
|---|---|
| User, object schema/name, action, and UTC interval | Match the actual reader action. |
CLIENT_IDENTIFIER | Correlate the context associated with that recorded action. |
UNIFIED_AUDIT_POLICIES | Inspect the list for the exact policy name CLIENT_READ_AUD. A record can name several policies. |
RETURN_CODE | 0 indicates success; a nonzero code indicates an Oracle error. It is not the count returned by the query. |
For the first successful read, expect this policy's name in the matching record. For the second read, expect this policy's name to be absent. Another applicable policy can still record the second action, and mandatory auditing can record actions in its own scope. A mandatory record may have a null policy-list column. The condition on this policy does not switch off other audit sources.
Do not filter only for the target policy and then conclude that a missing row means the action was never recorded. First correlate the user, object, action, and time, and inspect policy membership.
See UNIFIED_AUDIT_TRAIL.
Missing context and null comparisons
Test a different nonmatching label as well as a cleared label. They exercise different logical outcomes:
| Comparison | Result |
|---|---|
'IMPORT' = 'IMPORT' | True |
'OTHER' = 'IMPORT' | False |
NULL = 'IMPORT' | Unknown |
NULL <> 'IMPORT' | Unknown |
NULL IS NULL | True |
A not-equal comparison alone does not include missing values. For a design that must include an unset client label as well as labels other than IMPORT, the logical expression needs an explicit null branch:
SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') IS NULL
OR SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') <> 'IMPORT'This is an alternative expression to review, not an instruction to replace the example policy. Apply the required quote escaping if embedding it in WHEN. Choose the expression from the actual coverage requirement.
Check context availability for each connection type. For example, inspect SYS_CONTEXT('USERENV','IP_ADDRESS') rather than assuming a network address is always supplied. When a value is null, inequality has the same unknown-result problem. Record the actual value and use an explicit missing-value branch when the requirement includes it.
See null comparisons.
Recap
A conditional policy contributes only when its condition is true at the chosen evaluation point. Per-statement evaluation rechecks a client label that can change during the session. Per-session evaluation reuses the first decision for that session, including a false result. Ordinary comparisons with null are unknown unless the expression tests null explicitly, and exclusion from this policy leaves other policies and mandatory auditing in place.
No comments:
Post a Comment