Deploying SQL Firewall into Production

In the previous post, we walked through the setup and configuration of SQL Firewall in Oracle AI Database 26ai. We covered the licensing picture, set up the firewall administrator and the application user, captured a representative workload, generated the allow list, enabled enforcement, and watched the kernel block UPDATE statements, sensitive column queries, and table access outside the captured set. If you have not gone through that walkthrough yet, you can find it at SQL Firewall in Oracle AI Database 26ai.

Applications evolve over time. New versions of the applications roll out to production with new and modified SQL & PL/SQL packages. As a result, new SQL will need to be added to the allow list. Maintenance windows may also call for the firewall to be taken offline and brought back up cleanly.

In this post, we will work through each of those operational tasks. The same app_user and HR schema we set up in Part 1 carry over, so every example continues to run against the database we have been building.

A Phased Rollout: Context First, SQL Later

Going from no SQL Firewall to full SQL-text enforcement on day one is a leap that lot of companies are not ready for. The capture phase has to be thorough, the application workload has to be exercised for a lengthy duration, and the DBA team has to be ready to deal with SQL violations in a timely manner.

A phased rollout is the safer path. First start with context-only enforcement (ENFORCE_CONTEXT). Then deploy full enforcement (ENFORCE_ALL) once the team is operating the firewall comfortably and the SQL allow list is mature.

↳Phase I: Context-Only Enforcement

Security protocols are best adopted in a phased implementation. For Phase I, we can start with locking down:

• the source IP address
• the operating system user
• the client program

This phase delivers measurable security proof of value as IP ranges, OS users, and client programs are known quantities that can be addressed ahead of time. There is no full workload capture to get right before you turn enforcement on.

EXEC DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT(
  'APP_USER', DBMS_SQL_FIREWALL.ENFORCE_CONTEXT, TRUE
);

If you whitelist your application server IPs and the database server IP, plus whitelist sqlplus, sqldeveloper, and JDBC Thin Client as programs, then any connection from an unapproved IP is blocked regardless of which client program is being used. So sqlplus from a DBA laptop on the corporate VPN is blocked because that laptop's IP is not on the allow list. This gives us an example of how we can leverage context level lockdown for production environments.

What this also catches is a stolen credential being used from an unfamiliar IP, an attacker connecting from a tool the application never uses or a third-party SQL client, or a session originating from an OS user that has no business reaching the database. Those are common attack surface areas, and they all light up under context-only enforcement without any of the application workload being captured.

Phase I is not a substitute for SQL-text enforcement. It is a stepping stone that buys you real defense in week one while the team builds toward Phase II.

↳Phase II: Full Context and SQL Enforcement

Once Phase I has been running for a month or two (or quarter or two for complex applications), the team has settled into the operational rhythm of monitoring violations, refining the allow list, and auditing for new schemas. At that point, you have everything you need to add SQL-text enforcement on top.

EXEC DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT(
  'APP_USER', DBMS_SQL_FIREWALL.ENFORCE_ALL, TRUE
);

Phase II is where the firewall earns the rest of its license cost. SQL injection attempts, unauthorized ad hoc queries, and any SQL outside the captured workload are blocked before execution. The phase change itself is a single procedure call, but the capture work that feeds it has to be done well, which is why Phase II comes after the team has the discipline to do that work.

Catching a SQL Injection Attempt

SQL injection through a stored procedure is one of the more common attacks that ends up in the violation log. To demonstrate, we created a deliberately vulnerable procedure in the HR schema that builds a SQL statement by concatenating user input. This is the kind of code review finding that should never make it to production, but it shows up in legacy code more often than anyone wants to admit.

CREATE OR REPLACE PROCEDURE hr.lookup_employee(p_email IN VARCHAR2) AS
  v_sql   VARCHAR2(4000);
  v_first VARCHAR2(20);
  v_last  VARCHAR2(25);
