View and Report on the Unified Audit Trail in Oracle 26ai

Lesson 4 demonstrated how to create and enable a unified audit policy and then generate a controlled event. Collecting the event is only the beginning. An audit record becomes useful evidence when an authorized reviewer can identify who performed an action, when and where it occurred, what object or privilege was involved, whether it succeeded, and which policy caused Oracle to record it.

Oracle AI Database 26ai uses unified auditing for current audit configuration and reporting. Traditional auditing is desupported, so this lesson does not use SYS.AUD$ or the traditional USER_AUDIT_* and DBA_AUDIT_* views. The primary reporting interface is UNIFIED_AUDIT_TRAIL. It presents standard unified audit records, fine-grained audit records, mandatory events, and records from supported Oracle components in one wide data dictionary view.

A useful report begins with a specific question. Avoid retrieving every column from every record. Select the fields that answer the question, restrict the query to a bounded time interval, and record the report's database, container, filters, time basis, and generation time.

Audit evidence can expose user identities, object names, SQL text, bind information, client programs, hosts, and security failures. Access therefore belongs only to trusted users.

• AUDIT_VIEWER is the read-oriented role for an authorized reviewer who needs to query the unified audit trail.
• AUDIT_ADMIN supports audit policy and audit-trail administration in addition to access needed for those duties.

A reviewer who does not manage policies should normally receive AUDIT_VIEWER, not the broader administrative role. Production environments should separate policy administration, audit review, and routine database administration where practical. Connecting as SYS merely to generate a report is unnecessary and weakens that separation of duties.

UNIFIED_AUDIT_TRAIL is intentionally wide because it consolidates different audit sources. Many columns are null when they do not apply to a particular event. The following field guide identifies the columns most useful in general reports.

Question	Useful columns	Interpretation
When did it occur?	EVENT_TIMESTAMP_UTC, EVENT_TIMESTAMP	Use UTC for repeatable reports across systems; label the time basis.
Who performed it?	DBUSERNAME, CURRENT_USER, OS_USERNAME	The database, effective, and operating-system identities can differ.
Where did it originate?	USERHOST, CLIENT_PROGRAM_NAME, CLIENT_IDENTIFIER	Availability depends on the client and application instrumentation.
What happened?	AUDIT_TYPE, ACTION_NAME	Read the action with its audit type to understand the event source.
Did it succeed?	RETURN_CODE	Zero normally means success; a nonzero value is an Oracle error number.
What object was involved?	OBJECT_SCHEMA, OBJECT_NAME	These fields can be null for actions unrelated to a schema object.
Which policy recorded it?	UNIFIED_AUDIT_POLICIES, FGA_POLICY_NAME	Mandatory records may not identify a custom unified policy.
Which privilege was used?	SYSTEM_PRIVILEGE_USED	This can explain the privilege path used for a standard action.
How are events related?	SESSIONID, STATEMENT_ID, ENTRY_ID, TRANSACTION_ID, SCN	Choose correlation fields for the investigation; one statement can produce multiple actions.

An audit record is committed independently of the audited user's transaction. Rolling back an application transaction does not remove its audit evidence. Conversely, an empty report does not prove that no activity occurred. It can mean that the policy was not enabled, the filter did not match, the event occurred outside the interval, the query ran in the wrong container, or retention processing had already removed the record.

The following query forms a reusable foundation for scheduled or investigative reports. It uses bind variables and a half-open UTC interval:

SELECT event_timestamp_utc,
       dbusername,
       audit_type,
       action_name,
       object_schema,
       object_name,
       return_code,
       unified_audit_policies
FROM   unified_audit_trail
WHERE  event_timestamp_utc >= :start_utc
AND    event_timestamp_utc <  :end_utc
ORDER  BY event_timestamp_utc,
          entry_id;

Bind :start_utc and :end_utc as validated timestamps. The interval includes its starting instant and excludes its ending instant. Adjacent reports can therefore use the previous end as the next start without duplicating a boundary record. This is safer than an inclusive BETWEEN expression and avoids the ambiguity of date values that silently default to midnight.

Keep the exact interval, report SQL or version-controlled report identifier, database and container identity, execution time, and row count with the exported result. Those details make the report reproducible.

Lesson 4 created the illustrative AUDIT_CREATE_TABLE policy. A policy filter connects generated evidence to that configuration:

SELECT event_timestamp_utc,
       dbusername,
       action_name,
       object_schema,
       object_name,
       return_code,
       unified_audit_policies
FROM   unified_audit_trail
WHERE  unified_audit_policies LIKE '%AUDIT_CREATE_TABLE%'
AND    event_timestamp_utc >= :start_utc
AND    event_timestamp_utc <  :end_utc
ORDER  BY event_timestamp_utc DESC,
          entry_id DESC;

The policy name is only one part of the evidence. Confirm the action, user, result, timestamp, and container. AUDIT_UNIFIED_POLICIES describes a policy definition, and AUDIT_UNIFIED_ENABLED_POLICIES describes its enabled scope. Neither view proves that a qualifying event occurred. The generated event is verified in UNIFIED_AUDIT_TRAIL.

Failed authentication attempts are common security-reporting events:

SELECT event_timestamp_utc,
       dbusername,
       os_username,
       userhost,
       client_program_name,
       authentication_type,
       return_code
FROM   unified_audit_trail
WHERE  action_name = 'LOGON'
AND    return_code <> 0
AND    event_timestamp_utc >= :start_utc
AND    event_timestamp_utc <  :end_utc
ORDER  BY event_timestamp_utc DESC;

RETURN_CODE = 0 normally identifies a successful database action. For a failure, interpret the nonzero value as the applicable Oracle error number. Repeated failures may warrant investigation, but one failed logon does not by itself prove malicious intent. Database usernames and client fields can also be incomplete when authentication fails.

