Oracle 23ai SQL Firewall & Unified Audit: Real-Time Threat Prevention & Compliance Runbook

Executive Summary: Native Database Security in Oracle 23ai

ecuring enterprise databases against SQL injection, privilege abuse, and credential theft has historically relied on external Web Application Firewalls (WAF) or complex database activity monitoring (DAM) proxies. External proxies often fail to inspect encrypted connections, stored procedure calls, or direct SQL execution within database sessions.

Oracle Database 23ai introduces SQL Firewall (DBMS_SQL_FIREWALL) directly embedded inside the database kernel. SQL Firewall captures SQL statements, connection contexts, and execution paths issued by application users. By training allow-list profiles, SQL Firewall actively blocks unauthorized SQL syntax and abnormal session origins in real time before execution.

This guide delivers a step-by-step production runbook for configuring Oracle 23ai SQL Firewall alongside Unified Audit policies for continuous monitoring and compliance enforcement.

Technical Architecture: Dual Security Perimeter

Pre-Check Diagnostic Checklist

Run the following queries connected as SYSDBA or AUDIT_ADMIN to verify SQL Firewall and Unified Audit status:

-- Check SQL Firewall status in current PDB
SELECT status, status_updated_on, exclude_jobs FROM dba_sql_firewall_status;

-- Check active SQL Firewall capture logs
SELECT username, sql_text, sql_signature 
FROM dba_sql_firewall_allowed_sql 
WHERE ROWNUM <= 10;

-- Check Unified Audit configuration
SELECT VALUE FROM v$option WHERE PARAMETER = 'Unified Auditing';

-- Check active audit policies
SELECT policy_name, enabled_option, entity_name 
FROM audit_unified_enabled_policies;

Step-by-Step SQL Firewall & Unified Audit Runbook

Step 0: Initial Safety Checks & Enabling SQL Firewall

SQL Firewall must be enabled at the container level by a user with DBA or ADMINISTER SQL FIREWALL privileges:

-- Connect to target PDB
ALTER SESSION SET CONTAINER = BANNERPDB;

-- Enable SQL Firewall engine
BEGIN
  DBMS_SQL_FIREWALL.ENABLE;
END;
/

-- Verify status
SELECT status, status_updated_on FROM dba_sql_firewall_status;

Step 1: Create Application User Capture & Learning Baseline

Before enforcing blocking rules, place application schemas (e.g., BANPROXY or PSADMIN) into TRAINING/LEARNING mode during standard workload or staging regression runs:

-- Start capture profile for application user
BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE_INITIAL_SET(
    username => 'BANPROXY',
    top_level_only => TRUE
  );
  
  DBMS_SQL_FIREWALL.START_CAPTURE(
    username => 'BANPROXY'
  );
END;
/

Allow application workflows, batch jobs, and API calls to execute normally for a baseline window (e.g., 24–48 hours) to train the allow-list profile.

Step 2: Stop Capture & Generate Allow-List Rules

Once baseline application activity has been captured, stop training and inspect generated SQL signatures:

-- Stop capture mode
BEGIN
  DBMS_SQL_FIREWALL.STOP_CAPTURE(
    username => 'BANPROXY'
  );
END;
/

-- Review captured SQL statements
SELECT sql_text, sql_signature 
FROM dba_sql_firewall_allowed_sql 
WHERE username = 'BANPROXY';

Generate the enforcement allow-list profile:

-- Create allow-list profile from captured baseline
BEGIN
  DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST(
    username => 'BANPROXY'
  );
END;
/

Step 3: Enable Enforcement Mode (Observe vs. Block)

Phase 3A: AUDIT-ONLY Mode (Observe Phase)

Enable SQL Firewall in AUDIT_ONLY mode first to log violations without interrupting live application traffic:

BEGIN
  DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
    username => 'BANPROXY',
    enforce => DBMS_SQL_FIREWALL.ENFORCE_SQL,
    block => FALSE
  );
END;
/

Check violation logs in dba_sql_firewall_violations:

SELECT username, sql_text, client_program, ip_address, cause, firewall_action, occurred_at 
FROM dba_sql_firewall_violations 
ORDER BY occurred_at DESC;

Phase 3B: FULL BLOCKING Mode (Active Defense)

Once zero false positives are confirmed, enable active blocking to terminate unauthorized SQL execution:

BEGIN
  DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
    username => 'BANPROXY',
    enforce => DBMS_SQL_FIREWALL.ENFORCE_ALL,
    block => TRUE
  );
END;
/

Step 4: Configure Unified Audit Policy for Compliance

Combine SQL Firewall violation logging with Oracle Unified Audit policies to meet PCI-DSS, FERPA, and SOC 2 audit trail requirements:

-- Create Unified Audit policy for SQL Firewall violations and privileged DBA actions
CREATE AUDIT POLICY sys_firewall_compliance_pol
  ACTIONS COMPONENT = SQL_FIREWALL ALL,
          ACTIONS LOGON, LOGOFF, ALTER SYSTEM, ALTER USER;

-- Enable audit policy for all users
ENABLE AUDIT POLICY sys_firewall_compliance_pol;

Query unified audit records:

SELECT event_timestamp, dbusername, action_name, unified_audit_policies, sql_text 
FROM unified_audit_trail 
WHERE unified_audit_policies LIKE '%SYS_FIREWALL_COMPLIANCE_POL%'
ORDER BY event_timestamp DESC;

Troubleshooting Common SQL Firewall Errors

📚 Official Documentation & Technical References

• Oracle Database 23ai Security Guide: Using Oracle SQL Firewall
• Oracle Database 23ai Security Guide: Introduction to Auditing
• ORACLE-BASE: SQL Firewall in Oracle Database 23ai
• Oracle Security Advisory: SQL Injection Defense Architecture

Need assistance configuring Oracle 23ai SQL Firewall or tuning Unified Audit trails for your ERP environments? Schedule a Database Security Audit or Contact our Infrastructure Specialists.