Oracle SQL Firewall in Oracle database 26 ai

February 18, 2026 salem.houali@yahoo.ca Oracle AI Database 26ai, 0

About Oracle AI Database 26ai.
Oracle AI Database 26ai is the next long-term support release of Oracle AI Database. It includes over 300 new features with a focus on artificial intelligence (AI) and developer productivity. Ref. oracle documentation 

About Oracle SQL Firewall
With Oracle SQL Firewall, you are protected in real-time against common database attacks. Database accesses are restricted to only authorized SQL statements or connections for a specific user.

It mitigates risks from SQL injection attacks, anomalous access, and credential theft or abuse, preventing or detecting potential SQL injection attacks.

You can use SQL Firewall to control which SQL statements are allowed to be processed by the database. In addition, SQL Firewall can use session context data such as IP address to restrict database connections. Unauthorized SQL and database connection can be logged and blocked.

 

---

• A user logs in to the Oracle database through a web application.
• The user runs SQL statements, generating inbound traffic to the Oracle database.
• Finally, the SQL Firewall inspects the incoming database connections and SQL statements.

SQL Firewall’s processing does one of the following options:

• Allow the SQL for its subsequent execution.
• Allow the SQL and log it.
• Log and optionally block unauthorized SQL
  

  ---

Note: 1.2 Licensing Oracle SQL Firewall: Oracle SQL Firewall must be licensed for use.

Part 1: Getting Started with Oracle SQL Firewall
To get started with Oracle SQL Firewall, we need:
1. Enable Oracle SQL Firewall
2. Capture the user’s normal SQL activities
3. Enable and enforce allow-lists.

Enable SQL Firewall. As an administrator with appropriate privileges, enable SQL Firewall in the Oracle database.

Capture the normal SQL activities. You must enable SQL Firewall to learn the normal SQL traffic of the database user you want to protect with SQL Firewall.

This is done by capturing all the authorized SQL statements over trusted database connection paths.
To determine if the collected SQL statements and connection paths is adequate to constitute the allow-lists, you can query SQL Firewall-specific data dictionary views to review this captured data.
After that, you can generate a SQL Firewall policy with allow-lists that set the baseline for allowed SQL statements and allowed contexts. The allowed SQL statements constitute the approved SQL statements.
When a user runs an SQL statement and the policy is enforced, if the SQL statement has a structure syntactically similar to the SQL signature in the policy allow-list, then it will be passed for execution if the corresponding run-time execution context also meets the set of allowed contexts
Allowed contexts represent trusted database connection paths and consist of three distinct groups—client IP addresses, operating system program names, and operating system user names.

Enable and enforce the allow-lists. Once the generated SQL Firewall policy is enabled, it enforces and checks the allow-lists when the user connects to the database and issues SQL statements. You can let SQL Firewall know if you want to enforce checks on allowed contexts, allowed SQL statements, or both.

Part 2: Demonstration:
Let’s use the same demo schemas used for true cache in the previous posts

We will use DBMS_SQL_FIREWALL Package to configure Oracle SQL Firewall
First: Let’s create a user FWADMIN with SQL_FIREWALL_ADMIN role.
This user will use DBMS_SQL_FIREWALL PL/SQL package.
And then configure Oracle SQL Firewall in a pluggable database (PDB)

• Now, let’s connect as fwadmin and enable SQL Firewall.

oracle@ol9m1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 23.26.0.0.0 – Production on Sat Jan 3 13:07:15 2026
Version 23.26.0.0.0

Copyright (c) 1982, 2025, Oracle. All rights reserved.

Connected to:
Oracle AI Database 26ai Free Release 23.26.0.0.0 – Develop, Learn, and Run for Free
Version 23.26.0.0.0

SQL> alter session set container=freepdb1;
Session altered.

SQL> connect fwadmin/fwadmin@freepdb1;
Connected.
SQL>

SQL> EXEC DBMS_SQL_FIREWALL.ENABLE;
PL/SQL procedure successfully completed.
SQL>

• Verify  the status:

SQL>
SQL> column status format a20;
SQL> column status_updated_on format a50;
SQL> select status, status_updated_on
2 from dba_sql_firewall_status;

STATUS STATUS_UPDATED_ON
——————– ————————
ENABLED 03-JAN-26 01.11.59.789980 PM -05:00

SQL>

• Now, let’s enable SQL Firewall for SH user to learn the normal SQL traffic of SH user by capturing all the authorized SQL statements over trusted database connection paths.

SQL> BEGIN
2 DBMS_SQL_FIREWALL.CREATE_CAPTURE (
3 username => ‘sh’,
4 top_level_only => TRUE,
5 start_capture => TRUE
6 );
7 end;
8 /

PL/SQL procedure successfully completed.

In this specification:

sh: The name of the application user that SQL Firewall will monitor.
Only one capture can be created for each user. You cannot create SQL Firewall captures for the SYS, SYSDG, SYSBACKUP, SYSRAC, SYSKM, DVSYS, LBACSYS, or AUDSYS users.
top_level_only: Controls the level of SQL statements that are captured.
TRUE==> generates capture logs only for top-level SQL statements the user directly runs.
FALSE==> generates capture logs for both top-level SQL statements and SQL commands issued from PL/SQL units. The default is FALSE.
start_capture controls when the capture will be effective.
TRUE==> enables SQL Firewall to start capturing the target user’s (sh) activities right away. The default is TRUE.

