Introduction

Introduced in 23c the SQL Firewall offers real-time protection against common database attacks by monitoring and blocking unauthorized SQL and SQL injection attacks from inside the database. It does this by capturing a set of accepted SQL statements and generating an allow-list for them. Once enabled SQL that falls outside of the statements in the allow-list can either be permitted but logged to a violations list or blocked from executing. Environmental context such as IP address, or calling program, can also be applied and used for enforcement to ensure access via trusted paths.

In the below blog entry I test out some of the basic features of SQL firewall. This blog does not cover all functionailty but the documentation for SQL firewall can be found here and the packages and types reference here

Usage

To test the new feature I downloaded the free trial version of 23c which can be downloaded here, and installed a small Swingbench load testing schema. The firewall package can be used at the container or pluggable database level. I selected to try it at the pluggable database level.

The 23c database comes with one pdb. I created a local user in the PDB called SOE to own the Swingbench order entry schema.

I configured the firewall using the sys user, but any user granted the new SQL_FIREWALL_ADMIN role can control and maintain the firewall.

SQL Firewall Setup

Step one is to enable the firewall…

exec DBMS_SQL_FIREWALL.ENABLE;

 

Once the firewall is enabled, we are ready to train the firewall. We must set up a capture process to determine what will be captured:

 

BEGIN
DBMS_SQL_FIREWALL.CREATE_CAPTURE(
username=>'SOE',
top_level_only=>FALSE,
start_capture=>TRUE
);
END;
/
  • Username: Is the name of the database user who will be used for the capture. In this example I am using the SwingBench schema owner SOE. Commands exeuted by the SOE user will be logged.
  • Top_level_only: Controls the level at which SQL statements are captured. TRUE generates capture logs for only top-level SQL statements. I used FLASE, that captures not only top-level statements but also commands issued from PL/SQL modules. The default is FALSE.
  • Start_capture: For my demo I used TRUE, which is the default value. Selecting FALSE allows the capture to be created without starting the actual capture. If I had selected FALSE, Once ready to capture data I would then have needed to run DBMS_SQL_FIREWALL.START_CAPTURE(‘SOE’) 

With the capture running against the SOE user, I start the SwingBench load so that the commands Swingbench was running could be registered with the capture.

 

./charbench -c test.xml -v users,tps,tpm,rsr

Time     Users       TPS     TPM      Errors  
14:21:28 [4/4]       491     13867    0       
14:21:29 [4/4]       517     14384    0       
14:21:30 [4/4]       446     14830    0       
14:21:31 [4/4]       447     15277    0       
14:21:32 [4/4]       469     15746    0       
14:21:33 [4/4]       504     16250    0       
14:21:34 [4/4]       492     16742    0       
14:21:35 [4/4]       482     17224    0       
14:21:36 [4/4]       505     17729    0    
….
….
….

 

I left the load running for around five minutes, to ensure that all commands that the application needed to run were captured. No errors were observed. The load generated around 400-500 transactions per second. 

During the capture you can use the DBA_SQL_FIREWALL_CAPTURE_LOGS view to see which statements have been captured.

 

select SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS where username='SOE';

SQL_TEXT
--------------------------------------------------------------------------------
BEGIN :1 :=ORDERENTRY.WAREHOUSEORDERSQUERY (:2,:3,:4); END;
BEGIN :1 :=ORDERENTRY.WAREHOUSEORDERSQUERY (:2,:3,:4); END;
BEGIN :1 :=ORDERENTRY.WAREHOUSEORDERSQUERY (:2,:3,:4); END;
BEGIN :1 :=ORDERENTRY.WAREHOUSEORDERSQUERY (:2,:3,:4); END;
UPDATE INVENTORIES SET QUANTITY_ON_HAND=QUANTITY_ON_HAND -:B1 WHERE PRODUCT_ID=:
B3 AND WAREHOUSE_ID=:B2
UPDATE INVENTORIES SET QUANTITY_ON_HAND=QUANTITY_ON_HAND -:B1 WHERE PRODUCT_ID=:
B3 AND WAREHOUSE_ID=:B2
SELECT CUSTOMER_ID,CUST_FIRST_NAME,CUST_LAST_NAME,NLS_LANGUAGE,NLS_TERRITORY,CRE
DIT_LIMIT,CUST_EMAIL,ACCOUNT_MGR_ID,CUSTOMER_SINCE,CUSTOMER_CLASS,SUGGESTIONS,DO
B,MAILSHOT,PARTNER_MAILSHOT,PREFERRED_ADDRESS,PREFERRED_CARD FROM CUSTOMERS where ROWNUM < :B1
…
…
…

 

