Pete Finnigan's Oracle Security Weblog

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 11.2.0.4 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@//192.168.56.78:1523/xe as sysdba

SQL*Plus: Release 11.2.0.3.0 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 18.0.0.0.0 - Production

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

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

SQL> select dbid from v$database;

      DBID
----------
2883395441

SQL> 

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;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
XEPDB1

SQL> select dbid from v$database;

      DBID
----------
2883395441

SQL> 

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;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
XEPDB1

SQL> select dbid,con_dbid from v$database;

      DBID   CON_DBID
---------- ----------
2883395441 1392023073

SQL> 

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;

      DBID   CON_DBID
---------- ----------
2883395441 2883395441

SQL> 

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.