Audit Database Object Access in Oracle AI Database 26ai

Oracle AI Database 26ai can audit supported actions performed on named schema objects by using unified audit policies. Object-action auditing answers a focused question: was an action such as SELECT, UPDATE, or EXECUTE attempted on a particular table, view, or program unit? This differs from the system-privilege auditing discussed in Lesson 6, which determines whether a system privilege such as UPDATE ANY TABLE authorized an operation.

Traditional auditing is desupported in Oracle 26ai. Although settings retained during an upgrade may continue to operate while an organization migrates, new object-auditing configurations should use unified audit policies. The current workflow is to define a policy with CREATE AUDIT POLICY, enable it separately with AUDIT POLICY, verify its definition and scope, generate a controlled event, and inspect the evidence in UNIFIED_AUDIT_TRAIL.

Choose the Audit Mechanism That Answers the Question

Several Oracle auditing mechanisms can describe different aspects of the same SQL statement. Selecting the mechanism begins with the question the audit evidence must answer.

These categories can overlap. One statement can produce evidence associated with more than one enabled policy, so the audit record must be interpreted in the context of the policy definitions, the user's authorization, and the application that submitted the statement. An audit record documents activity; it does not by itself prove a security violation or demonstrate compliance.

Object Types and Auditable Actions

The action list varies by object type. The following summary shows common documented combinations in Oracle 26ai; it should not be read as permission to apply every action to every object.

A policy that audits SELECT also captures READ object operations; a separate object policy for READ is therefore not required. Before deploying a production policy, confirm each action and object combination in the documentation for the installed release update and in the database's available metadata.

Plan a Focused Object Audit

Assume that an audit administrator needs to answer this question: which selected users read or update the HR.EMPLOYEES table? Before creating a policy, identify the schema-qualified object, supported actions, audited users, success or failure requirements, PDB or CDB scope, expected event volume, and retention requirement. A narrow question produces evidence that is easier to test and interpret.

The following walkthrough uses APP_USER and should be performed in a nonproduction pluggable database where that account is authorized to read the target table. The administrator requires appropriate audit-management authority.

1. Create the Unified Audit Policy

CREATE AUDIT POLICY audit_employee_access
    ACTIONS SELECT ON hr.employees,
            UPDATE ON hr.employees;

This statement creates the policy definition; it does not begin collecting records. The two object-action options name the actions and their exact target. Unlike the desupported traditional syntax, the definition does not use BY ACCESS or BY SESSION.

2. Enable the Policy for the Intended User

AUDIT POLICY audit_employee_access BY app_user;

The BY app_user clause limits this enablement to the named database user. Omitting the BY clause enables the policy for all users, which may be appropriate for a sensitive object only after the audit volume and operational purpose have been evaluated. By default, both successful and failed attempts are relevant. A failure-only requirement could instead use:

AUDIT POLICY audit_employee_access BY app_user
    WHENEVER NOT SUCCESSFUL;

Use an outcome clause only when it matches the audit requirement. A policy enabled only for unsuccessful attempts will not produce the successful test event used later in this walkthrough.

3. Verify the Policy Definition

SELECT policy_name,
       audit_option,
       audit_option_type,
       object_schema,
       object_name,
       object_type,
       column_name
FROM   audit_unified_policies
WHERE  policy_name = 'AUDIT_EMPLOYEE_ACCESS'
ORDER  BY audit_option;

AUDIT_UNIFIED_POLICIES describes policy definitions. Expect a row for each configured option. Confirm that AUDIT_OPTION_TYPE identifies object actions and that the object owner and name are HR and EMPLOYEES. This check prevents a correctly named policy from silently targeting the wrong object or action.

4. Verify the Enabled Scope

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

AUDIT_UNIFIED_ENABLED_POLICIES answers a different question: where is the policy active? Confirm that ENTITY_NAME is APP_USER, ENTITY_TYPE is USER, and the success and failure columns match the intended outcome scope. A policy can exist without being enabled, so both dictionary checks are necessary.

5. Generate a Controlled Event

While connected as APP_USER, run a read-only statement in the approved test environment:

SELECT employee_id, last_name
FROM   hr.employees
WHERE  employee_id = 100;

Avoid changing production data merely to test an audit policy. If an UPDATE must also be tested, use a disposable row or a controlled transaction in a nonproduction database. Rolling back the data change does not erase an audit record that Oracle already generated.

6. Locate and Interpret the Audit Record

Query a bounded UTC interval and attribute the result to the policy, schema, and object rather than searching only by an object name that may exist in multiple schemas or containers.

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
AND    object_schema = 'HR'
AND    object_name = 'EMPLOYEES'
AND    unified_audit_policies LIKE '%AUDIT_EMPLOYEE_ACCESS%'
ORDER  BY event_timestamp_utc;

DBUSERNAME identifies the database user, while ACTION_NAME and AUDIT_TYPE describe the recorded activity. OBJECT_SCHEMA and OBJECT_NAME identify the target. A RETURN_CODE of 0 means the action succeeded; a nonzero value is an Oracle error code. UNIFIED_AUDIT_POLICIES identifies the policy or policies that caused the record and may contain a comma-separated list.

