Pete Finnigan's Oracle Security Weblog

I have just posted my MS PPT slides for the first time to my website for a talk I did at the UKOUG conference in Liverpool in 2018. These slides are available for the talk UserLeast Privilege and I have also updated our Oracle Security White papers page with a link to these new slides.

This was a talk I did at the UKOUG conference in December 2018 held in Liverpool. This was a good conference and I gave this talk about least rights for users in the Oracle database. The talk starts off by looking at the layers that need to be considered when securing an Oracle database. We then talk about least privileges, what it is and how its hard to achieve in an Oracle database. We talked about the different types of actors involved and also at a high level how the database works and how data moves and is processed by Oracle (at a high level). We then demonstrated some tools to gather all of the existing granted rights in the database.

We then did a demo of hacking my sample database and also listed all of the issues we located or that contributed to this hack. There are many layers of problems but least privilege is the main one so we choose to fix this in my sample application and then show how it worked and reduced the extend of the attacks to nearly zero.

I finish by talking about the different types of privilege and how they affect the security of data

Please have a look at the MS PPT slides; they are newly posted to my site, for more details.

#oracleace #oracle #user #least #privilege

I have just posted my slides from a talk I did at the ISACA event at Croke Park in Dublin in 2018. The talk was called "An Appreciation of Auditing and Securing Oracle" - I have also updated our Oracle securing white papers page to reference this talk.

This was a great event and I spoke about Oracle security at the time GDPR had just about become live. The talk looked at data hacking and hacking in general and then sought to place Oracle security in the bigger data security picture.

I discussed different hacks against Oracle databases and data hacks in general and then discussed the main steps that we must think about to secure data in an Oracle database. I went on to discuss GDPR in the context of Oracle data security, multitenant and also the cloud; again with reference to the Oracle database.

This was a good event; please have a look at my MS PPT slides which are newly posted today on our website

#oracleace #dbsec #isaca #auditing #gdpr #databreach #hacking

I did a presentation in Slovenia in 2021 around Oracle database passwords and I have today just posted the MS PPT slide to our site - Oracle Database Passwords and we have also updated our Oracle Security white papers page to link to this presentation.

This is an interesting talk and focuses on all things database password. We first define the problem; this is easy; if you find a password or guess or crack a password its the easiest way to exploit a database. We don't then need clever hacks using SQL Injection or anything else; we just log in as the attacker.

The talk then goes on to discuss the password algorithms used in the database and how they work in detail. We then focus on cracking password or more importantly potentially how long can a password stand up to being attacked. We use an Excel spreadsheet to look at this and we can vary the length of the password and the character set used and work out how long the password would take to crack. Why do we do this? we need to know how long passwords can last so we can design password profiles and complexity functions. It is no use stating that a password life time is 180 days and then not enforcing passwords that can not be cracked in less than 180 days.

We discuss the security of passwords and their hashes, the design of profiles and also the use of password safes.

This is an interesting talk so please have a look at my slides

#oracleace #23c #oracle #database #password #security #cracking

Continuing my job to post the slides from previous talks I did about Oracle Security I have today posted my MS PPT slides for a talk I did in 2020 at the UKOUG. The slides for this talk - Secure Coding in PL/SQL - have been posted to my site today and also the Oracle security white papers page has been updated to include this talk.

This talk is interesting for anyone who codes in PL/SQL. We look at common attacks and types of security issues in PL/SQL and do some demo hacks. We also discuss the creation of an Oracle secure coding standard for PL/SQL and how to implement this and how to make sure all staff work to it and check code against it. We cover some solutions for the common issues and also present some simple demos on how to look for security issues in your PL/SQL code.

This was a fun talk to give and included quite a few demos of hacks and fixes to common issues in PL/SQL. Please download the slides and have a look

#oracleace #23c #secure #code #plsql #oracle #security

If you have an application that includes data, PL/SQL then if it is secured can it still work without change?

Lets create a simple simulation of such an application and then discuss the faults and the fixes and possible issues. We do not have an application BUT I will simulate the database parts. First connect to SYS in my 23c database and create the schema and a procedure:

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

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 9 10:15:26 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 tt1 identified by tt1;

User created.

SQL> grant create session, create procedure to tt1;

Grant succeeded.

SQL>

And create a simple procedure:

SQL> connect tt1/tt1@//192.168.56.18:1521/freepdb1
Connected.
SQL> create procedure test is
  2  begin
  3     dbms_output.put_line('hello from tt1');
  4  end;
  5  /

Procedure created.

SQL>

SQL> grant execute on test to public;

Grant succeeded.

SQL>

Now imaging we have an application that uses this procedure "test" from its interface; this could be vb forms, php, Apex or indeed any interface BUT the application/interface has a bad design feature that I see regularly that application connects ad the schema to the database. Also the application has all of its statements not using full paths. So the application does "test();" and not "tt1.test();".

Let us simulate this in SQL*Plus; so connect to TT1 the schema and execute the procedure:

SQL> connect tt1/tt1@//192.168.56.18:1521/freepdb1
Connected.
SQL> set serveroutput on
SQL> begin
  2  test();
  3  end;
  4  /
hello from tt1