• Let’s START_CAPTURE procedure for the sh user

SQL> EXEC DBMS_SQL_FIREWALL.START_CAPTURE (‘sh’);
PL/SQL procedure successfully completed. SQL>

• let’s connect as sh user and run some queries

• Let’s run some sql statements as sh user:

set linesize 200;
set pagesize 200;
column COUNTRY_ID format 99999
column COUNTRY_ISO_CODE format a2
column COUNTRY_NAME format a40
column COUNTRY_SUBREGION format a20
column COUNTRY_SUBREGION_ID format 99999999
column COUNTRY_REGION format a15
column COUNTRY_REGION_ID format 99999
column COUNTRY_TOTAL format a12
column COUNTRY_TOTAL_ID format 99999

Let’s check the capture logs and see some operations captured.
In addition to the SQL text we can see a number of session attributes have been logged.

Let’s display the data dictionary views to query that allow us to find the permitted and allowed SQL statements that the user SH already executed

Find permitted and allowed SQL statements
Stop the capture

SQL> EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE (‘SH’);
PL/SQL procedure successfully completed.
SQL>

Generate the SQL Firewall policy with allow-lists for the SH user

SQL> EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST (‘SH’);
PL/SQL procedure successfully completed.

SQL>

To find the permitted and allowed SQL statements that the user can run, query the DBA_SQL_FIREWALL_ALLOWED_* data dictionary views.

SQL> column sql_text format a100;
SQL> SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE USERNAME = ‘SH’;

SQL_TEXT
————————————————–
SELECT * FROM COUNTRIES
SELECT DECODE (USER,:”SYS_B_0″,XS_SYS_CONTEXT (:”SYS_B_1″,:”SYS_B_2″),USER) FROM SYS.DUAL

SQL>

To find the trusted database connection paths for the user, perform the following queries:

SQL> SELECT OS_PROGRAM FROM DBA_SQL_FIREWALL_ALLOWED_OS_PROG WHERE USERNAME = ‘SH’;

OS_PROGRAM
—————————————————–
SQL Developer
sqlplus@ol9m1 (TNS V1-V3)

SQL>

SQL> SELECT IP_ADDRESS FROM DBA_SQL_FIREWALL_ALLOWED_IP_ADDR WHERE USERNAME = ‘SH’;

IP_ADDRESS
—————
172.25.129.76

SQL>

Enable the generated SQL Firewall policy to protect the database user.
The SQL Firewall enforces checks on the allow-lists when the user connects to the database and issues SQL statements.
This enablement becomes effective immediately, even in the existing sessions of the target user.

SQL> BEGIN
DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (
username => ‘SH’,
enforce => DBMS_SQL_FIREWALL.ENFORCE_SQL,
block => TRUE
);
END;
/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

SQL>

In this specification:

username can be a specific user whose allow-list has been generated, or it can be all users whose allow-list are not currently enabled. To specify all users, use NULL as the value.
enforce specifies one of the following enforcement types:
DBMS_SQL_FIREWALL.ENFORCE_CONTEXT enforces the allowed contexts that have been configured.
DBMS_SQL_FIREWALL.ENFORCE_SQL enforces the allowed SQL that has been configured.
DBMS_SQL_FIREWALL.ENFORCE_ALL enforces both allowed contexts and allowed SQL. This setting is the default.
block specifies the following:
TRUE blocks the user’s database connection or the user’s SQL execution whenever the user violates the allow-list definition.
FALSE allows unmatched user database connections or SQL commands to proceed. This setting is the default.

SQL Firewall always generates a violation log for any unmatched user database connection or SQL statement regardless of the enforcement option.

At this stage, if the user attempts to perform a SQL query that violates the allow-list and you have specified SQL Firewall to block this SQL, then an ORA-47605: SQL Firewall violation error appears.

We can see the status of the allow-list using the DBA_SQL_FIREWALL_ALLOW_LISTS view.

SQL> select username,
status,
top_level_only,
enforce,
block
from dba_sql_firewall_allow_lists
where username=’SH’; 2 3 4 5 6 7 ;

USERNAME STATUS TOP_LEVEL_ONLY ENFORCE BLOCK
———- ——– ————– ———
SH ENABLED Y ENFORCE_SQL Y

SQL>

Let's run some queries as SH user
Let's query the promotions table

SQL> show user;
USER is "SH"
SQL>
SQL>
SQL> select * from promotions;
select * from promotions
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
Help: https://docs.oracle.com/error-help/db/ora-47605/

Let’s update our allow-list

SQL> exec dbms_sql_firewall.update_allow_list_enforcement(‘SH’, dbms_sql_firewall.enforce_all, true);

PL/SQL procedure successfully completed.

SQL>

SQL> select username, top_level_only, status, enforce
2 from dba_sql_firewall_allow_lists
3 where username=’SH’;

USERNAME TOP_LEVEL_ONLY STATUS ENFORCE
———- ————– ——– —–
SH Y ENABLED ENFORCE_ALL

Let’s try to connect the user SH with sqlcl

As you can see, we get the error again
Error Message = ORA-47605: SQL Firewall violation

Now, let’s try to connect the user OE with sqlcl
As you can see, we get no error and the connection is successful

This ends the demo

Basic tests with true cache on oracle database 26 ai