Pete Finnigan's Oracle Security Weblog

Happy 29th June - One day late as I was working with a customer yesterday.

This date, 29th June for me seems to be like November the 5th 1955 for Back To The Future.

I was at Selby railway station on the 29th June 1976 ( some 47 years ago) when myself and a friend noticed something dragging from an express train that was passing. This turned out to be that the end of the axle on one carriage bogie had sheared off. The frame of the bogie was dragging on the track and it ripped gouges in the wooden crossings that were across the tracks. Paul and myself went to the station master and told him what we had seen and that it was likely because of the state of the train that it would crash and kill/hurt a lot of people. He didn't want to believe us at first as we were 13 years old.

Some time was wasted whilst he looked at the large new gouges out of the wood. The station master then decided that we were right and rang to the signals and they stopped the train. It had travelled 6 miles north of Selby by then but had not reached 100 miles per hour. The train was stopped and it could not be moved until they detached the front half and sent that on to York and then detached the back half with a new Locomotive sent to get it. This had to travel backwards down the line. The broken carriage ended up next to the BOCM factory just outside of Selby. My dad worked at BOCM, Selby at the time and he took myself and my brothers to see it. I have a small polaroid photo but its not here in the office as I write this, so cannot show it today.

We were big news at the time, in all the newspapers of the day and the news TV. We were awarded a medal and British Rail sent us on two paid trips; the TV came to see us leave on one of those trips and passengers sent us letters thanking us.

Also on the 29th June 1995 (some 28 years ago) I passed my honours degree in Electronics with First class honours. My final year project was to write a real time operating system in C and Assembler. This was written from the end of 1994 and during 1995.

So, the 29th June seems to be special for me; saving lives and getting a 1st class honours degree!!

#oracleace #oracle #security #traincrash #degree #electronics

Oracle has added the ability to create read only users in 23c. This is by using a new ALTER USER or CREATE USER syntax and including the READ ONLY or READ WRITE clauses. Lets do a simple demo of this new feature. First connect to the 23c database:

SQL> sho user
USER is "SYS"
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL>

Next we can create a sample user VV and grant some permissions to do READ and WRITE:

SQL> create user vv identified by vv;

User created.

SQL> grant create session to vv;

Grant succeeded.

SQL> grant select,insert on orablog.customer to vv;

Grant succeeded.

SQL> grant execute on orablog.custa to vv;

Grant succeeded.

SQL>

Next connect to the new user and test that we can do read and write:

SQL> connect vv/vv@//192.168.56.18:1521/freepdb1
Connected.
SQL>

-- test

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> insert into orablog.customer (fullname,firstname,lastname) values ('EmilFinnigan','Emil','Finnigan');

1 row created.

SQL> set serveroutput on
SQL> exec orablog.custa('Finnigan');
name:=[Pete Finnigan]
name:=[Zulia Finnigan]

PL/SQL procedure successfully completed.

SQL>

Now we can connect to SYS and change our sample user VV to be READ ONLY:

SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> alter user vv read only;

User altered.

SQL>

For interest this setting of READ ONLY can also be done at creation time of a user:

SQL> sho user
USER is "SYS"
SQL> create user vx identified by vx read only;

User created.

SQL>

Connect as vv and redo the select statements:

SQL> connect vv/vv@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from orablog.customer;

  COUNT(*)
----------
         4

SQL> select * from orablog.customer;

FULLNAME                       FIRSTNAME
------------------------------ ------------------------------
LASTNAME
------------------------------
Pete Finnigan                  Pete
Finnigan

Zulia Finnigan                 Zulia
Finnigan

EmilFinnigan                   Emil
Finnigan


FULLNAME                       FIRSTNAME
------------------------------ ------------------------------
LASTNAME
------------------------------
Eric Finnigan                  Eric
Finnigan


SQL>

These work correctly. What if we try an insert statement that VV has the right to do via grants:

SQL> insert into orablog.customer(fullname,firstname,lastname) values ('TestUser','Test','User');
insert into orablog.customer(fullname,firstname,lastname) values ('TestUser','Test','User')
                    *
ERROR at line 1:
ORA-28194: Can perform read operations only


SQL>

So, good, READ ONLY works as we are not allowed to do the INSERT even though we have permissions to do an INSERT on that table.

Now try the PL/SQL procedure:

SQL> set serveroutput on
SQL> exec orablog.custa('Finnigan');
name:=[Pete Finnigan]
name:=[Zulia Finnigan]

PL/SQL procedure successfully completed.

SQL>

Hmmm, so we only do READ on ORABLOG.CUSTOMER and not INSERT but we executed PL/SQL. Lets try a different example:

SQL> exec dbms_output.put_line('Test test');
Test test

PL/SQL procedure successfully completed.

SQL>

Hmmm, so we can run PL/SQL so it's not just READ ONLY to me, we can do EXECUTE?

Can we make common users read only. Connect to the PDB and try:

SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> alter session set container=cdb$root;

Session altered.

SQL> create user c##vv identified by c##vv read only;
create user c##vv identified by c##vv read only
*
ERROR at line 1:
ORA-28192: Enabling or disabling read-only property is not allowed on a common
user.


