Audit System Privilege Use in Oracle AI Database 26ai

Oracle AI Database 26ai uses unified audit policies to record selected security events. When the requirement is to audit a system privilege, the important question is not simply whether a user possesses that privilege. The policy records an operation when the audited system privilege is successfully used to authorize it.

For example, a user can possess SELECT ANY TABLE but select a table that the user owns or can access through a direct object grant. In that case, the broader system privilege is unnecessary and is not the authorization path used for the operation. A privilege-use policy should not be expected to create a SELECT ANY TABLE record.

Traditional auditing is desupported in Oracle 26ai. Current privilege auditing uses CREATE AUDIT POLICY with the PRIVILEGES clause, enables the definition separately with AUDIT POLICY, and reports generated events through UNIFIED_AUDIT_TRAIL.

Privilege auditing, action auditing, object auditing, and role auditing answer related but different questions.

An ACTIONS CREATE TABLE policy records the action. A PRIVILEGES CREATE TABLE policy records successful use of that system privilege. An object policy records an action against its named object. Select the policy type that matches the evidence requirement instead of treating all three as interchangeable.

Query the installed database rather than copying an old privilege list:

SELECT name
FROM   system_privilege_map
ORDER  BY name;

SYSTEM_PRIVILEGE_MAP maps privilege audit-option numbers to their current names. The installed mapping and the SQL Language Reference for the deployed release update are the operational authorities.

Oracle documents four system-privilege options that cannot be audited through the PRIVILEGES clause:

• INHERIT ANY PRIVILEGE
• INHERIT PRIVILEGE
• TRANSLATE ANY SQL
• TRANSLATE SQL

Administrative privileges such as SYSDBA and SYSOPER belong to a different security context. They should not be substituted for this documented list.

The following policy audits successful use of SELECT ANY TABLE:

CREATE AUDIT POLICY audit_select_any_table
    PRIVILEGES SELECT ANY TABLE;

This statement creates a policy definition in the current container. It does not enable the policy, assign it to a user, or generate an audit record. A qualifying event occurs only when an operation actually uses SELECT ANY TABLE.

One policy can contain multiple privilege options:

CREATE AUDIT POLICY audit_any_table_dml
    PRIVILEGES SELECT ANY TABLE,
               INSERT ANY TABLE,
               UPDATE ANY TABLE,
               DELETE ANY TABLE;

This broader policy can generate more audit activity and should be used only when all four privilege paths are part of the requirement. For a tutorial, use the narrower AUDIT_SELECT_ANY_TABLE policy so the resulting evidence is easier to interpret.

Enable the focused policy for the approved test account:

AUDIT POLICY audit_select_any_table BY app_reader;

The BY clause limits this enablement to APP_READER. Omitting user, exception, and role-grant scope enables the policy for all users in its applicable container scope. BY and EXCEPT are alternative forms and cannot be combined in one enablement statement.

Use the unified BY app_reader form shown above; the legacy statement grammar does not apply here. Also, do not use WHENEVER NOT SUCCESSFUL and claim that it records denied use of the privilege. Oracle defines system-privilege auditing as recording activity that successfully uses the named privilege. If the requirement is to record denied SQL attempts, define the appropriate action policy and unsuccessful outcome scope.

Check the policy definition with selected columns:

SELECT policy_name,
       audit_option,
       audit_option_type,
       common,
       inherited
FROM   audit_unified_policies
WHERE  policy_name = 'AUDIT_SELECT_ANY_TABLE';

Then verify the enabled entity and outcome metadata:

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

AUDIT_UNIFIED_POLICIES describes what the policy contains. AUDIT_UNIFIED_ENABLED_POLICIES describes whether and how it is enabled. Neither configuration view proves that SELECT ANY TABLE has been exercised; event evidence appears in UNIFIED_AUDIT_TRAIL.

Test the policy in a nonproduction environment as APP_READER. Use an approved table in another schema for which access depends on SELECT ANY TABLE:

SELECT COUNT(*)
FROM   hr.employees;

For this to be a valid privilege-use test, all of the following must be true:

• APP_READER has SELECT ANY TABLE through the grant path being tested.
• APP_READER does not own HR.EMPLOYEES.
• The user does not have a sufficient direct SELECT or READ privilege on the object.
• The object and test operation have been approved.

If ownership or a narrower object privilege authorizes the query, the system privilege is not used and the expected privilege-audit record is not generated. This distinction makes privilege auditing valuable: it shows use of the powerful authorization path, not merely that an account was granted the privilege.

Use the bounded UTC reporting pattern introduced in Lesson 5:

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

SYSTEM_PRIVILEGE_USED contains a comma-separated list of system privileges used by the action, so do not assume it always contains one exact value. ACTION_NAME identifies the operation, OBJECT_SCHEMA and OBJECT_NAME identify the object when applicable, and UNIFIED_AUDIT_POLICIES connects the event to its policy. A zero RETURN_CODE normally indicates a successful database operation.

No manual flush is required. The former DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT capability is desupported in Oracle 26ai because current unified records bypass the old queue and are written directly to relational audit storage.

Match the named-user enablement when the test or collection requirement ends:

NOAUDIT POLICY audit_select_any_table BY app_reader;

Disabling the policy stops future qualifying collection for that scope; it does not delete records already stored in the unified audit trail. If the definition will not be reused, verify that it is disabled and then remove it through change control:

DROP AUDIT POLICY audit_select_any_table;

Do not drop a shared production definition merely because one user's enablement is no longer needed.

Legacy auditing treated names such as CONNECT, RESOURCE, and DBA as shortcut bundles. Current unified auditing provides an explicit role policy when the requirement concerns system privileges supplied through a role:

CREATE AUDIT POLICY audit_data_admin_role
    ROLES data_admin;

A ROLES data_admin policy audits use of system privileges assigned directly or indirectly to that role. It does not audit object privileges merely because they were granted to the role.

Do not confuse policy definition with enablement scope. ROLES data_admin defines which role-supplied system privileges are audited. BY USERS WITH GRANTED ROLES data_admin in an AUDIT POLICY statement selects users according to grants of that role. The two clauses solve different problems.

Oracle advises against using ordinary system-privilege auditing to audit the privilege use of administrative users such as SYS. Use appropriately scoped object or action policies for administrative users, while recognizing that top-level administrative statements before the database opens are mandatorily audited. Administrative privileges such as SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM are not ordinary system-privilege options.

Creating and managing unified audit policies requires AUDIT_ADMIN or the appropriate AUDIT SYSTEM privilege. Read-oriented reviewers should normally use AUDIT_VIEWER. If Oracle Database Vault is enabled, the corresponding DBMS_MACADM.AUTHORIZE_AUDIT_ADMIN or AUTHORIZE_AUDIT_VIEWER authorization is an additional requirement; database privileges alone are insufficient.

Create a local policy while connected to the intended PDB. Create a common policy from the CDB root with CONTAINER = ALL and appropriate common authority and identities. Test and query the event in the container where the application action occurs.

Creating and enabling or disabling a policy takes effect immediately for ongoing sessions. However, changes to existing statement options, privilege options, or conditions made with ALTER AUDIT POLICY take effect in subsequent sessions. Do not assume that every type of policy change has identical timing.

For more advanced scoping, unified policies support restricted conditions and documented evaluation frequencies. Oracle privilege analysis is a complementary capability for discovering which privileges and roles a workload uses or does not use. Auditing captures configured evidence; it does not by itself prove that every granted privilege is necessary.

In the next lesson, you will learn how to audit access to specific database objects.