Pete Finnigan's Oracle Security Weblog

I spoke at the UKOUG conference just over a week ago twice and the second of my talks was about the new SQL Firewall in Oracle 23c. This was a walk through of the SQL Firewall and what it does and a live demos.

The talk went through enabling the SQL Firewall, setting up sample schemas and data to test with and also creation of a SQL Firewall admin user. We then set up a capture and did some work to let the SQL Firewall learn what is good SQL and PL/SQL. We stopped the capture and converted it into an allow and enabled it. We can then do the same work and show that the SQL firewall allows the work. I then started to demonstrate things that were not learned; such as actions not allowed by the firewall, actions not allowed due to permissions and also things like accessing data via views, synonyms and also proxy access. We finished by accessing data via SQL Injection that worked and then direct access to the same data without SQL Injection. I covered management, tweaking, reporting, alerting and audit trails. I finished with clearing down and turning off everything.

I have just posted the slides for Protect your database with SQL Firewall in 23c to our website and also updated our Oracle security white papers page to include a link to this new presentation.

This talk included a lot of demos and I have also posted all of the scripts for these demos to our website and these are listed here (each script is referenced in the slides where they are used):

• sf_status.sql : This script shows the status of all of the SQL Firewall components


• sf_dis.sql : This script removes and disables all of the components of the SQL Firewall


• sf_create_users.sql : This script creates the sample schema and connect user and data


• sf_create_sf.sql : This script creates the SQL Firewall admin user


• sf_capture.sql : This script creates the capture and starts it


• sf_enable.sql : This script enables the SQL Firewall


• sf_log.sql : This script shows the capture logs


• sf_stop.sql : This script turns off the capture


• sf_se_log.sql : This script reads the session logs


• sf_allow.sql : This script creates the allow and enables it


• sf_run.sql : This script tries banned actions by the SQL Firewall and permissions


• sf_run_vm.sql : This script runs the allowed SQL and PL/SQL


• sf_hack.sql : This script runs some hacks in the database to test the SQL Firewall


• sf_hack1.sql : This script runs more hacks in the database to test the SQL Firewall


• sf_vio.sql : This script reports on the violations captured


• sf_syn.sql : This script tests the use of


• sf_view.sql : This script tests the access of data via a view


• sf_desc.sql : This script


• sf_drop_users.sql : This script drops the sample schema and access user


• sf_proxy.sql : This script tests access to the connection user via proxy to test the SQL Firewall behaviour

Please have a look at the pdf or my MS PPT slides and also have a look at the scripts.

#oracleace #sym_42 #ukoug #ukougconference23 #ukoug23 #ukougconf23

I went down to Reading, UK last week on the train from York and presented at the conference being held at Oracles offices there in Reading. This is a short post to raise that i have posted a pdf of my slides to our website. The talk is "Using Database Vault in Real Life" and I have also updated our Oracle security white papers page.

This was a good talk and the focus was on securing data in the database and not just a checklist based approach. I covered the core areas to look at to secure and then placed context based security on top of that. Then showed where DV fits. I also showed hacking of my database and what happens when we deploy DB out of the box and also with a realm and even a mandatory realm. We also showed examples of achieving some of DV without DV; i.e. good practice and simple coding. The other key message is that Database Vault itself is an application and its use must be designed and planned and of course Database Vault itself must also be secured in the core database. The main message though, if you want to use DV in your database is:

• Secure the core database first


• Achieve some of the methods/features of DV first, i.e. stop using SYS, SYSTEM, DBA, %ANY%


• Design your DV implementation first to be as simple and consistent as possible


• Implement DV


• Secure DV itself


• Monitor DV and check its meta data against your security designs

Have a look at the slides which are just released to our site

#oracleace #23c #oracle #database #security #databasevault #dv #UKOUG #UKOUGConference23 #UKOUG23 #UKOUGConf23 #OracleExperts

Today the 15th November 2023 is the first day of the UKOUG annual conference this year held in Reading at Oracles office. The event is two days continuing into tomorrow. The event agenda is here.

I am going to be speaking twice tomorrow; My first talk is Oracle Database Vault in the real world and my second is Protect your database with SQL Firewall in 23c

