- Part 1 - Introduction: This is this post where we introduce the SQL Firewall and a high level view of it and I will introduce my test schema and test data
- Part 2 - Detailed set up demo: We will run through a complete demo of turning on the SQL Firewall, setting up learning and turning that into rules and then enabling the rule set
- Part 3 - Finishing up In the final post in the series we will run some tests and show what the firewall does and what it captures. I will look at and discuss the results and some detailed testing and talk about some pitfalls and variations that can be made and tweaks, i.e, how to change or extend the rules
Oracle released the 23c Free Developer release and included in it is the SQL Firewall. This was also announced prior to the release. I heard it at the UKOUG conference at the end of last year at the Oracle keynote.
The new SQL Firewall is as stated by Oracle in the UKOUG keynote or perhaps I read somewhere based on the Oracle Firewall and Audit Vault technology. The firewall was an external product many years ago by a company called "Secerno" and Oracle bought it. Before Oracle bought Secerno and before it was even released as a production product I got to see it when Secernos CEO and CTO came to see us and talk about it; they brought a small hardware box with them. The product then was a hardware/software blade. At that time there were quite a few DAM/Audit/IDS/IPS type products on the market and most sniffed network traffic and some (Sentrigo Hedgehog) sniffed the SGA for SQL Statements and some captured the OCI or UPI call stack (Quest). Most had rules based on regular expressions such as does the SQL Text contain "credit_card" but Secerno was different as it didn't just sniff traffic but was based on SQL and PL/SQL syntax and was looking to capture "good" and "bad" SQL and PL/SQL so could be "trained" to block non-authorised SQL or PL/SQL.
Over the years most of the products in this space also utilised sniffing of the SGA or call stacks as well as sniffing network traffic. There are a lot less products in this space now than 15 years ago. At the time Oracle bought Secerno, I said if Oracle bought one product in this space it would be Secerno as it had a different modus-operandi and Oracle could of course easily integrate this technology into the database kernel itself. Oracle do not need to hook the UPI or OCI call stack and do not need to sniff the SGA they have access to the C code that executes SQL and PL/SQL and all other technologies such as VPD, DV, OLS, auditing, trace etc. Oracle is uniquely positioned to grab SQL and PL/SQL as it executes and inspect it for violations such as access, program, SQL Injection,
I talked about the Secerno product and when it was released as the subsequent Oracle firewall back in 2011.
So, to finish up part 1 i am going to discuss my simple test data that i will connect to and use with the SQL Firewall. We will introduce some tweaks to this schema, tables, code later on. I will explain why when we do it.
First we create a schema ORABLOG and add a simple table and procedure and an additional table. We will create an access user and some grants to allow that user to access the PL/SQL code and data; then we will test it with no firewall at all
First create the schema:
C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 12 14:44:31 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 orablog identified by orablog;
User created.
SQL> grant create session, create table, create procedure to orablog;
Grant succeeded.
SQL> grant unlimited tablespace to orablog;
Grant succeeded.
SQL>
Now create a couple of tables and a procedure:
SQL> connect orablog/orablog@//192.168.56.18:1521/freepdb1
Connected.
SQL>
SQL> create table credit_card
2 (
3 name_on_card varchar2(100),
4 first_name varchar2(50),
5 last_name varchar2(50),
6 PAN raw(100)
7 )
8 /
Table created.
SQL>
SQL> insert into credit_card
2 (name_on_card,first_name,last_name,pan)
3 values('Pete Finnigan','Pete','Finnigan','4049877198543457');
1 row created.
SQL> insert into credit_card
2 (name_on_card,first_name,last_name,pan)
3 values('Zulia Finnigan','Zulia','Finnigan','3742345698766678');
1 row created.
SQL> commit;
Commit complete.
SQL> create table customer (fullname varchar2(30),firstname varchar2(30), lastname varchar2(30))
2 /
Table created.
SQL> insert into customer (fullname,firstname,lastname) values ('Pete Finnigan','Pete','Finnigan');
1 row created.
SQL> insert into customer (fullname,firstname,lastname) values ('Zulia Finnigan','Zulia','Finnigan');
1 row created.
SQL> commit;
Commit complete.
SQL>
Now create a simple PL/SQL procedure:
SQL> create or replace procedure custa(pv_name in varchar2) is
2 lv_stmt varchar2(2000);
3 type c_ref is ref cursor;
4 c c_ref;
5 name credit_card.name_on_card%type;
6 Begin
7 lv_stmt:='select name_on_card from credit_card '||
8 'where last_name = '''||pv_name||'''';
9 open c for lv_stmt;
10 loop
11 fetch c into name;
12 if(c%notfound) then
13 exit;
14 end if;
15 dbms_output.put_line('name:=['||name||']');
16
17 end loop;
18 close c;
19 end;
20 /
Procedure created.
SQL>
Now connect as SYS again and create an access user VM and give it permission to read and insert into ORABLOG.CUSTOMER and use the PL/SQL but not access the ORABLOG.CREDIT_CARD table
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> create user vm identified by vm;
User created.
SQL> grant create session to vm;
Grant succeeded.
SQL> grant select, insert on orablog.customer to vm;
Grant succeeded.
SQL> grant execute on orablog.custa to vm;
Grant succeeded.
SQL>
Finally test the user VM can read the ORABLOG.CUSTOMER table and use the ORABLOG.CUSTA procedure:
SQL> connect vm/vm@//192.168.56.18:1521/freepdb1
Connected.
SQL> set serveroutput on
SQL> select * from orablog.customer;
FULLNAME FIRSTNAME
------------------------------ ------------------------------
LASTNAME
------------------------------
Pete Finnigan Pete
Finnigan
Zulia Finnigan Zulia
Finnigan
SQL> insert into orablog.customer(fullname,firstname,lastname) values ('Eric Finnigan','Eric','Finnigan');
1 row created.
SQL> commit;
Commit complete.
SQL> exec orablog.custa('Finnigan');
name:=[Pete Finnigan]
name:=[Zulia Finnigan]
PL/SQL procedure successfully completed.
SQL>
OK, that seems to work and we can therefore use this user VM and his access to the application code and table to test the SQL Firewall in part 3 after we set it up and get it to learn some allowed actions in Part 2.
Some final points.
The SQL Firewall is obviously available in FREE 23c Developer release BUT I don't know the licensing for it in production 23c databases such as EE or SE when they are released. In the past security options such as Database Vault are available only in EE and not SE even though available in XE 21c for instance. Virtual Private Database (VPD) is available in XE 21c and in EE free but not available in SE. So, I don't know licensing but I assume that the SQL Firewall is either going to be free in EE or a cost option and not available in SE. We have to wait to see.
Why use the SQL Firewall? It seems like an after thought.
Should we not secure and harden the database, design the application permissions correctly and set up audit trails and ensure that we use secure coding techniques in our PL/SQL and Java; we can also use Database Vault and VPD and more to protect data. If we did all of these things then running the SQL Firewall should not be necessary as we secured the data; right?
Well in the real world we should design and secure our data BUT we cannot control everything and if its a third party application then maybe we cannot change and secure the permissions and the code. Running the firewall in non-blocking means it creates a log of violations and blocks non-authorised actions if we run them. The SQL Firewall is a final layer to protect against the things we cannot change and to capture missing vectors; so yes we should secure the database and the data but we should also use the firewall if we can.
In the next part we will enable the SQL Firewall, create a capture, do some work, create the access list and then enable it ready for testing and tweaking in part 3
#23c #dbsec #oracleace #oracle #security #sql #firewall #plsql #datasecurity