SQL>

So we cannot add READ ONLY to COMMON users although a COMMON READ ONLY user may be useful

Can we do an INSERT via PL/SQL

SQL> sho user
USER is "SYS"
SQL> grant create procedure to vv;

Grant succeeded.

SQL> connect vv/vv@//192.168.56.18:1521/freepdb1
Connected.
SQL> create procedure run as
  2  begin
  3  insert into orablog.customer(fullname,firstname,lastname) values ('TestName','Test','Name');
  4  end;
  5  /
create procedure run as
*
ERROR at line 1:
ORA-28194: Can perform read operations only


SQL>

Very interesting. So we cannot create a procedure as we are READ ONLY as the procedure is written to the database and violates READ ONLY. Can we create a dynamic procedure:

SQL> get dyn.sql
  1  -- test dyn proceudre to test read only
  2  -- --------------------------------------------------
  3  -- Create the "procedure"
  4  -- --------------------------------------------------
  5  var lv_str varchar2(2000);
  6  var curnum number;
  7  -- null the string
  8  exec :lv_str:='';
  9  declare
 10  begin
 11  :curnum:=dbms_sql.open_cursor;
 12  dbms_sql.parse(:curnum,'declare bv varchar2(2000):=:ipstr; begin dbms_output.put_line(bv);end;',dbms_sql.native);
 13  end;
 14  /
 15  print curnum
 16  -- -------------------------------------------------------
 17  -- end of declaring the "procedure"
 18  -- -------------------------------------------------------
 19  -- -------------------------------------------------------
 20  -- Call the dynamic procedure
 21  -- -------------------------------------------------------
 22  -- prime the string to print it
 23  exec :lv_str:='hello world';
 24  declare
 25  n number;
 26  begin
 27  -- bind the variable
 28  dbms_sql.bind_variable(:curnum,':ipstr',:lv_str);
 29  n:=dbms_sql.execute(:curnum);
 30  end;
 31  /
 32  -- --------------------------------------------------------
 33  -- End of dynamic procedure call
 34* -- --------------------------------------------------------
 35  .
SQL>


SQL> @dyn

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


    CURNUM
----------
1923939752


PL/SQL procedure successfully completed.

hello world

PL/SQL procedure successfully completed.

SQL>

So we can get around the restriction of creating a procedure BUT can we do an insert in this procedure. Lets modify dyn.sql:

SQL> get dyn.sql
  1  -- test dyn proceudre to test read only
  2  -- --------------------------------------------------
  3  -- Create the "procedure"
  4  -- --------------------------------------------------
  5  var curnum number;
  6  declare
  7  begin
  8  :curnum:=dbms_sql.open_cursor;
  9  dbms_sql.parse(:curnum,'begin insert into orablog.customer(fullname,firstname,lastname) values(''TestUser'',''Test'',''User'');end;',dbms_sql.native);
 10  end;
 11  /
 12  print curnum
 13  -- -------------------------------------------------------
 14  -- end of declaring the "procedure"
 15  -- -------------------------------------------------------
 16  -- -------------------------------------------------------
 17  -- Call the dynamic procedure
 18  -- -------------------------------------------------------
 19  declare
 20  n number;
 21  begin
 22  n:=dbms_sql.execute(:curnum);
 23  end;
 24  /
 25  -- --------------------------------------------------------
 26  -- End of dynamic procedure call
 27* -- --------------------------------------------------------
 28  .
SQL> @dyn
declare
*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1169
ORA-06512: at line 4



    CURNUM
----------


declare
*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1792
ORA-06512: at line 4


SQL>

No, we cannot do the INSERT via a dynamic block of PL/SQL. ORA-29471 is a security issue so it is blocked.

Turn READ ONLY off for the user VV to show how to reverse it:

SQL> sho user
USER is "SYS"
SQL> alter user vv read write;

User altered.

SQL>

Try the dynamic procedure again after we made the user READ WRITE:

SQL> connect vv/vv@//192.168.56.18:1521/freepdb1
Connected.
SQL> @dyn

PL/SQL procedure successfully completed.


    CURNUM
----------
1166850649


PL/SQL procedure successfully completed.

SQL> 

What about storage of the READ ONLY status for users. We need to be able to check the database and test which users are READ ONLY or not:

SQL> desc dba_users
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(128)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(4000)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 LOCAL_TEMP_TABLESPACE                              VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(128)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(128)
 EXTERNAL_NAME                                      VARCHAR2(4000)
 PASSWORD_VERSIONS                                  VARCHAR2(17)
 EDITIONS_ENABLED                                   VARCHAR2(1)
 AUTHENTICATION_TYPE                                VARCHAR2(8)
 PROXY_ONLY_CONNECT                                 VARCHAR2(1)
 COMMON                                             VARCHAR2(3)
 LAST_LOGIN                                         TIMESTAMP(9) WITH TIME ZONE
 ORACLE_MAINTAINED                                  VARCHAR2(1)
 INHERITED                                          VARCHAR2(3)
 DEFAULT_COLLATION                                  VARCHAR2(100)
 IMPLICIT                                           VARCHAR2(3)
 ALL_SHARD                                          VARCHAR2(3)
 EXTERNAL_SHARD                                     VARCHAR2(3)
 PASSWORD_CHANGE_DATE                               DATE
 MANDATORY_PROFILE_VIOLATION                        VARCHAR2(3)
 PROTECTED                                          VARCHAR2(3)
 READ_ONLY                                          VARCHAR2(3)
 DICTIONARY_PROTECTED                               VARCHAR2(3)

