Unified Auditing in Oracle AI Database 26ai: Conclusion

This module developed a complete introduction to unified auditing in Oracle AI Database 26ai. Auditing supplies evidence about security-relevant database activity: who attempted an action, when and where it occurred, which action, privilege, role, or object was involved, whether the operation succeeded, and which audit policy caused Oracle to record it. That evidence supports accountability, security monitoring, incident investigation, and compliance reporting.

Auditing is a detective control, not a substitute for authentication, authorization, encryption, least privilege, Database Vault, SQL Firewall, or separation of duties. It is most useful when each policy begins with a documented question and the organization knows who will review the resulting records. Collecting every possible event can increase storage, processing, privacy, and review costs without improving security.

Oracle AI Database 26ai uses unified auditing for current audit configuration and reporting. Traditional auditing is desupported. New requirements must therefore use unified audit policies instead of the legacy AUDIT_TRAIL initialization parameter, SYS.AUD$, or traditional audit views. Existing traditional settings inherited by an upgraded database can remain honored, but they should be identified and migrated through a controlled upgrade plan.

1. Introduction to Unified Auditing established auditing as a planned security and accountability control. It introduced mandatory, predefined, custom, component, and fine-grained auditing; the AUDIT_ADMIN and AUDIT_VIEWER roles; CDB and PDB scope; and UNIFIED_AUDIT_TRAIL as the consolidated reporting view. The lesson also emphasized that creating a policy, enabling it, reviewing its records, retaining evidence, and eventually purging eligible records are parts of one lifecycle.

2. Elements You Can Audit classified the principal auditable elements. A policy can record system actions, successful use of system privileges, use of privileges granted through roles, operations on named schema objects, and supported component events. Oracle 26ai also supports column-level unified auditing for tables and views. When the requirement depends on row values, a data predicate, or an event handler, DBMS_FGA provides fine-grained auditing rather than a normal unified policy definition.

3. Enable Unified Auditing replaced the obsolete database-wide initialization-parameter model with a policy workflow. The audit administrator first identifies the question, users, outcomes, objects, and container in scope. CREATE AUDIT POLICY defines a custom policy, while AUDIT POLICY enables it for the intended population. The lesson also showed how to inspect Oracle-supplied policies before creating a duplicate and how to use NOAUDIT POLICY without deleting historical records.

4. Audit SQL Statements explained why the phrase “audit a statement” must be classified more precisely. A system-action policy answers whether an action such as CREATE TABLE occurred. An object-action policy answers whether a selected operation affected a named object. A privilege policy answers whether a system privilege supplied the authority, and a role policy tracks use of system privileges granted through a role. This distinction produces focused policies that are easier to test and interpret.

5. View and Report on the Unified Audit Trail turned collected records into usable evidence. Reports should begin with a specific question, use a bounded UTC interval, filter structured columns before retrieving CLOB data, and retain the database, container, query, execution time, and reconciliation information needed to reproduce the result. Read-oriented reviewers should normally receive AUDIT_VIEWER rather than the broader administrative authority supplied by AUDIT_ADMIN.

6. Audit System Privilege Use focused on the authority actually used to perform an action. Possessing SELECT ANY TABLE does not prove that a statement used it; the user may own the object or hold a direct object grant. A valid test must ensure that the selected system privilege is the authorization path. The resulting record can identify that path through SYSTEM_PRIVILEGE_USED, which can contain more than one comma-separated privilege.

7. Audit Database Object Access developed a focused policy for operations on a named schema object. The lesson separated object actions from system actions and system privileges, added column-level auditing for sensitive table or view columns, and reserved DBMS_FGA for value-based conditions. It also demonstrated the create, enable, verify, test, query, disable, and drop sequence without using the legacy ON DEFAULT, BY ACCESS, or BY SESSION syntax.

8. Purge the Unified Audit Trail completed the evidence lifecycle. Retention, archival, the last archive timestamp, and purging are separate decisions. Records that must be preserved are archived and verified first. The audit administrator then records the verified UTC boundary and uses DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL or a controlled purge job. Direct DML against Oracle's internal audit tables is neither a supported nor an acceptable cleanup method.

Much of the module's practical work depended on distinguishing related controls. System-action auditing records that a configured action occurred. System-privilege auditing records an action when the named privilege supplied the authority. Role auditing records use of system privileges granted directly or indirectly through a selected role. Object-action auditing records configured operations against a named schema object. These mechanisms can describe different aspects of one event, but they are not interchangeable.