The Database Vault talk is pitched at how and when DV should be used with some ideas on some elements of this. I first talk about securing Oracle and the main steps needed; then look at what we can do for free and then look at the main features of DV. I then hack my database without DV, with DV "Out of the Box" OOTB, then with a realm around the main components of the application. We look at the effects of DV on these hack attempts and then also look at adding a mandatory realm around my applications main components. We also look at example command rules and compare to a trigger based version. The thrust of the story is that DV is a great product BUT it should be used on a database that already has security enabled and it should not be duct tape on an existing non-secured database. Also DV should itself be secured and its implementation be designed not OOTB or random.

The SQL Firewall in 23c is the subject of my second talk. I cover the set up of sample data, a connect users and a SQL Firewall admin user. Then I walk through the set up and teaching of the SQL Firewall so that it knows what to block. We also then set up the allow list and then go on and demonstrate its use with normal business use and also use of no authorised SQL and PL/SQL. We also hack the application with SQL Injection. We go on to then test further features of the SQL Firewall and finalise. This is a talk with a lot of demos being run. I do the setup, learning and hacking live.

I will post both sets of slides next week.

Hope to see you in Reading in person at my talk

#oracleace #oracle #database #security #sql #firewall #databasevault #dv #23c

In the last post we discussed the "set errorlogging on" SQL*Plus setting and the fact that we can direct errors in SQL to a log table so that errors that are in long running scripts or scripts run blind can be caught and reviewed.

In the last post we did our tests as a user ORABLOG and the log table SPERRORLOG is there in the ORABLOG schema. As it is in this schema how did Oracle know we needed this log table?

The table gets created first time we use SET ERRORLOGGING ON so each schema has its own table. Can we prove this?

First connect as SYS to create a sample user with just CREATE SESSION:

C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 3 14:20:18 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL>

We are connected to 23c Free and using an 11.2.0.4 client as thats what I happen to have around but its fine as errorlogging was added in Oracle 11.1 so the client supports it. Next create a sample user with just CREATE SESSION:

SQL> create user test33 identified by test33;

User created.

SQL> grant create session to test33;

Grant succeeded.

SQL>

Check what, if any SPERRORLOG tables exist:

SQL> col owner for a15
SQL> col object_name for a10
SQL> col object_type for a10
SQL> set lines 220
SQL> select owner,object_name,object_type from dba_objects where object_name='SPERRORLOG';

OWNER           OBJECT_NAM OBJECT_TYP
--------------- ---------- ----------
ORABLOG         SPERRORLOG TABLE

SQL>

So, no table exists for the user TEST33. What if we log in as TEST33, is the table created? Connect as TEST33 and then back as SYS and check

SQL> connect test33/test33@//192.168.56.18:1521/freepdb1
Connected.
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> col owner for a15
SQL> col object_name for a10
SQL> col object_type for a10
SQL> set lines 220
SQL> select owner,object_name,object_type from dba_objects where object_name='SPERRORLOG';

OWNER           OBJECT_NAM OBJECT_TYP
--------------- ---------- ----------
ORABLOG         SPERRORLOG TABLE

SQL>

No, connecting didnt create the log table. The next test is to connect as TEST33 and enable logging, log back as SYS and check:

SQL> connect test33/test33@//192.168.56.18:1521/freepdb1
Connected.
SQL> set errorlogging on
ERROR:
ORA-01031: insufficient privileges
ERROR:
ORA-00942: table or view does not exist


SP2-1518: Errorlogging table SPERRORLOG does not exist in schema


SP2-1507: Errorlogging table, role or privilege is missing or not accessible
SQL>
SQL> desc sperrorlog
ERROR:
ORA-04043: Object sperrorlog does not exist.

Interesting, very interesting. Why was there no error for ORABLOG? Lets check out ORABLOG rights:

C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 3 15:00: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> @find_all_privs



find_all_privs: Release 1.0.7.0.0 - Production on Fri Nov 03 15:00:06 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF USER TO CHECK                 [ORCL]: ORABLOG
OUTPUT METHOD Screen/File                [S]:
FILE NAME FOR OUTPUT              [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]:

User => ORABLOG has been granted the following privileges
====================================================================
        SYS PRIV => CREATE PROCEDURE grantable => NO
        SYS PRIV => CREATE SESSION grantable => NO
        SYS PRIV => CREATE TABLE grantable => NO
        SYS PRIV => UNLIMITED TABLESPACE grantable => NO

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

The clues were there in the error messages; The user who wants to use error logging needs CREATE TABLE privilege. This is good as otherwise the creation of the table would have to be done by another user and we dont have one in this context. Lets add CREATE TABLE to our user TEST33 and try again:

SQL> sho user
USER is "SYS"
SQL> grant create table to test33;

Grant succeeded.

SQL>
SQL> col owner for a15
SQL> col object_name for a10
SQL> col object_type for a10
SQL> set lines 220
SQL> select owner,object_name,object_type from dba_objects where object_name='SPERRORLOG';

OWNER           OBJECT_NAM OBJECT_TYP
--------------- ---------- ----------
ORABLOG         SPERRORLOG TABLE

1 row selected.

SQL>

Now connect to TEST33 and try and set errorlogging again

SQL> connect test33/test33@//192.168.56.18:1521/freepdb1
Connected.
SQL> set errorlogging on
SQL> desc sperrorlog
 Name                                                                                                                          Null?    Type
 ----------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------
 USERNAME                                                                                                                               VARCHAR2(256)
 TIMESTAMP                                                                                                                              TIMESTAMP(6)
 SCRIPT                                                                                                                                 CLOB
 IDENTIFIER                                                                                                                             VARCHAR2(256)
 MESSAGE                                                                                                                                CLOB
 STATEMENT                                                                                                                              CLOB

SQL>

So, for completeness connect as SYS and check the SPERRORLOG tables

SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> col owner for a15
SQL> col object_name for a10
SQL> col object_type for a10
SQL> set lines 220
SQL> select owner,object_name,object_type from dba_objects where object_name='SPERRORLOG';

OWNER           OBJECT_NAM OBJECT_TYP
--------------- ---------- ----------
ORABLOG         SPERRORLOG TABLE
TEST33          SPERRORLOG TABLE

2 rows selected.

SQL>

The security is a little askew as we need to grant CREATE TABLE to any users where we want to log any errors. This is a privilege that is potentially not needed after the creation of the table. There are two options.

1. We could grant CREATE TABLE, run SET ERRORLOGGING ON once and then revoke CREATE TABLE from the user


2. We could pre-create the table for the user from a DBA and then run SET ERRORLOGGING ON

We could create a table with a different name and access this with SET ERRORLOGGON ON TABLE {my_table_name} - For this option we need to create the table with the same structure as SPERRORLOG.

For security we must pre-create the SPERRORLOG table or use a different name or REVOKE CREATE TABLE from the schema as soon as the logging has been used once. Pre-creation as a DBA is the better option as then we do not need to grant CREATE TABLE to the schema/user

There is another aspect to this; if the user has CREATE TABLE then SET ERRORLOGGING ON will just work. BUT the user would have a table he/she didnt create themselves directly. I like the idea of storing the errors for use in security auditing or forensics but I dont like the idea that a system creates objects silently for you.

#oracleace #23c #oracle #database #security #logging #error

Oracle has improved error messaging in a number of places over the years and we will discuss one of these now in this blog. Oracle added logging errors to a table in Oracle 11.1. This is a useful feature that I don't see used on sites I visit. This feature allows errors from interactive commands or scripted SQL and PL/SQL to be logged. So if something runs unattended or the screen buffer is too small you can still see the errors that were made.

First lets connect to my 23c Free database (XE) and have a look at it:

C:\>sqlplus orablog/orablog@//192.168.56.18:1521/freepdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 3 10:21:32 2023
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Fri Nov 03 2023 09:02:32 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL>

The feature is turned off by default. The error logs are written to a table called SPERRORLOG. This is created by default. The table structure is:

SQL> desc sperrorlog
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             CLOB
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

SQL>

Check if there are any error messages already in the table:

SQL> select count(*) from sperrorlog;

  COUNT(*)
----------
         0

SQL>

Where is this table and who owns it?

SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL>
SQL> select owner,object_name,object_type from dba_objects where object_name='SPERRORLOG';

OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
ORABLOG
SPERRORLOG
TABLE


1 row selected.

