Creating_Auditing_in_Oracle_Database_23ai

How to Check and Create Auditing Policies in Oracle Database 23ai

Auditing in Oracle Database 23ai is not just about compliance — it’s about knowing exactly who did what in your database, in real time.
Here’s a quick guide to checking your current audit settings and creating your own policies.

Step 1: Check If Unified Auditing Is Enabled

SELECT value  FROM v$option  WHERE parameter = 'Unified Auditing';

If the value is TRUE, Unified Auditing is active.

Step 2: See What’s Being Audited

SELECT policy_name, enabled_option, entity_name FROM audit_unified_enabled_policies;

You’ll see a list of active audit policies and which users they apply to.

Step 3: Create an Audit Policy

Example — audit any SELECT from the CUSTOMERS table:

CREATE AUDIT POLICY customer_data_access ACTIONS SELECT ON hr.customers; AUDIT POLICY customer_data_access;

Step 4: View Audit Records

SELECT event_timestamp, dbusername, action_name, object_schema, object_name FROM unified_audit_trail WHERE policy_name = 'CUSTOMER_DATA_ACCESS' ORDER BY event_timestamp DESC;

Step 5: Remove the Policy

NOAUDIT POLICY customer_data_access; DROP AUDIT POLICY customer_data_access;

Best Practices

  • Audit selectively – Only track what matters.
  • Archive regularly – Use DBMS_AUDIT_MGMT to clean up old logs.
  • Secure access – Limit who can query UNIFIED_AUDIT_TRAIL.

Takeaway

With just a few SQL commands, Oracle 23ai lets you check, configure, and review audit activity in one place.
When combined with AI-powered security features, auditing moves from a passive log to an active security tool.

Recent Posts