Call: +44 (0)1904 557620 Call

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.

DBID Is Not Definitive When Used As An Identifier

Our Audit Trail toolkit PFCLATK has some brief documentation on the page that's linked here but in summary it is a comprehensive toolkit that allows quick and easy deployment of an audit trail into a customers database. We are currently selling this as software a service (SAAS) and the process is that we help customers design an audit trail policy and in that policy the main part is table of audit events that are useful to capture what the customer needs to know about abuse of their Oracle database engines.

This could include users sharing passwords, access to critical resources, 0-day attacks, escalation of duties and more. This document forms the basis for the core events and as part of that table we assess how and when events should be reacted to and which audit events warrant an alert being raised. The next step is to implement that documented policy as events in the database and this is where PFCLATK comes in. We use this rebuild toolkit that allows us to easily map documented audit events into events in the database to audit actual user actions on real systems and data. This toolkit includes policy based events and also triggered alerts. so that for a particular event we can decide when we are alerted, immediately or maybe once a day or maybe never and it just falls to collection of raw audit data.

One other big element of the PFCLATK toolkit is the fact that its very quick and easy to deploy this audit trail toolkit to every Oracle database in the organisation and then the toolkit manages centralised audit to a single (or multiple) database. The same toolkit is installed to each target database (ATK) and also to the central database (ATC) and then a simple setup call is made from the central (ATC) database to each target database (ATK) and then automatically all of the audit trails and alerts generated in each target (ATK) are transported to the central database. This means centralised alerts and reporting are easy to manage.

Currently this is offered as a software as a service and we charge the client consultancy to help design and write the policy and then implement the events into all databases and help with test and go live. We provide the toolkit as part of this service. Going forwards we will also license the toolkit as a software product without the consulting by us and we are in development of a new GUI dashboard and admin console for management and reporting.

Anyway to the point of this post. As part of the centralised audit trails we copy the audit trails once per hour automatically from each database to the central storage and we need a way to identify each target databases audit trails in the central database. In older databases such as then its simple we use the DBID column of V$DATABASE and thats fine but once we start to talk about multi-tenant architecture and CDB and PDB it unfortunately doesn't work. If we look at an 18c database and log into the CDB then we see:

Peters-MacBook-Pro:____atk pxf$ sqlplus sys/oracle1@// as sysdba

SQL*Plus: Release Production on Tue Mar 12 20:16:04 2019

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

Connected to:
Oracle Database 18c Express Edition Release - Production

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


SQL> select dbid from v$database;



And then go to the PDB and do the same:

SQL> alter session set container=XEPDB1;

Session altered.

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


SQL> select dbid from v$database;



