Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Locate an Error in Wrapped PL/SQL

I had a conversation a few weeks ago with someone who asked me how to find a missing table when you have a wrapped PL/SQL file and cannot see the source code and you install it and it gives an ORA-0942 error - Table or view does not exist. He asked me for advice on how you might find out what table or view cannot be accessed by the wrapped PL/SQL.

This was an interesting question.

Firstly why not just look at the clear text PL/SQL? - well in this example case it was a commercial application he was asked to install into a database and the PL/SQL was wrapped and he could not see the code. He did of course ask the vendor and in the end got an answer that some other scripts that created the tables needed to be run first and problem solved.

So assuming that you do not have access to the clear text PL/SQL what can you do?

I want to create a simple example. First connect to my 23c database as SYS and create a sample user PETE1:

[oracle@localhost ~]$ sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 09:58:52 2024
Version 23.2.0.0.0

Copyright (c) 1982, 2023, 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 pete1 identified by pete1;

User created.

SQL> grant create session to pete1;

Grant succeeded.

SQL> grant unlimited tablespace to pete1;

Grant succeeded.

SQL> grant create procedure to pete1;

Grant succeeded.

SQL>

Connect to my sample user PETE1 and test whether I can access SYS.USER$. Obviously I know the answer in advance that the error is my PL/SQL cannot access SYS.USER$ but I need to be sure there is an error first simply in SQL*Plus:

SQL> connect pete1/pete1@//192.168.56.18:1521/freepdb1
Connected.
SQL> select name from sys.user$;
select name from sys.user$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

Now create the PL/SQL and in it access SYS.USER$ - the table that is missing, inaccessible.

SQL> get test.sql
1 -- test PL/SQL for wrapped create
2 create or replace procedure test as
3 lv_password varchar2(4000);
4 begin
5 select password
6 into lv_password
7 from sys.user$
8 where name='SYS';
9* end;
10 .

Now wrap the PL/SQL:

[oracle@localhost ~]$ wrap iname=test.sql oname=test.plb

PL/SQL Wrapper: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 10:07:41 2024
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.

Processing test.sql to test.plb
[oracle@localhost ~]$

Show the code to prove it is wrapped:

[oracle@localhost ~]$ cat test.plb
create or replace procedure test wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
81 be
LlzHv6ZvxN4mhihv9rosYR6UAmEwg5nnm7+fMr2ywFwWoWLRzLh0iwbSvW0ouHTLy/4owMzn
x3TAM7h0ZSV8f3x8UKCLwMAy/tKGBnSfgVIyy8yp1YYG0tKZCOpnJfqVKW9duwovdojYH6uK
qxoVlYi57p6glXKzsT0rpBbYSNdD/MHg16ama3fKHg==

/
[oracle@localhost ~]$

Now let us try and install this code into my database:

SQL> @test.plb

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2 PL/SQL: SQL Statement ignored
6/11 PL/SQL: ORA-00942: table or view does not exist
SQL>

OK, so we know there is a ORA-00942 error but we do not know what table causes it so that we can fix it as the code is wrapped and looking at lines 4 and 6 is meaningless in this context

How to know what table or view does not exist?

Try the ALL_ERRORS view