SQL>

Now we can check the READ_ONLY column and see which users of the database are READ ONLY:

SQL> select username from dba_users where read_only='YES';

USERNAME
--------------------------------------------------------------------------------
VX

SQL>

This is correct as we made VV into READ WRITE above and created VX as READ ONLY

The flag is stored as a bit flag in the SPARE1 column of SYS.USER$:

     decode(bitand(u.spare1, 524288), 524288, 'YES', 'NO'),
     -- READ_ONLY

In summary, READ ONLY allows SELECT and blocks INSERT/UPDATE and DELETE but we can still do EXECUTE but where the PL/SQL didn't try and change data. I don't know the extent of what "is / is not" allowed in PL/SQL when a user is READ ONLY. More testing would show this further

This is a useful feature to block change BUT if an attacker wants to steal all of the Personal Information or credit card details from your database then he doesn't need change, he only needs read!!


#23c #oracleace #dbsec #oracle #security #databreach #readonly

I have recently posted a 3 part series exploring at a high level the new SQL Firewall released in Oracle 23c Free. The parts are:

1. Part 1 - Introduction:


2. Part 2 - Learning and set up phase:


3. Part 3 - Test the SQL Firewall:

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

This is the third part of a series of posts discussing and testing the new SQL Firewall released in 23c Free recently. The three parts are:

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

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

This is the second part of a series of posts discussing and testing the new SQL Firewall released in 23c Free recently.The three parts are:

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

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

I wrote this blog as one complete blog and demo a good few weeks ago but it became huge for one blog post; in fact I have over a 100 pages of notes; so I have decided to split the post into 3 parts. These three parts are:

1. 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


2. 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


3. 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

Let us have a brief look at Oracle 23c database parameters marked as deprecated in the database that can be in some respect related to security. Here are the parameters are marked as deprecated in 23c:

SQL> col name for a40
SQL> col value for a100
SQL> set lines 220
SQL> select name,value from v$parameter where isdeprecated='TRUE';

NAME                                     VALUE
---------------------------------------- ----------------------------------------------------------------------------------------------------
lock_name_space
instance_groups
pre_page_sga                             TRUE
resource_manager_cpu_allocation          0
active_instance_count
db_block_buffers                         0
buffer_pool_keep
buffer_pool_recycle
fast_start_io_target                     0
serial_reuse                             disable
rdbms_server_dn
cursor_space_for_time                    FALSE
plsql_v2_compatibility                   FALSE
plsql_debug                              FALSE
background_dump_dest                     /opt/oracle/product/23c/dbhomeFree/rdbms/log
user_dump_dest                           /opt/oracle/product/23c/dbhomeFree/rdbms/log
commit_write
sql_trace                                FALSE
parallel_adaptive_multi_user             FALSE
asm_preferred_read_failure_groups

20 rows selected.

SQL>

There are a small number of interest to securing the database. Lets have a look at some:

• rdbms_server_dn: This is used to get the distinguished name of the server (DN) from the database for use in enterprise roles when they are in an enterprise directory server


• plsql_v2_compatibility :This is deprecated for a long time and this allows if set to TRUE old behaviour to be used in PL/SQL. Use of this could occur if you use a very old third party PL/SQL based application. If the application is under your control, change it!


• plsql_debug :This parameter is retained for backwards compatibility but has been deprecated for some time as above. If this were set to TRUE then all PL/SQL would be compiled with debug so could be used to intercept a PL/SQL procedure/package at run time and more easily steal data


• background_dump_dest :Also deprecated for a long time since 12.1 but retained. It is ignored if set if the diagnostic_dest is used. If used this is the location of the alert log and also background process trace files. We should be aware that these files can contain structural details of the database and also in some cases data (in SQL or in memory dumps). We should control the locations of where the alert log is written and also these trace files BUT importantly make sure that they are managed; archived, purged and be aware that that can contain useful information to a hacker


• user_dump_dest :This is also deprecated a long time and also affected by the diagnostic_dest. This parameter controls where user created trace is written and for the same reasons as above these trace files must be managed, archived, purged as they can contain data that is not protected in the database with database security


• sql_trace :This parameter is dangerous as it could be used to kill the database by enabling it at the database level. If this were the case and the file systems are not adequate then the database could be hung. Also remember that setting sql_trace effectively gives only Oracle v6 trace as there are no binds and waits. Oracle suggest that you use DBMS_MONITOR or DBMS_SESSION instead and trace should be targeted not system wide

A lot of these parameters are deprecated for some time and not newly deprecated in 23c and should not be used for reasons above. Check in your database that these are not set and be aware that security is everywhere in an Oracle database.

#23c #dbsec #oracleace #oracle #security #parameters