SQL>

So, the table is owned by ORABLOG, the user I was connected to at the start. This table is empty until we turn on error logging. Lets do that and run some errors deliberately without error logging:

SQL> connect orablog/orablog@//192.168.56.18:1521/freepdb1
Connected.
SQL>
SQL> alter user system identified by oracle;
alter user system identified by oracle
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create or replace procedure test22 is
  2  negin
  3  null;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL>

We tried to use a system privilege we do not have and also access a table that does not exist and finally try and compile a PL/SQL procedure that has bugs. Check the error log:

SQL> select count(*) from sperrorlog;

  COUNT(*)
----------
         0

SQL>

Turn on error logging:

SQL> set errorlogging on

Now run the commands again:

SQL> alter user system identified by oracle;
alter user system identified by oracle
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create or replace procedure test22 is
  2  negin
  3  null;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL>

Check the error log again:

SQL> set serveroutput on
SQL> @sc_print 'select * from sperrorlog'
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from sperrorlog','''','''''');
Executing Query [select * from sperrorlog]
USERNAME                      : ORABLOG
TIMESTAMP                     : 03-NOV-23 11.50.36.000000 AM
SCRIPT                        :
IDENTIFIER                    :
MESSAGE                       : ORA-01031: insufficient privileges
STATEMENT                     : alter user system
-------------------------------------------
USERNAME                      : ORABLOG
TIMESTAMP                     : 03-NOV-23 11.50.37.000000 AM
SCRIPT                        :
IDENTIFIER                    :
MESSAGE                       : ORA-00942: table or view does not exist
STATEMENT                     : select * from scott.emp
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

This is interesting as the PL/SQL error is not included in the error log. BUT, we can see the compile time errors at the time by running "sho err" and listing the source to compare the line number in the error and the original source. We can also see the PL/SQL error as follows:

SQL> @sc_print 'select * from user_errors'
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from user_errors','''','''''');
Executing Query [select * from user_errors]
NAME                          : TEST22
TYPE                          : PROCEDURE
SEQUENCE                      : 1
LINE                          : 3
POSITION                      : 1
TEXT                          : PLS-00103: Encountered the symbol "NULL" when
expecting one of the following:

   constant exception 
   double-quoted delimited-identifier> table columns long
   double ref char
standard time timestamp interval date binary
   national character nchar
ora_property_graph
   ora_vertex_input_property ora_vertex_output_property

ora_edge_input_property ora_edge_output_property

ATTRIBUTE                     : ERROR
MESSAGE_NUMBER                : 103
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

So, we can log errors from SQL*Plus from DDL and DML and also PL/SQL. Good. We can also set the identifier in the session or in a script so that this is also recorded in SPERRORLOG so that we can tell where errors come from. So lets set an identifier and run an error again:

SQL> set errorlogging on identifier pete_id
SQL> alter user system identified by oracle;
alter user system identified by oracle
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select count(*) from sperrorlog;

  COUNT(*)
----------
         1

SQL> set serveroutput on
SQL> @sc_print 'select * from sperrorlog'
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from sperrorlog','''','''''');
Executing Query [select * from sperrorlog]
USERNAME                      : ORABLOG
TIMESTAMP                     : 03-NOV-23 12.11.36.000000 PM
SCRIPT                        :
IDENTIFIER                    : pete_id
MESSAGE                       : ORA-01031: insufficient privileges
STATEMENT                     : alter user system
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Great, we get the error message and we can see the ID that we set is also now there.

OK, I am a security person so why the interest in this error table?

It is also possible to create your own error table first and then use this in the "set errorlogging" functionality. The table must pre-exist before the command is used.

If I am doing a forensic analysis of a database I look for these tables and include the contents in the analysis; if the tables exist of course and also USER_ERRORS. It makes sense to do this for completeness. Also from a security perspective (not forensics) then we would not want this data to exist. I connected to my ORABLOG schema and if I were able to exploit my ORABLOG schema say with SQL Injection then I can read this table and also USER_ERRORS to get insight to the actions occurred in the database that may give me an insight to attack the database.

Error logging is good and useful but it should not be exposed back to the real end user who could be an attacker

#oracleace #23c #oracle #security #error #message #attack #databreach