For example, an ACTIONS CREATE TABLE policy asks whether the action occurred, while a PRIVILEGES CREATE ANY TABLE policy asks whether that broader privilege was used. Likewise, an object policy on HR.EMPLOYEES can record selected reads or changes regardless of whether the user was authorized through ownership, a direct object grant, a role, or a system privilege. Choose the option that corresponds to the evidence requirement instead of assuming that one record proves every possible authorization path.

Column-level unified auditing, introduced for tables and views in Oracle AI Database 26ai, narrows an object-action policy to statements that refer to designated columns. It is useful when the question concerns access to a sensitive column, such as a salary or government identifier. It does not apply a row-value predicate. When collection depends on the value of data, a condition evaluated against accessed rows, or an event handler, use a carefully tested DBMS_FGA policy. Fine-grained audit records are also available through UNIFIED_AUDIT_TRAIL, but they are identified through their FGA-specific fields.

Mandatory auditing provides a protected baseline for designated security-sensitive events. It covers categories such as audit-policy changes, execution of supported audit-management interfaces, attempts to interfere with protected audit data, and selected administrative activity. Mandatory collection cannot be turned off with a user-defined NOAUDIT statement, but it does not replace application-specific policies. A database can still require focused auditing for sensitive data, powerful privileges, privileged users, and business-critical operations.

Oracle-supplied predefined policies can address common security requirements and should be inspected before custom policies are added. Their enabled state can differ between newly created and upgraded databases. Review the actual policy definition and scope in the installed database rather than assuming that a named policy is present, enabled, or sufficient. Custom policies remain appropriate when a predefined policy is too broad, too narrow, or does not express the required users, objects, conditions, or outcomes.

Audit evidence can reveal account names, client hosts, object names, errors, SQL text, bind information, and details about privileged operations. Access to both configuration and evidence must therefore be restricted. AUDIT_ADMIN supports policy administration and audit-trail lifecycle operations, while AUDIT_VIEWER provides read-oriented access for reviewers who do not need authority to change policies or purge records. Oracle Database Vault can impose additional authorization requirements in protected environments.

Separation of duties makes audit evidence more credible. The person who performs routine review does not automatically need authority to disable the policy being reviewed or delete its records. Organizations should document who can create and enable policies, who investigates alerts and produces reports, who approves retention decisions, who archives evidence, and who executes cleanup. The broad DBA role should not be treated as the default authorization design for every audit participant.

A reliable audit policy begins with a question that can be answered from recorded fields. Examples include: Who attempted to update a protected table? Which account successfully used SELECT ANY TABLE? Which failed logons originated from a particular client host? Which policy recorded an event? The question determines whether the policy should name an action, privilege, role, object, column, component, condition, or some supported combination.

Scope is equally important. Identify the database users or granted roles to which the policy applies, whether successful operations, failed operations, or both are required, and whether the policy is local to one PDB or common across a CDB. Use AUDITABLE_SYSTEM_ACTIONS to discover configurable actions and SYSTEM_PRIVILEGE_MAP to confirm auditable system privileges. Inspect existing policy definitions and enabled scope before creating another policy.

The following example defines a local object-action policy for selected activity against HR.EMPLOYEES and enables it for one application account. Substitute an approved test account and execute the example in the intended nonproduction PDB:

CREATE AUDIT POLICY monitor_hr_employee_changes
  ACTIONS UPDATE, DELETE ON HR.EMPLOYEES;

AUDIT POLICY monitor_hr_employee_changes BY APP_USER;

The first statement creates the reusable definition. The second enables that definition for APP_USER. Omitting the user scope would enable the policy for all users, which should not be treated as the default production choice. Add an outcome clause only when it matches the evidence requirement. Broad options such as ACTIONS ALL can generate substantial volume and should be limited to a justified set of sensitive objects or privileged users.

Policy definition and policy enablement answer different questions and must be checked separately. AUDIT_UNIFIED_POLICIES describes what a policy contains. AUDIT_UNIFIED_ENABLED_POLICIES shows the entities and success or failure outcomes for which it is active. A policy that exists but is not enabled will not collect ordinary qualifying activity.

SELECT policy_name,
       audit_option,
       audit_option_type,
       object_schema,
       object_name
