SQL Firewall in Oracle 23c

SQL Firewall inspects all incoming statements and ensures only authorized SQL is run and it is embedded in the Oracle Database and unauthorized SQL Statements will be logged and blocked.

SQL firewall provides real-time protection from attacks and mitigate risks from SQL injection attacks, anomalous access, credential abuse or theft. SQL Firewall supports all commands except transaction control commands such as SAVEPOINT, COMMIT, ROLLBACK.

To administer SQL Firewall user must have SQL_FIREWALL_ADMIN role. To query DBA_SQL_FIREWALL* data dictionary the user must have SQL_FIREWALL_VIEWER role

You can Configure SQL Firewall using DBMS_SQL_FIREWALL package or Oracle Data Safe. SQL Firewall can be used in both root and Pluggable Database (PDB)


You can enable SQL Firewall using below command.
SQL> EXEC DBMS_SQL_FIREWALL.ENABLE;

Create and enable SQL Firewall capture for a user using below command

SQL> BEGIN
DBMS_SQL_FIREWALL.CAPTURE_CAPTURE (
Username => ‘SCOTT’
top_level_only => TRUE,
Start_capture => TRUE
);
END;

Enable SQL Firewall Allow List

SQL>BEGIN
DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (
username => ‘SCOTT’,
enforce => DBMS_SQL_FIREWALL.ENFORCE_SQL,
block => TRUE );
END;

You can use below commands to START and STOP capture.

SQL> EXEC DBMS_SQL_FIREWALL.START_CAPTURE (‘SCOTT’);
SQL> EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE (‘SCOTT’);

You can generate an allow list using below procedures.

DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT
DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT
DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL

The SQL Firewall may generate large volume of capture logs and to minimize performance impact the database memory needs to be sized to handle the load. It is advised to add additional 2GB to LARGE_POOL_SIZE parameter and also it advised to have SGA_TARGET to 8GB or more.

To purge logs you can use below procedure

BEGIN
DBMS_SQL_FIREWALL.PURGE_LOG (
username => ‘SCOTT’,
purge_time => '2024-01-10 12:00:00.00 -08:00',
log_type => 'DBMS_SQL_FIREWALL.ALL_LOGS'
);
END;
/

You can also enable and disable SQL Firewall Trace using below commands. The trace level value should be LOW, HIGH, HIGHEST based upon how much detail tracing you want to have.

-Session level Tracing Enable and Disable
ALTER SESSION SET EVENTS 'TRACE SQL_FIREWALL DISK=trace_level
ALTER SESSION SET EVENTS 'TRACE SQL_FIREWALL OFF

-System Level Tracing Enable and Disable
ALTER SYSTEM SET EVENTS 'TRACE SQL_FIREWALL DISK=trace_level
ALTER SYSTEM SET EVENTS 'TRACE SQL_FIREWALL OFF

You can query below Data Dictionary Views for SQL Firewall protections
DBA_SQL_FIREWALL_ALLOWED_SQL - View shows allowed SQL and Accessed objects
DBA_SQL_FIREWALL_ALLOWED_IP_ADDR – View shows the Users allowed IP address
DBA_SQL_FIREWALL_CAPTURE_LOGS – View shows the Capture log entries
DBA_SQL_FIREWALL_VIOLATIONS – View shows the SQL Firewall Violations

Thanks & Regards

https://oracleracexpert.com

Oracle ACE Pro