After I posted part 1, Jared asked a question on Facebook; he asked me to describe what the SQL Firwall is in one line. I said
The SQL Firewall blocks non authorised SQL and PL/SQL
The SQL Firewall is embedded inside the database engine so that it can operate on all SQL and PL/SQL statements issued. This is powerful as it can be then set up and used to block statements that are not intended or to catch things like SQL Injection. The SQL Firewall can also be used to simply log violations of the rules that have been set up in the tool.
The main principal of the SQL firewall is to set up a capture and then turn that capture on and then do some work; turn it off and generate the rules list of SQL that is allowed; enable the list and then use the SQL Firewall. In this part we are going to do the set up and learning and in the last part we will test it and see what it does for us. As I said in part 1 we can achieve some of the same results with good database security, good application security design, secure coding and audit. The SQL Firewall is the last layer in a good layered Oracle security design.
The approach of course has similarities in the workflow to the Privilege Analysis feature of the Oracle database.
The first step is to create a user to administer the SQL Firwall and grant some permissions. So we need to create an admin user that has the system privilege ADMINISTER SQL FIRWALL, has execute privilege on the DBMS_SQL_FIREWALL package and has access to the various DBA_SQL_FIREWALL_% views. In a production system all of these rights must be not given out and must be guarded to prevent anyone understanding the SQL Firewall settings and rules and to prevent any user from looking at the output of the SQL Firewall and to prevent anyone from changing the firewall settings - i.e. turn it off
In a production set up there would be two groups of SQL Firwall users - Fireall Admin and Firewall Reporting/Viewer. We are not going to do that here as its a simple demo
Lets look in 23c who already has ADMINISTER SQL FIREWALL:
who_has_priv: Release 1.0.3.0.0 - Production on Wed Jun 14 09:16:05 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
PRIVILEGE TO CHECK [SELECT ANY TABLE]: ADMINISTER SQL FIREWALL
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXCLUDE CERTAIN USERS [N]:
USER TO SKIP [TEST%]:
Privilege => ADMINISTER SQL FIREWALL has been granted to =>
====================================================================
User => SYS (ADM = NO)
Role => SQL_FIREWALL_ADMIN (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
So only SYS but interestingly SYS has been granted the right twice. This is normal in Oracle databases to see duplication of grants. Lets make sure there are no other SYSTEM PRIVILEGES for the SQL Firewall. Obviously the role SQL_FIREWALL_ADMIN has been created by SYS and by default in Oracle if you create a role then you get granted that role by default.
SQL> select * from system_privilege_map where name like '%FIR%';
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-418 ADMINISTER SQL FIREWALL 0
1 row selected.
SQL>
Next lets see if any users have the two SQL firewall roles:
SQL> select role from dba_roles where role like '%SQL_FIRE%';
ROLE
--------------------------------------------------------------------------------
SQL_FIREWALL_ADMIN
SQL_FIREWALL_VIEWER
2 rows selected.
SQL>
There are just two roles. What permissions does each have:
SQL> @find_all_privs
find_all_privs: Release 1.0.7.0.0 - Production on Wed Jun 14 09:47:50 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: SQL_FIREWALL_ADMIN
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
User => SQL_FIREWALL_ADMIN has been granted the following privileges
====================================================================
ROLE => SQL_FIREWALL_VIEWER which contains =>
TABLE PRIV => READ object => SYS.CDB_SQL_FIREWALL_STATUS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_ALLOWED_IP_ADDR grantable =>
NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_ALLOWED_OS_PROG grantable =>
NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_ALLOWED_OS_USER grantable =>
NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_ALLOWED_SQL grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_ALLOW_LISTS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_CAPTURES grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_CAPTURE_LOGS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_SESSION_LOGS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_SQL_LOGS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_STATUS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_VIOLATIONS grantable => NO
SYS PRIV => ADMINISTER SQL FIREWALL grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_SQL_FIREWALL grantable => NO
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
SQL> @find_all_privs
find_all_privs: Release 1.0.7.0.0 - Production on Wed Jun 14 09:48:36 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: SQL_FIREWALL_VIEWER
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
User => SQL_FIREWALL_VIEWER has been granted the following privileges
====================================================================
TABLE PRIV => READ object => SYS.CDB_SQL_FIREWALL_STATUS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_ALLOWED_IP_ADDR grantable =>
NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_ALLOWED_OS_PROG grantable =>
NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_ALLOWED_OS_USER grantable =>
NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_ALLOWED_SQL grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_ALLOW_LISTS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_CAPTURES grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_CAPTURE_LOGS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_SESSION_LOGS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_SQL_LOGS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_STATUS grantable => NO
TABLE PRIV => READ object => SYS.DBA_SQL_FIREWALL_VIOLATIONS grantable => NO
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
Interesting; the SQL_FIREWALL_ADMIN role has everything we need, the system grant, the package and also access to all of the views needed and the SQL_FIREWALL_VIEWER only has access to the views. Which users by default have access to these roles:
SQL> @who_has_role
Enter value for role_to_find: SQL_FIREWALL_ADMIN
Investigating Role => SQL_FIREWALL_ADMIN (PWD = NO) which is granted to =>
====================================================================
User => SYS (ADM = YES)
PL/SQL procedure successfully completed.
SQL> @who_has_role
Enter value for role_to_find: SQL_FIREWALL_VIEWER
Investigating Role => SQL_FIREWALL_VIEWER (PWD = NO) which is granted to =>
====================================================================
User => SYS (ADM = YES)
Role => SQL_FIREWALL_ADMIN (ADM = YES|PWD = NO) which is granted to =>
User => SYS (ADM = YES)
PL/SQL procedure successfully completed.
SQL>
Just SYS, good by default so that the access is not spread. What about default access to the admin PL/SQL package:
who_can_access: Release 1.0.3.0.0 - Production on Wed Jun 14 09:55:29 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF OBJECT TO CHECK [USER_OBJECTS]: DBMS_SQL_FIREWALL
OWNER OF THE OBJECT TO CHECK [USER]: SYS
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXCLUDE CERTAIN USERS [N]:
USER TO SKIP [TEST%]:
Checking object => SYS.DBMS_SQL_FIREWALL
====================================================================
Object type is => PACKAGE (TAB)
Privilege => EXECUTE is granted to =>
Role => SQL_FIREWALL_ADMIN (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
By default obviously SYS through the object owner principal as it owns the package but only one grant to SQL_FIREWALL_ADMIN and that is granted to SYS by default. OK, that gives a good overview of the roles, PL/SQL Admin and views. As i said in production we would create separation of duties and not use SYSDBA.
In this simple example I am going to set up a user with the SQL_FIREWALL_ADMIN role and use that:
SQL> sho user
USER is "SYS"
SQL> create user sql_f identified by sql_f;
User created.
SQL> grant create session,sql_firewall_admin to sql_f;
Grant succeeded.
SQL>
Connect to this SQL Firewall admin user and enable it and then check the status:
SQL> connect sql_f/sql_f@//192.168.56.18:1521/freepdb1
Connected.
SQL> exec dbms_sql_firewall.enable;
PL/SQL procedure successfully completed.
SQL>
SQL> select status,to_char(status_updated_on,'DD-MON-YY HH24:MI:SS'),to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dba_sql_firewall_status;
STATUS TO_CHAR(STATUS_UPDATED_ON,' TO_CHAR(SYSDATE,'DD-MON-YYH
-------- --------------------------- ---------------------------
ENABLED 14-JUN-23 10:51:37 14-JUN-23 10:56:34
1 row selected.
SQL>
We have enabled the SQL Firewall so now we must train the firewall to allow good SQL and to not allow bad SQL. To do this we must create a CAPTURE and then do work to allow the SQL Firewall to learn. We are going to monitor the user VM so we must specify this:
SQL> exec dbms_sql_firewall.create_capture('VM',true,true);
PL/SQL procedure successfully completed.
SQL>
The capture is created and turned on. We decided to only capture top level statements at this stage. This means we only capture the statements executed by the user and not those issued by PL/SQL on its behalf.
Now we can check the capture status from the view:
SQL> col username for a10
SQL> col top_level_only for a3
SQL> col status for a8
SQL> col last_started_on for a15
SQL> col last_stopped_on for a15
SQL> set lines 220
SQL> select username, top_level_only,status,to_char(last_started_on,'DD-MON-YY HH24:MI:SS'),to_char(last_stopped_on,'DD-MON-YY HH24:MI:SS') from dba_sql_firewall_captures;
USERNAME TOP STATUS TO_CHAR(LAST_STARTED_ON,'DD TO_CHAR(LAST_STOPPED_ON,'DD
---------- --- -------- --------------------------- ---------------------------
VM Y ENABLED 14-JUN-23 11:50:32
1 row selected.
SQL>
As we can see it is turned on and the capture has started. Now lets connect as the sample user VM and do a small number of things that we will allow; think of doing this during a UAT session or a specific SQL Firewall training session where all Good SQL should be run. This way we would capture ever authorised statement and be able to then use the SQL Firewall to block other statements. As I said we will do a very small number of statements as VM just to show the principal:
SQL> connect vm/vm@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from orablog.customer;
COUNT(*)
----------
3
1 row selected.
SQL> select * from orablog.customer;
FULLNAME FIRSTNAME LASTNAME
------------------------------ ------------------------------ ------------------------------
Pete Finnigan Pete Finnigan
Zulia Finnigan Zulia Finnigan
Eric Finnigan Eric Finnigan
3 rows selected.
SQL> set serveroutput on
SQL> exec orablog.custa('Finnigan');
name:=[Pete Finnigan]
name:=[Zulia Finnigan]
PL/SQL procedure successfully completed.
SQL>
We kept it simple but keep in mind these are the things we deem as Good SQL and anything else is not Good SQL. So we did SELECT statements on the ORABLOG.CUSTOMERS table which we are granted SELECT on as VM and also executed the PL/SQL procedure ORABLOG.CUSTA. We didnt try an INSERT on ORABLOG.CUSTOMER deliberately. We as the user VM have INSERT rights but we want in this simple test to mark INSERT as an invalid SQL for the firewall. We also didnt try and access the ORABLOG.CREDIT_CARD table as we dont have any permissions on that table.
This is trying to reflect a little of real life. We may have a system where a user has permissions but he is not supposed to use them; i.e. we have as VM SELECT and INSERT on ORABLOG.CUSTOMER (maybe we cannto remove the INSERT grant as we don't own the application?) BUT INSERT is not normally done as VM therefore the SQL Firewall could block this. Conversly we do not have UPDATE on ORABLOG.CUSTOMER so we need to check in Part 3 testing does the Firewall come into play blocking an UPDATE or does the lack of permission work first?
We can check the capure logs to see whats been captured and decide whether more work is needed:
SQL> connect sql_f/sql_f@//192.168.56.18:1521/freepdb1
Connected.
SQL>
SQL> get cap.sql
1 -- cap.sql - get the capture logs
2 col username for a10
3 col session_id for 99999999
4 col command_type for a8
5 col sql_signature for a65
6 col sql_text for a90
7 col accessed_objects for a30
8 col current_user for a10
9 col top_level for a3
10 col client_program for a12
11 col os_user for a8
12 col ip_address for a12
13 set lines 220
14 select username,
15 command_type,
16 sql_text,
17 accessed_objects,
18 current_user,
19 top_level,
20 client_program,
21 os_user,
22 ip_address
23* from dba_sql_firewall_capture_logs
24 .
SQL> @cap
USERNAME COMMAND_ SQL_TEXT ACCESSED_OBJECTS CURRENT_US TOP CLIENT_PROGR OS_USER IP_ADDRESS
---------- -------- ------------------------------------------------------------------------------------------ ------------------------------ ---------- --- ------------ -------- ------------
VM SELECT SELECT COUNT (*) FROM ORABLOG.CUSTOMER "ORABLOG"."CUSTOMER" VM Y sqlplus.exe Pete 192.168.56.1
VM EXECUTE BEGIN DBMS_OUTPUT.GET_LINES (:LINES,:NUMLINES); END; "SYS"."DBMS_OUTPUT" VM Y sqlplus.exe Pete 192.168.56.1
VM EXECUTE BEGIN ORABLOG.CUSTA (?); END; "ORABLOG"."CUSTA" VM Y sqlplus.exe Pete 192.168.56.1
VM SELECT SELECT * FROM ORABLOG.CUSTOMER "ORABLOG"."CUSTOMER" VM Y sqlplus.exe Pete 192.168.56.1
VM EXECUTE BEGIN DBMS_OUTPUT.ENABLE (NULL); END; "SYS"."DBMS_OUTPUT" VM Y sqlplus.exe Pete 192.168.56.1
VM SELECT SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL "SYS"."DUAL" VM Y sqlplus.exe Pete 192.168.56.1
VM EXECUTE BEGIN DBMS_APPLICATION_INFO.SET_MODULE (:1,NULL); END; "SYS"."DBMS_APPLICATION_INFO" VM Y sqlplus.exe Pete 192.168.56.1
7 rows selected.
SQL>
This is interesting as I issued a "select count(*)..." and "select *..." from customers and an "exec orablog.custa()..." but there are additional statements that I did not directly issue and I have Top Level Only turned on. The DBMS_APPLICATION_INFO and DBMS_OUTPUT.ENABLE and DBMS_OUTPUT.PUTLINE come from the tool i used (SQL*Plus). I am sure of the DBMS_OUTPUT and reasonably sure of the DBMS_APPLICATION_INFO. We can check this by opening SQL*Plus in a binary editor and see this is true:
PS C:\_aa\Oracle_client19c> Format-Hex sqlplus.exe | select-string a37d0
000A37D0 01 00 00 00 00 00 00 00 42 45 47 49 4E 20 44 42 ........BEGIN DB
PS C:\_aa\Oracle_client19c> Format-Hex sqlplus.exe | select-string a37e0
000A37E0 4D 53 5F 41 50 50 4C 49 43 41 54 49 4F 4E 5F 49 MS_APPLICATION_I
PS C:\_aa\Oracle_client19c> Format-Hex sqlplus.exe | select-string a37f0
000A37F0 4E 46 4F 2E 53 45 54 5F 4D 4F 44 55 4C 45 28 3A NFO.SET_MODULE(:
Bit of a hack tp get three lines of the binary SQL*Plus as i had to search for hex lines but you can easily see that this call came from inside SQL*Plus. We must be aware of this when testing applications that they are also likely to embed hidden SQL and checking what SQL is captured can quickly become difficult. We must check the capture log and apporve all SQL. In my case here we issued 3 commands but captured 7 commands.
These are all valid but in real systems a problem that I said to Secerno maybe 15 years ago and I talked about in this blog 12 years go is its fine to capture the SQL during the learning phase BUT what if the attacker is part of the learning/training phase and they include their hacking statements?
Its also worth noting that the IP address is the VM gateway address and the same issue would likely apply in a real production system that the IP address cannot be properly relied on. This is not my end IP address which is not 192.168.56.1 but 192.168.56.50. Also the program is just "sqlplus.exe" but the listener.log shows the complete path to the program so if its generic like sqlplus.exe we could not limit to the sqlplus.exe running in folder c:\_x\z and not c:\_x\y for instance. Also the OS user will be the user that runs the application in some cases and in others individual accounts or the account that runs apache or weblogic or...
So now stop the CAPTURE and check the capture status:
SQL> exec dbms_sql_firewall.stop_capture('VM');
PL/SQL procedure successfully completed.
SQL> get sta.sql
1 col username for a10
2 col top_level_only for a3
3 col status for a8
4 col last_started_on for a15
5 col last_stopped_on for a15
6 set lines 220
7 select username,
8 top_level_only,
9 status,to_char(last_started_on,'DD-MON-YY HH24:MI:SS'),
10 to_char(last_stopped_on,'DD-MON-YY HH24:MI:SS')
11* from dba_sql_firewall_captures;
SQL> @sta
USERNAME TOP STATUS TO_CHAR(LAST_STARTED_ON,'DD TO_CHAR(LAST_STOPPED_ON,'DD
---------- --- -------- --------------------------- ---------------------------
VM Y DISABLED 14-JUN-23 11:50:32 14-JUN-23 13:40:03
SQL>
So the status of the capture is now DISABLED and we also now have a stop time. We can also check the session logs:
SQL> get se.sql
1 -- se.sql - session logs
2 col login_time for a20
3 col username for a10
4 col client_program for a12
5 col os_user for a8
6 col ip_address for a12
7 set lines 220
8 select username,
9 to_char(login_time,'DD-MON-YY HH24:MI:SS') login_time,
10 ip_address,
11 client_program,
12 os_user
13* from dba_sql_firewall_session_logs
14 .
SQL> @se
USERNAME LOGIN_TIME IP_ADDRESS CLIENT_PROGR OS_USER
---------- -------------------- ------------ ------------ --------
VM 14-JUN-23 12:21:00 192.168.56.1 sqlplus.exe Pete
SQL>
Now we must generate the allow list:
SQL> exec dbms_sql_firewall.generate_allow_list('VM');
PL/SQL procedure successfully completed.
SQL>
Now we can check the allow list:
SQL> get al.sql
1 --al.sql - allowed list of SQL
2 col sql_text for a90
3 col accessed_objects for a30
4 col current_user for a10
5 col top_level for a3
6 col username for a10
7 col version for 9999
8 set lines 220
9 select username,
10 sql_text,
11 accessed_objects,
12 current_user,
13 top_level,
14 version
15* from dba_sql_firewall_allowed_sql
16 .
SQL> @al.sql
USERNAME SQL_TEXT ACCESSED_OBJECTS CURRENT_US TOP VERSION
---------- ------------------------------------------------------------------------------------------ ------------------------------ ---------- --- -------
VM SELECT COUNT (*) FROM ORABLOG.CUSTOMER "ORABLOG"."CUSTOMER" VM Y 1
VM BEGIN DBMS_OUTPUT.GET_LINES (:LINES,:NUMLINES); END; "SYS"."DBMS_OUTPUT" VM Y 1
VM BEGIN ORABLOG.CUSTA (?); END; "ORABLOG"."CUSTA" VM Y 1
VM SELECT * FROM ORABLOG.CUSTOMER "ORABLOG"."CUSTOMER" VM Y 1
VM BEGIN DBMS_OUTPUT.ENABLE (NULL); END; "SYS"."DBMS_OUTPUT" VM Y 1
VM SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL "SYS"."DUAL" VM Y 1
VM BEGIN DBMS_APPLICATION_INFO.SET_MODULE (:1,NULL); END; "SYS"."DBMS_APPLICATION_INFO" VM Y 1
7 rows selected.
SQL>
Now we can enable the allow list and we are set to start testing and blocking statements that should not work and are blocked by the SQL Firewall:
SQL> exec dbms_sql_firewall.enable_allow_list('VM',DBMS_SQL_FIREWALL.ENFORCE_SQL,TRUE);
PL/SQL procedure successfully completed.
SQL>
The SQL Firewall is now enabled and up and running.
Thats the end of part 2. In Part 3 of this blog series we will test the firewall and look to extend and change it and discuss some of the management
#23c #dbsec #oracleace #oracle #security #sql #firewall #plsql #datasecurity
