Enable Unified Auditing in Oracle AI Database 26ai

In Oracle AI Database 26ai, enabling auditing no longer means turning on a database-wide traditional audit trail with an initialization parameter. Traditional auditing is desupported. New requirements are implemented by selecting an Oracle-supplied unified audit policy or creating a custom policy and then enabling it for the intended users, roles, outcomes, and container.

A unified audit policy is a named definition of the actions, privileges, roles, objects, components, columns, or conditions to audit. Creating a policy and enabling it are separate operations. CREATE AUDIT POLICY defines a custom policy; the unified-auditing AUDIT POLICY statement activates it. Mandatory auditing remains active independently of custom policy enablement.

Audit collection consumes storage and processing resources, and its records require review, protection, retention, and eventual cleanup. Begin with a documented security or evidence requirement instead of enabling broad collection without a review plan.

Before writing SQL, identify:

• the action, privilege, role, object, column, or component that supplies the required evidence;
• the users or roles whose activity is in scope;
• whether successful operations, unsuccessful operations, or both matter;
• whether the policy belongs in one PDB or must be common across the CDB;
• who will review the records and respond to exceptions; and
• how long the evidence must be retained and protected.

The account that creates or enables a policy needs the required authority, normally the AUDIT_ADMIN role or the appropriate AUDIT SYSTEM privilege. AUDIT_VIEWER provides read-oriented authority for reviewing audit evidence without granting full audit administration.

For a local policy, connect to the PDB in which the policy will be created and enabled. A common policy is created and enabled from the CDB root using the required commonly granted authority. Common and local users or roles must follow the scope rules for the policy's container.

Oracle AI Database supplies predefined policies for common security requirements. Before creating another policy, list the Oracle-supplied definitions available in the installed release:

SELECT DISTINCT policy_name
FROM   audit_unified_policies
WHERE  oracle_supplied = 'YES'
ORDER  BY policy_name;

Policies such as ORA_SECURECONFIG and ORA_LOGIN_LOGOUT may already address part of the requirement. Availability and default enablement can differ between newly created and upgraded databases, so inspect the current environment rather than assuming a particular state.

The following query shows which policies are enabled and how they are applied:

SELECT policy_name,
       enabled_option,
       entity_name,
       entity_type,
       success,
       failure
FROM   audit_unified_enabled_policies
ORDER  BY policy_name, entity_name;

AUDIT_UNIFIED_POLICIES describes policy definitions. AUDIT_UNIFIED_ENABLED_POLICIES describes policies that have been enabled and shows their user, role, or all-user scope. A policy can exist in the first view without appearing in the second.

Suppose the requirement is to record updates to the HR.EMPLOYEES table. The following SQL statement creates a focused object-action policy:

CREATE AUDIT POLICY monitor_hr_updates
    ACTIONS UPDATE ON hr.employees;

This statement defines MONITOR_HR_UPDATES in the current container. It does not start collecting records. The definition identifies the object and qualifying action, but it does not yet identify the users to whom the policy applies.

Check for an existing policy before using the name. Use supported ALTER AUDIT POLICY or policy-lifecycle procedures when a definition must change; do not assume that an unsupported OR REPLACE form is available.

Enable the example for the application user that is within scope:

AUDIT POLICY monitor_hr_updates BY hr_app_user;

The unified-auditing AUDIT statement is SQL, not a PL/SQL procedure. The BY clause limits this example to HR_APP_USER. Oracle also supports other documented forms:

• BY enables a policy for specified users.
• EXCEPT enables it for all users except specified users.
• Role-based scope can apply a policy to users with specified roles.
• WHENEVER SUCCESSFUL limits records to successful qualifying activity.
• WHENEVER NOT SUCCESSFUL limits records to failed qualifying activity.
• Omitting an outcome clause audits qualifying successes and failures.

If user and role scope are omitted, the policy is enabled for all users:

AUDIT POLICY monitor_hr_updates;

This all-user form is shown to explain its meaning, not as a default production recommendation. Broad scope can create substantial audit volume and should be supported by a defined requirement.

Enabling a unified audit policy takes effect immediately for the current and active sessions. It does not require an instance restart.

Verify the example policy with a focused query:

SELECT policy_name,
       enabled_option,
       entity_name,
       entity_type,
       success,
       failure
FROM   audit_unified_enabled_policies
WHERE  policy_name = 'MONITOR_HR_UPDATES'
ORDER  BY entity_name;

A returned row confirms that Oracle reports the policy as enabled for the displayed entity and outcome scope. It does not prove that a qualifying operation has occurred, that an audit record was generated, or that the record contains every field required by a report.

In an approved test environment or controlled maintenance window, connect as the user for whom the policy is enabled and perform a qualifying update. Use test data or an operation approved by the data owner; do not experiment on production salary information merely to generate an audit record.

After the test, an authorized reviewer can search the consolidated audit trail:

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name,
       return_code,
       unified_audit_policies
FROM   unified_audit_trail
WHERE  unified_audit_policies LIKE '%MONITOR_HR_UPDATES%'
ORDER  BY event_timestamp DESC
FETCH FIRST 25 ROWS ONLY;

A RETURN_CODE of zero normally identifies a successful database operation. A nonzero value represents an error and must be interpreted using the applicable Oracle error information. Account for the event timestamp, database user, container, and test timing when validating the result. Some columns can be null because they do not apply to a particular event source.

If no record appears, confirm that the test action matched the policy, the correct user and container were used, the policy was enabled for the required outcome, and the reviewer has suitable access. Do not treat successful policy DDL alone as proof of collection.

When the named-user policy is no longer required, disable it using matching scope:

NOAUDIT POLICY monitor_hr_updates BY hr_app_user;

If the policy was enabled for all users, disable the all-user form with:

NOAUDIT POLICY monitor_hr_updates;

Unified NOAUDIT POLICY does not provide an EXCEPT clause. Follow the documented transition rules when changing a policy between BY and EXCEPT enablement. Disabling a policy stops future qualifying collection according to policy semantics; it does not delete records already stored in the audit trail.

Do not drop a policy merely to pause collection. If a definition must be removed, disable it with the appropriate scope before using DROP AUDIT POLICY. A common policy must be managed from the CDB root rather than dropped from a local PDB.

The traditional AUDIT_TRAIL initialization parameter does not enable unified audit policies. In Oracle AI Database 26ai, it is deprecated, applies only to traditional auditing, and must not be used for a new audit configuration. Attempting to set it produces messages that include:

• ORA-32004: obsolete or deprecated parameter(s) specified for string instance
• ORA-32006: AUDIT_TRAIL initialization parameter has been deprecated

Oracle documents that traditional settings inherited during an upgrade can remain honored, but administrators cannot create new traditional audit settings and can only remove existing settings. Migration and cleanup should follow release-specific Oracle guidance and the organization's change-control process.

AUDIT_FILE_DEST is also deprecated and applies to traditional auditing. Attempts to set it produce ORA-32004 and a parameter-specific ORA-32006 message. It must not be used as a switch for unified policy collection.

However, the parameter is not operationally meaningless. The Oracle AI Database 26ai Database Reference also documents this directory as a destination for mandatory auditing information and, when applicable settings request it, certain operating-system audit records for SYS. If no value is explicitly supplied, at least one documented default audit directory must exist and be writable; otherwise, the instance can fail to start.

Do not remove or relocate the audit directory casually. Review the running configuration, platform defaults, filesystem permissions, inherited settings, and current Oracle documentation before making an upgrade-cleanup change.

Enabling a policy begins an operational lifecycle. Monitor audit-record volume and storage growth, protect audit data from unauthorized access, and define regular review and escalation procedures. Required evidence should be archived securely before it becomes eligible for deletion.

Use supported DBMS_AUDIT_MGMT procedures and the documented 26ai process for audit-trail maintenance. Do not directly delete or modify protected AUDSYS data. Cleanup authority should be restricted, and policy or trail-management activity is itself security-sensitive.

In the next lesson, you will learn how to audit selected SQL statements through unified audit policies.

Use the quiz to test your understanding of Oracle AI Database auditing.