Hmm, its the same DBID in the root container as in each PDB. This means that we can use DBID in an 11g or earlier database (unfortunately for some clients they don't get to run everything on the latest versions) to identify the source database and indeed we can do the same in 12c and above with a legacy installation (whilst we can still create legacy installs) but the problem lies in pluggable architecture. In this case we also need to look at the CON_DBID column of V$DATABASE so that we can detect if the database is CDB and if so replace the DBID as the identifier with the DBID. So for the PDB in my 18c database:

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


SQL> select dbid,con_dbid from v$database;

---------- ----------
2883395441 1392023073


So now in the PDB we have a different CON_DBID to the DBID and in the root container we have:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select dbid,con_dbid from v$database;

---------- ----------
2883395441 2883395441


The CDB has the same CON_DBID as the DBID. SO for identification purposes we must resort to the CON_DBID not the DBID in pluggable database. The PFCLATK easily checks the database version and if its CDB our not and if we are in the root container or the PDB and uses the correct value.

Would be nice if Oracle would use the DBID as a unique identifier across PDB / CDB so that its consistent across legacy as well; but they don't..:-(

If anyone would like more details of PFCLATK and our audit trail service then please contact me.

Hardening and Securing The Oracle Database Training in London

I posted last week that I will teach my two day class "How to Perform a Security Audit of an Oracle Database" with Oracle University in London on the 29th and 30th April 2019.

We have now added a third day on the 1st may 2019 for my one day class "Hardening and Securing Oracle". This is a great fast paced one day class. In this class at a high level I start by showing why my Oracle database and sample application is vulnerable to attack and data breach by hacking it as various different actors (web user, database user with just CREATE SESSION, DBA user and more) then we analyse why the attacks work and why the data is insecure. The biggest part of the day is then take by walking through all of the core areas we can make improvements of database security in; patching, hardening, operating system security related to the database, user security, data access controls, context based security and audit trails.

During the day I show demos and lock down and secure my sample database and applications. At the end of the day we hack it again and show that it's not possible to steal the data. Of course in real life you would not hack your own database to prove its insecure and i would not do that at client sites but its a good graphical way too emphasise the reality of the current security of the data and the security of the database. Also you would not simply dive in and start fixing things without first planning and documenting and creating a security policy for all of your Oracle databases (we cover that) BUT i do dive in and secure in this class to show what can be achieved in a very short time in terms of securing data in your own Oracle databases.

This is a one day class and can be attended just as the one day BUT it is intended to be taught along with the two day class "How to perform a security audit in an Oracle database" so attending all three days is a great idea.

To register for this one day class with Oracle University then please click this link. The details for the two day class are in the link at the start of this post.

I look forwards to meeting you all in London in 2019

Stop The DBA Reading Data in Subtle Ways

The Problem:

Dan asked me a question about whether the DBA can be stopped from accessing views such as V$SQL or V$SQL_BIND_CAPTURE with Database Vault because these views can be used to read data from the SGA.

I have covered the issue of reading data from outside of the planned data model - i.e. access outside of SELECT, INSERT, UPDATE and DELETE grants on data tables or via PL/SQL execute privileges - many many times in the past on this site blog and in my trainings and consulting.

A blog entry here called Accessing data outside of the data model is a good example. I cover this very issue in my two day class "How to perform a security audit of an Oracle Database" and also "Secure coding in PL/SQL" from the view point of stealing data and I also cover the SGA and binds from the other side of needing that information to track a hacker in my "Oracle Incident response and forensics class".

The Problem Stated Even More:

Dans point that a DBA can read data by simply sampling SQL statements and PL/SQL statements or bind data is very valid and indeed this is the problem he wants to solve. If a DBA has access to the database then they need to do DBA like work (we haven't defined what that is exactly) and of course the DBA should not be able to see data; why in this data breach age should the DBA have carte blanche to read data along side his normal job. Business people are limited in what data they can see so why not limit the DBA as well.

So the first thoughts are to limit access to all tables and views and code so that the DBA cannot select data from business tables or views. Then the next thought is to limit PL/SQL procedures for the same reason so that they cannot run business code and indirectly access data. The permissions on data are not very granular; they are at the table, view procedure level. This is why Oracle brought out tools such as Virtual Private database to limit data by row or column or combinations if you care to do the setup and write the rules. It is possible to also do a lot with the old fashioned methods of using views and triggers and PL/SQL code to limit and enforce rules but these can be by passed if you can access the tables directly for select and not via the views that have had the security implemented in them. Of course Oracle also has Label Security, TSDP etc.

Then there is Database Vault which can be used to protect data, commands and roles via realms, command rules and secure application roles. The de-facto position is to block so called SYSTEM ANY rights and this is the next logical thing to mention for DBAs they tend to get the DBA role, The IMP_FULL_DATABASE or DATAPUMP versions or the CATALOG roles or worse the SYSDBA privilege. Included in all of this mess is usually most SYSTEM ANY rights. This includes SELECT ANY TABLE so the DBA can bypass the direct grants that he doesn't have now against business data or business code and access tables, views and code again. Now Database Vault out of the box stops the SYSTEM ANY rights when a realm is made around data. This is fine BUT as i said in my presentation "You don't have database vault; what can you do instead?" a simpler fix is possible without buying database vault. That is do not have the SYSTEM ANY grants in the first place.

Why does a DBA need SYSTEM ANY grants? in general for three main reasons:

  1. Access to data to trouble shoot application issues: this is the fundamental issue we are trying to resolve. the DBA should not have these ANY rights and if the DBA needs to trouble shoot then create a role with the specific direct grants needed and grant it only when completing that task.

  2. Access to deploy code/applications:This is normally because the DBA is also the release team and they then have the choice of connecting to the schema and running code that says create table ... or connecting as a DBA with CREATE ANY like rights and running create schema.table.... There is no need for the DBA to connect direct to the schema or to have CREATE ANY rights. The better approach is to use proxy and connect as the DBA account and proxy to the schema. In this way we can also have a log on trigger that executes AUDIT ALL IN SESSION CURRENT and AUDIT ALL STATEMENTS IN SESSION CURRENT and this will create a log of all actions as the DBA as a proxy to the schema during a release.

  3. Create other objects or changes: For instance create a directory object in another schema. The approach here is to create these objects once and a good approach is to isolate these rights into a shared security manager and access this via proxy - so no direct logins - and audit in the same way

So already it is complex and we have not talked about sneakily accessing data via lots of other means:

  • Select code and data embedded in it from the SGA; i.e. v$SQL, v$SQLAREA et al plus all of the other methods such as via stats pack or tuning pack or Quest products....the list is very big of possible ways

  • Use tools such as Log Miner, CDC, Streams, Goldengate to extract data from redo or archive logs

  • Dump data with ALTER SESSION and ALTER SYSTEM in many formats and ways

  • Access data via export files

  • Access data files

  • The list goes on to many many ways to get at data without selecting it from tables

There are then more subtle ways to read data outside of the areas we have just discussed:

  • Utilise ALTER USER, change the SYS password and connect as SYS and read data!

  • Do SQL Injection as a database user on vulnerable business logic written in PL/SQL and then use that avenue to read data!

  • Utilise CREATE ANY PROCEDURE and overwrite the F procedure of APEX (its usually installed in my experience) and steal the ALTER USER privilege of APEX and change the SYS password, log in as SYS and read the data!

  • There are many, many more ways...

The Real Problem; What is a DBA and what can he do:

All of this leads us to the conclusion that we need to solve two problems:

  1. What is the DBA and what should he be allowed to do (in the database or on the server)?

  2. What data are we protecting and just how many ways are there to access it?

So Dans question of how to protect access to V$SQL and V$SQL_BIND_CAPTURE is a good one but it must be extended to include all possible ways that data can be read outside of normal select statements but firstly it must be defined what is the DBA, what access should he have and for what purposes and when. In my view all DBAs should have their own accounts in the database and they must not use any built in Oracle roles such as DBA or the IMPORT roles or grants of all rights or SYSDBA. A DBA account must be designed for purpose and to design for purpose we must understand what the DBA is allowed to do and then we can start to make plans to limit access to only that which is necessary.

At a simple level that would mean no direct grants on tables, views, procedures etc, no SYSTEM ANY rights including DML and DDL rights. No rights to bypass the restrictions in place; i.e. Dans example of no access to V$SQL but also no access to GRANT roles, no access to ALTER USER and many more as we have stated these could be used to bypass the rules already in place.

This is an oxymoron waiting to happen though.

As soon as we start to limit some rights that clearly also have DBA like needs then we have problems. For instance ALTER USER is a good example. A DBA would need access to this BUT we would not want the DBA accessing SYS or SYSTEM on a daily basis and we may limit their access so we would need to also add a DDL trigger that stops the DBA from changing the SYS or SYSTEM password. This then brings in a next layer. We also dont want the DBA to be able to disable the DDL trigger so he should not have ALTER TRIGGER and ADMINISTER DATABASE TRIGGER; that means our DDL trigger must be a database wide system trigger so he needs ADMINISTER DATABASE TRIGGER to disable it so we dont grant this to the DBA. But also the parameter _system_trig_enabled could be turned off preventing system triggers to fire so the DBA should not have ALTER SYSTEM or access to stop and start the database and edit the parameters file. Stopping him have ALTER SYSTEM would be an issue; stopping him getting to the box on a daily basis to edit files can be managed.

12c brought LOCK DOWN PROFILES and this is a fantastic tool to limit things like ALTER SYSTEM in a fine grained way. So we could stop him turning on trace and stop him setting the _system_trig_enabled parameter.

We can also use layered automated security; we can test all these key elements in a regular way externally so that we can determine if the security of the database has been compromised. PFCLScan is a good tool to allow you to monitor this. A video showing a quick demo of PFCLScan is here.

We could even go as far as automatically re-applying the security if it is found to have been reduced. This can be tricky and needs to be managed properly but this is the start of the path towards adaptive security and we can also do adaptive auditing.. i.e. we can have a database go from defcon 5 to defcon 1.

Other Issues:

So far it seems complex and it gets worse. We also have since 12c the problem of multitenant databases - i say problem purely in the sense that we now need to manage at least two databases the CDB and the PDB; where it works out well is when we have multiple PDB in a single CDB; then the administration can be centralised. This brings in the issue of COMMON or LOCAL users, privileges, profiles and the side issue of COMMON grants to LOCAL and LOCAL grants to COMMON.

In this scenario of stopping the DBA team looking at data then in one sense having a centralised DBA (a COMMON user) makes sense and indeed Oracle provides some tools to assist in fine grained security around this. So imagine that the COMMON DBA only accesses the CDB and looks at data in V$SQL from the CDB then we can use CONTAINER_DATA to limit access to individual PDBs and also views within those. So we can stop the DBA from viewing the contents of V$SQL from the CDB BUT we have the secondary problem that the DBA is a COMMON user and we would then need to stop him accessing the PDB directly. This could be achieved via lack of CREATE SESSION for instance BUT as a DBA he may need to be in the PDB at some point to do something.

Clearly if a DBA has his own account and its not SYSDBA we can probably limit his access to data with some careful design and also layered security such as DDL triggers - but not limited to those - and maybe tools like Database vault if you have licensed it. Also we must make good use of audit trails to capture all actions performed by the DBA. We must also accept that in some circumstances he/she may need to be logged in as SYSDBA BUT not all day every day; and we should audit that access and actions and control it with proper processes.

Securing Oracle properly and limiting access to a DBA can also use tools like VPD, OLD and of course Database Vault but we can do a lot with normal rights and ideas and audit trails and of course management of people and processes and knowing what people are supposed to do and not do.

We need to analyse privileges of all users and they must be least rights for all jobs and that includes the DBA. Oracles Privilege Analysis is now free for 18c and above and back ported to 12c so thats a useful tool. You can also use all of my scripts, who_has_priv.sql, who_can_access.sql and find_all_privs.sql available on my Oracle security tools page.

Limit core grants such as Oracle roles, ANY rights, SELECT ANY DICTIONARY and more. We clearly need lots of thought and careful design and need layered security. I did a post a while ago about super locking an Oracle database and this post enhances that post in part.

I will return to this discussion as time goes on here in this blog as i have just created a new 18c XE database to form the new basis of my training sample database - most of the cost options such as Database Vault are available for free in 18c XE - and I have started to create a simple application (A CRM) as a sample for my training classes and I am creating a new two or possibly three day class where I want to super lock a database as a step by step process and show whats possible in a short period of time utilising many many layers and tools and techniques in an Oracle database.

This would be great as a follow along (hands on) class as i plan to use XE and everyone can download and use it.

More later on this and if anyone is interested to register an interest in attending the first sitting of this class later this year contact me via email or my social media accounts

Oracle Security Training in London with Oracle University

I have just agreed some training dates with Oracle University in London and I will be teaching my very popular two day class How to Perform a security audit of an Oracle database on the 29th and 30th April 2019. This class teaches you how to secure data in all of your Oracle databases. The class covers Oracle 11g, 12.1c, 12.2c and 18c. The focus of the class is to look at why data is insecure and how people can steal and hack it and then to use the vehicle of an audit of an Oracle database to walk through the process of assessing all aspects of an Oracle database for security issues and also discussing solutions as we go.

The class includes a download of a lot real world useful tools and scripts that you can take away and use in your own security assessments of your own Oracle databases.

I made a video of the outline of the class a while ago and this is a good 5 minute discussion of what to expect in the class.

The details from Oracle to enable you to register for this class are here.

I will also be teaching my one day class "Hardening and securing Oracle" on the 1st May also at the same venue with Oracle in London so it will be possible to take both classes as a 3 day event. I will have the registration details for the 3rd day soon from Oracle.

I look forwards to meeting everyone in London in April and being able to teach and discuss securing data in your Oracle databases.