Elements You Can Audit in Oracle AI Database 26ai

Auditing records selected database activity so authorized reviewers can determine who attempted an action, what was attempted, when and where it occurred, which database object or privilege was involved, and whether the action succeeded. In Oracle AI Database 26ai, unified auditing is the supported auditing architecture. Traditional auditing is desupported.

Unified auditing is broader than the older classification of statement, privilege, and schema-object auditing. A unified audit policy can cover system actions, system privileges, roles, object actions, individual table or view columns, administrative users, and supported Oracle components. Mandatory auditing records designated security-sensitive events independently of user-created policies, while fine-grained auditing can apply conditions based on accessed data.

Auditing is a planned detective and accountability control. It complements authentication, authorization, least privilege, encryption, Database Vault, SQL Firewall, and other preventive controls; it does not replace them or guarantee that every misuse will be detected. A useful audit design begins with a security, evidence, investigation, or compliance requirement and collects the events needed to satisfy that requirement.

Oracle AI Database 26ai brings records from supported audit sources into the unified audit trail. The following categories describe the principal elements that can be audited:

These categories can overlap within one policy. For example, a custom policy can contain both system privilege options and object-specific actions. Oracle also supplies predefined policies that group actions for common security objectives.

System-action auditing records selected database-wide SQL or operational actions. Representative actions include CREATE USER, ALTER USER, DROP USER, CREATE TABLE, ALTER SYSTEM, LOGON, and LOGOFF. The ALL option can provide broad coverage, but it should not be treated as the default for every database.

The available actions are release- and component-dependent. Query AUDITABLE_SYSTEM_ACTIONS instead of relying on an old static list:

SELECT component,
       name
FROM   auditable_system_actions
ORDER  BY component, name;

To list only the standard database actions:

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

Not every command represented in V$SQLCOMMAND is necessarily configurable as a standard unified audit action. The installed data dictionary and the documentation for the applicable release update are authoritative.

System privilege auditing answers a different question from system-action auditing. An action option records the occurrence of a selected action; a privilege option records activity that successfully exercises a selected system privilege. For example, auditing the CREATE TABLE action and auditing use of a corresponding system privilege provide related but distinct evidence.

Representative privilege options include CREATE ANY TABLE, SELECT ANY TABLE, READ ANY TABLE, GRANT ANY PRIVILEGE, and ALTER SYSTEM. The names defined by the installed database can be inspected with:

SELECT name
FROM   system_privilege_map
ORDER  BY name;

Oracle AI Database 26ai documents four system privileges that cannot be audited through the unified policy PRIVILEGES clause:

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

A unified audit policy can also name database roles. Role auditing records use of system privileges directly granted to the specified role. It should not be described as recording every possible semantic use of a role regardless of the privilege's grant path. In a multitenant database, common policies and common roles must follow the applicable common-policy rules.

Object-action auditing focuses on activity against a named schema object. It can include DDL and DML, such as an UPDATE against a particular table or an EXECUTE of a particular package. Oracle AI Database 26ai supports the following object-action combinations:

An object audit policy for SELECT captures both SELECT and READ actions on the audited object. Object-level MERGE on a table or view is also auditable. This does not imply that every similarly named command is available as a standard system-action option; system-action and object-action auditing have different scopes.

Starting with Oracle AI Database 26ai, a unified audit policy can target individual columns in a table or view. This feature can reduce irrelevant records when the requirement concerns access to a specific sensitive column. The following statement defines a policy for selecting the SALARY column:

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

The statement defines the policy but does not enable it. A separate AUDIT POLICY statement must apply the policy to the intended users or roles. Column-level unified auditing is appropriate when the requirement concerns a named column. When the decision also depends on row values or another data-sensitive predicate, fine-grained auditing may be more suitable.

Mandatory auditing records designated security-sensitive events independently of user-created policies. These records cannot be disabled through an ordinary NOAUDIT operation and are commonly associated with ORA$MANDATORY in unified audit policy information.

Mandatory categories include selected top-level statements by administrative users before the database opens, audit-policy and audit-enablement changes, attempts to modify protected audit records or AUDSYS objects, and selected use of audit-management interfaces. Mandatory auditing also covers specified Database Vault configuration changes, ORADEBUG operations, and SQL Firewall administrative activity.

