SQL Firewall in Oracle AI Database 26ai: Allow-Lists, Blocking and Overhead, Measured

 

Tested on: Oracle AI Database 26ai (23.26.4.1.0) on Oracle Autonomous AI Database | Level: Intermediate

SQL Firewall in Oracle AI Database 26ai learns the SQL an application account normally runs, then blocks anything else, inside the database kernel. Before turning it on in a bank, two questions matter: what does it cost, and what can go wrong? I tested it on Autonomous AI Database. With blocking enabled, a workload of 40,000 SQL executions per run showed no measurable overhead (7.53 s average without the firewall, 7.46 s with it). A query the application had never run was blocked with ORA-47605. The surprises were elsewhere: 56 of the 59 statements in the allow-list came from the client tool, not the application, and the tool's own housekeeping statement was blocked once enforcement started.

Results at a glance

Test	Result
Workload, firewall off (3 runs)	7.54 / 7.65 / 7.41 s (average 7.53 s)
Workload, firewall on, enforce SQL, block (3 runs)	7.51 / 7.41 / 7.46 s (average 7.46 s)
Allow-list size after capture	59 statements, 3 of them written by me
Query outside the allow-list	Blocked, ORA-47605: SQL Firewall violation
Side effect	The client tool's own CREATE SEQUENCE was also blocked

How SQL Firewall works

SQL Firewall runs inside the database and protects one database account at a time, typically an application's service account. It works in three phases:

1. Capture: while the application runs its normal work, the database records every distinct SQL statement the account executes, in normalised form.
2. Allow-list: the captured statements become the list of approved SQL for that account.
3. Enforce: any statement not on the list is logged as a violation and, if blocking is on, rejected with ORA-47605.

Because it checks the statement the database actually receives, it also catches SQL issued with stolen application credentials from an unexpected place, as long as that SQL differs from the application's normal statements. It is configured with the DBMS_SQL_FIREWALL package.

Lab setup

An application account FW_APP owns a 10,000-row customer table with fictional data, and a procedure that represents the application's normal work: a customer lookup by ID and a count by branch, both with bind variables.

CREATE OR REPLACE PROCEDURE fw_app.app_workload (p_calls PLS_INTEGER) AS
  l_name VARCHAR2(60);
  l_bal  NUMBER;
  l_cnt  NUMBER;
BEGIN
  FOR i IN 1 .. p_calls LOOP
    SELECT full_name, balance INTO l_name, l_bal
    FROM   fw_app.customers WHERE customer_id = MOD(i, 10000) + 1;
    SELECT COUNT(*) INTO l_cnt
    FROM   fw_app.customers WHERE branch_code = 'BR' || LPAD(MOD(i, 20), 2, '0');
  END LOOP;
END;
/

The timing test, run while signed in as FW_APP, with exactly the same text every time. Each run makes 20,000 calls, which is 40,000 SQL executions:

DECLARE
  t0 TIMESTAMP := SYSTIMESTAMP;
  d  INTERVAL DAY TO SECOND;
BEGIN
  app_workload(20000);
  d := SYSTIMESTAMP - t0;
  DBMS_OUTPUT.PUT_LINE('ELAPSED_SECONDS=' ||
    TO_CHAR(EXTRACT(MINUTE FROM d) * 60 + EXTRACT(SECOND FROM d)));
END;
/

Step 1: enable and capture

As ADMIN:

EXEC DBMS_SQL_FIREWALL.ENABLE

BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE(
    username       => 'FW_APP',
    top_level_only => FALSE,   -- also capture SQL run inside PL/SQL
    start_capture  => TRUE);
END;
/

Then I signed in to Database Actions as FW_APP in a separate browser session and ran the timing block once. Back as ADMIN, I flushed the in-memory logs, stopped the capture and generated the allow-list:

EXEC DBMS_SQL_FIREWALL.FLUSH_LOGS
EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE('FW_APP')
EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST('FW_APP')

SELECT sql_text FROM dba_sql_firewall_allowed_sql WHERE username = 'FW_APP';

Finding 1: the allow-list is mostly not your application

The allow-list had 59 statements. Only three were mine, normalised by the firewall (literals became bind placeholders):

