Unified Auditing: practical policies and trail hygiene

Problem

Auditing everything produces a trail nobody reads. Auditing nothing leaves you guessing after an incident. Traditional auditing was also split across several places, which made it hard to search.

Unified Auditing puts events in one trail. My question was which policies to switch on first, and how to keep that trail from filling a tablespace.

Options and trade-offs

• Predefined policies such as ORA_SECURECONFIG and ORA_LOGIN_LOGOUT: quick wins, and worth enabling selectively.
• Custom policies with BY, EXCEPT and WHEN: precise, and they need testing so they do not create blind spots.
• Mirroring to syslog: improves integrity checking, and adds infrastructure to run.

My recommendation

Enable a small number of predefined policies as a baseline, then add custom policies for the actions that matter, such as privileged schema changes and key management. Schedule purging, and use the audit management package instead of deleting rows by hand.

Worked example

Start by enabling the baseline policies and confirming they are active:

AUDIT POLICY ORA_SECURECONFIG;
AUDIT POLICY ORA_LOGIN_LOGOUT;
 
SELECT policy_name FROM audit_unified_enabled_policies;

Add custom policies for high-value actions. The EXCEPT clause keeps a batch account from flooding the trail.

CREATE AUDIT POLICY ddl_pol PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE;
AUDIT POLICY ddl_pol EXCEPT app_batch;
 
CREATE AUDIT POLICY keymgmt_pol ACTIONS ADMINISTER KEY MANAGEMENT;
AUDIT POLICY keymgmt_pol;
 
CREATE AUDIT POLICY hr_fail_pol ACTIONS SELECT ON hr.employees;
AUDIT POLICY hr_fail_pol WHENEVER NOT SUCCESSFUL;

Keep the trail healthy by archiving and purging through DBMS_AUDIT_MGMT:

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    last_archive_time => SYSTIMESTAMP - INTERVAL '90' DAY);
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    use_last_arch_timestamp => TRUE);
END;
/

Only run the purge after you have exported what you need to keep. For stronger integrity, mirror selected events to syslog so a privileged database user cannot quietly erase the only copy.

Checklist

1. Turn on the secure configuration and login policies first.
2. Write named policies for privileged and high-value actions.
3. Use EXCEPT and WHEN to cut noise from known service accounts.
4. Mirror critical events to syslog or an external system.
5. Purge only with DBMS_AUDIT_MGMT, after archiving.
6. Document who owns each policy and how long events are kept.

Where this doesn't apply

If a central audit platform already collects database events, coordinate scopes with it so you do not store everything twice.

One honest limitation

Broad policies create a lot of data very quickly. Start narrow, look at what you actually collect for a week, and widen only where the gaps are real.

Sources

• Oracle Database Security Guide: Configuring Audit Policies
• Oracle Database SQL Language Reference: CREATE AUDIT POLICY (Unified Auditing)
• Oracle Database PL/SQL Packages Reference: DBMS_AUDIT_MGMT