One of the new features related to database security introduced in Oracle Database 23c is SQL Firewall.
It is embedded in the Oracle Database 23c kernel and conceptually is quite similar to Database Firewall (Audit Vault and Database Firewall AVDF).
SQL Firewall offers real-time protection to the database from external threats like SQL Injection attacks and also by blocking any unauthorized SQL statements from being executed inside the database.
We can build an ‘allow list’ of authorized SQL statements and SQL Firewall monitors and then can also potentially block any SQL statements violating the ‘allow list’.
SQL Firewall can also use session context data such as IP address, OS User and Program Type for its enforcement.
This post illustrates SQL Firewall in operation by a simple example. We are using the 23c Free Developer Release database for this example.
Let us assume the security requirement is to allow the application developer database user DEV1 to only issue SELECT queries on certain tables and not allow any non-SELECT statements to be run like UPDATE /INSERT/DELETE statements.
Create a user DF_ADMIN to administer and manage SQL Firewall. The user needs to be granted the SQL_FIREWALL_ADMIN role.
SQL> create user DF_ADMIN identified by Oracle_4U; User created. SQL> grant create session, sql_firewall_admin, audit_admin to df_admin; Grant succeeded.
Enable the SQL Firewall. Check the status via DBA_SQL_FIREWALL view.
SQL> conn df_admin/Oracle_4U Connected. SQL> exec dbms_sql_firewall.enable; PL/SQL procedure successfully completed. SQL> select status from dba_sql_firewall_status; STATUS -------- ENABLED
“Train” the SQL firewall by creating and enabling the SQL Firewall capture for a specified database user – in this case the developer user account DEV1.
SQL> exec dbms_sql_firewall.create_capture('DEV1');
PL/SQL procedure successfully completed.
SQL> select username,status from dba_sql_firewall_captures;
USERNAME STATUS
-------- --------
DEV1 ENABLED
Connect as the application developer user and issue some SQL statements. Let us consider these to be the “normal authorized or trusted” SQL statements which will typically issued by the application.
SQL> conn dev1/Oracle_4U@freepdb1
Connected.
SQL> select count(*) from mytab;
COUNT(*)
----------
121
SQL> select count(*) from myobjects;
COUNT(*)
----------
58619
SQL> select distinct owner from mytab;
OWNER
--------------------------------------------------------------------------------
SYS
XDB
SYSTEM
...
...
7 rows selected.
SQL> select distinct owner from myobjects;
OWNER
--------------------------------------------------------------------------------
SYS
PUBLIC
SYSTEM
...
...
15 rows selected.
SQL> select object_name from myobjects where owner='DEV1';
OBJECT_NAME
--------------------------------------------------------------------------------
MYTAB
MYVIEW
MYPROC
Connect as the SQL Firewall admin user and stop the capture when desired.
SQL> conn df_admin/Oracle_4U
Connected.
SQL> exec dbms_sql_firewall.stop_capture ('DEV1');
PL/SQL procedure successfully completed.
Review the captured SQL statements from the SQL Firewall capture logs and the “trusted” database connection paths.
SQL> select username,ip_address,client_program,os_user from dba_sql_firewall_session_logs where username='DEV1'; USERNAME IP_ADDRESS CLIENT_PROGRAM OS_USER ------------- ----------------- ----------------------------------------- ------------- DEV1 127.0.0.1 sqlplus@localhost.localdomain (TNS V1-V3) oracle
SQL> select sql_text,accessed_objects from dba_sql_firewall_capture_logs where username='DEV1'; SQL_TEXT -------------------------------------------------------------------------------- ACCESSED_OBJECTS -------------------------------------------------------------------------------- SELECT COUNT (*) FROM MYTAB "DEV1"."MYTAB" SELECT DISTINCT OWNER FROM MYTAB "DEV1"."MYTAB" SELECT OBJECT_NAME FROM MYOBJECTS WHERE OWNER=:"SYS_B_0" "DEV1"."MYOBJECTS" SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL "SYS"."DUAL" SELECT DISTINCT OWNER FROM MYOBJECTS "DEV1"."MYOBJECTS" SELECT COUNT (*) FROM MYOBJECTS "DEV1"."MYOBJECTS" 6 rows selected.
Generate the allow-list for the user DEV1.
The allow-list is based on data collected from existing SQL Firewall capture logs for the user DEV1.
SQL> EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('DEV1');
PL/SQL procedure successfully completed.
Once generation is completed, review the “allowed” SQL as well as the trusted database connection paths – these will include trusted IP addresses, OS users and client programs.
SQL> SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE USERNAME ='DEV1'; SQL_TEXT -------------------------------------------------------------------------------- SELECT COUNT (*) FROM MYTAB SELECT DISTINCT OWNER FROM MYTAB SELECT OBJECT_NAME FROM MYOBJECTS WHERE OWNER=:"SYS_B_0" SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL SELECT DISTINCT OWNER FROM MYOBJECTS SELECT COUNT (*) FROM MYOBJECTS 6 rows selected.
SQL> select ip_address from dba_sql_firewall_allowed_ip_addr where username='DEV1'; IP_ADDRESS -------------------------------------------------------------------------------- 127.0.0.1 SQL> select os_user from dba_sql_firewall_allowed_os_user where username='DEV1'; OS_USER -------------------------------------------------------------------------------- oracle SQL> select os_program from dba_sql_firewall_allowed_os_prog where username='DEV1'; OS_PROGRAM -------------------------------------------------------------------------------- sqlplus@localhost.localdomain (TNS V1-V3)
Enable the ‘allow-list’ for the user DEV1.
Note that at this stage the SQL Firewall is not operating in ‘Blocking’ mode.
SQL> exec dbms_sql_firewall.enable_allow_list ('DEV1');
PL/SQL procedure successfully completed.
SQL> select username,status,block from dba_sql_firewall_allow_lists;
USERNAME
--------------------------------------------------------------------------------
STATUS BLOCK
-------- --------------
DEV1
ENABLED N
Connect as the application developer user DEV1 and now issue an ‘unauthorized’ SQL statement – an UPDATE statement on the MYTAB table.
SQL> conn dev1/Oracle_4U@freepdb1 Connected. SQL> update mytab set owner='JOE_BLOGG' where rownum=1; 1 row updated. SQL> commit; Commit complete.
This unauthorized statement has now triggered a violation. Query DBA_SQL_FIREWALL_VIOLATIONS to obtain the details of the violation.
As blocking is not enabled at the moment, unauthorized SQL traffic is permitted by the SQL Firewall, but all violations are being logged for review.
SQL> select username,sql_text,cause,firewall_action from dba_sql_firewall_violations; USERNAME SQL_TEXT CAUSE FIREWAL --------- ---------------------------------------------------------- ------- ----------- DEV1 UPDATE MYTAB SET OWNER=:"SYS_B_0" WHERE ROWNUM=:"SYS_B_1" SQL violation Allowed
We now turn on SQL Firewall ‘blocking’ – unauthorized or non-trusted SQL will be blocked as well as the violations will be logged for review.
SQL> exec dbms_sql_firewall.update_allow_list_enforcement ('DEV1',block=>TRUE);
PL/SQL procedure successfully completed.
SQL> select username,status,block from dba_sql_firewall_allow_lists;
USERNAME
--------------------------------------------------------------------------------
STATUS BLOCK
-------- --------------
DEV1
ENABLED Y
Connect as the DEV1 user and issue the same UPDATE statement which earlier had been permitted to run by the SQL Firewall.
Note now with blocking enabled, the SQL Firewall has not permitted the execution of the unauthorized SQL statement.
SQL> conn dev1/Oracle_4U@freepdb1 Connected. SQL> update mytab set owner='JOE_BLOGG' where rownum=1; update mytab set owner='JOE_BLOGG' where rownum=1 * ERROR at line 1: ORA-47605: SQL Firewall violation
The violation has caused an additional row to be logged in DBA_SQL_FIREWALL_VIOLATIONS with the FIREWALL_ACTION now showing the value ‘Blocked’.
SQL> select username,sql_text,cause,firewall_action from dba_sql_firewall_violations; USERNAME -------------------------------------------------------------------------------- SQL_TEXT -------------------------------------------------------------------------------- CAUSE FIREWAL -------------------- ------- DEV1 UPDATE MYTAB SET OWNER=:"SYS_B_0" WHERE ROWNUM=:"SYS_B_1" SQL violation Allowed DEV1 UPDATE MYTAB SET OWNER=:"SYS_B_0" WHERE ROWNUM=:"SYS_B_1" SQL violation Blocked
The SQL Firewall still permits trusted and authorized SQL statements even though it is running in blocking mode.
SQL> conn dev1/Oracle_4U@freepdb1
Connected.
SQL> select count(*) from mytab;
COUNT(*)
----------
121
What happens if we try and use a non-trusted client program like sqlcl – instead of the trusted program ‘sqlplus‘.
We can see that any connection using the sqlcl client program is also blocked while connections made using sqlplus are permitted.
[oracle@localhost ~]$ sql dev1/Oracle_4U@localhost:1521/FREEPDB1
SQLcl: Release 23.1 Production on Mon May 22 02:56:08 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
USER = dev1
URL = jdbc:oracle:oci8:@localhost:1521/FREEPDB1
Error Message = ORA-47605: SQL Firewall violation
USER = dev1
URL = jdbc:oracle:thin:@localhost:1521/FREEPDB1
Error Message = ORA-47605: SQL Firewall violation
Username? (RETRYING) ('dev1/*********@localhost:1521/FREEPDB1'?)
[oracle@localhost ~]$ sqlplus dev1/Oracle_4U@localhost:1521/FREEPDB1 SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon May 22 02:58:39 2023 Version 23.2.0.0.0 Copyright (c) 1982, 2023, Oracle. All rights reserved. Last Successful login time: Mon May 22 2023 02:47:17 +00:00 Connected to: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 SQL>