BEGIN
  v_sql := 'SELECT first_name, last_name FROM hr.employees WHERE email = '''
           || p_email || '''';
  EXECUTE IMMEDIATE v_sql INTO v_first, v_last;
  DBMS_OUTPUT.PUT_LINE(v_first || ' ' || v_last);
END;
/

GRANT EXECUTE ON hr.lookup_employee TO app_user;

A classic injection attack against this procedure would inject a UNION query into the email parameter that the firewall has never observed during capture.

EXEC hr.lookup_employee(
  'x'' UNION SELECT salary FROM hr.employees--'
);

ORA-47605: SQL Firewall violation

The injected SQL is, as far as the firewall is concerned, a brand new statement. The firewall blocks it at the kernel level before execution, regardless of the privileges the user has and regardless of whatever flaws live inside the procedure's code. This is the kind of defense that you cannot get from a network firewall or a WAF, because by the time the SQL has reached the database, the WAF is already out of the picture.

Reviewing Violations

Every blocked or logged statement is recorded in the DBA_SQL_FIREWALL_VIOLATIONS view. That view is where a DBA or a security analyst goes to understand what happened after the fact.

SELECT sql_text,
       cause,
       firewall_action,
       ip_address,
       TO_CHAR(occurred_at, 'YYYY-MM-DD HH24:MI:SS') occurred_at
  FROM dba_sql_firewall_violations
 WHERE username = 'APP_USER'
 ORDER BY occurred_at DESC;

SQL_TEXT                                          CAUSE           ACTION    IP_ADDRESS    OCCURRED_AT
------------------------------------------------  --------------  -------   -----------   -------------------
UPDATE HR.EMPLOYEES SET SALARY ...     SQL violation   BLOCKED   10.211.55.2   2026-04-18 14:22:31
SELECT EMPLOYEE_ID, SALARY, COMMISSION_PCT ...    SQL violation   BLOCKED   10.211.55.2   2026-04-18 14:22:45
SELECT EMPLOYEE, CLIENT_NAME, HOURS FROM ...      SQL violation   BLOCKED   10.211.55.2   2026-04-18 14:22:58
x' UNION SELECT SALARY FROM ...                   SQL violation   BLOCKED   10.211.55.2   2026-04-18 14:23:01

• The cause column tells you whether the violation was a:

• SQL violation, meaning an unrecognized statement
• Context violation, meaning an unrecognized IP address, OS user, or client program.

• The firewall_action column tells you whether the offending statement was blocked outright or only logged. Together, those two columns give you the full picture of what the firewall did and why it did it.

Monitor the Violation Log and Set Up Alerts

The violation log is only useful if someone is actually looking at it. In a small environment, opening the view once a day might be enough. In a production system, you need an alert that pages a human when the volume of new violations crosses a threshold. SQL Firewall is the kind of control that benefits from a human in the loop (at lease for now until AI Agents become lot better). The violations it records can be either legitimate new SQL that was missed during capture or actual attack traffic.

The threshold you set depends on how confident you are that the capture phase covered the full workload. If you exercised every code path the application runs in production and you trust the resulting allow list, set the threshold low. If you did your homework, an alert at 10 new violations may be a good starting point. For others, an alert at 100 new violations in a day can be a reasonable starting point. At that level, something unexpected is happening and a DBA needs to look. If you know the capture only covered a subset of the application, or the allow list is still being shaped, set the threshold higher. An alert at 1000 new violations gives the application room to ship new SQL through the violation log without paging a human every hour, but it still flags genuine spikes that need attention.

A simple counting query is enough to drive the alert. The query below returns the number of violations recorded in the last 24 hours.

SELECT COUNT(*) AS new_violations
  FROM dba_sql_firewall_violations
 WHERE occurred_at >= SYSTIMESTAMP - 1;

Wrap that count in a database scheduler job (or cron job) that runs on a regular interval and sends an email or a webhook to your monitoring system when the count exceeds your threshold. If you already have a monitoring stack that polls the database, add the same query as a custom probe and let the existing alerting pipeline handle it. Either way, do not run SQL Firewall in production without an alert.

The whole point of the violation log is to be in front of it when unexpected activity shows up. If nobody is watching it, you are paying for the firewall to record attacks you will never see.

Refining the Allow List After Go Live

Applications evolve and SQL statements deviate with tuning. New features ship and they bring new SQL with them. Rather than tearing down the allow list and rebuilding it from scratch every time a new release goes out, you can append new entries directly out of the violation log.

EXEC DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST(
  'APP_USER', DBMS_SQL_FIREWALL.VIOLATION_LOG
);

That call takes every statement that ended up in the violation log for the user and promotes it onto the allow list. Use it with care, because the violation log will contain both legitimate new SQL and any malicious traffic that has been hitting the firewall. Thoroughly review the violations one by one before promoting them, and confirming that every entry you are about to promote is actual application SQL rather than something that should stay blocked.

You can also adjust enforcement granularity without disabling the allow list entirely. There are two adjustments that come up often during a release.

-- Switch to SQL only enforcement (stop checking connection context)
EXEC DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT(
  'APP_USER', DBMS_SQL_FIREWALL.ENFORCE_SQL, TRUE
);

-- Switch to log only mode (stop blocking, keep logging)
EXEC DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT(
  'APP_USER', DBMS_SQL_FIREWALL.ENFORCE_ALL, FALSE
);

The second set of options is particularly useful during a release deployment. You can flip the firewall into log only mode just before the deployment, let the new SQL run and get captured into the violation log without disrupting users, promote the legitimate entries onto the allow list once the release is stable, and then flip the firewall back into blocking mode.

Auditing for New Schemas and Applications

SQL Firewall coverage is not a setup and forget implementation. It does not take much maintenance, but it does take some care. New schemas get created as applications mature. New connection users get provisioned for new data integrations. New developers get their own credentials. None of that activity is automatically picked up by an existing capture or allow list. In short, every new connection user that issues SQL against the database accesses the database untethered (not quite, but the extra level of security will make you feel like it :-) )

Build a periodic audit into your DBA runbook. Once a month or quarter should be a reasonable cadence for most environments. Run it more often if your application footprint changes frequently. The audit needs to surface any user account that is open in the database but does not have a SQL Firewall allow list.

SELECT u.username
  FROM dba_users u
  LEFT JOIN dba_sql_firewall_allow_lists f
    ON u.username = f.username
 WHERE u.account_status = 'OPEN'
   AND u.oracle_maintained = 'N'
   AND f.username IS NULL
  AND u.username NOT IN ('SQLFIRE_ADMIN');

USERNAME
----------------------------------------------
C##DEBEZIUM
PDBADMIN
HR
MYSQLDEMO
VECTORDEMO

Anything that comes back is a user that can reach the database without firewall coverage. That is the gap to close, either by adding a capture for the user or by deciding that the user does not need protection.

Log Maintenance

The capture and violation logs grow over time and they need periodic housekeeping in order to keep them from filling up the underlying tablespace. Oracle provides three procedures for that work.

-- Flush in memory log entries to the on disk tables
EXEC DBMS_SQL_FIREWALL.FLUSH_LOGS;

-- Purge all capture logs for a specific user
EXEC DBMS_SQL_FIREWALL.PURGE_LOG(
  'APP_USER', NULL, DBMS_SQL_FIREWALL.CAPTURE_LOG
);

-- Purge violations older than 30 days
EXEC DBMS_SQL_FIREWALL.PURGE_LOG(
  'APP_USER', SYSTIMESTAMP - 30, DBMS_SQL_FIREWALL.VIOLATION_LOG
);

-- Purge all log types older than 30 days
EXEC DBMS_SQL_FIREWALL.PURGE_LOG(
  'APP_USER', SYSTIMESTAMP - 30, DBMS_SQL_FIREWALL.ALL_LOGS
);

We recommend scheduling the purge as a database job (or as a cron job) so that old violation data does not pile up. Thirty days of retention is a reasonable starting point for most environments. Your security and compliance teams may require 90 days or even years of retention depending on the kind of violation data the database holds.

Moving Firewall Tables to a Dedicated Tablespace

The three underlying tables that store firewall data, SQL_LOG$, EVENT_LOG$, and VIOLATION_LOG$, all live in the SYSAUX tablespace.

SELECT table_name, tablespace_name
  FROM dba_tables
 WHERE table_name IN ('SQL_LOG$', 'EVENT_LOG$', 'VIOLATION_LOG$');

TABLE_NAME        TABLESPACE_NAME
----------------  ---------------
SQL_LOG$          SYSAUX
EVENT_LOG$        SYSAUX
VIOLATION_LOG$    SYSAUX

For most environments, leaving the tables in the SYSAUX tablespace is adequate. In the event that your tables grow into tens or hundreds of gigabytes, you can move the tables out to a dedicated tablespace. Otherwise this section is optional.

You will need to disable the firewall before moving the tables, and reenable it once the move is complete.

EXEC DBMS_SQL_FIREWALL.DISABLE;

CREATE TABLESPACE fw_ts
  DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/fw_ts01.dbf'
  SIZE 8G AUTOEXTEND ON NEXT 4G;

ALTER TABLE SYS.SQL_LOG$       MOVE TABLESPACE fw_ts;
ALTER TABLE SYS.EVENT_LOG$     MOVE TABLESPACE fw_ts;
ALTER TABLE SYS.VIOLATION_LOG$ MOVE TABLESPACE fw_ts;

EXEC DBMS_SQL_FIREWALL.ENABLE;

You can verify the move by querying DBA_TABLES.

SELECT table_name, tablespace_name
  FROM dba_tables
 WHERE table_name IN ('SQL_LOG$', 'EVENT_LOG$', 'VIOLATION_LOG$')
 ORDER BY 1;

TABLE_NAME        TABLESPACE_NAME
----------------  ---------------
EVENT_LOG$        FW_TS
SQL_LOG$          FW_TS
VIOLATION_LOG$    FW_TS

Quick Reference of SQL Firewall DBA Views

Every aspect of the SQL Firewall is visible through a set of data dictionary views. The table below shows the inventory in one place for quick reference.

Disabling the Firewall

If you need to take the firewall offline for maintenance or troubleshooting, the safe order is to disable the allow list for the user first, and then disable the firewall globally.

EXEC DBMS_SQL_FIREWALL.DISABLE_ALLOW_LIST('APP_USER');

EXEC DBMS_SQL_FIREWALL.DISABLE;

Disabling the firewall does not delete your allow lists or your violation logs. Everything is preserved inside of the LOG$ tables.

Patching and Maintenance Considerations

Every user you protect with SQL Firewall is a user whose allow list has to be maintained. That sounds obvious, but it has consequences during patching and maintenance windows that bite teams who do not plan for them ahead of time.

The maintenance overhead is real. For every schema you protect, you have a capture, an allow list, and an enforcement state to manage, even for schemas that have no traffic 364 days a year. When the 365th day rolls around and a patch needs to connect to that schema, the firewall is in the path of that connection.

Before any maintenance that touches a protected schema, work through the following steps:

1. Unlock the schema account if it was locked at rest.
2. Disable the SQL Firewall allow list for that user.
3. Run the patch.
4. Re-enable the allow list.
5. Relock the account.

Make sure to document the unlock/relock and disable/enable steps in the patching runbook. If those steps are part of the same checklist that runs the actual patch, they will not be skipped.

Conclusion

We have covered the operational support when dealing with SQL Firewall in Oracle AI Database 26ai. You know how to review violations after the fact, append legitimate new SQL onto the allow list when applications release new features, keep the capture and violation logs from filling up the underlying tablespace, and bring the firewall up and down cleanly for maintenance.

Look at Part 1 for the introduction and step-by-step procedures to set up SQL Firewall. For an Oracle environment that holds anything sensitive, leaving SQL Firewall off is a choice that is harder to defend with every passing release.

Charles is an Oracle ACE Director, VMware vExpert, and author of over a dozen books in the Oracle ecosystem.