Oracle to the rescue of MGM with SQL Firewall?

Introduction

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:

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.

Example

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; 
STATUS
------------
DISABLED

SQL> exec dbms_sql_firewall.enable;

PL/SQL procedure successfully completed.

SQL> select status
from dba_sql_firewall_status;
STATUS
------------
Enable

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);
end;
 
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 23.0.0.0.0 - Developer-Release on Mon Sep 18 03:16:56 2023
Version 23.2.0.0.0Copyright (c) 1982, 2023, Oracle. All rights reserved.Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
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';

COMMAND_TYPE CURRENT_US CLIENT_PROGRAM       OS_USER  IP_ADDRESS SQL_TEXT
------------ ---------- -------------------- -------- ---------- ------------------------------
SELECT       APP_USER   sqlplus@grs-oraclecl oracle   10.0.0.79  SELECT * FROM SCHEMA_OWNER.NON
                        oudinstance (TNS V1-                     _SENSITIVE_VIEW
                        V3)

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   10.0.0.79  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:

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

Using DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST we generate the allow_list:

SQL> exec DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('APP_USER');
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);
end;
/ 

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,
firewall_action,
ip_address,
cause,
occurred_at
from dba_sql_firewall_violations;

SQL_TEXT                       FIREWAL IP_ADDRESS CAUSE                OCCURRED_AT
------------------------------ ------- ---------- -------------------- -------------
SELECT CREDIT_CARD FROM SCHEMA Blocked 10.0.0.79  SQL violation        18-SEP-23 04.08.54.371122 AM +00:00
_OWNER.CUSTOMERS

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

Conclusion

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:

• 23c Oracle Firewall from Rob Watson
• SQL Firewall Oracle 23c from Pete Finnigan
• SQL Firewall in Oracle Database 23c from Oracle-Base (Tim)