Skip to content

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:

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)
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>

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Oracle Team

Oracle Team

Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.View Author posts

1,009 posts since December 2010

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *