Hackers have AI in their toolbelt now. The threat surface is getting bigger and bigger with every model release. AI driven attack tools can throw SQL injection payloads at an application faster than any human could. They can read defensive responses and adjust their next attempt in real time. They can probe application logic at a scale that a manual attacker would not have the patience for.
I tell everyone that AI accelerates AI developed applications and hacking. With each new generation of language models, the sophistication of these attacks improves exponentially. What used to take a skilled adversary an afternoon of reconnaissance and hand crafted payloads can now be done in a few minutes by a script.
Most organizations have responded by hardening the layers they can see, including application firewalls, network segmentation, intrusion detection systems, endpoint protection, and operating system hardening. Those investments matter. We are not going to argue that any of them should be removed. They share a blind spot, however. Once a SQL statement makes it through the application tier and arrives at the database on a trusted connection, the database simply runs it. There is no challenge, there is no allow list at the kernel, and there is no verification that the statement is something the application was ever designed to issue in the first place. The network is protected, the operating system is patched, and the application has a WAF in front of it. Yet the database that holds the actual data the attackers are after, including the employee records, the financial transactions, and the credentials, is often the least protected layer in the stack.
We were recently engaged in a database security assessment for a client. One of the recommendations that came out of that engagement was to purchase and implement SQL Firewall as part of their defense in depth strategy. That conversation is what prompted this blog post. If you are an Oracle customer who is working through how to protect the database layer itself, or if your security team is asking questions that you do not yet have a confident answer to, this walkthrough is for you.
In this post, we will walk through the setup and configuration of SQL Firewall in Oracle AI Database 26ai (26ai), starting from enabling the firewall, then capturing what an application's normal workload looks like, then generating an allow list out of that capture, and finally enforcing the allow list at the kernel level. We will use the HR schema that we built up in our MCP blog series so that the examples line up with code you have already seen. Every command in this post was run against a real PDB with real tables and real data.
What Is SQL Firewall?
SQL Firewall is a security feature that lives inside the Oracle database kernel itself. It was first introduced with Oracle Database 23c. The feature has been available in every release since, including the current Oracle AI Database 26ai that we are running through in this post. It works in two phases. In the first phase, it watches a user run SQL and learns which statements that user is supposed to be running. From that observation, it builds an allow list. In the second phase, it sits in front of the execution engine and checks every incoming statement against the allow list. Anything that does not match is either blocked outright or written to a violation log, depending on how you choose to configure the enforcement.
Because the firewall is inside the kernel, there is no execution path that can sneak around it. It does not matter whether the statement is arriving over JDBC, OCI, a PL/SQL procedure, an MCP connection, or anything else. If the statement is not on the allow list for that user, the database refuses to execute the SQL statement. The database goes from being the soft target at the bottom of the stack to being an impenetrable fortress.
Licensing Requirements
Before you start configuring SQL Firewall, you should understand the licensing picture, because SQL Firewall is not part of the base Oracle Database Enterprise Edition license.
SQL Firewall is included as part of the Database Vault feature set: https://docs.oracle.com/en/database/oracle/oracle-database/26/dblic/Licensing-Information.html#GUID-AB354617-6614-487E-A022-7FC9A5A08472
According to the following Oracle licensing information, SQL Firewall is included with the Oracle Database Vault option or with Oracle Audit Vault and Database Firewall.
For customers running on Oracle Cloud Infrastructure (OCI), the licensing picture is much friendlier. Database Vault is included at no additional cost on the BaseDB EE-HP, BaseDB EE-EP, and ExaDB-D services. That means SQL Firewall, along with the rest of the Database Vault feature set, is available for every database on those tiers.
This is one of the reasons we can support Oracle's claim that OCI is the most secure cloud for an Oracle workload. On the other clouds, or on premises, Database Vault is an extra line item that a security and database team has to budget for. On OCI, the option is included as part of the subscription. By removing the licensing friction, customers tend to adopt and implement SQL Firewall, resulting in protected database fortresses.
SQL Firewall is not available on Standard Edition.
Setting Up the Users
We will need two users for our code examples. The first user is the firewall administrator. This user will own the captures, manage the allow lists, and review violations. Oracle ships a built in role called SQL_FIREWALL_ADMIN that grants the privileges this administrator needs. There is also another role called SQL_FIREWALL_VIEWER that gives a user read only access to the firewall views without the ability to change any rules. This role is useful for a security analyst who needs to monitor activity but should not be able to alter enforcement.
The second user is a low privilege application user that represents the connection that an application server, a reporting tool, or an MCP server would use against the database in production. We will grant this user SELECT access to the HR schema that we built in earlier posts.
Here are the SQL setup scripts for both users.
CREATE USER sqlfire_admin IDENTIFIED BY "FwAdm#2026!Sec";
GRANT CREATE SESSION TO sqlfire_admin;
GRANT SQL_FIREWALL_ADMIN TO sqlfire_admin;
CREATE USER app_user IDENTIFIED BY "AppUsr#26ai!RO";
GRANT CREATE SESSION TO app_user;
GRANT SELECT ANY TABLE ON SCHEMA hr TO app_user;Notice that app_user is granted SELECT only, with no INSERT, UPDATE, or DELETE privileges anywhere. That is the kind of least privilege connection that SQL Firewall is designed to wrap around. Even with SELECT only privileges, you still want to control which specific queries this user is allowed to run, because not every SELECT is equal once sensitive columns and lookup tables come into the picture.
Enabling SQL Firewall
SQL Firewall is disabled by default. The first step is to turn it on at the PDB level. You only have to do this one time per PDB.
EXEC DBMS_SQL_FIREWALL.ENABLE;You can verify the status by querying the DBA_SQL_FIREWALL_STATUS view.
SELECT status FROM dba_sql_firewall_status;STATUS
--------
ENABLEDAt this point the firewall is "locked and loaded", but it is not enforcing anything yet. There are no users being watched and there are no rules in place.
Capturing the Application Workload
The CREATE_CAPTURE procedure starts the learning phase. We "tell SQL Firewall" to follow a specific user around and record every SQL statement that user issues during a window of time. The application executes its normal workload through the database while the firewall is watching, so the firewall can build a picture of what the normal workload looks like for that user.
We will create a capture for APP_USER and start the recording:
BEGIN
DBMS_SQL_FIREWALL.CREATE_CAPTURE (
username => 'APP_USER',
top_level_only => TRUE,
start_capture => TRUE
);
END;
/The top_level_only parameter is worth a comment. When you set it to TRUE, the firewall captures only the SQL that the user issues directly. It ignores the recursive SQL that Oracle generates internally for things like dictionary lookups, space management, and parsing. In almost every real engagement, you want this set to TRUE, because it keeps the allow list lean and focused on the application's own statements instead of bloated with thousands of internal queries that the user did not actually write.
Alternatively, you can also create the capture without starting it right away. That is useful when you want precise control over the observation window or when you want to coordinate the capture with a specific test cycle.
BEGIN
DBMS_SQL_FIREWALL.CREATE_CAPTURE (
username => 'APP_USER',
top_level_only => TRUE,
start_capture => FALSE
);
END;
/
EXEC DBMS_SQL_FIREWALL.START_CAPTURE('APP_USER');With the capture running, we connect as app_user and run every SQL statement that the application is expected to issue in production. Anything that does not run during the capture window will be blocked the moment enforcement turns on, so it is important to exercise the full workload before stopping.
A capture is only as good as the workload that runs through it. If you start the capture on a normal Tuesday and stop it that Friday, the firewall has no record of the SQL that runs during month-end close, quarter-end consolidation, or year-end financial reporting. Those cycles often include batch jobs, reports, and validation queries that simply do not show up in everyday traffic. Plan to leave the capture running across at least one full month-end, and ideally a quarter-end and a year-end as well, before you generate the allow list. If the application has annual cycles such as fiscal year close, audit reporting, or 1099 generation, the capture window needs to include those events. Otherwise the firewall will block them the first time they run after enforcement turns on. What should have been a routine close cycle becomes an emergency ticket at 2 AM.
We start with a basic single table query.
SELECT employee_id, first_name, last_name, email, department_id
FROM hr.employees
WHERE department_id = 60;EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL DEPARTMENT_ID
----------- ---------- --------- --------- -------------
103 Alexander James AJAMES 60
104 Bruce Miller BMILLER 60
105 David Williams DWILLIAMS 60
106 Valli Jackson VJACKSON 60
107 Diana Nguyen DNGUYEN 60Next, here is a three-table join that a reporting application might run on a regular basis to list IT staff with their job titles and department names.
SELECT e.first_name, e.last_name, j.job_title, d.department_name
FROM hr.employees e
JOIN hr.jobs j
ON e.job_id = j.job_id
JOIN hr.departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'IT'
ORDER BY e.last_name;FIRST_NAME LAST_NAME JOB_TITLE DEPARTMENT_NAME
---------- --------- ---------- ---------------
David Austin Programmer IT
Bruce Ernst Programmer IT
Alexander Hunold Programmer IT
Diana Lorentz Programmer IT
Valli Pataballa Programmer ITAnd finally a simple aggregate that a dashboard tile might call once per minute.
SELECT COUNT(*) FROM hr.employees
WHERE department_id = 60;COUNT(*)
--------
5Once the workload has been exercised end to end, we stop the capture.
EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE('APP_USER');We can then check what the capture recorded by querying the capture log as the firewall administrator.
SELECT command_type, sql_text, ip_address, os_user
FROM dba_sql_firewall_capture_logs
WHERE username = 'APP_USER';COMMAND_TYPE SQL_TEXT IP_ADDRESS OS_USER
------------ ---------------------------------------------------- ---------- -------
SELECT SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,... 10.211.55.2 oracle
SELECT SELECT E.FIRST_NAME,E.LAST_NAME,J.JOB_TITLE,... 10.211.55.2 oracle
SELECT SELECT COUNT (*) FROM HR.EMPLOYEES WHERE DEPART... 10.211.55.2 oracleEvery statement that the user issued during the capture window is now sitting in the log along with the IP address that originated the connection, the operating system user, and the client program. Those connection attributes will matter in a moment, because the firewall can enforce on more than just the SQL text itself.
Generating the Allow List
With the capture stopped, we generate the allow list. This call takes everything that the firewall recorded during the capture window and turns it into the approved set of SQL statements and connection contexts for that user.
EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST('APP_USER');You can inspect the allow list across four views. Each one covers a different dimension of what the firewall learned.
SELECT sql_text, accessed_objects
FROM dba_sql_firewall_allowed_sql
WHERE username = 'APP_USER';
SELECT ip_address
FROM dba_sql_firewall_allowed_ip_addr
WHERE username = 'APP_USER';
SELECT os_program
FROM dba_sql_firewall_allowed_os_prog
WHERE username = 'APP_USER';
SELECT os_user
FROM dba_sql_firewall_allowed_os_user
WHERE username = 'APP_USER';If the capture happened to miss a legitimate IP address or client program, you do not have to rerun the entire capture in order to add it. You can append the missing context manually.
BEGIN
DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT (
username => 'APP_USER',
context_type => DBMS_SQL_FIREWALL.IP_ADDRESS,
value => '10.211.55.3'
);
END;
/If you find an entry that should not be on the allow list, you can call DELETE_ALLOWED_CONTEXT with the same parameters to take it back off.
Enabling Enforcement
This is the step where the firewall stops observing and starts acting. You decide what should be enforced and whether violations should be blocked outright or simply logged for review.
BEGIN
DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (
username => 'APP_USER',
enforce => DBMS_SQL_FIREWALL.ENFORCE_ALL,
block => TRUE
);
END;
/Oracle provides three levels of enforcement with the DBMS_SQL_FIREWALL package for the enforce parameter:
CONSTANT WHAT IT CHECKS
-------------------- --------------------------------------------------
1. ENFORCE_SQL SQL text only. Blocks unrecognized statements.
2. ENFORCE_CONTEXT Connection context only. Blocks unrecognized IPs,
OS users, or client programs.
3. ENFORCE_ALL Both SQL text and connection context.The block parameter controls what the firewall does when it sees a violation. When it is set to TRUE, the firewall rejects the offending statement and the session sees ORA-47605. When it is set to FALSE, the firewall lets the statement run but records the violation in the violation log.
As general best practices, we recommend that you start any SQL Firewall deployment with block => FALSE. You can validate your allow list against real database workloads without disrupting the application. Once you have validated the violation log for a few days (or even weeks or months) and are confident that the allow list is right, you can flip the parameter to TRUE and let the database kernel start enforcing. This approach is very similar to what we would do with SELinux.
The next level of firewalling can be the IP addresses, OS users and client programs. You can white list all the resources that are allowed to connect to the database server. This is a simpler defense protocol that does not need application involvement.
You can verify the enforcement status at any time.
SELECT username, status, enforce, block
FROM dba_sql_firewall_allow_lists
WHERE username = 'APP_USER';USERNAME STATUS ENFORCE BLOCK
---------- -------- ----------- -----
APP_USER ENABLED ENFORCE_ALL YWhat Gets Blocked
With enforcement active, let's look at an example. We connect as app_user and run a few statements that were not part of the learning phase. Then we watch what happens.
A statement that is on the allow list runs normally. The query below was captured during the learning phase, so it passes through without any complaint from the firewall.
SELECT employee_id, first_name, last_name, email, department_id
FROM hr.employees
WHERE department_id = 60;5 rows selected.An UPDATE that was never captured during the learning phase gets blocked. The application user only has SELECT privileges in this example, but even if we had granted the user UPDATE, the firewall would have rejected the statement because the SQL text was not on the allow list.
UPDATE hr.employees
SET salary = 999999
WHERE employee_id = 100;ORA-47605: SQL Firewall violationA SELECT against sensitive columns that were not in the learned pattern is also blocked. The learning phase captured a specific column list filtered by department_id. This particular column combination of salary and commission was never seen during that window.
SELECT employee_id, salary, commission_pct
FROM hr.employees;ORA-47605: SQL Firewall violationThis is an important detail to call out, because it changes how you think about table level privileges. The firewall is checking the actual SQL text rather than only the table being accessed, so two queries that hit the same table with different column lists are treated as two different statements as far as the firewall is concerned. That is how SQL Firewall protects sensitive data even when the user has the underlying privilege to read it. The privilege says the user is allowed to read the table. The firewall says only in the specific shapes we have seen before.
A query against a table that was never accessed during the learning phase is also blocked. The JOB_HISTORY table holds historical employment records, including past department transfers and job changes. We did not include it in the workload we captured.
SELECT employee_id, start_date, end_date, job_id
FROM hr.job_history;ORA-47605: SQL Firewall violationEven though app_user was granted SELECT ANY TABLE ON SCHEMA hr, the firewall blocks access to tables that were not part of the learned workload. The privilege says "you can read it." The firewall says "but you never have before, and you have not earned the right to start now."
How It Works
SQL Firewall operates in two phases that we have already touched on. It is worth restating them in one place. The first phase is learning, during which the firewall observes a user's session and records every SQL statement the user issues, along with the connection context such as the IP address, the operating system user, and the client program. The second phase is enforcement, during which the firewall compares every incoming SQL statement against the allow list it built and either blocks or logs anything that does not match.
The comparison itself is based on the normalized SQL text rather than on the literal string. Oracle normalizes bind variables and whitespace, so that SELECT * FROM employees WHERE id = 1 and SELECT * FROM employees WHERE id = 2 are treated as the same statement by the firewall. That keeps the allow list compact. It focuses enforcement on the shape of the query rather than on the specific parameter values that the application is passing in at runtime.
Because the firewall sits inside the kernel, it sees every SQL statement regardless of how the statement arrives. A SQL injection payload that comes through a PL/SQL procedure is blocked just as effectively as one that comes through a JDBC connection. Network level firewalls and WAFs can only inspect the traffic they can see on the wire. Once the traffic has reached the database, they are out of the picture. SQL Firewall, by contrast, inspects everything that reaches the execution engine.
Conclusion
SQL Firewall turns the database itself into a data fortress. The learning phase captures what your application actually does with the database, the allow list codifies that behavior into rules, and enforcement blocks everything else at the kernel level. SQL injection attempts, unauthorized ad hoc queries, and privilege escalation efforts are all rejected by the same mechanism. There is no execution path through the database that gets to skip the check.
The workflow we walked through in this post is a short one. You enable the firewall, you capture an application's normal workload, you generate the allow list, you turn on enforcement, and you watch the firewall block the things that should never have run.
The next step is operating it day to day, and that is what we will cover in the next post. Topics will include reviewing the violation log, refining the allow list when an application releases new features, log maintenance, moving the firewall tables out of SYSAUX when growth justifies it, and bringing the firewall up and down cleanly for maintenance windows.
Charles is an Oracle ACE Director, VMware vExpert, and author of over a dozen books in the Oracle ecosystem.