PL/SQL procedure successfully completed.

SQL>

This simulates a much more complex application with a lot of tables, views, PL/SQL that runs this code.

There are numerous faults with this design BUT it is a design model I see regularly on audits of customer systems. The faults:

• Usually code and data in one schema - no grants needed so I can see why people do it BUT it also means no object/data level security


• Connect to the schema - regularly see this in commercial products and also internal projects; again it makes the design and development simple as no grants needed


• Permissions - in this example the application connects as the schema so not grants were needed BUT this is not a good design and in this example the schema granted execute to PUBLIC on its PL/SQL


• Code executed as object(); not schema.object(); - again written for simplicity and ease for the developer


• more - There could be more issues such as vulnerabilities in the PL/SQL such as SQL Injection, multiple applications installed into the same schema - This could create a bridge between applications that should not be there

One simple initial fix could be to not connect the application as the schema. So for this example lets connect as SYS and create a new connection user and then imagine we are connecting the application to the database as the new user and test it. i.e. run the "test();" procedure with no path. Create the user:

SQL> create user uu1 identified by uu1;

User created.

SQL> grant create session to uu1;

Grant succeeded.

SQL>

Now simulate the application connecting as UU1 and running the the PL/SQL as "TEST();" - the application has no path to the use of TEST(); so as we are connected as UU1 and try and execute it - it does have PUBLIC EXECUTE

SQL> connect uu1/uu1@//192.168.56.18:1521/freepdb1
Connected.
SQL> set serveroutput on
SQL> begin
  2  test();
  3  end;
  4  /
test();
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'TEST' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored


SQL>

This of course fails as expected as there is no procedure TEST() in the UU1 schema BUT UU1 can execute TEST():

SQL> begin
  2  tt1.test();
  3  end;
  4  /
hello from tt1

PL/SQL procedure successfully completed.

SQL>

BUT, this does not work for the application which hard codes the path to TEST(); in this example and all other objects in the TT1 schema. So we can use CURRENT_SCHEMA instead:

SQL> sho user
USER is "UU1"
SQL> alter session set current_schema=tt1;

Session altered.

SQL>

And now execute the example procedure without a path:

SQL> begin
  2  test();
  3  end;
  4  /
hello from tt1

PL/SQL procedure successfully completed.

SQL>

It works!!, so a simple security fix to allow more control of access to objects in the database is to not use the schema to connect the application to the database and create a connect user for each application and make grants on only the needed objects and then use ALTER SESSION SET CURRENT_SCHEMA=TT1;

This can be added to a logon trigger to make the application work.

It would be better to design for security in the first place; put data in one schema, code in another, critical data in another, critical code in another and make proper grants and design proper connection users. Also use secure coding and lock down the database BUT this simple setting can allow better security and separation more easily.

The user DOES NOT need ALTER SESSION system privilege to do this. CURRENT_SCHEMA is not a parameter:

SQL> sho parameter curre

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_currency                         string
nls_dual_currency                    string
nls_iso_currency                     string
SQL>

It is a memory setting:

SQL> select sys_context('userenv','current_schema') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
TT1

SQL>

As a final thought. This ALTER SESSION SET CURRENT_SCHEMA could also be used for abuse. Imagine there is an application that does not use paths to database objects and it relies on access to objects in the connected schema. If there is a "better" object in another schema where the owner has more rights then use of ALTER USER SET CURRENT_SCHEMA could be used to direct an application to use the wrong object

#oracleace #23c #oracle #database #security #grants #session #schema #alter

Continuing with the series of posting the MS PPT slides (as pdf's) from recent past talks on all subjects Oracle security I have a new one for you. There are a few more still to post. I don't really know why I didn't post the slides directly after the talks themselves but they got left so I am now ploughing through them and releasing the material to my website.

Todays new MS PPT slides just posted to the site is from a talk I did in 2020 at the UKOUG. This is a talk Designing a good database audit trail and a link to it is also posted to our Oracle Security white papers page.

This talk focuses on the need to have a useful and well designed audit trail in the database that focuses on capturing an attack at the database level and not just at the application level. Often companies have audit trails that are part of the application but neglect to design and implement a good audit trail in the database at the database level itself. This is important. If an attacker goes in direct and steals data or causes damage to the database not via the application the logging mechanisms in application screens are not going to show the direct access to data or the database itself.

The talk focuses on the need to design an audit trail and shows that we cannot just take some settings from documents such as CIS as we need to understand what we as a business needs to know about the database; i.e. who logs in, who accesses as a DBA, who accesses out of hours, who tries SQL Injection, who tries to escalate rights and much more. We discuss the need to design events that should be captured and from there decide how we may implement those events as an audit trail; this could be using standard audit, triggers, unified audit or indeed any solution. The most important aspect is to design what you want to know before you decide on the technical solution.

I include some ideas of the basics to include in your audit trail and then I hack a sample database with just standard audit settings from Oracle and show that these standard settings do not capture the hacks. We next implement a sample designed audit trail and then finally hack again to show that these hacks are indeed audited at the database audit level.

That's it, please have a read of the slides

#oracleace #dbsec #oracle #database #security #databreach #audit #audittrail