It is true that it’s a somewhat daring title, and it’s unlikely that SQL firewall has changed anything about the attack that several casinos in Las Vegas suffered last week. However, the opportunity was too good to pass up on this topic.

As I write this blog, Las Vegas casinos are experiencing a series of cyberattacks. It turns out that at the same time, OracleCloud World is taking place in Las Vegas, and one of its flagship products, the database, offers a new feature in its 23c version called ‘SQL firewall.’ So, the opportunity was too good not to write a short blog about this new functionality. I would like to emphasize here that this is only a very brief overview of all the features of DBMS_SQL_FIREWALL. Oracle provides extensive documentation on this package/feature, and you will find several blogs on the internet that go beyond the tests below (have a look in my conclusion)

How does SQL Firewall work?

The principle of the package is very simple and is illustrated through the image below, which has been taken from the Oracle documentation:

SQL Firewall flow – Source: Oracle documentation
  1. First, we capture the standard activity of an application (Capture SQL Activities). Naturally, the capture must run for a sufficient amount of time to cover all specific scenarios.
  2. By examining this activity (Review), we ensure that there is nothing abnormal.
  3. We generate the list (Generate Allow-List) of allowed execution contexts. This list can be modified if necessary by adding or removing specific executions using the following package procedures: DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT, DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT, and DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL.
  4. We enable the list (Enable Allow-List).
  5. We monitor (Monitor) the operation using the DBA_SQL_FIREWALL_VIOLATIONS dictionary view.


For this example I will need two users:

  • schema_owner who is the owner of the tables & views of the application
  • app_user who needs to access to the application

We could use a specific user to manage database firewall by providing him the sql_firewall_admin role but as stated in my introduction I’ll keep this example very short and simple.

Users creation

Creation of the user schema_owner:

SQL> create user schema_owner identified by schema_owner quota unlimited on users;
grant connect,resource to schema_owner;
grant create session grant any privilege to schema_owner;
User created.

Creation of the user app_user:

SQL> create user app_user identified by app_user;
grant create session to app_user;
grant select any table on schema schema_owner to app_user;
User created.

Objects creation

Now we will create few objects in our schema_owner schema:

SQL> CREATE TABLE schema_owner.customers
( customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
city varchar2(50),
credit_card number(16),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
Table created.

SQL> create view schema_owner.non_sensitive_view as select customer_name, city from schema_owner.customers;

View created.

Data insertion

We will now insert data in our table:

SQL> insert into schema_owner.customers values (001,'steulet','Delemont',00001111222233334444);
insert into schema_owner.customers values (002,'errard','Courrendlin',2222333344445555);
insert into schema_owner.customers values (003,'frund','Porrendutry',7777888899990000)
1 row created.

Start the capture of the statements (Capture SQL Activities)

Before being able to capture the statements, we have to be sure that SQL Firewall is enabled:

SQL> select status
from dba_sql_firewall_status; 
SQL> exec dbms_sql_firewall.enable;

PL/SQL procedure successfully completed.
SQL> select status
from dba_sql_firewall_status;

We can now start the capure process of the standard context for the user app_user using the below procedure:

SQL> begin
dbms_sql_firewall.create_capture (
username => 'app_user',
top_level_only => false,
start_capture => true);
PL/SQL procedure successfully completed.

Let’s run the application

We now need to run the application for the necessary duration in order to collect all user context information related to those who will be querying the application. We will greatly simplify this phase by simply performing a select on the view we created earlier. This select will be done in a separate session using the application user login & password:

[oracle@grs-oraclecloudinstance bin]$ ./sqlplus app_user/app_user@free

SQL*Plus: Release - Developer-Release on Mon Sep 18 03:16:56 2023
Version (c) 1982, 2023, Oracle. All rights reserved.Connected to:
Oracle Database 23c Free, Release - Developer-Release
SQL> set linesize 200 pagesize 40
SQL> select * from schema_owner.non_sensitive_view;

CUSTOMER_NAME                                      CITY
-------------------------------------------------- ---------------------------------
steulet                                            Delemont
errard                                             Courrendlin

Let’s stop the capture

We can admit that the application ran for the necessary duration in order to catch all the contextual information and we stop the capture:

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

Let’s have a look on what we captured (Review)

When querying dba_sql_fiewall_capture_logs, one can view the logs as demonstrated below:

SQL> column commande_type format a9
column current_user format a10
column client_program format a20
column os_user format a8
column ip_address format a10
column sql_text format a30
select command_type, current_user, client_program, os_user, ip_address, sql_text from dba_sql_firewall_capture_logs where username='APP_USER';

------------ ---------- -------------------- -------- ---------- ------------------------------
SELECT       APP_USER   sqlplus@grs-oraclecl oracle  SELECT * FROM SCHEMA_OWNER.NON
                        oudinstance (TNS V1-                     _SENSITIVE_VIEW

SELECT       APP_USER   sqlplus@grs-oraclecl oracle   Local      SELECT DECODE (USER,:"SYS_B_0"
                        oudinstance (TNS V1-                     ,XS_SYS_CONTEXT (:"SYS_B_1",:"
                        V3)                                      SYS_B_2"),USER) FROM SYS.DUAL

SELECT       APP_USER   sqlplus@grs-oraclecl oracle  SELECT DECODE (USER,:"SYS_B_0"
                        oudinstance (TNS V1-                     ,XS_SYS_CONTEXT (:"SYS_B_1",:"
                        V3)                                      SYS_B_2"),USER) FROM SYS.DUAL

To read the output more easily you will find a screenshot below:

Output of the SQL command

Let’s generate the allow list (Generate Allow-List)

Using DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST we generate the allow_list:

PL/SQL procedure successfully completed.

Let’s enable protect our database (Enable Allow-List)

Using the below procedure we will activate the allow list and block violations:

SQL> begin
dbms_sql_firewall.enable_allow_list (
username => 'app_user',
enforce => dbms_sql_firewall.enforce_all,
block => true);

PL/SQL procedure successfully completed.

What about a test?

Let’s run a query that hasn’t been run before. We get a SQL Firewall violation:

[oracle@grs-oraclecloudinstance bin]$ ./sqlplus app_user/app_user@free

SQL> select credit_card from schema_owner.customers;
select credit_card from schema_owner.customers
ERROR at line 1:
ORA-47605: SQL Firewall violation

What can we find in the log?

Inspecting the log we can have some details regarding the rule violation:

SQL> select sql_text,
from dba_sql_firewall_violations;
SQL_TEXT                       FIREWAL IP_ADDRESS CAUSE                OCCURRED_AT
------------------------------ ------- ---------- -------------------- -------------
SELECT CREDIT_CARD FROM SCHEMA Blocked  SQL violation        18-SEP-23 AM +00:00

To read the output more easily you will find a screenshot below:

Output of the SQL command


Oracle provides a series of tools to control data access. SQL Firewall is added to the list of these tools, such as DBM_CRYPTO, TDE, Oracle Virtual Private Database, to name just a few. SQL Firewall is used in this blog to block queries, but it can also be configured for query monitoring only. It’s also interesting to note that we can use Oracle Data Pump to export and import SQL Firewall captures and allow-list metadata. Of course on large volume of Data SQL Firewall can have an impact on performance (I didn’t test it). In such a context Oracle recommends the two below measures:

  • Allocate at least an additional 2G to the LARGE_POOL_SIZE parameter setting, on top of the existing LARGE_POOL_SIZE requirement.
  • Resize the SGA_TARGET parameter setting to include this additional requirement. Ensure that the final size is 8G or more.

Among the different blogs that address this topic we can find: