Introduction to Unified Auditing in Oracle Database 23ai

You design an Oracle Database to support specific applications, users, and business processes. After the database enters service, you also need reliable evidence of security-sensitive activity: who connected, which privileges were used, which protected objects were accessed, what action was attempted, whether it succeeded, and when it occurred.

Oracle Database 23ai provides this evidence through unified auditing. Unified auditing combines records from supported audit sources and exposes them through the UNIFIED_AUDIT_TRAIL data dictionary view. Administrators define audit policies for selected actions, privileges, roles, users, or schema objects and then enable those policies for the required population.

Auditing is a detective and accountability control. It supplements authentication, authorization, encryption, separation of duties, and other preventive controls; it does not replace them. A useful audit program also includes regular review, alerting, secure retention, and controlled cleanup of audit records.

Auditing should be designed as part of normal database security rather than reserved only for exceptional circumstances. Organizations use audit evidence to:

• establish accountability for privileged and sensitive operations;
• investigate failed logons, unauthorized attempts, configuration changes, and unusual data access;
• verify that administrators and applications follow approved procedures;
• support internal, contractual, and regulatory requirements;
• reconstruct relevant events during incident response; and
• monitor the use of powerful privileges, roles, and protected schema objects.

Audit records can help explain an operational event, but unified auditing is not Oracle Database's primary performance-diagnostics facility. Performance investigations normally use execution plans, performance views, Automatic Workload Repository data where licensed, traces, and related diagnostic tools.

More collection is not automatically better. An indiscriminate policy can consume storage, increase processing and review effort, and capture information that requires additional privacy protection. Begin with a defined risk or evidence requirement, select the events that address it, and document who will review the results.

Creating a policy and enabling it are separate operations. CREATE AUDIT POLICY defines the events that belong to a policy. AUDIT POLICY enables that policy for all users or for a selected scope supported by the statement. For example, the following statements define and enable a policy for user-account administration:

CREATE AUDIT POLICY account_admin_pol
    ACTIONS CREATE USER, ALTER USER, DROP USER;

AUDIT POLICY account_admin_pol;

A successful policy definition proves only that Oracle accepted the configuration. Test the policy with a controlled action in a nonproduction environment or an approved test window, and then query UNIFIED_AUDIT_TRAIL to verify that the expected record appears. Use NOAUDIT POLICY when a custom policy should no longer be enabled; disabling a policy does not by itself remove audit records that have already been generated.

Oracle also supplies predefined unified audit policies. These policies are useful starting points, but their availability and enabled state can depend on the release and installation or upgrade history. Inspect the current database instead of assuming that a particular predefined policy is enabled:

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

For example, ORA_SECURECONFIG covers a set of security-relevant operations. Review any Oracle-supplied policy against the organization's requirements before enabling, changing, or relying on it.

Unified audit policies can cover several categories of database activity. The following examples are representative rather than exhaustive:

Oracle provides dictionary views for discovering valid actions and existing policy configuration. For example, AUDITABLE_SYSTEM_ACTIONS describes auditable system actions, while SYSTEM_PRIVILEGE_MAP maps system privilege numbers and names. AUDIT_UNIFIED_POLICIES describes unified policy definitions, and AUDIT_UNIFIED_ENABLED_POLICIES shows their enabled scope.

Fine-grained auditing, or FGA, is appropriate when a normal object-action policy is broader than the evidence requirement. Using the DBMS_FGA package, an authorized administrator can create a policy on a table or view and associate it with a condition, relevant columns, and selected statement types. For example, a policy could record access to designated sensitive columns when a predicate is satisfied. FGA records are available through the unified audit trail.

FGA conditions and handlers require careful design and testing. They should not be presented as a guarantee that every misuse will be detected, and they should not capture more SQL text or contextual information than the organization can protect and legitimately retain.

Some security-sensitive events are mandatorily audited. Mandatory auditing is always active and cannot be disabled by a user-defined NOAUDIT statement. It covers protected categories such as selected administrative activity and attempts to interfere with audit configuration or audit records. These records can be identified in the unified trail through Oracle-supplied policy information such as ORA$MANDATORY, where applicable.

Mandatory auditing provides a security baseline; it is not a substitute for policies tailored to the application's data, privileges, and risks.

Authorized reviewers use UNIFIED_AUDIT_TRAIL as the central reporting view. A focused introductory query is:

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 50 ROWS ONLY;

EVENT_TIMESTAMP identifies when the event was recorded, DBUSERNAME identifies the database user when applicable, and ACTION_NAME describes the audited operation. Object columns identify the affected schema object when the event concerns one. RETURN_CODE helps distinguish successful operations from errors, and UNIFIED_AUDIT_POLICIES identifies policies associated with the record.

The unified view covers different audit sources, so not every column applies to every row. A null value does not automatically indicate a defective record. Reports should select the fields relevant to the event being investigated and interpret them in the context of the applicable policy and audit source.

Access to audit configuration and audit evidence must be restricted. Oracle Database provides purpose-specific administrative roles:

• AUDIT_ADMIN supports administration of unified and fine-grained audit policies and audit-trail management, and it includes authority to view audit data.
• AUDIT_VIEWER provides read-oriented access for reviewing and analyzing audit data without granting full policy and trail administration.

Apply least privilege and separation of duties. The person who reviews evidence does not automatically need authority to change policies or purge records. Ordinary database users should not be described as having unrestricted access to the audit trail, and the broad DBA role should not be the default authorization strategy for an auditor.

Oracle Database 23ai uses a multitenant architecture. The CDB root and each pluggable database have their own audit context, and unified policies can have common or local scope depending on where and how they are created. Connect to the intended container when defining, enabling, querying, or maintaining local audit configuration and data.

Fine-grained audit policies are created in the applicable PDB rather than as CDB-root policies. Reporting across containers requires suitable common-user privileges and a container-aware reporting approach. Later lessons and the documentation for your deployment should guide the exact common-policy and PDB administration procedures.

Before enabling a policy, identify the users, roles, objects, privileges, and containers in scope. Decide whether successful operations, unsuccessful operations, or both provide the required evidence. Define who reviews the records, how often review occurs, and what conditions require escalation.

Audit data must be protected and retained long enough to meet investigation and compliance requirements. Monitor trail growth, archive required evidence securely, and restrict cleanup authority. Use supported DBMS_AUDIT_MGMT procedures and the documented process for the target release when records are eligible for deletion. Do not directly delete or modify the protected unified audit trail.

By the end of this module, you should be able to:

1. List the categories of activity that Oracle Database 23ai unified auditing can record.
2. Explain mandatory, predefined, custom, component, and fine-grained auditing.
3. Create and enable a unified audit policy for selected actions, privileges, roles, users, or objects.
4. Audit relevant SQL statements and the use of powerful system privileges.
5. Audit access to selected schema objects and sensitive columns where supported.
6. Query and interpret records in UNIFIED_AUDIT_TRAIL.
7. Build focused reports for successful and unsuccessful activity.
8. Plan secure retention, archival, and purge procedures for unified audit data.
9. Apply audit policy scope correctly in CDB and PDB environments.

In the next lesson, you will examine the types of database activity that can be audited.