SQL Firewall

By- Sabalesh Mahajan

Oracle Database 23c introduces SQL Firewall, enhancing database security by providing real-time protection against external threats like SQL Injection attacks and unauthorized SQL statements.

What it is

SQL Firewall is a new security feature in Oracle Database 23c, embedded in the kernel.
Conceptually, it is similar to Database Firewall (Audit Vault and Database Firewall AVDF).
It offers real-time protection against external threats like SQL Injection attacks.
It can block unauthorized SQL statements from executing within the database.
SQL Firewall allows the creation of an 'allow list' for authorized SQL statements.
It monitors and can potentially block SQL statements violating the 'allow list.'
SQL Firewall can utilize session context data, such as IP address, OS User, and Program Type for enforcement.

Example-

Database- Oracle 23c Developer database

Assume the security requirement is to allow the application developer database user DV_USER_001 to only issue SELECT queries on certain tables and not allow any non-SELECT statements to be run like UPDATE /INSERT/DELETE statements.

Security Requirement-

Restrict database user DV_USER_001 to perform only SELECT queries on specific tables.

Prevent non-SELECT statements like UPDATE, INSERT, and DELETE.

Create a user SQLFW_ADMIN_001 to administer and manage SQL Firewall. The user needs to be granted the SQL_FIREWALL_ADMIN role.

SQL> create user SQLFW_ADMIN_001 identified by SomePassword;

User created.

SQL> grant create session, sql_firewall_admin, audit_admin to SQLFW_ADMIN_001;

Grant succeeded.

Enable the SQL Firewall & check the status via DBA_SQL_FIREWALL view.

SQL> conn SQLFW_ADMIN_001/SomePassword
Connected.

SQL> exec dbms_sql_firewall.enable;
PL/SQL procedure successfully completed.

SQL> select status from dba_sql_firewall_status;

STATUS
--------
ENABLED

Create and enable the SQL Firewall capture for a specified database user – in our example it is developer user account DV_USER_001.

SQL> exec dbms_sql_firewall.create_capture('DV_USER_001');
PL/SQL procedure successfully completed.

SQL> select username,status from dba_sql_firewall_captures;
USERNAME            STATUS
--------            --------
DV_USER_001         ENABLED

Connect as DV_USER_001 user and issue some SQL statements.

SQL> conn DV_USER_001/SomePassword@freepdb1
Connected.

SQL> select count(*) from my_table_001;
  COUNT(*)
----------
       78

SQL> select count(*) from my_test_obj_001;
  COUNT(*)
----------
     232

SQL>  select distinct employee_f_name from my_table_001;
EMPLOYEE_F_NAME
-------------------------------------------------------------------
JOE
DONALD
BILL
...
...

7 rows selected.

SQL>  select distinct employee_f_name from my_test_obj_001;
EMPLOYEE_F_NAME
------------------------------------------------------------------
JOE
DONALD
BILL
...
...

15 rows selected.

Connect as the SQL Firewall admin user and stop the capture when desired.

SQL> conn SQLFW_ADMIN_001/SomePassword
Connected.

SQL> exec dbms_sql_firewall.stop_capture ('DV_USER_001');
PL/SQL procedure successfully completed.

Review the captured SQL statements from the SQL Firewall capture logs and the “trusted” database connection paths.

SQL> select username,ip_address,client_program,os_user
  from dba_sql_firewall_session_logs
  where username='DV_USER_001';
USERNAME		IP_ADDRESS			CLIENT_PROGRAM						OS_USER
-------------	----------------- 	-----------------------------------------   	-------------
DV_USER_001			127.0.0.1			sqlplus@rhelhost001.localdomain (TNS V1-V3)		oracle

SQL> select sql_text,accessed_objects from dba_sql_firewall_capture_logs where username='DV_USER_001';
SQL_TEXT
--------------------------------------------------------------------------------
ACCESSED_OBJECTS
--------------------------------------------------------------------------------
SELECT COUNT (*) FROM MY_TABLE_001
"DV_USER_001"."MY_TABLE_001"

SELECT DISTINCT EMPLOYEE_F_NAME FROM MY_TABLE_001
"DV_USER_001"."MY_TABLE_001"

SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM
 SYS.DUAL
"SYS"."DUAL"

SELECT DISTINCT EMPLOYEE_F_NAME FROM MY_TEST_OBJ_001
"DV_USER_001"."MY_TEST_OBJ_001"

SELECT COUNT (*) FROM MY_TEST_OBJ_001
"DV_USER_001"."MY_TEST_OBJ_001"

6 rows selected.

Generate the allow-list for the user DV_USER_001. The allow-list is based on data collected from existing SQL Firewall capture logs for the user DV_USER_001.

SQL> EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('DV_USER_001');
PL/SQL procedure successfully completed.

Once generation is completed, review the “allowed” SQL as well as the trusted database connection paths – these will include trusted IP addresses, OS users and client programs.

SQL> SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE USERNAME ='DV_USER_001';
SQL_TEXT
-------------------------------------------------------------------------
SELECT COUNT (*) FROM MY_TABLE_001
SELECT DISTINCT EMPLOYEE_F_NAME FROM MY_TABLE_001
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM
 SYS.DUAL
