Oracle AI Database 26ai / SQL Firewall / Injection Countermeasures / Security
In this article, I will explain the implementation steps for the new “SQL Firewall” feature in Oracle Database 26ai (23ai). Previously, this required a separate product called “Audit Vault and Database Firewall (AVDF),” but starting from 26ai, it is included in the database kernel, making it available as a standard feature that is easier to use. This time, I will cover everything from creating an allow-list to verifying the blocking behavior, and finally, the recovery procedure for cases where “necessary operations (such as login) are accidentally blocked.”
Note regarding the verification environment: The verification results and logs in this article were obtained in an Oracle Database 26ai Free Release 23.26.0.0.0 environment. Please be aware that behavior or output messages may differ in the official version (Enterprise Edition, etc.) or future versions.
1. Introduction and Licensing
SQL Firewall is a security feature that blocks unauthorized SQL.
License Requirements
To use this feature, one of the following licenses is required:
- Oracle Database Vault
- Oracle Audit Vault and Database Firewall
However, if you are using Oracle Cloud (OCI), the “Oracle Database Vault” license is included in the following services, so it is available at no additional cost:
- BaseDB EE-HP (High Performance)
- BaseDB EE-EP (Extreme Performance)
- ExaDB-D (Exadata Database Service)
Note: Licensing details and application conditions are subject to change. Please ensure you check the Oracle Official Documentation (Licensing Information) for the latest accurate information.
2. Mechanism of SQL Firewall (SQL Standardization)
SQL Firewall registers captured SQL as an “Allow List.” During this process, literal values (specific numbers or strings) in the SQL statement are automatically replaced with system variables (such as :"SYS_B_0") to standardize the SQL.
- Executed SQL:
SELECT * FROM products WHERE id = 123 - Stored SQL:
SELECT * FROM products WHERE id = :"SYS_B_0"
This means you do not need to register a separate SQL statement every time the ID value changes, allowing for flexibility even with applications that do not use bind variables.
3. Preparing the Verification Environment
Create a user FW_TEST and a table for verification. This procedure assumes execution on a PDB (such as FREEPDB1).
-- Connect as PDB administrator (SYS, etc.)
CONN sys/password@FREEPDB1 AS SYSDBA
-- Create user and grant privileges
CREATE USER fw_test IDENTIFIED BY Password_123;
GRANT CONNECT, RESOURCE TO fw_test;
GRANT UNLIMITED TABLESPACE TO fw_test;
-- Create test data
CREATE TABLE fw_test.products (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
price NUMBER
);
INSERT INTO fw_test.products VALUES (101, 'Mouse', 3000);
INSERT INTO fw_test.products VALUES (102, 'Keyboard', 8000);
COMMIT;
4. Verification Steps
Step 1: Enabling SQL Firewall
First, enable the feature at the PDB level. Requires SQL_FIREWALL_ADMIN privilege or SYSDBA privilege.
-- Execute as PDB administrator
EXEC DBMS_SQL_FIREWALL.ENABLE;
-- Verify (Ensure it is ENABLED)
SELECT status FROM DBA_SQL_FIREWALL_STATUS;
Example Execution Log:
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
--------
ENABLED
Step 2: Starting Capture
Start SQL capture for the target verification user FW_TEST. By specifying start_capture => TRUE, you can start capturing immediately upon creation.
-- Reset if existing settings exist (just in case)
BEGIN
BEGIN DBMS_SQL_FIREWALL.STOP_CAPTURE('FW_TEST'); EXCEPTION WHEN OTHERS THEN NULL; END;
BEGIN DBMS_SQL_FIREWALL.DROP_CAPTURE('FW_TEST'); EXCEPTION WHEN OTHERS THEN NULL; END;
BEGIN DBMS_SQL_FIREWALL.DISABLE_ALLOW_LIST('FW_TEST'); EXCEPTION WHEN OTHERS THEN NULL; END;
BEGIN DBMS_SQL_FIREWALL.DROP_ALLOW_LIST('FW_TEST'); EXCEPTION WHEN OTHERS THEN NULL; END;
END;
/
-- Create and start capture
BEGIN
DBMS_SQL_FIREWALL.CREATE_CAPTURE (
username => 'FW_TEST',
top_level_only => TRUE,
start_capture => TRUE -- ★ Start immediately here
);
END;
/
Example Execution Log:
SQL> BEGIN
2 BEGIN DBMS_SQL_FIREWALL.STOP_CAPTURE('FW_TEST'); EXCEPTION WHEN OTHERS THEN NULL; END;
3 BEGIN DBMS_SQL_FIREWALL.DROP_CAPTURE('FW_TEST'); EXCEPTION WHEN OTHERS THEN NULL; END;
4 BEGIN DBMS_SQL_FIREWALL.DISABLE_ALLOW_LIST('FW_TEST'); EXCEPTION WHEN OTHERS THEN NULL; END;
5 BEGIN DBMS_SQL_FIREWALL.DROP_ALLOW_LIST('FW_TEST'); EXCEPTION WHEN OTHERS THEN NULL; END;
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_SQL_FIREWALL.CREATE_CAPTURE (
3 username => 'FW_TEST',
4 top_level_only => TRUE,
5 start_capture => TRUE
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
Step 3: Normal SQL Execution (Learning)
Operations performed here will be registered in the Allow List as “normal access.” Log in as the FW_TEST user and execute legitimate SQL.
-- Connect as FW_TEST
CONN fw_test/Password_123@FREEPDB1
-- Execute legitimate SQL (These will be allowed)
SELECT * FROM products WHERE id = 101;
SELECT name, price FROM products;
Example Execution Log:
SQL> CONN fw_test/Password_123@FREEPDB1
Connected.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
FREEPDB1
SQL> SELECT * FROM products WHERE id = 101;
ID NAME PRICE
---------- ---------------------------------------- ----------
101 Mouse 3000
SQL> SELECT name, price FROM products;
NAME PRICE
-------------------------------------------------- ----------
Mouse 3000
Keyboard 8000
SQL>
Step 4: Stopping Capture and Checking Logs
Once sufficient logs have been collected, stop the capture and check the recorded content.
-- Reconnect as SYS
CONN sys/password@FREEPDB1 AS SYSDBA
-- Stop capture
EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE('FW_TEST');
-- Check capture logs (Output in CSV format for readability)
-- Checking client_program also helps identify the source (sqlplus, jdbc, etc.)
SET MARKUP CSV ON
SELECT command_type, sql_text FROM DBA_SQL_FIREWALL_CAPTURE_LOGS WHERE username = 'FW_TEST';
SET MARKUP CSV OFF
Example Execution Log:
SQL> show con_name
CON_NAME
------------------------------
FREEPDB1
SQL> EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST('FW_TEST');
PL/SQL procedure successfully completed.
SQL> SELECT sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE username = 'FW_TEST';
SQL_TEXT
--------------------------------------------------------------------------------------
SELECT NAME,PRICE FROM PRODUCTS
SELECT * FROM PRODUCTS WHERE ID=:"SYS_B_0"
SELECT SYS_CONTEXT (:"SYS_B_0",:"SYS_B_1") NAME_COL_PLUS_PDB_CONTAINER FROM SYS.DUAL
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
SQL> SELECT username, status, enforce, block FROM DBA_SQL_FIREWALL_ALLOW_LISTS WHERE username = 'FW_TEST';
USERNAME STATUS ENFORCE BLOCK
---------- -------- --------------- --------------
FW_TEST DISABLED ENFORCE_ALL N
SQL>
Step 5: Creating and Verifying the Allow List
Generate the Allow List based on the capture logs.
-- Generate list
EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST('FW_TEST');
-- Verify the generated SQL list
-- Confirm that literal values have been replaced with :"SYS_B_0" etc.
SELECT sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE username = 'FW_TEST';
-- Check list configuration status
-- Since it is not enabled yet, BLOCK='N'
SELECT username, status, enforce, block FROM DBA_SQL_FIREWALL_ALLOW_LISTS WHERE username = 'FW_TEST';
Step 6: Enabling the Allow List
Now, enable the defense mode. In addition to enforce => ENFORCE_ALL, explicitly specify block => TRUE.
BEGIN
DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
username => 'FW_TEST',
enforce => DBMS_SQL_FIREWALL.ENFORCE_ALL, -- Enforce block mode
block => TRUE -- ★ This is important
);
END;
/
-- Verify after setting (Confirm BLOCK='Y')
SELECT username, status, enforce, block FROM DBA_SQL_FIREWALL_ALLOW_LISTS WHERE username = 'FW_TEST';
Example Execution Log:
SQL> BEGIN
2 DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
3 username => 'FW_TEST',
4 enforce => DBMS_SQL_FIREWALL.ENFORCE_ALL,
5 block => TRUE
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> SELECT username, status, enforce, block FROM DBA_SQL_FIREWALL_ALLOW_LISTS WHERE username = 'FW_TEST';
USERNAME STATUS ENFORCE BLOCK
---------- -------- --------------- --------------
FW_TEST ENABLED ENFORCE_ALL Y
SQL>
Step 7: Verifying Blocking Behavior
Execute unauthorized SQL (such as an injection attack) and confirm that it results in an error.
-- Connect as FW_TEST
CONN fw_test/Password_123@FREEPDB1
-- [OK] Legitimate SQL in the list
SELECT * FROM products WHERE id = 101;
-- [NG] Unknown SQL (Injection attack)
SELECT * FROM products WHERE id = 101 OR 1=1;
Example Execution Log:
SQL> CONN fw_test/Password_123@FREEPDB1
Connected.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
FREEPDB1
SQL> SELECT * FROM products WHERE id = 101;
ID NAME PRICE
---------- -------------------------------------------------- ----------
101 Mouse 3000
SQL> SELECT * FROM products WHERE id = 101 OR 1=1;
SELECT * FROM products WHERE id = 101 OR 1=1
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
Help: https://docs.oracle.com/error-help/db/ora-47605/
SQL>
If this error occurs, the Firewall is functioning perfectly!
5. [Bonus] How to Handle Cases Where Even Login is Blocked
After enabling SQL Firewall, you may find that “logging in via SQLPlus is denied.” This happens because the internal SQL or program information during connection differs between applications (like JDBC) and tools (like SQLPlus), causing the SQL*Plus login operation to be considered an “unknown operation.” In this case, add the missing parts to the Allow List (Append) using the following steps.
Procedure: Learn and Add Missing Operations
- Temporarily disable the listSQL
-- Execute as SYS EXEC DBMS_SQL_FIREWALL.DISABLE_ALLOW_LIST('FW_TEST'); - Start additional captureSQL
EXEC DBMS_SQL_FIREWALL.START_CAPTURE('FW_TEST'); - Execute the blocked operation (Login, etc.) Run
sqlplus fw_test/...in another terminal to confirm you can login normally. - Stop capture and Add to list (APPEND) Use
APPEND_ALLOW_LISTinstead ofGENERATEto add the differential data to the existing list.SQL-- Stop capture EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE('FW_TEST'); -- ★ Key Point: Add capture log content to existing list BEGIN DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST ( username => 'FW_TEST', source => DBMS_SQL_FIREWALL.CAPTURE_LOG ); END; / - Re-enable the Allow ListSQL
BEGIN DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST( username => 'FW_TEST', enforce => DBMS_SQL_FIREWALL.ENFORCE_ALL, block => TRUE ); END; /
Now, you can permit operations from operational tools without interfering with existing application traffic.
6. FAQ
Q1: How is this different from the previous SQL Firewall (AVDF)? A: The biggest difference is that it is “kernel-integrated.” Since it runs inside the database engine, it has the advantages of extremely low latency compared to the network proxy type (AVDF) and is more resistant to encrypted traffic or bypass attacks. However, AVDF is more comprehensive in terms of audit reporting features.
Q2: Can I manually add to the Allow List? A: Yes. You can use the DBMS_SQL_FIREWALL.ADD_ALLOWED_SQL procedure to manually add specific SQL text to the list. However, since you need to match the syntax and literal replacement rules, it is generally recommended to use the capture function (APPEND_ALLOW_LIST).
Q3: What is the impact on performance? A: The design ensures very low runtime overhead because verification is performed during the SQL Parse phase. However, if the Allow List swells to hundreds of thousands of entries, it may affect memory usage and search costs. It is recommended to periodically clean up unnecessary SQL.
7. Summary
In Oracle 26ai (23ai), SQL Firewall has become available as a standard feature. Using the allow-list method (rejecting everything except allowed SQL), you can reliably prevent unknown SQL injection attacks. Implementation is completed in 4 simple steps: “Enable → Learn (Capture) → Generate List → Defend (Block).” Even if necessary operations are blocked, you can flexibly handle them with additional learning (Append). Once configured, SQL Firewall serves as a robust security foundation where “nothing but allowed SQL can be executed.” Please give it a try.
[reference]
DBMS_SQL_FIREWALL
コメント