Oracle Unified Auditing Without the Noise

Most Oracle audit trails are useless, and they fail in one of two ways. Either nobody ever enabled anything, so when a breach investigation asks “who altered that account,” the answer is a shrug. Or someone once turned on everything — every statement, every user, every object — and the trail became a firehose that filled SYSAUX, slowed the database, and got quietly ignored until it was disabled again. Neither of those is auditing. One is blindness; the other is noise you can’t read.

The skill isn’t auditing more. It’s auditing the few things that actually matter — privilege abuse, credential attacks, changes to your security configuration — in a trail you can trust and actually read. Oracle’s Unified Auditing makes that easy, and there’s a deadline attached: as of Oracle Database 23ai, traditional auditing is desupported. If your process still leans on AUDIT ... writing to AUD$, it has an expiry date. Here’s how to do auditing right, and what changes underneath you as you move to 23ai.

The short version. Unified Auditing (the model since 12c) writes every audit record to a single protected, read-only internal table owned by AUDSYS, and you turn it on through policies, not statement by statement. Keep the baseline Oracle pre-enables — ORA_SECURECONFIG (privileged and structural changes) and ORA_LOGON_FAILURES — then add a few narrow policies of your own, not a firehose. 19c ships in mixed mode (traditional AUD$ and unified both active); move to pure unified auditing by relinking the binary (uniaud_on) and restarting. In 23ai, traditional auditing is gone — unified is all there is. Read the trail in UNIFIED_AUDIT_TRAIL; keep it from growing forever with DBMS_AUDIT_MGMT.

What Unified Auditing actually changed

Before 12c, auditing was a scatter of destinations — AUD$ in the database, FGA_LOG$ for fine-grained policies, OS files, XML files, the SYS operations audit — each configured differently and each with its own gaps. Unified Auditing collapses all of it into one trail and one way to configure it. Three properties make it worth adopting on purpose:

• A single, protected trail. Every record lands in a read-only internal table in the AUDSYS schema. You cannot INSERT, UPDATE, or DELETE it with SQL — not even as SYS. An attacker who compromises a privileged account can’t quietly scrub their tracks the way they could when audit rows lived in a normal table. You read it through the UNIFIED_AUDIT_TRAIL view.
• Policy-based, not statement-by-statement. You define audit policies — named bundles of actions, privileges, or roles to watch — and enable them, optionally scoped to specific users. It’s declarative and far easier to review than a pile of individual AUDIT statements.
• Better performance, by default. Unified records are written in queued (asynchronous) mode: they buffer in the SGA and flush to disk in batches, rather than forcing a write on every audited action. (That buffering has one practical consequence for the impatient — see “reading the trail” below.)

The baseline is already on — don’t turn it off

Here’s the part that surprises people: a modern Oracle database is already auditing the right security-critical actions, because Oracle pre-enables a sensible baseline. Two policies do most of the work:

• ORA_SECURECONFIG — audits the actions that change your security posture: CREATE/ALTER/DROP USER, GRANT, REVOKE, ALTER SYSTEM, CREATE/DROP of directories and database links, and more. This is enabled by default from 12.2 onward.
• ORA_LOGON_FAILURES — audits failed logons, which is exactly the fingerprint of a credential attack or a misconfigured application hammering the listener.

Check what’s actually enabled right now — the first query every audit review should start with:

SELECT policy_name, enabled_option, entity_name
FROM   audit_unified_enabled_policies
ORDER  BY policy_name;

If ORA_SECURECONFIG isn’t in that list, someone disabled it, and that is your finding — a database that isn’t recording who changes its own security is a database you can’t investigate. The baseline is the floor, not the ceiling, but it is a floor most estates already have for free. Don’t rip it out in the name of “reducing overhead”; the overhead is negligible and the coverage is exactly the high-value set.

Audit the few things that are specific to you

On top of the baseline, add narrow policies for the things that matter in your database — a sensitive table, a powerful privilege, a specific service account. This is where discipline pays: every policy you add is a policy someone has to read, so add few and make them sharp.

-- Watch reads of one sensitive table (and who did them)
CREATE AUDIT POLICY aud_salary_access
  ACTIONS SELECT, UPDATE, DELETE ON hr.employee_salary;
AUDIT POLICY aud_salary_access;

-- Watch any use of a skeleton-key system privilege
CREATE AUDIT POLICY aud_any_table
  PRIVILEGES SELECT ANY TABLE, DELETE ANY TABLE;
AUDIT POLICY aud_any_table;

Policies can be scoped and conditioned so they capture signal, not volume: AUDIT POLICY aud_x BY app_user limits it to one account, ... WHENEVER NOT SUCCESSFUL records only the failed attempts (often the interesting ones), and an audit_condition on a policy lets you fire only when a column or context matches. The instinct to “audit all DML on everything” is the firehose again — resist it. A handful of targeted policies plus the baseline is a trail a human will actually read during an incident.

Mixed mode, pure mode, and the 23ai cliff

Whether unified auditing is the only audit system running depends on your release, and this trips people up during upgrades.

• 12c–19c ship in mixed mode. Both the old traditional auditing (AUDIT → AUD$) and unified auditing are active. Mixed mode exists so you can adopt unified auditing gradually without breaking legacy scripts. It also means your audit data is split across two systems — the thing unified auditing was supposed to end.
• Pure unified auditing turns traditional auditing off entirely. Moving to it isn’t a SET — you relink the Oracle binary with unified auditing on and restart the instance:

