Oracle 23ai Real-Time Dynamic SQL Firewall Policy Training Runbook

1. Overview & Executive Summary

Oracle Database 23ai introduces a native, kernel-level SQL Firewall (DBMS_SQL_FIREWALL) designed to prevent SQL injection (SQLi) attacks and unauthorized database access. Unlike external web application firewalls (WAF) or network sniffers, SQL Firewall executes inside the database kernel, inspecting incoming SQL statements, bind variables, and execution contexts before query parsing occurs.

Deploying SQL Firewall in enterprise ERP environments requires a systematic Training & Enforce lifecycle. Blocking unapproved SQL immediately without prior baseline training will disrupt valid application workflows.

This runbook details how to enable SQL Firewall, capture workload baselines in LEARNING mode, review and approve SQL allow-list policies, enforce real-time block/allow rules, and integrate SQL Firewall alerts with Oracle Unified Auditing.

Process Flow

---

2. Diagnostic Checklist

Run these diagnostic queries as SYSDBA or SYSFIREWALL_ADMIN to verify SQL Firewall status and active training policies.

2.1 SQL Firewall Global Status

-- Check global status of DBMS_SQL_FIREWALL
SELECT status, learning_mode, log_level 
FROM v$sql_firewall_status;

Expected output: STATUS = 'ENABLED', indicating that the kernel-level SQL Firewall engine is active.

2.2 Active Capture & Training Profiles

-- List users currently in LEARNING or PROTECTING mode
SELECT username, status, top_level_only, job_name
FROM dba_sql_firewall_users;

Expected output: Shows targeted application schemas (e.g., SYSADM, BANINST1) and their current enforcement state.

2.3 Inspect Violations & Blocked Statements

-- Query SQL Firewall violation logs in Unified Audit
SELECT event_timestamp, dbusername, client_program_name, userhost, sql_text, firewall_action
FROM unified_audit_trail
WHERE audit_type = 'SQL FIREWALL'
ORDER BY event_timestamp DESC;

---

3. Step-by-Step Resolution Runbook

Step 0: Safety Checks

⚠️ CRITICAL: Always run new application schemas in LEARNING mode for at least 7–14 days (covering full business & payroll cycles) before switching to ENFORCE mode.

1. Verify Unified Auditing is enabled:

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

2. Grant SQL Firewall Administration Role:

GRANT SYSFIREWALL_ADMIN TO sec_admin;

---

Step 1: Enable SQL Firewall Globally

Enable the SQL Firewall engine in the PDB container.

-- Connect to target PDB as SYSDBA
sqlplus sys/Password123@pdb_erp as sysdba

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

-- Verify status
SELECT status FROM v$sql_firewall_status;

---

Step 2: Start Training (Learning Mode) for Application Schemas

Begin capturing baseline SQL traffic for your target enterprise application user (SYSADM for PeopleSoft or BANINST1 for Banner).

BEGIN
  -- Create training profile for application user
  DBMS_SQL_FIREWALL.ADD_USER(
    username       => 'SYSADM',
    top_level_only => TRUE
  );

  -- Start learning mode to log all incoming SQL statements
  DBMS_SQL_FIREWALL.START_TRAINING(
    username => 'SYSADM'
  );
END;
/

Verify that training mode is active:

SELECT username, status FROM dba_sql_firewall_users WHERE username = 'SYSADM';

Expected output: STATUS = 'LEARNING'.

---

Step 3: Stop Training & Generate Allow-List Policy

After the training period (e.g., 7 days of production traffic including batch jobs), stop training and build the baseline policy allow-list.

BEGIN
  -- 1. Stop training capture
  DBMS_SQL_FIREWALL.STOP_TRAINING(
    username => 'SYSADM'
  );

  -- 2. Generate allow-list policy rules from captured training data
  DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST(
    username => 'SYSADM'
  );
END;
/

Inspect the generated SQL allow-list rules:

-- Review allowed SQL commands
SELECT username, sql_command, count_executed, first_executed_time
FROM dba_sql_firewall_allowed_sql
WHERE username = 'SYSADM'
ORDER BY count_executed DESC;

-- Review allowed client IP addresses and OS hosts
SELECT username, client_ip, ip_type, os_user
FROM dba_sql_firewall_allowed_ip
WHERE username = 'SYSADM';

---

Step 4: Enable Real-Time Enforcement & Violation Blocking

Switch SQL Firewall from LEARNING to ENFORCE mode to automatically block unapproved SQL queries or unauthorized connection origins.

BEGIN
  -- Enable enforcement with blocking and auditing
  DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
    username       => 'SYSADM',
    enforce        => DBMS_SQL_FIREWALL.ENFORCE_ALL, -- Enforce IP, OS user, and SQL text
    block          => TRUE                           -- Block non-compliant queries
  );
END;
/

Verify enforcement mode:

SELECT username, status, enforce, block
FROM dba_sql_firewall_users
WHERE username = 'SYSADM';

Expected output: STATUS = 'PROTECTING', ENFORCE = 'ENFORCE_ALL', BLOCK = 'TRUE'.

---

Step 5: Configure Unified Audit Alerting for Firewall Violations

Create a Unified Audit policy to record all SQL Firewall violations and trigger security alerts.

-- Create Unified Audit policy for SQL Firewall
CREATE AUDIT POLICY audit_sql_firewall_violations
  ACTIONS COMPONENT = SQL_FIREWALL ALL;

-- Enable the audit policy
AUDIT POLICY audit_sql_firewall_violations;

-- Query recent blocked SQL Firewall violations
SELECT event_timestamp, dbusername, client_program_name, userhost, sql_text
FROM unified_audit_trail
WHERE audit_type = 'SQL FIREWALL'
  AND firewall_action = 'BLOCKED'
ORDER BY event_timestamp DESC;

---

📚 Official Documentation & Technical References

Oracle Documentation

• Oracle Database 23ai Security Guide — Managing SQL Firewall
• Oracle Database 23ai PL/SQL Packages Reference — DBMS_SQL_FIREWALL

My Oracle Support

For official My Oracle Support SQL Firewall deployment guidelines, search the MOS Knowledge Base directly:

• Oracle 23ai SQL Firewall Policy Generation & Troubleshooting
• Unified Audit Trail Integration for DBMS_SQL_FIREWALL

Related Resources

• Oracle Database Security Solutions & Best Practices

---

Need Expert Security Help?

Implementing native 23ai SQL Firewall across complex enterprise ERP schemas (PeopleSoft, Banner, EBS) requires precise baseline training to avoid application disruption. DBPros.Net’s certified Oracle security engineers can help you:

• Design & Conduct zero-downtime SQL Firewall training cycles across staging and production
• Audit & Refine generated SQL allow-lists and IP/OS user rules
• Configure & Integrate Unified Audit alerting with your enterprise SIEM (Splunk, Datadog, QRadar)
• Harden Oracle 23ai databases with Vault, TDE, and SQL Firewall 24/7/365

Contact DBPros.Net Security Team | Explore Enterprise Security Services