Oracle SQL Firewall: Production Deployment Guide

SQL Firewall is powerful, but deploying it in production without a proper plan can cause application outages. Here’s a safe, phased deployment guide.

Phase 1: Observation (2-4 weeks)

Enable SQL Firewall in capture mode for all application users. Let it observe the full range of SQL generated by your application — including end-of-month batch jobs, reporting queries, and administrative scripts.

EXEC DBMS_SQL_FIREWALL.ENABLE;
EXEC DBMS_SQL_FIREWALL.START_CAPTURE('APP_USER');
EXEC DBMS_SQL_FIREWALL.START_CAPTURE('REPORT_USER');
EXEC DBMS_SQL_FIREWALL.START_CAPTURE('ETL_USER');

Phase 2: Allow-list review

After the observation period, review what was captured:

SELECT sql_text, capture_count, first_seen, last_seen
FROM   dba_sql_firewall_allowed_sql
WHERE  username = 'APP_USER'
ORDER BY last_seen DESC;

Remove any SQL that shouldn’t be on the allow-list (e.g., ad-hoc queries a developer ran during the capture period):

EXEC DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL('APP_USER', :sql_id);

Phase 3: Enable in LOG mode (not BLOCK)

EXEC DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST('APP_USER', DBMS_SQL_FIREWALL.ENFORCE_SQL, FALSE);
-- FALSE = log violations but don't block

Monitor DBA_SQL_FIREWALL_VIOLATIONS for 1-2 weeks. Any legitimate application SQL that triggers violations needs to be added to the allow-list.

Phase 4: Enable BLOCK mode

Only after violations are zero (or only known attack patterns):

EXEC DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST('APP_USER', DBMS_SQL_FIREWALL.ENFORCE_SQL, TRUE);

Ongoing maintenance: When application code changes, update the allow-list before deploying to production. A CI/CD step that runs a short capture against a staging environment and updates the allow-list is ideal.