Unified Auditing, Audit Policies, Fine-Grained Auditing, and Forensic Design

Learning outcomes

A sensitive ServiceHub payload is queried at 02:15, but application logs are incomplete. The incident team needs database evidence: which authenticated identity connected, which object/action occurred, whether the statement succeeded, and—only where justified—which sensitive column access met a condition. Oracle AI Database 26ai has desupported creating/changing traditional audit settings; Unified Auditing is the current policy framework, while Fine-Grained Auditing (FGA) adds selective row/column conditions.

1. Traditional auditing is desupported in 26ai

Starting in Oracle AI Database 26ai, traditional audit configuration is desupported. Upgraded databases can continue honoring existing traditional settings, but you cannot create or update those settings; Oracle recommends migrating to unified audit policies.

Newer databases have security-focused predefined policies such as ORA_SECURECONFIG; 26ai adds/enables ORA_LOGIN_LOGOUT for new databases. Upgrades may differ, so inspect actual enabled policies.

ALTER SESSION SET CONTAINER=FREEPDB1;SELECT  policy_name,  enabled_option,  entity_name,  entity_type,  success,  failureFROM audit_unified_enabled_policiesORDER BY policy_name,entity_name;

2. Create a narrow ServiceHub policy instead of AUDIT ALL

BEGIN  EXECUTE IMMEDIATE 'NOAUDIT POLICY sh20_work_orders_pol';EXCEPTION WHEN OTHERS THEN NULL;END;/BEGIN  EXECUTE IMMEDIATE 'DROP AUDIT POLICY sh20_work_orders_pol';EXCEPTION WHEN OTHERS THEN NULL;END;/CREATE AUDIT POLICY sh20_work_orders_pol  ACTIONS    SELECT ON servicehub_owner.work_orders,    UPDATE ON servicehub_owner.work_orders;AUDIT POLICY sh20_work_orders_pol  BY servicehub_app;

This policy records the selected object actions only for the runtime identity. It does not audit every SELECT in the PDB and therefore avoids turning ordinary application traffic into uncontrolled audit volume.

3. Generate and inspect a real unified audit record

SELECT COUNT(*)FROM servicehub_owner.work_orders;SELECT work_order_id,payload_jsonFROM servicehub_owner.work_ordersFETCH FIRST 1 ROW ONLY;

SELECT *FROM (  SELECT    event_timestamp,    dbusername,    action_name,    object_schema,    object_name,    return_code,    unified_audit_policies,    sql_text,    client_program_name  FROM unified_audit_trail  WHERE object_schema='SERVICEHUB_OWNER'    AND object_name='WORK_ORDERS'  ORDER BY event_timestamp DESC)FETCH FIRST 20 ROWS ONLY;

A return code of zero is success; nonzero maps to an Oracle error number. The trail can include SQL text and client metadata, which are operationally valuable but may themselves contain sensitive values. Protect access to the audit trail.

4. Fine-Grained Auditing watches sensitive access conditions/columns

FGA is useful when “all SELECTs on this table” is too broad. DBMS_FGA.ADD_POLICY can audit only when specified columns are referenced and an audit_condition is true. FGA records are exposed through the unified audit trail in current unified-audit environments.

BEGIN  DBMS_FGA.DROP_POLICY(    object_schema => 'SERVICEHUB_OWNER',    object_name   => 'WORK_ORDERS',    policy_name   => 'SH20_PAYLOAD_FGA'  );EXCEPTION  WHEN OTHERS THEN    IF SQLCODE != -28102 THEN NULL; END IF;END;/BEGIN  DBMS_FGA.ADD_POLICY(    object_schema   => 'SERVICEHUB_OWNER',    object_name     => 'WORK_ORDERS',    policy_name     => 'SH20_PAYLOAD_FGA',    audit_condition => '1=1',    audit_column    => 'PAYLOAD_JSON',    statement_types => 'SELECT',    enable          => TRUE  );END;/

The unconditional condition is deliberate in a tiny lab: FGA fires only when PAYLOAD_JSON is referenced. In 26ai, omit the legacy audit_trail argument: it is desupported and FGA records are written to UNIFIED_AUDIT_TRAIL. Production should use the minimum useful condition/column set and test optimizer/access-path behavior.

5. Verify FGA attribution

SELECT payload_jsonFROM servicehub_owner.work_ordersFETCH FIRST 1 ROW ONLY;

SELECT *FROM (  SELECT    event_timestamp,    dbusername,    object_schema,    object_name,    action_name,    fga_policy_name,    sql_text,    sql_binds,    return_code  FROM unified_audit_trail  WHERE fga_policy_name='SH20_PAYLOAD_FGA'  ORDER BY event_timestamp DESC)FETCH FIRST 10 ROWS ONLY;

SQL_BINDS/SQL text can increase forensic value and privacy exposure. Do not export audit rows into a broadly accessible log lake without classification, encryption and retention controls.

6. Deliberately wrong: audit everything forever

A policy that records every high-frequency statement for every application user can consume storage, increase processing overhead, bury incidents in noise and collect personal/secrets data beyond the approved retention purpose. The correct response is threat/use-case-driven policy design, external retention, and periodic review/purge.

7. Purge is a governed lifecycle operation

DBMS_AUDIT_MGMT manages cleanup/purge of unified audit records. Establish an archive/collection checkpoint first, then purge according to documented legal/security retention. Never use “disk is full” as the sole retention policy.

SELECT  TRUNC(event_timestamp) AS audit_day,  COUNT(*) AS recordsFROM unified_audit_trailGROUP BY TRUNC(event_timestamp)ORDER BY audit_day DESC;

The mandatory lab does not purge the shared audit trail, because unrelated course/system records may be present.

8. Forensic design requires identity context

• DBUSERNAME/PROXY_SESSIONID: who authenticated/proxied.
• CLIENT_IDENTIFIER/MODULE/ACTION: application context set by the pool/application.
• OBJECT/ACTION/SQL: what database operation occurred.
• RETURN_CODE: success/failure evidence.
• SCN/timestamp/transaction context: correlate with Chapter 16 versions/redo/application logs.
• External immutable-ish retention: preserve evidence outside the compromised database boundary.

9. Cleanup only the chapter policies

NOAUDIT POLICY sh20_work_orders_pol;DROP AUDIT POLICY sh20_work_orders_pol;BEGIN  DBMS_FGA.DROP_POLICY(    object_schema => 'SERVICEHUB_OWNER',    object_name   => 'WORK_ORDERS',    policy_name   => 'SH20_PAYLOAD_FGA'  );END;/

Audit records already generated remain in the audit trail according to retention/purge policy; dropping a policy does not retroactively erase evidence.

10. Production judgment

Start with Oracle's predefined security policies, then add focused custom/FGA policies for real threat/compliance questions. Audit privileged users and proxy/client identity, monitor audit pipeline health and volume, export critical evidence to an independently controlled SIEM/archive, and restrict AUDIT_ADMIN/AUDIT_VIEWER.

Fine-Grained Auditing is included in current Free. Unified Auditing is the 26ai audit framework and requires no optional pack. Lesson 4 moves from recording access to preventing inappropriate rows/values from being returned in the first place.

Authoritative references

• Provisioning Audit Policies — unified/FGA workflow and retention
• Creating Custom Unified Audit Policies — CREATE/AUDIT POLICY syntax
• Database Security Guide — 26ai traditional-audit desupport
• DBMS_FGA — fine-grained audit policies
• Licensing Information — FGA/security feature availability