SELECT DISTINCT EMPLOYEE_F_NAME FROM MY_TEST_OBJ_001
SELECT COUNT (*) FROM MY_TEST_OBJ_001

6 rows selected.

SQL> select ip_address from dba_sql_firewall_allowed_ip_addr where username='DV_USER_001';
IP_ADDRESS
--------------------------------------------------------------------------
127.0.0.1

SQL> select os_user from dba_sql_firewall_allowed_os_user where username='DV_USER_001';
OS_USER
-------------------------------------------------------------------------
oracle

SQL> select os_program from dba_sql_firewall_allowed_os_prog where username='DV_USER_001';
OS_PROGRAM
--------------------------------------------------------------------------
sqlplus@rhelhost001.localdomain (TNS V1-V3)

Enable the ‘allow-list’ for the user DV_USER_001. Note that at this stage the SQL Firewall is not operating in ‘Blocking’ mode.

SQL> exec dbms_sql_firewall.enable_allow_list ('DV_USER_001');
PL/SQL procedure successfully completed.

SQL> select username,status,block from dba_sql_firewall_allow_lists;
USERNAME				STATUS   BLOCK
-------------------------------	-------- --------------
DV_USER_001				ENABLED  N

Connect as the application developer user DV_USER_001 and now issue an ‘unauthorized’ SQL statement – an UPDATE statement on the MY_TABLE_001 table.

SQL> conn DV_USER_001/SomePassword@freepdb1
Connected.

SQL> update my_table_001 set employee_f_name='JOE BID' where rownum=1;
1 row updated.

SQL> commit;
Commit complete.

This unauthorized statement has now triggered a violation. Query DBA_SQL_FIREWALL_VIOLATIONS to obtain the details of the violation. As blocking is not enabled at the moment, unauthorized SQL traffic is permitted by the SQL Firewall, but all violations are being logged for review.

SQL> select username,sql_text,cause,firewall_action from dba_sql_firewall_violations;

USERNAME	SQL_TEXT									CAUSE                FIREWAL
--------- ----------------------------------------------------------	-------  		   -----------
DV_USER_001	    UPDATE MY_TABLE_001 SET EMPLOYEE_F_NAME=:"SYS_B_0" WHERE ROWNUM=:"SYS_B_1"     SQL violation         Allowed

We now turn on SQL Firewall ‘blocking’ – unauthorized or non-trusted SQL will be blocked as well as the violations will be logged for review.

SQL> exec dbms_sql_firewall.update_allow_list_enforcement ('DV_USER_001',block=>TRUE);
PL/SQL procedure successfully completed.

SQL> select username,status,block from dba_sql_firewall_allow_lists;
USERNAME				STATUS   BLOCK
-------------------------------	-------- --------------
DV_USER_001				ENABLED  Y

Connect as the DV_USER_001 user and issue the same UPDATE statement which earlier had been permitted to run by the SQL Firewall. Note now with blocking enabled, the SQL Firewall has not permitted the execution of the unauthorized SQL statement.

SQL> conn DV_USER_001/SomePassword@freepdb1
Connected.

SQL> update my_table_001 set employee_f_name='JOE BID' where rownum=1;
update my_table_001 set employee_f_name='JOE BID' where rownum=1
*
ERROR at line 1:
ORA-47605: SQL Firewall violation

The violation has caused an additional row to be logged in DBA_SQL_FIREWALL_VIOLATIONS with the FIREWALL_ACTION now showing the value ‘Blocked’.

SQL> select username,sql_text,cause,firewall_action from dba_sql_firewall_violations;
USERNAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
CAUSE                FIREWAL
-------------------- -------
DV_USER_001
UPDATE MY_TABLE_001 SET EMPLOYEE_F_NAME=:"SYS_B_0" WHERE ROWNUM=:"SYS_B_1"
SQL violation        Allowed

DV_USER_001
UPDATE MY_TABLE_001 SET EMPLOYEE_F_NAME=:"SYS_B_0" WHERE ROWNUM=:"SYS_B_1"
SQL violation        Blocked

The SQL Firewall still permits trusted and authorized SQL statements even though it is running in blocking mode.

SQL> conn DV_USER_001/SomePassword@freepdb1
Connected.

SQL> select count(*) from my_table_001;
  COUNT(*)
----------
       78

SQL Firewall

Sabalesh Mahajan

Recommended by LinkedIn

More articles by this author

Others also viewed

SQL Friday newsletter #7

Your SQL Server Is Handing Attackers a Map — By Default

SQL Server Page Level Corruption

How to Repair SQL Server Database Using DBCC CHECKDB and Stellar Repair for MSSQL

Preventing and Investigating Unauthorized Actions in PostgreSQL Databases.

How to find all D365 Security ROLES inheritance relations?

Understanding the Differences Between LOAD DATA INFILE and LOAD DATA "LOCAL" INFILE in MySQL and MariaDB: Security Considerations for DBAs

Alerts and Email notifications in Grafana

3 Steps to End SQL Setup Errors

Explore content categories

Recommended by LinkedIn

Multi-Version Concurrency Control (MVCC)

Apr 7, 2025

Boost Your Database Performance: Basics but still effective

Mar 31, 2025

SELECT AI using ChatGPT Is here

Sep 20, 2023

Wide Tables

Sep 19, 2023