FROM   audit_unified_policies
WHERE  policy_name = 'MONITOR_HR_EMPLOYEE_CHANGES';

SELECT policy_name,
       enabled_option,
       entity_name,
       entity_type,
       success,
       failure
FROM   audit_unified_enabled_policies
WHERE  policy_name = 'MONITOR_HR_EMPLOYEE_CHANGES';

Confirm that the returned definition, user or role scope, outcome settings, and current container match the approved design. Test the policy with a controlled qualifying action in a nonproduction database or approved test window. Avoid changing production data solely to prove that an audit policy works. When a policy contains ONLY TOPLEVEL, ensure that the test action is issued directly rather than only through a nested program unit.

An authorized reviewer should search a bounded interval and use the policy, identity, action, object, privilege, and outcome columns that answer the investigative question. The following query checks the example policy during a supplied UTC interval:

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

RETURN_CODE is zero when the recorded database action succeeded; a nonzero value is an Oracle error code. Read ACTION_NAME with AUDIT_TYPE because the unified trail consolidates records from different sources. Some columns do not apply to every source and can legitimately be null. For mandatory records, Oracle identifies the mandatory source differently from an ordinary custom-policy record.

Prefer structured columns to searches of arbitrary SQL text. Retrieve SQL_TEXT and SQL_BINDS only when the investigation requires them because they can contain predicates, identifiers, or sensitive application values. Exported evidence must be protected, access-controlled, and retained with enough context to establish its source and integrity. Audit reporting is not complete merely because a query produced rows.

Multitenant scope remains part of interpretation. Each PDB, including the root, has its own audit context. A local policy must be created and enabled in the correct container, and a local query reports the current container's trail. Cross-container reporting requires appropriate common-user access and container-aware queries. Include database and container identity with reports so similarly named users, policies, or objects are not confused.

Disabling a policy stops future qualifying collection for the matching scope, but it does not erase records already generated. Dropping a policy removes its definition after enabled scopes have been disabled; it also does not serve as a purge operation. Historical records remain evidence until they become eligible for disposal under an approved retention rule.

Before purging, identify the correct PDB, the approved UTC interval, applicable investigations or legal holds, the archive destination, and the expected record count. Archive records that must be retained, verify that the archive is readable and recoverable, reconcile the source interval, and preserve database identifiers such as DBID, DB_UNIQUE_NAME, and container identity. Only after successful verification should the audit administrator set the last archive timestamp.

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP records a cutoff; it does not copy, protect, or validate evidence. A manual or scheduled cleanup should enforce that stored boundary. Using CLEAN_AUDIT_TRAIL with use_last_arch_timestamp => FALSE ignores the boundary and makes all records in the selected trail eligible, so it is inappropriate for the controlled archive-before-purge workflow developed in this module. Each PDB requires its own correct boundary and cleanup context.

A scheduled purge job is appropriate only after the manual archive, boundary, cleanup, and verification process has been tested. The archival process must continue to advance the verified timestamp; an old static boundary will not preserve a rolling retention period. Review the configured job, monitor its outcome, and investigate unexpected remaining or missing records rather than assuming that scheduling alone proves correct retention.

1. Document the security, investigation, evidence, or compliance question that the audit policy must answer.
2. Select the appropriate action, privilege, role, object, column, component, or fine-grained mechanism.
3. Choose the intended users, roles, success or failure outcomes, and CDB or PDB scope.
4. Estimate volume and protect sensitive audit fields before enabling broad collection.
5. Create or select the policy, enable it, and verify its definition and active scope in separate dictionary views.
6. Generate a controlled qualifying event and confirm the expected record in UNIFIED_AUDIT_TRAIL.
7. Grant reviewers only the access they need and separate evidence review from policy and purge administration where practical.
8. Use bounded UTC reports, structured filters, database and container identifiers, and reproducible export procedures.
9. Archive and verify required evidence before setting the last archive timestamp or running a purge.
10. Use supported DBMS_AUDIT_MGMT procedures and verify every manual or scheduled cleanup operation.

With these practices, unified auditing becomes more than record collection. It becomes a controlled evidence system that connects a defined risk to a focused policy, a verified event, an accountable review process, and a defensible retention decision. That lifecycle is the central lesson of Oracle AI Database 26ai auditing.

Before you go on, click the Quiz link below to test your knowledge of Oracle auditing.
Auditing Database Privileges - Quiz