DBAtlas · Database operations atlas

Create a Unified Auditing policy, enable it conditionally, review records, remove it

OracleSecurity

Command

-- 1) Object + condition based policy (excluding the batch module):
CREATE AUDIT POLICY pol_customer_dml
  ACTIONS SELECT ON customer_app.customer,
          UPDATE ON customer_app.customer,
          DELETE ON customer_app.customer
  WHEN 'SYS_CONTEXT(''USERENV'',''MODULE'') NOT LIKE ''batch%'''
  EVALUATE PER SESSION;

-- 2) Privileged operations policy:
CREATE AUDIT POLICY pol_ddl_priv
  PRIVILEGES DROP ANY TABLE, ALTER SYSTEM
  ACTIONS DROP USER, ALTER USER, CREATE USER;

-- 3) Enable:
AUDIT POLICY pol_customer_dml EXCEPT customer_app;   -- everyone except the schema owner
AUDIT POLICY pol_ddl_priv WHENEVER SUCCESSFUL;
AUDIT POLICY ORA_LOGON_FAILURES WHENEVER NOT SUCCESSFUL;

-- 4) Which policy is enabled for whom?
SELECT policy_name, enabled_option, entity_name, success, failure
FROM   audit_unified_enabled_policies ORDER BY policy_name;
SELECT policy_name, audit_option, object_schema, object_name, audit_condition
FROM   audit_unified_policies WHERE policy_name LIKE 'POL_%';

-- 5) Records:
SELECT event_timestamp, dbusername, action_name, object_schema, object_name,
       return_code, unified_audit_policies, os_username, userhost
FROM   unified_audit_trail
WHERE  event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
  AND  unified_audit_policies LIKE '%POL_%'
ORDER  BY event_timestamp DESC;
-- 19c (queued-write): EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;  (not in 23ai)

-- 6) Disable and remove:
NOAUDIT POLICY pol_customer_dml;
NOAUDIT POLICY pol_ddl_priv;
DROP AUDIT POLICY pol_customer_dml;
DROP AUDIT POLICY pol_ddl_priv;

What it does

Unified Auditing collects all audit records in a single trail (AUDSYS schema, UNIFIED_AUDIT_TRAIL). Policies are defined per object action, system privilege or action; the WHEN condition and BY/EXCEPT user lists reduce noise.

When to use it

To record access to sensitive tables, monitor privileged DDL and privilege changes, see failed logon attempts, and meet audit requirements.

Caution

No extra license. Auditing SELECT on busy tables produces many records and SYSAUX growth; narrow it with conditions and EXCEPT, and schedule archiving/cleanup with DBMS_AUDIT_MGMT (CLEAN_AUDIT_TRAIL, SET_LAST_ARCHIVE_TIMESTAMP). An enabled policy cannot be dropped (ORA-46361); run NOAUDIT first. Policy changes take effect in new sessions. 19c runs in mixed mode by default, so traditional AUDIT_TRAIL records may also be written.

Open in DBAtlas →

Related