SQL> set serveroutput on
SQL> @sc_print 'select * from all_errors'
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from all_errors','''','''''');
Executing Query [select * from all_errors]
OWNER : PETE1
NAME : TEST
TYPE : PROCEDURE
SEQUENCE : 1
LINE : 6
POSITION : 11
TEXT : PL/SQL: ORA-00942: table or view does not exist
ATTRIBUTE : ERROR
MESSAGE_NUMBER : 0
-------------------------------------------
OWNER : PETE1
NAME : TEST
TYPE : PROCEDURE
SEQUENCE : 2
LINE : 4
POSITION : 2
TEXT : PL/SQL: SQL Statement ignored
ATTRIBUTE : ERROR
MESSAGE_NUMBER : 0
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

This is not useful as it doesn't tell me what table does not exist and is a repeat of the error we saw trying to compile the wrapped code in SQL*Pus.

We can try dependencies instead:

SQL> @sc_print 'select * from all_dependencies where name=''''TEST'''' and referenced_type=''''TABLE'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from all_dependencies where name=''TEST'' and referenced_type=''TABLE''','''','''''');
Executing Query [select * from all_dependencies where name='TEST' and
referenced_type='TABLE']
OWNER : PETE1
NAME : TEST
TYPE : PROCEDURE
REFERENCED_OWNER : SYS
REFERENCED_NAME : USER$
REFERENCED_TYPE : TABLE
REFERENCED_LINK_NAME :
DEPENDENCY_TYPE : HARD
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Yes, the issue is there in the dependencies view and shows that it is indeed SYS.USER$ but if there were hundreds of tables then its harder to locate the actual one. Test if we can access it as PETE1 in SQL*Plus:

SQL> desc sys.user$
ERROR:
ORA-04043: Object sys.user$ does not exist.


SQL>

Reconnect as SYS and grant ALTER SESSION to PETE1 to allow the use of trace:

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

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 18 10:22:48 2024

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


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

SQL> grant alter session to pete1;

Grant succeeded.

SQL>

Set trace and install the PLB again:

[oracle@localhost ~]$ sqlplus pete1/pete1@//192.168.56.18:1521/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 10:23:02 2024
Version 23.2.0.0.0

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

Last Successful login time: Mon Mar 18 2024 10:22:32 +00:00

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

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> @test.plb

Warning: Procedure created with compilation errors.

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL>

locate the trace file:

SQL> sho parameter diag

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /opt/oracle
diagnostics_control string IGNORE
SQL>

Look for the potential trace file:

[oracle@localhost oracle]$ find . -name "*.trc" -print 2>/dev/null | xargs ls -al 2>/dev/null | grep "Mar 18"
-rw-r-----. 1 oracle oinstall 2243134 Mar 18 10:30 ./diag/rdbms/free/FREE/trace/FREE_dbrm_3163.trc
-rw-r-----. 1 oracle oinstall 101670 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr0_184977.trc
-rw-r-----. 1 oracle oinstall 905271 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr1_179252.trc
-rw-r-----. 1 oracle oinstall 42302 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr2_185301.trc
-rw-r-----. 1 oracle oinstall 71822 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr3_185112.trc
-rw-r-----. 1 oracle oinstall 41974 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr4_185336.trc
-rw-r-----. 1 oracle oinstall 1151 Mar 18 09:57 ./diag/rdbms/free/FREE/trace/FREE_j001_183579.trc
-rw-r-----. 1 oracle oinstall 1150 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_j003_184635.trc
-rw-r-----. 1 oracle oinstall 1453 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_j005_184641.trc
-rw-r-----. 1 oracle oinstall 1449 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_j006_184644.trc
-rw-r-----. 1 oracle oinstall 943553 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_lmhb_3199_data.trc
-rw-r--r--. 1 oracle oinstall 31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m000_183247.trc
-rw-r-----. 1 oracle oinstall 72908 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m000_184934.trc
-rw-r-----. 1 oracle oinstall 35341 Mar 18 10:20 ./diag/rdbms/free/FREE/trace/FREE_m001_184937.trc
-rw-r--r--. 1 oracle oinstall 31938 Mar 18 10:09 ./diag/rdbms/free/FREE/trace/FREE_m002_184940.trc
-rw-r--r--. 1 oracle oinstall 31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m004_184027.trc
-rw-r--r--. 1 oracle oinstall 31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m005_180811.trc
-rw-r-----. 1 oracle oinstall 1744 Mar 18 10:22 ./diag/rdbms/free/FREE/trace/FREE_ora_185518.trc
-rw-r-----. 1 oracle oinstall 537629 Mar 18 10:24 ./diag/rdbms/free/FREE/trace/FREE_ora_185538.trc
[oracle@localhost oracle]$

Looks like the last ones are a potential match so grep now for the PL/SQL procedure:

[oracle@localhost trace]$ grep -i test *.trc
FREE_m000_178093.trc: SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1608) into (partition WRH$_AWR_TEST_1_1405253007_1608, partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m000_178093.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_178093.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_184934.trc: SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1656) into (partition WRH$_AWR_TEST_1_1405253007_1656, partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m000_184934.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_184934.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m001_179164.trc: SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1632) into (partition WRH$_AWR_TEST_1_1405253007_1632, partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m001_179164.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m001_179164.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_ora_185538.trc:create or replace procedure test wrapped
FREE_ora_185538.trc: value="procedure test wrapped
FREE_ora_185538.trc: value="TEST"
[oracle@localhost trace]$

Nothing useful is found in the trace. We can see the ORA-00942 and the insert into error$ but not the SQL that causes the error in the trace file.

There are other things we can do; the first is talk to the vendor and ask why their wrapped code fails to compile or ask the vendor about the line number in the code and what table is missing. We can use simple ideas such as dependencies to try and locate the missing table or permissions when we try and compile wrapped code where we don't have access to the clear text code.

Also remember the 9i wrap.exe and lower was the front end to a PL/SQL compiler, the 10g and higher is a simple obfuscation of the clear text code.

#oracleace #sym_42 #oracle #database #security #plsql #compile #permissions #error #942 #23c

Attention PL/SQL Programmers - is your PL/SQL at risk of breach?

Do you develop software in PL/SQL?

I will show you in the next few minutes how you can learn to find security vulnerabilities in your PL/SQL code

Even if the database that your PL/SQL is deployed to is secure then if you do not program your PL/SQL defensively and securely then it is a matter of when and not if that a breach of your customer or employers data could occur through non-secure PL/SQL applications.

If you are not aware of what security issues in PL/SQL look like and how they could be exploited then it is not easy for you to code your PL/SQL securely. How would you feel if it was your lack of knowledge that caused a data breach to occur?

We have an easy solution for you. We are running two live training events online on the 27th March 2024 on UK hours and the same class is re-run on the 28th March 2024 but this is on USA EST time zones. Some highlights of the class next:

  • The classes are taught by Pete Finnigan live but on-line via webex so you do not need to leave your office or home to attend.

  • You can ask questions at any time during the class day and get access to Pete and learn from him and his over 20 years of experience in this field of secure coding in PL/SQL.

  • The class is one day and is called Secure Coding in PL/SQL and is taught live from 9am to 5pm. The class includes taught lessons and many demonstrations throughout the day.

  • Each student will receive pdfs of full course notes and lessons and also over 100 free SQL and PL/SQL scripts and tools covering the demonstrations and also the many free tools used in the class.

  • You do not need to be a PL/SQL developer to attend although most attendees are usually PL/SQL developers. We have also taught DBA staff and even managers who would like to understand the security risks likely to be found in PL/SQL their developers create.

  • The high level agenda is as follows:

    • Data Theft: This lesson covers why data can be stolen or privilege escalated in a
      database focusing on issues related to privileges assigned to PL/SQL, bad
      programming practices and leakage of data. This section is an overview to allow the student to see how PL/SQL fits into
      the security model intended to protect Data

    • Permissions: We cover permissions of packages and procedures and design decisions that affect security

    • Coding Errors: This section introduces common PL/SQL Security programming issues and
      for each shows the issue in code form and exploitation and then also in terms
      of secure coding and solution. These include: Input validation, Object validation, Open interfaces, SQL and PL/SQL and Other Injection issues, File and external access, Operating system commands, Vulnerable and dangerous package use and more

    • Secure Coding Best Practice: We look at fixing the issues and secure coding best practice

    • Encryption: We use encryption as an example to demonstrate everything we have covered so far

    • Protecting PL/SQL: This section discusses techniques to lock down PL/SQL in terms of Preventing IPR loss, Prevent unauthorised execution both in the host database or if the code is removed, License type features and wrapping and unwrapping

    • Finishing Up: We cover processes to secure code and review coding and also automated checking of your code and finally creating secure coding policies




The course material and demonstrations have recently been extensively updated and cover up to Oracle version 23c. Of course all earlier versions are also covered.

The course fee is just £440 GBP (plus VAT if applicable).

To register your place on our class then simply send an email to securecode@petefinnigan.com and we will help you secure your place

As a bonus if you book more than one place we will include a 10% discount for all places booked by you.

Your PL/SQL code will be more secure after this class so please register your place now.

To register your place on our class then simply send an email to securecode@petefinnigan.com and we will help you secure your place

#oracleace #sym_42 #oracle #plsql #secure #coding #training

How to Secure all of Your Oracle Databases - Part 1

How do you know how secure your Oracle databases are?

How secure should your Oracle databases be?

These are interesting questions that we will cover in this three part post. This first part is going to cover the high level discussions / strategy and issues related to that. The second part will cover the process of reviewing what you have now and then the third part is going to look at the ongoing strategy to fix and secure all databases in the organisation.

You do not need a percentage secure figure or some other artificial measure of data security. These figures can be useful though to trace progress but if a database in your opinion is 75% secure what does that really mean?.

If it is measuring 75% secure this week and 71% last week and 69% the week before then that's useful as an indicator that progress is going in the right direction.

Some companies use standards like the CIS Benchmark to secure their database against. In the absence of anything else this is a starting point. Checklists like CIS focus on defaults and simple hardening. Imagine if someone applied all of the CIS to all of their databases; This would be a large task and in this case as a simple score they could claim 100% or high compliance against CIS and think that they are secure BUT imagine also that passwords are found or guessable or all data is granted SELECT, INSERT, UPDATE and DELETE to PUBLIC on all tables. This means that CIS does not protect the data. Actual data security and design measures are needed. Yes, the databases are hardened BUT the design of the database and application and data model are weak and the data can easily be compromised and hardening of the core database does not protect it.

There are multiple layers that we must consider when securing any Oracle database:

  • Patching We must apply the security patches released by Oracle BUT their application in general does not affect the security of data itself

  • Hardening This is the revokes, defaults etc from documents such as CIS or Stig or NIST. These are useful to harden the base Oracle software BUT they do not in general secure actual data

  • Data Security

    • Access Controls We must design security to allow access to the database to only those authorised and only when needed

    • User Security Each user must have least privileges only and suitable password management and controls

    • Data Access Controls All data must be designed and code and data separated in separate schemas and permissions created between data and code and users and roles

    • Context Based Security The use of context based security can be added to allow more fine grained control to access to data, users and permissions. This can be by using Oracle technologies such as Database Vault or VPD or OLS etc or can be custom coded with triggers, code and more

    • Audit Trails Each layer of the database should also have suitable audit trails designed and enabled to allow use of the database to be properly monitored




Of course other layers should also be considered from a security perspective such as the underlying Operating System and networking also also if necessary application layers

The security of data in Oracle databases is also affected by the number of databases - i.e. if you have 1,000 databases and 200 issues to secure in each that is 200,000 items to secure across the estate. This is excessive. The available budget to secure all data and the time available and number of people available to work on it must also be considered.

Inevitably this means that we need a security policy / design that is layered and is achievable across the estate.

One other factor to take into account is existing processes and working practices. Even if we change the security at a hardening level or re-design the actual data security if staff/users all share the schema password or SYS password that security work is useless.

We need an all-encompassing security design for data in an Oracle database including patching, hardening, data security, other layers and also process.

What is really needed at a high level is a risk assessment to create a valid list of all possible threats to the database and a valid list of possible vulnerabilities in the database and finally a list of counter-measures that can be used to mitigate the threats via exploiting the located vulnerabilities.

Join me soon for part 2 where I will discuss the actual audit process itself

#oracleace #sym_42 #oracle #database #security #audit #hardening #patching #vulnerability

Happy 21st Birthday to PeteFinnigan.com Limited

My company PeteFinnigan.com Limited is 21 years old today!!

It seems that time has gone so fast. When I started the company my oldest son was a baby and now he is almost 22 years old and works here in our offices doing marketing.

I wanted to focus on helping people secure data in their Oracle databases. I think I have achieved this goal very successfully. We (and I) help people in many ways secure data in their Oracle databases and sometimes other databases. We specialise in securing data in Oracle databases but the ideas and techniques and knowledge we use also transcends other databases.

We do:

  • Oracle Security Audits :We do a detailed review of customers Oracle databases and present the best cost effective strategy for them to secure their data

  • Consult in all areas of Oracle security :We have consulted in so many areas of securing Oracle over the years and still do. Anything that relates to Oracle security we have helped with including audit trail designs, encryption in the database, use of HSM, Oracle key Vault, Database Vault, VPD, OLS, Masking and many many more...

  • Specialist consulting; part of your team :We also are the Oracle security specialist in some companies teams. We work on a call off basis so that you can include us as needed in your projects and we bill to the minute. We work with a small number of companies doing this now and we keep it small to be able to fully support the clients. Talk to us if you would like a very cost effective way to have an Oracle security expert as part of your team when needed

  • Securing PL/SQL : We do PL/SQL security code reviews and also help customer protect their PL/SQL with obfuscation

  • Development consulting :We help companies in the development of software in the area of Oracle security with consulting and sometimes development help

  • We have multiple software products :

    • PFCLScan :Scan your database for security issues and vulnerabilities

    • PFCLCode :Review your PL/SQL code for security flaws

    • PFCLObfuscate :Protect your PL/SQL

    • PFCLForensics :Manage a database data breach, perform live response and perform forensic analysis

    • PFCLCookie :Assess a website for cookies used



  • Oracle Security Training :We have over ten days of expert training in all areas of securing data in an Oracle database

  • Blogging, Speaking and presenting :We like to give away expertise for free via blogging, presenting, our website and free scripts and tools



What about the next 21 years of helping people secure data in an Oracle database (or other database)?

The one thing I can say about the last 21 years is that when I started there was literally no one else doing what I did which was specialising in deep detailed help and advice to secure data in Oracle. There was little to no evidence of much Oracle security going on. Security patches had not long started at that point; there were limited hardening advice and most people did not do a deep job on designing and securing databases. I remember cold calling companies back in 2023 and being able to speak to the right person and they were in the most part interested in what I had to say and offer BUT there was no budget to secure Oracle databases; the budgets went on network security and desktop security.

Most databases back then I did get to see had no security and most were the reverse of secure; i.e. everyone used SYS and SYSTEM and schemas, passwords not protected or changed in more than 10 years, no schema level security design, no hardening and ....

There was also often a kick back against security of Oracle often for fear it would break the running system and often because a lot of people didn't want to give away their elevated access. Some didn't want me to see and report on the bad practices as they knew deep down they were bad practice.

One thing back then was there were very few specialists in securing Oracle and amazingly after 21 years there are still not many out there. Why is this?, I have taught a lot through training but I guess security of Oracle is still regarded as the last task to be done in ten minutes before a new database / application goes live? so we are not needed?

I still see databases now that look like they did 21 years ago BUT the attitude and willingness to secure and learn has changed drastically in customers

The world has changed in the last 21 years; much more data theft and identity theft. Data in databases has become the new target; the new gold rush!!


#oracleace #sym_42 #oracle #database #security #databreach #forensics #plsql #securecode #obfuscate

Securing APEX

I have liked APEX for many years and been involved in auditing and securing Oracle databases that include APEX for many years.

What surprises me sometimes is that those deploying and developing an APEX application treat it like a box and do not consider that APEX is written in PL/SQL, uses PL/SQL and is deployed to an Oracle database. Its like they believe that the database security is someone else's problem or they didn't realise that its in a database - with the data!! Sometimes, the Oracle database is a side issue for them; it's not considered in terms of security and the only factor for the customer is that an Oracle database is needed to run APEX so its provided and not secured in any meaningful way

I wanted to do a post now to highlight at a high level the areas that should be considered for securing an APEX application and database when using and deploying APEX in your database. Lets list and discuss briefly:

  • Web Based Security: Your APEX application may use JavaScript to enhance its features and operation but Javascript can be the target of attackers through input manipulation for attacks such as Cross Site Scripting (XSS) and more. This point is not just limited to JS but to any web level technology that can be used from APEX

  • Webserver and network security: The webserver is likely to be ORDS and it provides the web content to the end users. The security of ORDS must be considered and the network design of the database, ORDS and access must be secured

  • APEX Security: The APEX application itself must be secured at the APEX level. This includes user security, profiles, passwords, instance level security, deployment security, APEX database object security, workspace security and more

  • APEX Application security: APEX provides security at the application level and allows security of users, authorisation, authentication and page level security. The application in APEX is written as snippets of PL/SQL that are stored in the APEX repository and these also must be written with security in mind and secure web and database coding techniques used

  • Supporting PL/SQL: Often the bulk of the APEX application will be written in separate PL/SQL packages called from a thin veneer of PL/SQL in the APEX repository. These packages must be also secured and designed with security in mind. The schema that owns them must be limited in rights and access to he rest of the Oracle database. The PL/SQL must be written with secure coding techniques in mind. This includes avoiding SQL Injection but also managing access to resources and features securely from the applications PL/SQL

  • Schema design and security: The schema that owns the PL/SQL of the application must also be designed with security in mind. The data should be held in one schema and application code (PL/SQL) in another (at least) to allow grants to be made between code and data and not a free for all. Least privilege of the schemas must be observed

  • Database Level Security: Database level security is a complex topic of course and I have covered the basic areas here many times. At a high level we must patch and harden. These alone will not secure data. We must also then secure the data within the database. This is the biggest part of the task of securing data in an Oracle database. This part includes user access controls, user level privileges and data level privileges.

  • Context Based Security: An additional layer that can be applied is to use context based security such as Database Vault, Virtual Private Database, TSDP and more. The normal rights at the database level are granular but Oracle provides extra (often cost based) features that allow more controlled and fine grained security to be applied. We can also create context based security of our own using PL/SQL code, triggers and views. Context based security can also be used at the APEX level as well as the database level

  • Audit Trails: There must be well designed and useful audit trails implemented at the database and also at the PL/SQL level and APEX levels so that any potential misuse of the application or data can be captured to make any breach analysis or forensic analysis easier

  • OS Security: The operating system that hosts the database must also be secured. There are many layers that are similar to the database and APEX. We must secure defaults, hardening, users and privileges, audit trails and more

  • Admin and User Access: We also must consider how users and particularly types of admin users (sys admins, DBA, support etc) access the OS, the Oracle database and APEX. We must ensure that data is protected through these types of access and that the access is audited



Attacks are many and varied from the web level to abusing APEX itself or abusing the PL/SQL or database or even OS level. Securing an APEX application is multi-layered and we must consider all layers for security when we deploy an APEX application from OS to web.

In general we can take the same approach with each layer:

  • Review existing security policies

  • Review the actual systems (Database, OS, APEX, PL/SQL)

  • Understand the security requirements and design a new policy, update the existing policy, create a fixing strategy

  • Fix and secure all systems

  • Perform continuous review of all systems (Database, OS, APEX, PL/SQL...)




#oracleace #sym_42 #plsql #apex #oracle #database #security

Investigate an Oracle Database Breach

I have investigated a number of possible and later proved data breaches in Oracle databases over the years and more recently just before Christmas I was doing the same again for a client. I cannot talk about any specifics of course here of the recent client BUT i want to go over the basic steps and what we were trying to achieve.

The subject of a response to a breach is huge; there are many factors that must be taken into account as part of the investigation and also going forwards after the initial breach and into the fixing, correction, protection phase.

This is a huge subject that cannot be done detailed justice here as a blog BUT lets dive in and discuss the high level points of investigating a breach of an Oracle database. We will go through roughly chronologically at a high level and at the end bring in some elements that should be done now in advance:

  • Planning : In advance of any breach you should take some actions.


    • Appoint an incident response co-ordinator : Identify someone who will manage the response when it happens. This ideally should not be someone deeply involved in a breach. I.e. the DBA sounds like a good option BUT they will be needed in the response at a technical level and they could potentially be part of the attack

    • Identify and create an incident response team : Create a team in advance that will be put into action as soon as a breach occurs. Where that team knows what to do and how to do it

    • Create an Incident response process : Write down the steps that should be taken under any potential breach so that a structured response can be taken

    • Create a toolkit : Build a toolkit to use when a breach occurs that is validated and tested and where everyone knows how to use them

    • Provide Training: Ensure that all of the response team in advance of a breach are trained on how to respond to a data breach in an Oracle database and trained on how to use the relevant tools and how to review and analyse data that can be part of the breach



  • When a potential breach occurs verify it : If a breach is reported then the first step is not to disrupt business initially and verify that it really is a breach. How to do this is different based on each breach. For instance if someone says that personal data was breached and that data was for sale on the dark net or being promoted for free on public social media sites. In this case if we can show that the data is really ours - by content and order then we can get started and agree there is a breach. Another example could be that evidence is shown that someone is selling data or taking data out on disc or other means. This step is different per breach and we are trying to prove that the breach is real

  • Enable the Team : Once we know the breach is real, enable the team. The team co-Ordinator takes charge of everything and works to a check list that is your incident response process

  • Don't initially disconnect and switch off : The first reaction can be to disconnect the database from the network or pull the power cord. This may reduce the further risk BUT it removes the change to get all transient data

  • Perform Live Response : Collect the live data that is held in memory; users logged in, contents of current SQL and more

  • Collect less transient data : Collect the less transient data

  • Collect other data : Collect other relevant data such as server logs, web logs and more

  • Repeat the process on all servers/databases : Repeat the same process to gather data from all relevant systems

  • Shutdown / Disconnect : If its possible disconnect the database and server and shutdown

  • Perform Forensic analysis: Now we must build a timeline of relevant evidence and actions from the data that has been gathered in the live response. We must narrow the timeline to the actual breach; when it started and when it ended. Initially this timeline will be much wider but as the investigation grows we will narrow it to the main events that contribute to the breach

  • Answer questions : We must answer some basic questions; 1) how did the attacker get it?, 2) What rights did the attacker have?, 3) What did they see, steal? 4) What did they change?, 5) What could they have done with more skills?

  • Create a Report : At this stage we cab create a report that summarises the attack and answers the questions and suggests what is next

  • Fix, Rebuild or?: If we know exactly what happened we cab decide how to rectify the database and security and applications. The ideal scenario is to fix the database or data and of course security. Rebuilding will be a risk if the backups span the actual attack

  • Implement auditing : Implement a detailed audit trail so that any future breach is much easier to investigate and resolve

  • Implement data security : My experience is that often data security is not as good as it should be and in some cases its shocking. Implement a good secure data security regime


The above details the high level flow of the actions to take if there is a breach. One thing that pops out of an Oracle database forensic analsyis is that unlike analysis of a PC we cannot simply remove the discs of a database and byte copy them for analysis, for a number of reasons; license?, size, tools to analyse the database as data on disk.

Another key issue is the Heisenberg principal in Oracle. The more we question the database; the more we change the database and change the evidence. We also cannot simply take checksums of discs for the reasons above as we would in a PC analysis.

One of the biggest issues is that if the breach is READ based then without pre-defined database level audit trails in the database detecting any read action is difficult

It is clear that forensic analysis and live response of an Oracle database breach is difficult so it makes sense that training in this area is performed in advance of any potential breach so that the relevant teams know what to do and how to do it. It also makes sense to have tools ready for any potential breach and training in the use of those tools

Quite clearly it makes sense to secure your Oracle database before a breach and have detailed audit trails!!

Plan ahead

#oracleace #sym_42 #oracle #databreach #security #forensics #liveresponse #hacked #audittrail #audit

Happy New Year for 2024

It has been a while since my last blog on the 29th December on the ACCESSIBLEBY Clause in PL/SQL .

We had a well earned break after the New Year and myself, my wife and my youngest son visited New York City for 5 nights for my wifes 50th birthday and managed to walk over 40 miles whilst we were there. We saw all of the main sites including the Statue of Liberty, the Chrysler Building, Went up The Empire State, walked the Brooklyn Bridge, Central Park, the modern art gallery, the National gallery, Staten Island, Times Square, The Dakota Building, The flat Iron Building, the 911 memorial and site of the twin Towers, the new World Trade center, The Intrepid Museum and many more. It was a great break from Oracle Security and nice to take a proper holiday even though it was hard on my feet to walk such a distance!!

For quite some time I have managed to blog about Oracle Security once or sometimes twice a week. The good news is that the posting schedule up to the New Year will continue into 2024. So expect more technical blog posts every week and some more higher level posts throughout the next year.

We are working in many areas all related to Oracle security and these influence what I want to write about here. We have a custom application that we have created to manage future blog posts for the future. This just allows me to write and start many posts and add to them as time allows. We have over 170 posts currently part written, complete or just titles and some research n this small custom application. It's not supporting direct posts to the blog but it could and has some of that functionality built in already so we could post from it in the future.

We have a lot of things Oracle Security wise going on in the last months and also coming up in the New Year. I cannot talk about specific client details but these pieces of work can certainly inspire some blog posts that discuss the generics of things I have done and seen that could be useful to others. Here are some examples that I will discuss very soon:

  • Oracle Database Breach: I was asked to investigate a breach of an Oracle database via a web site that turned into a very large loss of data for the client. They wanted to know how the attacker got in and what they stole and what needed to be done to prevent another attack. I cannot discuss any specifics but I will discuss in a new blog post the overall process of investigating a data breach of an Oracle database. Watch out for that soon

  • Protect the PL/SQL in an Apex Application : I helped a customer protect their PL/SQL code in an Apex application. I will not of course go into any details of the customer system and code but I will highlight here in a new blog post the process we went through and what to do and what to look out for

  • Review an organisations Oracle databases and security policy: Before Christmas we reviewed the data security currently in place on a customers site for their Oracle databases and their policies. Again I cannot discuss specifics but I will create a blog post soon that will discuss the overall process to review the Oracle data security across an organisations Oracle database estate

  • Adding customisation to PL/SQL: I spoke here some time ago about writing assemblers, compliers, interpreters and more in PL/SQL in a post titled "Adding Scripting Languages to PL/SQL Applications - Part 1" and I do plan to return to that blog series very soon as I have done quite a bit of work on it. Someone contacted me because of that post about the idea of adding customisation to PL/SQL but not in PL/SQL. This is exactly what I intended to do with this series of blogs on compilers etc. The reason to not customise in PL/SQL such as you would in essence in an Apex application is to provide a much simpler interface for an end user where their are perhaps some library functions and a vert simple language where the customiser is not necessarily a programmer. Think of games where an end user can extend the game in a language such as Lua. Why not extend in PL/SQL? - its obvious, if you allow the end user to add PL/SQL to your code its an open SQL or PL/SQL Injection interface


Well that's a flavour of what's coming soon plus a lot more.

Happy New Year for 2024!!!!


#oracleace #sym_42 #oracle #database #security #securecode #plsql #forensics