Host, operating-system user, and client-program values describe the connection environment. For strong attribution, correlate the database record with application, listener, identity-provider, network, and operating-system evidence.

Use structured columns rather than searching arbitrary SQL text. The following query reports activity attributed to a selected database user:

SELECT event_timestamp_utc,
       dbusername,
       action_name,
       object_schema,
       object_name,
       system_privilege_used,
       return_code
FROM   unified_audit_trail
WHERE  dbusername = :database_username
AND    event_timestamp_utc >= :start_utc
AND    event_timestamp_utc <  :end_utc
ORDER  BY event_timestamp_utc,
          entry_id;

Unquoted Oracle user names are normally stored in uppercase, while quoted identifiers can preserve case. Validate and normalize the bound value according to the database's naming rules. In proxy or application environments, also consider CURRENT_USER, DBPROXY_USERNAME, Real Application Security identities, and CLIENT_IDENTIFIER.

To focus on a protected object, filter the schema and object names:

SELECT event_timestamp_utc,
       dbusername,
       audit_type,
       action_name,
       object_schema,
       object_name,
       return_code,
       unified_audit_policies
FROM   unified_audit_trail
WHERE  object_schema = 'HR'
AND    object_name = 'EMPLOYEES'
AND    event_timestamp_utc >= :start_utc
AND    event_timestamp_utc <  :end_utc
ORDER  BY event_timestamp_utc DESC,
          entry_id DESC;

Object fields identify the recorded object; they do not guarantee that this filter finds every access made through every view, synonym, stored program, or application layer. Interpret the result according to the policy definition and recorded action semantics.

SQL_TEXT and SQL_BINDS are CLOB fields that can reveal predicates and sensitive application values. Keep them out of routine summary reports unless the reviewer is authorized and the investigation requires them. After identifying an event through structured filters, use a narrow drill-down query:

SELECT event_timestamp_utc,
       dbusername,
       action_name,
       return_code,
       sql_text,
       sql_binds
FROM   unified_audit_trail
WHERE  entry_id = :entry_id
AND    sessionid = :session_id
AND    event_timestamp_utc >= :start_utc
AND    event_timestamp_utc <  :end_utc;

These columns may be null for events to which they do not apply. Do not use SQL_TEXT LIKE '%SELECT%' as the primary event classifier: text searches can be incomplete, expensive, and misleading. Oracle 26ai also provides the UNIFIED_AUDIT_TRAIL_EXCLUDE_COLUMNS parameter for excluding supported sensitive columns from the view when that control matches organizational requirements.

Read ACTION_NAME together with AUDIT_TYPE. The unified trail can contain standard policy records, mandatory events, and records from fine-grained auditing and supported Oracle components. Fine-grained policies created with DBMS_FGA write their evidence to the unified trail and identify the applicable policy in FGA_POLICY_NAME.

Component-specific activity can include RMAN, Data Pump, Database Vault, Label Security, Real Application Security, SQL Firewall, and protocol events. These sources populate their applicable fields, so null values in unrelated columns are normal. A mandatory audit record can also have a null UNIFIED_AUDIT_POLICIES value because no custom policy caused its creation.

Each pluggable database, including the root, has its own unified audit trail. Connect to the intended PDB and query UNIFIED_AUDIT_TRAIL for a PDB-local report. An authorized reviewer working from the CDB root can query CDB_UNIFIED_AUDIT_TRAIL for records across available containers.

SELECT a.con_id,
       c.name AS container_name,
       a.event_timestamp_utc,
       a.dbusername,
       a.action_name,
       a.object_schema,
       a.object_name,
       a.return_code
FROM   cdb_unified_audit_trail a
JOIN   v$containers c
  ON   c.con_id = a.con_id
WHERE  a.event_timestamp_utc >= :start_utc
AND    a.event_timestamp_utc <  :end_utc
ORDER  BY a.con_id,
          a.event_timestamp_utc,
          a.entry_id;

This query requires appropriate access to both views. Include the container identifier and resolved name in a cross-container export. A query from one PDB does not automatically return records belonging to other PDBs, and a closed PDB may not contribute rows to a current cross-container query until it becomes available.

SQL Developer, SQLcl, SQL*Plus, controlled applications, and reporting tools can export a bounded query to formats such as CSV. The export is a derived report, not automatically a tamper-proof audit repository. Store it in an approved location and preserve its database and container identity, UTC interval, generation timestamp, exact query or report version, filters, row count, reviewer, classification, and retention period.

For efficient routine reporting, select only required columns, constrain every query by time, filter structured fields before retrieving CLOB data, and avoid repeatedly scanning the entire trail. Large exports should be paginated or processed through an approved reporting workflow.

Oracle recommends archiving and purging unified audit records regularly. When long-term centralized retention is required, Oracle Audit Vault and Database Firewall or Oracle Data Safe may be used as separately managed services. Supported database cleanup uses DBMS_AUDIT_MGMT under appropriate AUDIT_ADMIN authority; do not issue direct modifications against Oracle-managed AUDSYS storage. Required evidence must be archived before its purge boundary is advanced.

In the next lesson, you will learn how to audit the use of specific system privileges.

Use the quiz to test your understanding of auditing SQL statements and reviewing unified audit records.

Auditing SQL Statements - Quiz

Use the exercise to practice querying and interpreting records in the unified audit trail.

Viewing Audit Records - Exercise

Click the Quiz link to test your knowledge on auditing SQL statements.
Auditing SQL Statements - Quiz

Click the Exercise link below to practice using auditing.
Viewing Audit Records - Exercise