The capture represents the correct load that this application should run against the database. With the full load captured we can stop the capture.

 

exec DBMS_SQL_FIREWALL.STOP_CAPTURE('SOE');

PL/SQL procedure successfully completed.

 

We can generate an allow-list from the captured statements. The allow-list will be used by the firewall to filter unauthorized SQL issued by the SOE user. We can check if an allow-list exists for the user, and if it does, the statements in it.

 

select SQL_TEXT from DBA_SQL_FIREWALL_ALLOWED_SQL where username = 'SOE';

no rows selected

 

To populate the statements from the capture into the allow-list, we must generate them.

 

exec DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST('SOE');

PL/SQL procedure successfully completed.

 

Rerunning the previous select we can now see that the allow-list has been populated with the captured statements.

 

select SQL_TEXT from DBA_SQL_FIREWALL_ALLOWED_SQL where username = 'SOE';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT (TO_CHAR (SYSTIMESTAMP,:"SYS_B_00")||:"SYS_B_01"||SYS_CONTEXT (:"SYS_B_02
",:"SYS_B_03")||:"SYS_B_04"||SYS_CONTEXT (:"SYS_B_05",:"SYS_B_06")||:"SYS_B_07"|
|SYS_CONTEXT (:"SYS_B_08",:"SYS_B_09")||:"SYS_B_10"||SYS_CONTEXT (:"SYS_B_11",:"
SYS_B_12")||:"SYS_B_13"||SYS_CONTEXT (:"SYS_B_14",:"SYS_B_15")) FROM DUAL
INSERT INTO ORDER_ITEMS (ORDER_ID,LINE_ITEM_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY,GI
FT_WRAP,CONDITION,ESTIMATED_DELIVERY) VALUES (:B4,:B3,:B2,:B1,:"SYS_B_0",:"SYS_B
_1",:"SYS_B_2",(SYSDATE +:"SYS_B_3"))
…
…
…

 

With the allow-list in place for the SOE user we can now enable the allow-list, which will ensure only the SQL in the allow-list will be allowed to execute.

 

BEGIN​​
DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
username=>'SOE',
enforce=>DBMS_SQL_FIREWALL.ENFORCE_SQL,
block=>TRUE
);
END;
/
  • Username: Specifies the user that the allow-list was created for. If this value is NULL, then the allow-lists for all users who do not yet have allow-lists enabled are enabled.
  • Enforce: This parameter specifies whether contexts (such as IP address, calling program etc) are to also be used for enforcement. The possible values are:
    • DBMS_SQL.ENFORCE_CONTEXT enforces the allowed contexts that have been configured.
    • DBMS_SQL.ENFORCE_SQL enforces the allowed SQL that has been configured.
    • DBMS_SQL.ENFORCE_ALL enforces both allowed contexts and allowed SQL. This setting is the default.
  • Block: If set to TRUE SQL executed that is not in the allow-list for the user will be blocked and not executed. The default value for this parameter is FALSE. FALSE will allow all SQL (matched and unmatched) to be executed, but SQL that is not in the allow-list with will be logged as a violation.

To start with I selected to only enforce the SQL (ignore the environmental contexts) but set block to TRUE so only statements in the allow-list may be executed by te SOE user.

We can check the status of the various allow-lists created  using the DBA_SQL_FIREWALL_ALLOW_LISTS:

 

select username, status, generated_on, enforce, block from DBA_SQL_FIREWALL_ALLOW_LISTS

USERNAME   STATUS   GENERATED_ON ENFORCE BLOCK
---------- -------- --------------------------------------------------------------------------- --------------- --------------
SOE   ENABLED  02-MAY-23 17.20.34  ENFORCE_SQL Y

 

SQL Firewall in Action

Violations can be viewed via the DBA_SQL_FIREWALL_VIOLATIONS view.

 

select SQL_TEXT, SQL_SIGNATURE, COMMAND_TYPE , IP_ADDRESS, OCCURRED_AT from DBA_SQL_FIREWALL_VIOLATIONS where username = 'SOE';

no rows selected

 

Now the firewall is enabled with a block status of TRUE I will not be able to run any SQL that is not on the allow-list as the SOE user. If I now run a statement, as the SOE user,  that is not on the allow list the firewall will log the violation and because block is set to TRUE stop the SQL from executing:

 

select customer_id from customers where rownum < 10;
select customer_id from customers where rownum < 10
*ERROR at line 1:
ORA-47605: SQL Firewall violation.      <<< The firewall has blocked it

 

If we check the violation log again, we see that the violation has also been logged, including the SQL text that was blocked, the IP address and the time of the violation.

 

select SQL_TEXT, IP_ADDRESS, OCCURRED_AT from DBA_SQL_FIREWALL_VIOLATIONS where username = 'SOE';

SQL_TEXT       IP_ADDRESS     OCCURRED_AT
---------------------------------------------------------------------- -------------------- ------------------------------
SELECT CUSTOMER_ID FROM CUSTOMERS WHERE ROWNUM <:"SYS_B_0"       10.0.0.253     02-MAY-23 15.54.11.167040 +00:

 

When we run the Swingbench application again as the SOE user we can see no errors are generated.

 

./charbench -c test.xml -v users,tps,tpm,errs

Time     Users       TPS     TPM      Errors   
15:59:06 [4/4]       217     217      0        
15:59:07 [4/4]       336     553      0        
15:59:08 [4/4]       384     937      0        
15:59:09 [4/4]       385     1322     0        
15:59:10 [4/4]       489     1707     0        
15:59:11 [4/4]       470     2196     0        
15:59:12 [4/4]       509     2666     0        
15:59:13 [4/4]       423     3175     0        
15:59:14 [4/4]       499     3598     0        
15:59:15 [4/4]       437     4097     0        
…
…
…

 

As we captured all the applications SQL and added them it the allow-list the application functions as expected without errors. If we check the violations log again it still only has the one previous violation, that we saw before the run:

 

select SQL_TEXT, IP_ADDRESS, OCCURRED_AT from DBA_SQL_FIREWALL_VIOLATIONS where username = ‘SOE’;

SQL_TEXT       IP_ADDRESS     OCCURRED_AT
———————————————————————- ——————– ——————————
SELECT CUSTOMER_ID FROM CUSTOMERS WHERE ROWNUM <:”SYS_B_0″       10.0.0.253     02-MAY-23 15.54.11.167040 +00:00

 

It is possible to purge the violations log using the DBMS_SQL_FIREWALL.PURGE_LOG procedure which enables you to completely purge, or purge the logs by filter (user, time range).

If I connect as another user that has access to the SOE schemas objects and does not have an enabled allow-list in place, that user can run any statement on the SOE schemas objects. Allow-lists are attached to database users. As well as controlling which statements can be blocked and/or logged as violations we can also control the context. The DBMS_SQL_FIREWALL package helps to ensure data is accessed via a trusted path.

The DBMS_SQL_FIREWALL package can control enforcement via context. The context can be based on the users IP address, the underlying OS program making the call, or the OS user.

Current context settings can be viewed via the DBA_SQL_FIREWALL_ALLOWED_OS_PROG, DBA_SQL_FIREWALL_OS_USER and DBA_SQL_FIREWQLL_ALLOWED_IP_ADDR views.

 

SQL> select * from DBA_SQL_FIREWALL_ALLOWED_OS_PROG;

USERNAME   OS_PROGRAM
---------- ---------------------------------------------------
SOE   JDBC Thin Client
SOE   sqlplus
SOE   sqlplus@instance-20230502-1247 (TNS V1-V3)

 

We can see for the SOE user only the above OS program contexts are set. The SwingBench application uses the JDBC Thin Client driver. Perhaps we only want the SOE user to be able to run the application, but not have access to SQL statements via SQLPlus. 

For this we must update the context list.

 

BEGIN
DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT(
username=>'SOE',
context_type=>DBMS_SQL_FIREWALL.OS_PROGRAM,
value=>'sqlplus');
END;
/

PL/SQL procedure successfully completed.

BEGIN  DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT(
username=>'SOE',
context_type=>DBMS_SQL_FIREWALL.OS_PROGRAM,
value=>'sqlplus@instance-20230502-1247 (TNS V1-V3)');
END;
/


PL/SQL procedure successfully completed.
  • username: The user for which we are going to delete context information
  • context_type: the type of context we are altering. In this case it is calling program, but it can be on of:
    • DBMS_SQL_FIREWALL.OS_PROGRAM: The calling program
    • DBMS_SQL_FIREWALL.IP_ADDRERSS: The IP Address of the caller
    • DBMS_SQL_FIREWALL.OS_USERNAME: The OS user making the call.
  • value: The value of the context you wish to remove

After the alterations we can check the new context information:

 

select * from DBA_SQL_FIREWALL_ALLOWED_OS_PROG;

USERNAME   OS_PROGRAM
---------- ---------------------------------------------------
SOE   JDBC Thin Client

 

With the new context for calling OS program only connections from a JDBC Thin Client will be accepted.

We now must update our enforcement rules to include context. When we originally enabled the allow-list earlier in this blog we specified for the firewall to only look at SQL statements and not environmental context.

 

BEGIN
DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT(
username=>'soe',
enforce=>DBMS_SQL_FIREWALL.ENFORCE_ALL,
block=>TRUE);
END;
/

PL/SQL procedure successfully completed.
  • username: The user of the allow-list we want to modify. In our case the SOE user
  • enforce: We will change this value from DBMS_FIREWALL.ENFORCE_SQL (sql only) to DBMS_FIREWALL.ENFORCE_ALL (sql and contexts)
  • block: We will keep block as TRUE, so that sql not on the allow-list will not be executed.

The firewall will now take context as well as the SQL statements into account. As the SOE user if I now try to run a sql statement, even one that exists in the allow-list for the SOE user, from sqlplus it will be rejected because of the context. 

SELECT ADDRESS_SEQ.NEXTVAL FROM DUAL;
SELECT ADDRESS_SEQ.NEXTVAL FROM DUAL

*ERROR at line 1:
ORA-47605: SQL Firewall violation.

 

Even though the above query is in the allow-list it is blocked as the command was issued from sqlplus, which is no longer an accepted context. The SwingBench application will still run without issue as it uses JDBC not sqlplus.

Currently we are running our application on the database VM. We may want to run our application on a separate VM. If we check the currently allowed IP addresses we see:

select * from DBA_SQL_FIREWALL_ALLOWED_IP_ADDR;

USERNAME   IP_ADDRESS
---------- ------------
SOE   10.0.0.253

 

Our new application host vm has an IP address of 10.0.0.250. Let’s remove the current IP address and add the new one. Note that we use a context_type of DBMS_SQL_FIREWALL.IP_ADDRESS in the DELETE_ALLOWED_CONTEXT and ADD_ALLOWED_CONTEXT procedures.

 

BEGIN
DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT(
username=>'SOE',
context_type=>DBMS_SQL_FIREWALL.IP_ADDRESS,
value=>'10.0.0.253');
END;
/

PL/SQL procedure successfully completed.


BEGIN
DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT(
username=>'SOE',
context_type=>DBMS_SQL_FIREWALL.IP_ADDRESS,
value=>'10.0.0.250');
END;
/

PL/SQL procedure successfully completed.


select * from DBA_SQL_FIREWALL_ALLOWED_IP_ADDR;

USERNAME   IP_ADDRESS
---------- ------------
SOE   10.0.0.250

 

If we now try to run the application from the database server (the old IP address) it will be blocked by the firewall as the users IP address does not come from 10.0.0.250.

 

 
/home/oracle/swingbench/bin/charbench -c test.xml -v users,tpm,tps,errs

Time     Users       TPM      TPS     Errors   
18:31:04 [0/4]       0        0       0        
May 04, 2023 6:31:05 PM com.dom.benchmarking.swingbench.kernel.SwingBenchConnectionFactory
getDedicatedConnection
SEVERE: getDedicatedConnection() threw exception
java.sql.SQLException: ORA-47605: SQL Firewall violation.   <<< Firewall blocked application
…
…
…

 

Finally

In most scenarios, you may want to exclude Oracle Scheduler jobs from SQL Firewall enforcement because these are not typically run by users.

You can enable or disable the operation of SQL Firewall during Oracle Scheduler operations by setting the FEATURE parameter to the DBMS_SQL_FIREWALL.SCHEDULER_JOB constant, using the following procedures:

  • DBMS_SQL_FIREWALL.INCLUDE permits SQL Firewall to capture any SQL or enforce any allow-lists during Oracle Scheduler operations.
  • DBMS_SQL_FIREWALL_EXCLUDE prevents SQL Firewall from capturing any SQL or enforcing any allow-lists during Oracle Scheduler operations.

For example:

EXEC DBMS_SQL_FIREWALL.EXCLUDE (DBMS_SQL_FIREWALL.SCHEDULER_JOB);

 

Conclusion

This is only a basic introduction into the functionality of SQL Firewall, but hopefully it is possible to see how easily this can increase security and help ensure access via trusted paths. For full functionality please visit the documentation.