SQL_TEXT can be added to a targeted investigation when justified, but SQL text and bind-related details may expose sensitive values. Audit reports and exports therefore require access controls and retention practices appropriate for security evidence.

Audit Access to a Sensitive Column

When the question is whether a statement referenced a sensitive column, create a column-level unified policy. The following policy audits qualifying reads that reference HR.EMPLOYEES.SALARY:

CREATE AUDIT POLICY audit_employee_salary_reads
    ACTIONS SELECT(salary) ON hr.employees;

AUDIT POLICY audit_employee_salary_reads BY app_user;

Verify this definition through AUDIT_UNIFIED_POLICIES and inspect COLUMN_NAME. Column-level auditing determines whether the audited action referenced the column; it does not evaluate the value stored in a row.

Virtual columns require additional care. Defining an UPDATE or INSERT policy on a virtual column does not generate a record merely because changing a base column recalculates the virtual column. However, when an audited base column is accessed through a virtual column, Oracle can generate the column-level audit record.

Use Fine-Grained Auditing for Value-Based Conditions

Use DBMS_FGA when the audit requirement depends on row values or a data predicate. Fine-grained auditing is a separate policy mechanism available in Oracle AI Database Enterprise Edition, but its evidence is included in UNIFIED_AUDIT_TRAIL. For example, the following policy targets access to the SALARY column for employees in department 50:

BEGIN
  DBMS_FGA.ADD_POLICY(
      object_schema   => 'HR',
      object_name     => 'EMPLOYEES',
      policy_name     => 'audit_dept50_salary',
      audit_condition => 'DEPARTMENT_ID = 50',
      audit_column    => 'SALARY',
      statement_types => 'SELECT',
      enable          => TRUE);
END;
/

For qualifying events, filter the unified trail with AUDIT_TYPE = 'FineGrainedAudit' and identify the policy with FGA_POLICY_NAME. Do not describe this package-created FGA policy as a unified audit policy. The distinction is important when policies are created, enabled, disabled, reviewed, and removed.

Advanced Object-Auditing Considerations

All Actions on a Sensitive Object

Oracle recommends considering ACTIONS ALL for sensitive tables because it can capture indirect SELECT operations:

CREATE AUDIT POLICY audit_all_employee_actions
    ACTIONS ALL ON hr.employees;

AUDIT POLICY audit_all_employee_actions BY app_user;

This broader policy can generate substantially more evidence. Do not apply it indiscriminately to high-volume OLTP users or expand it database-wide without measuring the event rate, review capacity, storage impact, and retention obligation.

Program Units, Grants, and Editions

• Auditing EXECUTE on a package covers the procedures and functions inside that package; an individual packaged subprogram cannot be selected as a separate object-action target. Standalone procedures and functions can be audited as standalone objects.
• For an audited EXECUTE, success means Oracle found the program unit and authorized its execution. A runtime exception inside the program does not necessarily make the audit outcome unsuccessful.
• Auditing GRANT on an object also audits REVOKE. Inspect TARGET_USER and OBJECT_PRIVILEGES when interpreting those records.
• A policy on an editioned object applies to all editions of that object.
• A policy can include options for multiple objects and can combine supported action and privilege options, but focused policies are easier to test, explain, retain, and review.

Newly Created Objects

Unified object-action policies name their object targets; there is no direct unified replacement for the legacy ON DEFAULT pattern. If newly created objects must receive consistent auditing, incorporate the requirement into controlled DDL deployment, policy-as-code, inventory checks, and approved automation that creates or alters the required unified policies after object provisioning. Do not assume that an unverified schema wildcard will provide the required coverage.

Authorization and Multitenant Scope

AUDIT_ADMIN is the administrative role for creating, enabling, disabling, altering, and dropping audit policies and for audit-trail lifecycle administration. AUDIT_VIEWER supports authorized review of audit data. The AUDIT SYSTEM system privilege can authorize policy configuration, but production assignments should follow least privilege and separation of duties. Application users do not need audit-administration authority for their activity to be audited.

Oracle Database Vault can impose additional audit-administrator and audit-viewer authorization requirements. Possessing an audit role may therefore be insufficient in a protected environment; follow the documented Database Vault authorization procedures.

Container scope must match object location. Create a local object policy in the PDB that owns or exposes the target object when the requirement is local; CONTAINER = CURRENT limits a policy to the current container. A common policy requires an appropriately privileged common user in the CDB root and CONTAINER = ALL, and applies only where common-policy rules permit it. A local PDB policy does not automatically cover objects in every PDB. Before interpreting an empty result set, confirm the current container, object location, policy definition, and enabled scope.

Disable and Remove a Test Policy

When testing is complete, disable the policy using the same user scope with which it was enabled:

NOAUDIT POLICY audit_employee_access BY app_user;

If the definition is no longer required, first disable every enabled scope and then drop it:

DROP AUDIT POLICY audit_employee_access;

Disabling a policy stops future collection for that scope, and dropping it removes the definition. Neither operation deletes audit records already written to the unified audit trail. Lesson 8 explains how to manage the retention and removal of old audit records with supported audit-trail management procedures.