-- database down, then from $ORACLE_HOME/rdbms/lib:
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
-- restart; it's reversible with uniaud_off if you must go back

• 23ai removes the choice. As of Oracle Database 23ai, traditional auditing is desupported. A freshly created 23ai database has no traditional audit at all — unified auditing is the audit system. On an upgrade, any traditional audit settings you were still using are carried forward for compatibility, but the direction is one-way: new work goes to unified, and the old model is on its way out. The practical takeaway — if your compliance runbook still says “AUDIT this, check AUD$,” it needs rewriting before you reach 23ai, not after.

flowchart TD
A["Auditing review"] --> B{"ORA_SECURECONFIG +<br/>ORA_LOGON_FAILURES enabled?"}
B -- No --> B1["Re-enable them<br/>(that's your finding)"]
B -- Yes --> C{"Anything specific to<br/>audit? sensitive table,<br/>ANY privilege, an account"}
C -- Yes --> C1["CREATE AUDIT POLICY,<br/>narrow + conditioned"]
C -- No --> D
C1 --> D{"Which release?"}
D -- "12c–19c" --> D1["Mixed mode: plan the<br/>relink to pure unified"]
D -- "23ai" --> D2["Traditional desupported —<br/>unified is all there is"]
D1 --> E["Read UNIFIED_AUDIT_TRAIL;<br/>purge with DBMS_AUDIT_MGMT"]
D2 --> E

Prove your policies actually capture what you think. The unified-auditing lab stands up an Oracle Database Free container, enables the baseline plus a custom policy on a sensitive table, then triggers the real events — a failed login, a GRANT, a read of the protected table — flushes the audit buffer, and queries UNIFIED_AUDIT_TRAIL to assert every one was recorded. If a policy silently isn’t capturing, the run fails. It’s the difference between “we have auditing” and “we checked.”

Reading and keeping the trail

A trail you never read is theater. Reading unified auditing is one view — UNIFIED_AUDIT_TRAIL — filtered by what you care about:

-- failed logons in the last day (credential-attack fingerprint)
SELECT event_timestamp, dbusername, userhost, return_code
FROM   unified_audit_trail
WHERE  action_name = 'LOGON' AND return_code <> 0
AND    event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER  BY event_timestamp;

Two operational details keep this working. First, because records are written in queued mode, the newest ones may still be in the SGA buffer, not yet in the view — force them out with DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL before you trust “nothing’s there” (the lab does exactly this before it checks). Second, the trail grows forever unless you manage it: set a retention window and a scheduled purge with DBMS_AUDIT_MGMT so the audit table doesn’t quietly consume your tablespace.

-- keep 180 days, then schedule automatic cleanup
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    last_archive_time => SYSTIMESTAMP - INTERVAL '180' DAY);
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    audit_trail_type       => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_purge_interval => 24,
    audit_trail_purge_name => 'unified_purge',
    use_last_arch_timestamp => TRUE);
END;
/

What teams get wrong

• Auditing everything. “Audit all actions on all objects” is the firehose that gets muted and then disabled. Enable the baseline, add a few sharp policies, and review them. Coverage you never read is worse than none — it’s cost with no benefit.
• Turning off the baseline “for performance.” ORA_SECURECONFIG is cheap and its coverage is exactly the security-critical actions. Disabling it is how a database loses the ability to explain who changed its own configuration.
• Assuming traditional auditing still works forever. It’s desupported in 23ai. A runbook built on AUDIT statements and AUD$ queries breaks on the upgrade you haven’t scheduled yet.
• Living in mixed mode by accident. On 19c, “we use unified auditing” is often only half true — the old system is still running alongside it, splitting your trail. Decide to go pure, and relink.
• Forgetting to purge. The unified trail grows without bound. No retention policy means an audit table that eventually pressures SYSAUX/its tablespace — set a DBMS_AUDIT_MGMT purge job on day one.
• Not flushing before you conclude “nothing happened.” Queued-mode records lag. Flush the buffer before you trust an empty result during an investigation.

The one-paragraph version

Auditing isn’t about volume, it’s about signal. Use Unified Auditing — one protected, read-only trail in AUDSYS, configured by policies. Keep the baseline Oracle already enabled (ORA_SECURECONFIG + ORA_LOGON_FAILURES) and add a few narrow policies for your sensitive tables and powerful privileges — never a firehose. Know your mode: 19c runs mixed (traditional and unified together), you move to pure by relinking uniaud_on and restarting, and 23ai desupports traditional auditing outright, so rewrite any AUD$-based runbook before you get there. Read it through UNIFIED_AUDIT_TRAIL (flush the buffer first), and keep it bounded with a DBMS_AUDIT_MGMT purge job. A handful of policies you actually read beats a trail of everything you don’t.

Auditing is the third leg of the security-and-ops discipline, alongside patching and hardening — patching closes known vulnerabilities, hardening closes the configuration gaps, and auditing is how you know when either one is being tested. Do it the same way you do the other two: not exhaustively, but deliberately — the few high-value policies, enabled, purged, and actually read. Prove yours capture what you think with the unified-auditing lab, and put the review on the same cadence as your quarterly patch.