I still have a lot of notes around the new SQL Firewall in Oracle 23c that I captured whilst testing so I was going to release a bigger part 4 but I have decided to create a simple short post here because I wanted to discuss the use of Proxy with the SQL Firewall.
Proxy has been around a long time in Oracle and I have been recommending people use proxy for admin probably for 14 - 15 years. I noted a long time ago that proxy could be used in different way or meaning to its use at the time. The general use was to provide identity in connection pooling. So in this sense lots of users piggy-backed onto a single or small group of database users to provide their identity on top of the shared connection user. The different way was to lock a key account with an impossible password. My examples to use this with were powerful accounts or schemas. The schema is a good example. Lock the schema with an impossible password (now schema only accounts do the same) and then do not allow direct access to the schema as had been done in many sites.
Now a release of new code, tables etc can be done by proxying as the release user to the schema and then running the code. For the release user he/she is the schema in all senses except for Proxy. This means scripts run as though the user were connected direct to the schema. This means that instead of audit trails on the schema where we don't know who was really connected as its a shared account we can audit release users as they have their own account and we know then "who dunnit".
So, I wanted to test proxy with the SQL Firewall in Oracle 23c. Remember in Part 1 we set up a database user VM to connect and use the ORABLOG.CUSTOMER table and the ORABLOG.CUSTA PL/SQL procedure. We used 3 simple SQL, PL/SQL calls as the learning phase in part 2 and tested these again in Part 3.
The only SQL allowed by VM are these three actions we set up. First lets create a new user VQ and allow it to proxy to VM:
C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 16 14:23:59 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> create user vq identified by vq;
User created.
SQL> grant create session to vq;
Grant succeeded.
SQL> alter user vm grant connect through vq;
User altered.
SQL>
Now we can connect to the database and VQ and proxy to VM:
SQL> connect vq[vm]/vq@//192.168.56.18:1521/freepdb1
Connected.
SQL> sho user
USER is "VM"
So we are VQ but act as VM. Now try all of the SQLs that is allowed by the SQL Firewall in my rule set up:
SQL> sho user
USER is "VM"
SQL> select * from orablog.customer;
FULLNAME FIRSTNAME
------------------------------ ------------------------------
LASTNAME
------------------------------
Pete Finnigan Pete
Finnigan
Zulia Finnigan Zulia
Finnigan
Eric Finnigan Eric
Finnigan
SQL> select count(*) from orablog.customer;
COUNT(*)
----------
3
SQL> set serveroutput on
SQL> exec orablog.custa('Finnigan');
name:=[Pete Finnigan]
name:=[Zulia Finnigan]
PL/SQL procedure successfully completed.
SQL>
They all work as expected. What if we now try something that is not allowed. Remember VM has INSERT rights on the ORABLOG.CUSTOMER table but this is not in the SQL Firewall rules:
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>
So, this works. The SQL Firewall allows the rules that were learned and applied to VM and blocks SQL that were not allowed. We are in the database though as VQ not VM although the database sees us in all respects as VM. This is what we should expect so that we can connect to a schema and do a release and the firewall rules still work.
BUT, there is a flaw. If an attacker has access to ALTER USER then he can simply allow himself to proxy to another user that has valid SQL Firewall rules to then allow him to see or change data that the SQL Firewall has blocked. The users who have ALTER USER are:
who_has_priv: Release 1.0.3.0.0 - Production on Thu Jun 22 11:05:20 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
PRIVILEGE TO CHECK [SELECT ANY TABLE]: ALTER USER
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 => ALTER USER has been granted to =>
====================================================================
User => APEX_220200 (ADM = NO)
User => ORDS_METADATA (ADM = NO)
User => HRREST (ADM = NO)
User => VF (ADM = NO)
User => SYS (ADM = NO)
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
User => SYS (ADM = YES)
User => GSMADMIN_INTERNAL (ADM = NO)
User => SYS (ADM = YES)
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
User => SYS (ADM = YES)
User => GSMADMIN_INTERNAL (ADM = NO)
Role => DV_ACCTMGR (ADM = NO) which is granted to =>
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
So any user with DBA or IMP_FULL_DATABASE or the users DV_ACCTMGR or APEX or... can simply change their own user to allow proxy access to a user with SQL Firewall rules already set up therefore bypassing the SQL Firewall. If a user had number of other %ANY% privileges they can also bypass the SQL Firewall by first attacking a user with ALTER USER and then gain access to a SQL Firewall user and then the data.
As you will know we need layered defences to secure data. Also we need to remember that to use the SQL Firewall we must protect the SQL Firewall from bypass and abuse. We must have security in depth to secure data.
So, yes its great that the SQL Firewall works when we proxy because we have reasons to proxy such as release to a schema BUT remember that proxy can also be used to piggy-back and steal access to SQL Firewall rules.
#23c #oracleace #oracle #sql #firewall #hacking
