Audit SQL Statements in Oracle AI Database 26ai

Oracle AI Database 26ai uses unified audit policies to record selected database activity. To audit a SQL statement effectively, first identify what the requirement actually describes: a system action throughout the database, an action on a particular schema object, or the use of a system privilege. You then create a focused policy, enable it for the intended users and outcomes, generate a controlled test event, and examine the resulting record in UNIFIED_AUDIT_TRAIL.

Traditional auditing is desupported in Oracle 26ai. Do not configure new auditing with the legacy AUDIT_TRAIL initialization parameter, the traditional AUDIT statement syntax, or views such as DBA_AUDIT_TRAIL. The current workflow uses CREATE AUDIT POLICY, AUDIT POLICY, and the unified audit trail.

The phrase “audit a SQL statement” can describe several different controls. Choosing the narrowest appropriate control keeps the audit trail useful and avoids collecting unrelated activity.

1. System action: Audit a category of SQL operation, such as CREATE TABLE, wherever that action is performed. This is appropriate when the operation itself is the security or compliance concern.
2. Object action: Audit an operation against a named schema object, such as SELECT on HR.EMPLOYEES. This is appropriate when access to a specific table, view, procedure, or other supported object is the concern.
3. System privilege: Audit the exercise of a privilege such as CREATE ANY TABLE. This answers a different question from auditing the CREATE TABLE action: it focuses on whether the operation was authorized through that privilege.

A policy can contain multiple compatible audit options, but a short policy with one clear purpose is usually easier to test, review, and retire. Broad policies can produce a high volume of records and can obscure the activity that prompted the audit requirement.

Do not rely on an old static list of statement options. Query the installed database to obtain the system actions supported by that Oracle release:

SELECT name
FROM   auditable_system_actions
WHERE  component = 'Standard'
ORDER  BY name;

AUDITABLE_SYSTEM_ACTIONS maps configurable action codes to their unified-auditing names. The standard component contains supported RDBMS actions and entries such as ALL, LOGON, and LOGOFF. The list is not identical to every command represented by V$SQLCOMMAND, because some database commands cannot be configured as unified audit system actions.

Use the returned NAME value in the ACTIONS clause. Discovering the name from the target database makes scripts easier to validate and less dependent on assumptions carried forward from older Oracle releases.

The following policy audits the CREATE TABLE system action:

CREATE AUDIT POLICY audit_create_table
    ACTIONS CREATE TABLE;

Creating a policy defines what Oracle can audit; it does not by itself begin collecting records for ordinary user activity. The policy must also be enabled. Keeping definition and enablement separate lets an administrator reuse the same definition while changing its user or outcome scope.

When a requirement concerns use of a system privilege instead, define that control with the PRIVILEGES clause. For example, auditing CREATE ANY TABLE records use of that powerful privilege rather than every occurrence of the CREATE TABLE action. The distinction matters when reviewing why a user was able to perform an operation.

To audit reads of a particular table, name both the action and the object:

CREATE AUDIT POLICY audit_select_employees
    ACTIONS SELECT ON hr.employees;

This policy is more focused than a database-wide action policy because it applies to HR.EMPLOYEES. An object policy for SELECT also captures qualifying READ actions on that object. Object-action support varies by object type, so verify the action and object combination before deploying a policy.

Oracle 26ai also supports column-level object-action auditing for tables and views. The following example audits updates that reference the SALARY column:

CREATE AUDIT POLICY audit_salary_update
    ACTIONS UPDATE(salary) ON hr.employees;

Column-level auditing narrows collection to activity involving named columns. It does not test the data value or a business predicate. If the requirement is to audit only when a value or row condition is met—for example, a query that accesses salaries above a defined amount—use fine-grained auditing through DBMS_FGA. Fine-grained audit records also appear in the unified audit trail.

A top-level statement is issued directly by a user. A statement executed from within a PL/SQL procedure or function is not top-level. When the security requirement concerns direct user commands, ONLY TOPLEVEL can prevent nested SQL activity from creating unwanted records.

CREATE AUDIT POLICY audit_top_level_employee_reads
    ACTIONS SELECT ON hr.employees
    ONLY TOPLEVEL;

Oracle also permits a broad top-level policy:

CREATE AUDIT POLICY audit_all_top_level_actions
    ACTIONS ALL
    ONLY TOPLEVEL;

Use a policy this broad only for a defined requirement and after estimating the expected audit volume. A narrowly scoped policy is generally easier to interpret and maintain.

The next statement enables the system-action policy for successful operations performed by APP_ADMIN:

AUDIT POLICY audit_create_table
    BY app_admin
    WHENEVER SUCCESSFUL;

The BY clause includes the named user. An EXCEPT clause can instead exclude named users, but BY and EXCEPT are alternatives and cannot be combined in the same enablement statement. If no user scope is specified, the policy applies to all users within its applicable container scope.

WHENEVER SUCCESSFUL records only successful qualifying actions. WHENEVER NOT SUCCESSFUL records failed attempts. If neither outcome clause is specified, both successful and unsuccessful qualifying events are audited. Select the outcome deliberately: failed attempts can be important security signals, while successful operations may be required for accountability or change tracking.

In a multitenant database, consider where the policy is created and enabled. A local policy in a pluggable database applies locally. A common policy is managed from the CDB root with the required common scope. Test the policy in the same container in which the application activity occurs.

Before generating test activity, verify the policy definition:

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

This view shows what the policy contains. A system-action policy may have null object columns because it is not attached to one schema object. Policy identifiers created without quoted names are normally stored in uppercase.

Next, verify that the policy is enabled for the intended entity and outcomes:

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

AUDIT_UNIFIED_POLICIES describes policy definitions, whereas AUDIT_UNIFIED_ENABLED_POLICIES shows their active scope. A correctly defined policy that is absent from the enabled-policy view will not collect the expected ordinary user events.

Connect as the deliberately scoped test user in a nonproduction environment and issue a qualifying statement. For the example policy, create and then clean up a disposable test table using the normal change-control process. The event must match the policy action, user, outcome, and container. Simply enabling a policy does not create an audit record.

Query the unified trail with a focused filter:

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

EVENT_TIMESTAMP identifies when the event occurred, DBUSERNAME identifies the database user, and ACTION_NAME describes the action. RETURN_CODE is zero for a successful operation and contains an Oracle error number for many failed operations. Object fields can be null when they do not apply to the audited action.

UNIFIED_AUDIT_POLICIES identifies the policy or policies associated with the record and is usually a better test filter than searching arbitrary SQL text. Treat SQL_TEXT as sensitive audit data: it can reveal object names, predicates, and application values. Access to the audit trail should be granted only to authorized personnel.

If no record appears, confirm the current container, the spelling of the user and policy, the selected success or failure outcome, and whether the action was issued directly when ONLY TOPLEVEL is present. Also confirm that the test occurred after the policy was enabled.

When collection is no longer required for the named user, disable the matching policy scope:

NOAUDIT POLICY audit_create_table BY app_admin;

Unified NOAUDIT POLICY does not provide an EXCEPT form. If the policy was enabled for all users, disable it without a BY clause. Disabling a policy stops future matching collection for that scope; it does not delete records already written to the unified audit trail. Verify the result again in AUDIT_UNIFIED_ENABLED_POLICIES.

Creating and managing unified audit policies requires appropriate authority, normally the AUDIT_ADMIN role or the required AUDIT SYSTEM privilege. The AUDIT_VIEWER role is intended for authorized read access and does not grant policy-management authority. Separate policy administration from routine audit review where practical.

Audit data consumes storage and may contain sensitive information. Review policy volume, archive records according to organizational retention rules, and use supported DBMS_AUDIT_MGMT procedures to manage and purge eligible records. Do not delete or modify protected audit data directly. In the next lesson, you will learn how to examine and interpret records in the unified audit trail.

Use the exercise to practice auditing selected SQL statements with unified audit policies.