SELECT FULL_NAME,BALANCE FROM FW_APP.CUSTOMERS WHERE CUSTOMER_ID=MOD (:B1,:"SYS_B_0") +:"SYS_B_1"
SELECT COUNT (*) FROM FW_APP.CUSTOMERS WHERE BRANCH_CODE=:"SYS_B_0"||LPAD (MOD (:B1,:"SYS_B_1"),...
DECLARE T0 TIMESTAMP :=SYSTIMESTAMP; D INTERVAL DAY TO SECOND; BEGIN APP_WORKLOAD (?); ...

The other 56 came from the client tool and its connection layer: session setup, NLS settings, metadata lookups, paging wrappers, output handling. A few examples (trimmed):

ALTER SESSION SET CURRENT_SCHEMA=FW_APP
ALTER SESSION SET NLS_LANGUAGE=?
SELECT PARAMETER,VALUE FROM NLS_SESSION_PARAMETERS UNION ALL ...
SELECT * FROM (SELECT Q_.*,ROW_NUMBER () OVER (ORDER BY :"SYS_B_0") RN___ FROM (...
BEGIN DBMS_OUTPUT.ENABLE (?); END;
CREATE TABLE DBTOOLS$EXECUTION_HISTORY (ID NUMBER NOT NULL, ...

SQL Firewall protects a database account, and it learns everything that account runs, whatever program sends it. In production, application servers, connection pools, monitoring agents and ORMs all add their own statements. Expect the allow-list to be much bigger than the SQL your developers wrote, and review it before enforcing.

Step 2: measure, then enforce

I ran the timing block three times with the allow-list generated but not enabled. Then I enabled it, enforcing SQL only and with blocking on:

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

USERNAME STATUS  ENFORCE     BLOCK
-------- ------- ----------- -----
FW_APP   ENABLED ENFORCE_SQL Y

Then I ran the same timing block three more times.

Finding 2: no measurable overhead at this scale

Firewall off : 7.543823  7.651046  7.405978   (average 7.53 s)
Firewall on  : 7.510572  7.411722  7.460245   (average 7.46 s)

Each run executed 40,000 statements, all of them checked against the allow-list when enforcement was on. The difference between the two sets is smaller than the variation between runs of the same set, so I cannot measure any overhead from these numbers. That is good news for adoption, with a caveat: this was a single session on a small sandbox. A busy system with many sessions and a very large allow-list should be measured on its own workload.

Finding 3: blocking works, and so does collateral blocking

Signed in as FW_APP, I ran a query the application had never run, on the same table it uses every day:

SELECT COUNT(DISTINCT national_id) FROM customers;

ORA-47605: SQL Firewall violation
*Cause: Operation blocked by the SQL Firewall due to enforcement of the firewall policy.

The account has full rights on its own table. The firewall blocked the statement anyway, because it was not part of the learned behaviour. That is the point of SQL Firewall: privileges say what an account may do, the allow-list says what it normally does.

The violation log had a second entry I did not cause directly:

SQL_TEXT                                                   FIREWALL_ACTION CAUSE
---------------------------------------------------------- --------------- -------------
SELECT * FROM (SELECT Q_.*,ROW_NUMBER () OVER (ORDER BY ... Blocked         SQL violation
CREATE SEQUENCE DBTOOLS$EXECUTION_HISTORY_SEQ START WITH ?  Blocked         SQL violation

• The first row is my blocked query, but logged as the tool sent it, wrapped in a paging query. When you investigate violations, expect to see the client's version of a statement, not what the user typed.
• The second row is the tool's own housekeeping. It never ran during capture, so once enforcement started it was blocked. In production this is the real risk: a code path that did not run during capture, such as a month-end job or a rare error handler, will fail on its first run after enforcement.

Things to know before using it

• Flush before you generate. Captured SQL is held in memory first. My first attempt generated nothing (ORA-47629: capture log not found) until I added DBMS_SQL_FIREWALL.FLUSH_LOGS.
• Capture through the real connection path. When I ran the workload as a DBMS_SCHEDULER job owned by FW_APP, nothing was captured. Signed in as FW_APP, everything was. I observed this, but did not confirm the reason, so capture the way the application actually connects.
• Capture long enough to cover every code path. Include month-end, batch, error handling and support tools, or they will be blocked later.
• Start with logging, then block. block => FALSE logs violations without stopping anything. Review the violation log for a while, add what is legitimate, then switch to blocking.
• Choose what to enforce. ENFORCE_SQL checks statements. ENFORCE_CONTEXT checks where the session comes from (IP address, OS user, program), and ENFORCE_ALL checks both.

Clean-up

EXEC DBMS_SQL_FIREWALL.DISABLE_ALLOW_LIST('FW_APP')
EXEC DBMS_SQL_FIREWALL.DROP_ALLOW_LIST('FW_APP')
EXEC DBMS_SQL_FIREWALL.DROP_CAPTURE('FW_APP')
DROP USER fw_app CASCADE;

Summary

On Oracle AI Database 26ai, SQL Firewall learned an application account's behaviour, blocked a query outside it with ORA-47605, and added no overhead I could measure across 40,000 statements per run. The practical lessons were about the allow-list itself: 56 of its 59 entries came from the client tool rather than the application, and a tool statement that was missed during capture was blocked like any other. Capture through the real connection path, cover every code path, start in logging mode, and SQL Firewall becomes a strong extra layer on top of privileges.

Lab design, testing and all results are my own, run on an Oracle LiveLabs sandbox. 

Tamilselvan Perumal is an Oracle ACE Apprentice and Senior DBA with 12+ years in banking databases, working on database performance and benchmarking. Connect on LinkedIn.