Strengthening Your Defense: New SQL Firewall Features in Oracle 26ai

Oracle Database has long been the fortress of enterprise data, but with the release of Oracle 26ai, that fortress just got a lot smarter. One of the most critical security enhancements in recent years is the SQL Firewall, and the latest update introduces a surgical way to manage with the dbms_sql_firewall.append_allow_list_single_sql procedure.

What is Oracle SQL Firewall?

Before diving into the new features, let’s recap how the SQL Firewall works. Think of it as a "VIP list" for your database. It provides real-time protection against SQL injection and unauthorized access by restricting users to a specific set of:

• Authorized SQL statements
• Approved connection paths (IP addresses, programs, etc.)

For the firewall to be effective, it first needs to be trained. You enable a "capture" period where the database learns the normal behavior of a user. Once you're confident you've captured all legitimate traffic, you generate an allow-list. Anything not on that list? Blocked.

The New Player: append_allow_list_single_sql

Previously, updating an allow-list could be a bit "all or nothing." In Oracle 26ai, the new append_allow_list_single_sql procedure allows for much more granular control.

Why is this a big deal? Imagine you’ve already deployed your security policy, but a developer introduces a single new (and legitimate) query. Instead of restarting a full capture session or manually rebuilding the entire list, you can now surgically pluck a single SQL record from your violation log or capture log and add it directly to the allow-list.

Key Benefit: It simplifies maintenance. You can address "false positives" (legitimate queries that were accidentally blocked) individually without lowering your guard for the rest of the system.

How to Get Started

1. Enable the Firewall

By default, the SQL Firewall is disabled. Your first step is to activate the functionality within the database:

-- Ensure the firewall is ready for action

SQL> select STATUS from DBA_SQL_FIREWALL_STATUS;

STATUS

DISABLED

To enable it

SQL> EXEC DBMS_SQL_FIREWALL.ENABLE;

PL/SQL procedure successfully completed.

SQL> select STATUS from DBA_SQL_FIREWALL_STATUS;

STATUS

ENABLED

Phase 1: Training the Firewall (Capture)

You cannot protect what you don't understand. You must enable a capture session for a specific user during a period of normal activity to "educate" the firewall on what legitimate traffic looks like. This is done through a Capture.

 To start a capture for a user (e.g., SCOTT), we use the CREATE_CAPTURE procedure:

top_level_only: Set to TRUE for statements executed directly by the user; FALSE includes background PL/SQL programs.

start_capture: If set to FALSE, you must manually run DBMS_SQL_FIREWALL.START_CAPTURE('SCOTT') later.

You can monitor the learning progress via the DBA_SQL_FIREWALL_CAPTURES and DBA_SQL_FIREWALL_CAPTURE_LOGS views to see exactly what patterns Oracle is recording.

Phase 2: Building the Fortress (Allow-Lists)

Once the capture period is complete (ensuring all legitimate application queries have been run), you stop the capture and generate the list of permitted actions:

With this active, any query not in the DBA_SQL_FIREWALL_ALLOWED_SQL view will trigger an ORA-47605: SQL Firewall violation.

The 26ai Advantage: Granular Control

In previous versions, if you missed a single query during the capture phase, your only choice was to use APPEND_ALLOW_LIST, which would add every recorded violation to the allow-list at once. This was risky—if a hacker had attempted an injection during that time, you might accidentally authorize their attack.

Oracle 26ai solves this with APPEND_ALLOW_LIST_SINGLE_SQL. This allows you to pick one specific query from the violation log and authorize it.

How to surgically add a query:

1. Identify the culprit: Find the SQL_SIGNATURE in the violation logs.

SELECT SQL_TEXT, SQL_SIGNATURE FROM DBA_SQL_FIREWALL_VIOLATIONS WHERE USERNAME = 'SCOTT';

Summary

The addition of append_allow_list_single_sql in Oracle 26ai represents a shift toward more agile database security. It allows DBAs to maintain a "Zero Trust" environment while providing the flexibility to handle legitimate application changes without lowering the shield for the entire system.