Administrative activity can involve SYS and sessions using privileges such as SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM, according to the applicable mandatory rule. Mandatory auditing supplies a protected baseline; custom policies are still required for application-specific users, objects, and risks.

The unified trail can include audit information generated by Oracle products, utilities, protocols, and security components. Representative sources include:

• SQL Firewall;
• Oracle Database Vault;
• Oracle Label Security;
• Oracle Database Real Application Security;
• Oracle Data Pump export and import operations;
• Oracle SQL*Loader direct-path loads;
• Oracle XML DB HTTP and FTP protocol messages;
• Oracle Machine Learning for SQL;
• Oracle Deep Data Security administrative actions; and
• Oracle Recovery Manager operations.

Component action names can be discovered through the COMPONENT and NAME columns of AUDITABLE_SYSTEM_ACTIONS. Components do not all use identical policy syntax. RMAN is a notable exception: supported RMAN events are written to the unified audit trail without requiring a custom unified audit policy for RMAN. The reviewer still requires suitable authority to query those records.

Fine-grained auditing, or FGA, uses the DBMS_FGA package to create policies on tables or views. An FGA policy can consider statement types, relevant columns, data values, and permitted conditions. It can therefore record access only when a security-sensitive predicate is satisfied—for example, when a query accesses the SALARY column for rows whose values meet a specified condition.

FGA differs from column-level unified auditing. A column-level unified policy targets named columns through CREATE AUDIT POLICY; FGA can add value- or predicate-sensitive behavior through DBMS_FGA. Fine-grained audit records are available through UNIFIED_AUDIT_TRAIL.

Auditable elements can be narrowed to the activity that supplies useful evidence. Depending on the policy and syntax context, Oracle supports:

• a WHEN condition based on permitted session or application context;
• ONLY TOPLEVEL to focus on qualifying user-initiated top-level statements;
• enablement for selected users or roles; and
• WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL outcome filtering when a policy is enabled.

Oracle also provides predefined unified audit policies. Examples include ORA_SECURECONFIG, ORA_ACCOUNT_MGMT, ORA_DATABASE_PARAMETER, ORA_CIS_RECOMMENDATIONS, ORA_STIG_RECOMMENDATIONS, and ORA_ALL_TOPLEVEL_ACTIONS. ORA_LOGIN_LOGOUT, previously called ORA_LOGON_FAILURES, covers logon and logoff activity.

Do not assume that every predefined policy is enabled. Oracle documents ORA_LOGIN_LOGOUT as enabled by default for new databases but not automatically enabled for databases upgraded from earlier releases. Inspect the actual database:

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

Authorized reviewers query UNIFIED_AUDIT_TRAIL as the consolidated reporting view. Depending on the event and source, a record can identify the timestamp, database and operating-system users, client host and program, service, session and container, action, object, privilege, role, SQL text, return code, and the policy associated with the record. Component-specific columns provide additional information for sources such as Database Vault, Label Security, Real Application Security, Data Pump, RMAN, and SQL Firewall.

Not every column applies to every record. A null value can simply mean that a field is irrelevant to that event type. Reports should select and interpret the columns that correspond to the policy and audit source being investigated.

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

This query is an introductory view of recent records, not a complete security report. Access is protected: AUDIT_ADMIN provides audit-policy and trail-administration authority as well as audit-data access, while AUDIT_VIEWER supports read-oriented review without granting full administration. Ordinary users should not be assumed to have access to the trail.

Oracle AI Database 26ai uses the multitenant architecture. Local policies are created and enabled in the intended pluggable database. Common policies are created in the CDB root and follow common-policy rules. Options such as CONTAINER = CURRENT and CONTAINER = ALL apply only in supported contexts and with suitable common-user authority. Administrators must connect to the appropriate container when inspecting local policies or audit records.

Audit collection consumes storage and processing resources, and review and retention add operational and privacy obligations. This does not mean auditing should begin only after a suspected incident. Use focused actions, users, roles, objects, columns, conditions, and outcomes; test the policy; monitor trail growth; and connect every policy to a documented evidence requirement.

In the next lesson, you will learn how to create and enable unified audit policies for selected activity.