SQL FIREWALL

This blog comes to complete the excellent blog written by my colleague Gregory Steulet on the same topic.

SQL FIREWALL is a new security layer integrated into Oracle Database 23c.

First, we create a admin user for which we grant it a new admin privilege “sql_firewall_admin”:

SQL> drop user if exists lfeadmin cascade;

User dropped.

SQL>
SQL> create user lfeadmin identified by lfeadmin;

User created.

SQL> grant create session to lfeadmin;

Grant succeeded.

SQL> grant sql_firewall_admin to lfeadmin;

Grant succeeded.

We enable the SQL FIREWALL:

SQL> prompt ENABLE SQL FIREWALL
ENABLE SQL FIREWALL
SQL> pause

SQL> conn lfeadmin/lfeadmin@//localhost:1521/freepdb1
Connected.
SQL> exec dbms_sql_firewall.enable;

PL/SQL procedure successfully completed.

SQL>
SQL> prompt CHECK THE STATUS OF SQL FIREWALL
CHECK THE STATUS OF SQL FIREWALL
SQL> pause

SQL> select status from dba_sql_firewall_status;

STATUS
--------
ENABLED

We create a capture process on the user LFE:

SQL> prompt CREATE CAPTURE FOR USER LFE
CREATE CAPTURE FOR USER LFE
SQL> pause

SQL> begin
  2    dbms_sql_firewall.create_capture (
  3  	 username	=> 'LFE',
  4  	 top_level_only => false,
  5  	 start_capture	=> true);
  6  end;
  7  /

PL/SQL procedure successfully completed.

We execute some sql from the user LFE:

SQL> conn LFE/LFE4DBIXCHANGE2023@//localhost:1521/freepdb1
Connected.
SQL> start demo_sql_firewall_load.sql
SQL> set echo on
SQL> set linesize 2000
SQL> prompt SESSION 1 - GET SID
SESSION 1 - GET SID
SQL> pause

SQL> SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
44

SQL> prompt SESSION 1 - UPDATE PRICE FOR PRODUCT A1 --> PRICE = PRICE+10
SESSION 1 - UPDATE PRICE FOR PRODUCT A1 --> PRICE = PRICE+10
SQL> pause

SQL> select price from products where product='A1';

     PRICE
----------
      1629

SQL> update products set price = price+10 where product= 'A1';

1 row updated.

SQL> select price from products where product='A1';

     PRICE
----------
      1629

SQL>

We stop the capture and we check the capture logs:

SQL> prompt STOP THE CAPTURE
STOP THE CAPTURE
SQL> pause

SQL> exec dbms_sql_firewall.stop_capture('LFE');

PL/SQL procedure successfully completed.

SQL>
SQL> prompt CHECK CAPTURE LOGS
CHECK CAPTURE LOGS
SQL> pause

SQL> set linesize 150 pagesize 40
SQL> column command_type format a12
SQL> column current_user format a15
SQL> column client_program format a45
SQL> column os_user format a10
SQL> column ip_address format a10
SQL> column sql_text format a30
SQL>
SQL> select command_type,
  2  	    current_user,
  3  	    client_program,
  4  	    os_user,
  5  	    ip_address,
  6  	    sql_text
  7  from   dba_sql_firewall_capture_logs
  8  where  username = 'LFE';

COMMAND_TYPE CURRENT_USER CLIENT_PROGRAM OS_USER    IP_ADDRESS SQL_TEXT
------------ --------------- --------------------------------------------- --
SELECT	     LFE	     sqlplus@db23c (TNS V1-V3) oracle     127.0.0.1  SELECT SYS_CONTEXT (:"SYS_B_0",:"SYS_B_1") FROM DUAL

SELECT	     LFE	     sqlplus@db23c (TNS V1-V3) oracle     127.0.0.1  SELECT PRICE FROM PRODUCTS WHERE PRODUCT=:"SYS_B_0"

UPDATE	     LFE	     sqlplus@db23c (TNS V1-V3) oracle     127.0.0.1  UPDATE PRODUCTS SET PRICE=PRICE +:"SYS_B_0" WHERE PRODUCT=:"SYS_B_1"

Now we generate an allow list based on the capture logs:

SQL> exec dbms_sql_firewall.generate_allow_list ('LFE');

PL/SQL procedure successfully completed.

SQL>
SQL> prompt CHECK THE ALLOW LIST AND NEW DATA DICTIONARY VIEWS
CHECK THE ALLOW LIST AND NEW DATA DICTIONARY VIEWS
SQL> pause

SQL> column username format a20
SQL> select * from  dba_sql_firewall_allowed_ip_addr where username = 'LFE';

USERNAME	     IP_ADDRESS
-------------------- ----------
LFE		     127.0.0.1

SQL>
SQL> column os_program format a50
SQL> select * from dba_sql_firewall_allowed_os_prog where username = 'LFE';

USERNAME	     OS_PROGRAM
-------------------- --------------------------------------------------
LFE		     sqlplus@db23c (TNS V1-V3)

SQL>
SQL> column os_user format a10
SQL> select * from dba_sql_firewall_allowed_os_user where username = 'LFE';

USERNAME	     OS_USER
-------------------- ----------
LFE		     oracle

SQL>
SQL> column sql_text format A50
SQL> select current_user,sql_text from dba_sql_firewall_allowed_sql where username = 'LFE';

CURRENT_USER	SQL_TEXT
--------------- --------------------------------------------------
LFE		UPDATE PRODUCTS SET PRICE=PRICE +:"SYS_B_0" WHERE
		PRODUCT=:"SYS_B_1"

LFE		SELECT PRICE FROM PRODUCTS WHERE PRODUCT=:"SYS_B_0
		"

LFE		SELECT SYS_CONTEXT (:"SYS_B_0",:"SYS_B_1") FROM DU
		AL

The allow list stores the sql statement captures plus his context (IP ADDRESS, OS_PROGRAM, OS_USER).

We check this allow list and decides which sql statement / context authorized or un-authorized to be executed and we enable the allow list, we use the option “dbms_sql_firewall.enforfe_all” to authorize all : the sql statement plus the context. There are different list of options.

SQL> begin
  2    dbms_sql_firewall.enable_allow_list (
  3  	 username => 'LFE',
  4  	 enforce  => dbms_sql_firewall.enforce_all,
  5  	 block	  => true);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select username,
    	    status,
    	    top_level_only,
    	    enforce,
    	    block
    from   dba_sql_firewall_allow_lists
    where username='LFE';

USERNAME	     STATUS   TOP_LEVEL_ONLY ENFORCE	     BLOCK
-------------------- -------- -------------- --------------- --------------
LFE		     ENABLED  N 	     ENFORCE_ALL     Y

Now, SQL which are in the allow list are authorized:

SQL> select price from products where product='A1';

     PRICE
----------
      1629

But SQL which are not in the allow list are un-authorized:

SQL> select price,product from products where product='A1';
select price,product from products where product='A1'
                          *
ERROR at line 1:
ORA-47605: SQL Firewall violation

We check the sql firewall violation log table:

select sql_text,
           firewall_action,
           ip_address,
           cause,
           occurred_at
    from   dba_sql_firewall_violations
    where  username = 'LFE';

SQL_TEXT
--------------------------------------------------------------------------------
FIREWAL IP_ADDRESS					 CAUSE
------- ------------------------------------------------ --------------------
OCCURRED_AT
---------------------------------------------------------------------------
SELECT PRICE,PRODUCT FROM PRODUCTS WHERE PRODUCT=:"SYS_B_0"
Blocked 127.0.0.1					 SQL violation
27-SEP-23 06.30.31.875532 PM +00:00


SQL>