A salary review asks who queried an employee table between 09:00 and 10:00. A useful answer needs an auditable database action, its actor, the intended object, a clear time interval, and the outcome. Start by checking where the database records the relevant activity and whether its policies cover that activity.
Oracle DBA Lesson 77 Part 1 — Identify Audit Coverage Before Relying on It
This lesson covers audit inventory and evidence requirements. Creating, enabling, and testing a new policy belongs to the following lesson.
Authentication, permission, and auditing
Authentication establishes the database account used by a session. Permission determines which actions that session may perform. Auditing records selected activity under the applicable settings.
A grant permitting SELECT establishes permission. A logon record establishes an authentication event. To investigate an employee-table query, inspect the relevant object-action record, including its database identity, actor, and time. An account can hold a privilege and still never use it.
Database identity and individual identity are different questions. DBUSERNAME identifies the database user in an audit record. If several application users share one database account, individual attribution needs a trustworthy application identity, such as a managed client identifier, plus its application-side context. An audit record for a SELECT establishes the audited database action. It does not establish which individual read every returned value. Column- or condition-specific requirements may need fine-grained auditing rather than a broad object-action policy. See auditing introduction and application identity.
Check database and container scope
Check the intended database and pluggable database. In SQL*Plus, SHOW USER and SHOW CON_NAME show the current identity and container. Record the release update and the intended scope with the audit inventory.
Each container has its own unified audit trail. Local and common policies differ in scope and inheritance. Visibility from the root does not replace a check of the intended application container. Cross-container reporting through CDB_UNIFIED_AUDIT_TRAIL is a separate, privileged root-side task.
The AUDIT_VIEWER role can inspect audit information. The mode query also needs the relevant dynamic view, normally SYS.V_$OPTION. An access error does not mean the audit feature is off. AUDIT_ADMIN is for policy and trail administration and is not required for this inventory. See audit roles and multitenant scope.
Check pure versus mixed operation
Read the Unified Auditing option. Keep the option string's exact spelling and case:
SELECT parameter, value
FROM v$option
WHERE parameter = 'Unified Auditing';Interpret the value in the auditing context:
| VALUE | Meaning and next check |
|---|---|
TRUE | Pure unified auditing is enabled. Inspect the applicable unified policies and the unified trail. |
FALSE | Pure mode is off. Unified policies can still operate with traditional auditing in mixed mode. Inspect actual policy enablement and traditional settings. |
In mixed mode, events selected by unified policies go to the unified audit trail. Traditional settings use the traditional destinations they configure. Oracle documents mandatory audit records in traditional trails in mixed mode, with further distinctions for administrative auditing. In pure mode, supported audit sources are consolidated through unified auditing. Some operating conditions, including a database that cannot write its normal trail, use operating-system audit files. Choose the destination from the documented mode, event, and operating condition.
The mode value alone does not show that a particular salary query was captured. V$OPTION is not a licensing entitlement check. Reading the option does not relink Oracle, restart the database, or change the audit mode. See mode check and mixed-mode destinations and V$OPTION.
Check policy enablement and definition
These views answer separate questions:
| View | What to inspect |
|---|---|
AUDIT_UNIFIED_POLICIES | Created policy definitions: actions, object scope, conditions, and their evaluation options. |
AUDIT_UNIFIED_ENABLED_POLICIES | Which policies are enabled, for whom, and for which success or failure outcomes. |
UNIFIED_AUDIT_TRAIL | Records of audited events. Match records to the intended event and scope. |
Inventory enablement:
SELECT policy_name, enabled_option, entity_name,
success, failure
FROM audit_unified_enabled_policies
ORDER BY policy_name, entity_name;ENABLED_OPTION can identify BY USER, EXCEPT USER, or BY GRANTED ROLE. Read it with ENTITY_NAME. ALL USERS can appear when a policy applies to all users. Read inclusion, exclusion, and role-based applicability together. SUCCESS and FAILURE specify captured outcomes as YES or NO.
Then read the definitions:
SELECT policy_name, audit_option, audit_option_type,
object_schema, object_name, audit_condition
FROM audit_unified_policies
ORDER BY policy_name;For any relevant policy, also read CONDITION_EVAL_OPT and, where it matters, COMMON, INHERITED, and top-level scope in that release's view. A policy can contain several audit options, which produces several definition rows. An enabled login policy covers authentication. The employee-access requirement needs the corresponding object action, user applicability, and outcomes. A policy name alone does not establish that coverage. See audit dictionary views, enabled-policy columns, policy-definition columns, and configuring audit policies.
Connect the requirement to a controlled test
After suitable policy coverage has been configured and enabled, a controlled query is:
SELECT salary
FROM app_data.employees;Record the database and container, test user, action, object, time, and expected outcome. Then read the correct destination. For a unified-policy event, bound the trail query:
SELECT event_timestamp_utc, dbusername, action_name,
object_schema, object_name, return_code
FROM unified_audit_trail
WHERE object_schema = 'APP_DATA'
AND object_name = 'EMPLOYEES'
AND action_name = 'SELECT'
AND event_timestamp_utc >= TIMESTAMP '2026-10-21 09:00:00'
AND event_timestamp_utc < TIMESTAMP '2026-10-21 10:00:00'
ORDER BY event_timestamp_utc;Replace the date and interval with the test's UTC bounds. The start is included and the end is excluded, so adjacent intervals do not count a boundary event twice. The video uses :start_utc and :end_utc as typed timestamp bind placeholders. Supply typed values through the client. In SQL*Plus, the explicit TIMESTAMP literals above avoid dependence on string-to-timestamp defaults. Use the test interval. The example date matches only the interval written in the statement.
Interpret these fields:
| Field | Meaning |
|---|---|
EVENT_TIMESTAMP_UTC | Audit entry timestamp in coordinated universal time. |
DBUSERNAME | Database user whose action was audited. |
ACTION_NAME | Audited action, interpreted with its audit type when necessary. |
OBJECT_SCHEMA, OBJECT_NAME | Relevant object identity. |
RETURN_CODE | Oracle return code. Zero means the action succeeded. |
Match the rows to the controlled event. An empty result calls for checking time conversion, container, policy scope, conditions, enablement, outcome selection, destination, visibility, and whether the record is still available. Records written or kept in another destination need that destination's collection process. An empty filtered query does not prove the event never occurred.
Object-action auditing records an audited action. It is not a copy of every returned employee row. Requirements for column, value, client, or individual detail must be defined before the audit mechanism is chosen. See unified audit record fields.
Record the requirement and its ownership
Write down the actor scope, database and container, object, action, UTC interval, successful and failed cases, required identity context, retention owner, and the readers who may see the evidence. Include the policy definition, the enablement, and the verification result. An item with no matching record is still unchecked.
Audit SQL text and bind values can contain sensitive data. Limit who can read them, and keep only the material the investigation needs. Retention and purge follow the evidence requirement. Inventory does not purge, move, or rewrite trails. See audit strategy and retention.
No comments:
Post a Comment