SQL Firewall serves as a robust defense mechanism embedded in the Oracle Database 23c engine, offering real-time protection against prevalent database attacks. It achieves this by restricting database access exclusively to authorized SQL statements or connections for designated users. This article delves into the fundamental aspects of utilizing SQL Firewall within Oracle Database 23c, elucidating its implementation process with illustrative code examples.
For the purposes of this demonstration, I utilized a VirtualBox appliance featuring Oracle Database 23c Free Developer-Release (version 23.0). Within this environment, I established a new Pluggable Database (PDB1), created a schema named SOE, and connected it to the Swingbench testing application, which can be downloaded from this link
Note: If you plan to replicate this demonstration in a virtual environment, it is advisable to create a snapshot of the VM before proceeding.
Demonstration Steps
To initiate SQL Firewall, connect to the root or PDB as a user granted the SQL_FIREWALL_ADMIN role and enable SQL Firewall:
[oracle@localhost ~]$ sql sys/oracle@//localhost/pdb2 as sysdba
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> EXEC DBMS_SQL_FIREWALL.ENABLE;
PL/SQL procedure successfully completed.Next, create and enable the SQL Firewall capture for the user SOE to enter training mode:
BEGIN
DBMS_SQL_FIREWALL.CREATE_CAPTURE (
username => 'SOE',
top_level_only => TRUE,
start_capture => TRUE
);
END;
/Monitor the capture progress by querying its logs:
SQL> SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS WHERE USERNAME = 'SOE';
SQL_TEXT
____________________________________
INSERT INTO LOGON (LOGON_ID,CUSTOMER_ID,LOGON_DATE) VALUES (LOGON_SEQ.NEXTVAL,:1,:2)
INSERT INTO LOGON (LOGON_ID,CUSTOMER_ID,LOGON_DATE) VALUES (LOGON_SEQ.NEXTVAL,:1,:2)
INSERT INTO LOGON (LOGON_ID,CUSTOMER_ID,LOGON_DATE) VALUES (LOGON_SEQ.NEXTVAL,:1,:2)
INSERT INTO LOGON (LOGON_ID,CUSTOMER_ID,LOGON_DATE) VALUES (LOGON_SEQ.NEXTVAL,:1,:2)
BEGIN DBMS_APPLICATION_INFO.SET_CLIENT_INFO (?); END;
...After capturing the application workload, stop the capture:
EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE ('SOE');Generate the allow-list for the user based on captured data:
The allow-list defines the SQL statements that the user will be allowed to perform. SQL Firewall creates the allow-list based on data collected from existing capture logs for the user.
EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('SOE');Query the allowed SQL statements for the user:
SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE USERNAME = 'SOE';
SQL_TEXT
______________
INSERT INTO ORDERS (ORDER_ID,ORDER_DATE,CUSTOMER_ID,WAREHOUSE_ID,DELIVERY_TYPE,COST_OF_DELIVERY,WAIT_TILL_ALL_AVAILABLE) VALUES (:1,:2,:3,:4,:5,:6,:7)
SELECT PRODUCT_ID,PRODUCT_NAME,PRODUCT_DESCRIPTION,CATEGORY_ID,WEIGHT_CLASS,SUPPLIER_ID,PRODUCT_STATUS,LIST_PRICE,MIN_PRICE,CATALOG_URL FROM PRODUCT_INFORMATION WHERE PRODUCT_ID=:1
SELECT MIN (WAREHOUSE_ID),MAX (WAREHOUSE_ID) FROM WAREHOUSES
SELECT ADDRESS_ID,CUSTOMER_ID,DATE_CREATED,HOUSE_NO_OR_NAME,STREET_NAME,TOWN,COUNTY,COUNTRY,POST_CODE,ZIP_CODE FROM ADDRESSES WHERE CUSTOMER_ID=:1 AND ROWNUM <:"SYS_B_0"
SELECT CUSTOMER_SEQ.NEXTVAL,ADDRESS_SEQ.NEXTVAL,CARD_DETAILS_SEQ.NEXTVAL FROM DUAL
UPDATE ORDERS SET ORDER_STATUS=:1 WHERE ORDER_ID=:2
UPDATE ORDER_ITEMS SET QUANTITY=QUANTITY +:"SYS_B_0" WHERE ORDER_ITEMS.ORDER_ID=:1 AND ORDER_ITEMS.LINE_ITEM_ID=:2
...
29 rows selected.The allowed IP Address, OS Program, and User should automatically be set.
SELECT COUNT(*) FROM DBA_SQL_FIREWALL_ALLOWED_IP_ADDR;
SELECT COUNT(*) FROM DBA_SQL_FIREWALL_ALLOWED_OS_PROG;
SELECT COUNT(*) FROM DBA_SQL_FIREWALL_ALLOWED_OS_USER;Enable SQL Firewall protection by enforcing the generated allow-list:
This enablement becomes effective immediately, even in the existing sessions of the target user.
BEGIN
DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (
username => 'SOE',
enforce => DBMS_SQL_FIREWALL.ENFORCE_SQL,
block => TRUE
);
END;
/To test a violation, attempt to execute a query not in the allow-list:
Observe the error response due to a SQL Firewall violation.
[oracle@localhost ~]$ sqlplus soe/ABcd##1234@//localhost/pdb2
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0
SQL> SELECT COUNT(*) FROM customers;
select count(*) from customers
*
ERROR at line 1:
ORA-47605: SQL Firewall violationReview the violation logs:
set linesize 180
col SQL_TEXT format a20
col FIREWALL_ACTION format a20
col CLIENT_PROGRAM for a30
col OCCURRED_AT for a20
col IP_ADDRESS for a20
SELECT substr(SQL_TEXT,1,20) SQL_TEXT, FIREWALL_ACTION, IP_ADDRESS, CAUSE, CLIENT_PROGRAM, to_char(OCCURRED_AT,'dd-mm-yy hh12:mi am') OCCURRED_AT
FROM DBA_SQL_FIREWALL_VIOLATIONS WHERE USERNAME = 'SOE' ORDER BY OCCURRED_AT;
SQL_TEXT FIREWALL_ACTION IP_ADDRESS CAUSE CLIENT_PROGRAM OCCURRED_AT
-------------------- -------------- ------------ ---------------- ------------------------------ -----------------
SELECT DECODE (USER, Blocked 127.0.0.1 SQL violation sqlplus@localhost.localdomain 31-12-23 06:00 am
(TNS V1-V3)
SELECT COUNT (*) FRO Blocked 127.0.0.1 SQL violation sqlplus@localhost.localdomain 31-12-23 06:00 am
(TNS V1-V3)
To allow the previously violated actions, use the procedure DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST:
BEGIN
DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST (
username => 'SOE',
source => DBMS_SQL_FIREWALL.ALL_LOGS
);
END;
/Re-run the previously failing SELECT statement, and observe its success without reconnecting to the database.
SQL> select count(*) from customers ;
COUNT(*)
----------
370229For more information, check out the following documentation link:
Security Guide
Using SQL Firewall
https://docs.oracle.com/en/database/oracle/oracle-database/23/dbseg/using-sql-firewall.html#GUID-F53EAE01-CE78-47F4-80AD-A0091BA3C434
Conclusion
Oracle SQL Firewall offers robust protection against database attacks by restricting unauthorized SQL activity. It’s a valuable addition to Oracle Database 23c’s security arsenal, helping organizations safeguard their critical data.