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.

Adaptive Database Auditing and Security

We are working with customers to design security for their Oracle databases and also to help and design audit trails.

An audit trail is the easiest countermeasure or control that can be added to a database because if you do not have any audit trails then you don't know what is happening in the database. We have a audit toolkit called PFCLATK that can be used to design and implement audit trails really easily and quickly.

This toolkit and service is a set of PL/SQL and meta data that allows policy based audit (not the same as Unified audit) to be added. We can also use it with Unified audit or syslog as well. We combine different sources with audit and different policies and events. The toolkit is policy based and each policy is split in two. The policy includes what raw audit to collect and then how to test if an audit event has come true. So, an example could be:

@@check.sql "CORE: Create EVE.1.10 Policy"
-- ---------------------------------------------------------------------------
-- create the policy
-- ---------------------------------------------------------------------------
-- ---------------------------------------------------------------------------
-- create the rules to collect data
-- ---------------------------------------------------------------------------
pfclatk.createandaddrule('EVE.1.10','EVE.1.10:All Statements by special user','CORE-P','all statements',pfclatk.getfactor('SPECIAL-USER'));
-- ---------------------------------------------------------------------------
-- create the filter
-- ---------------------------------------------------------------------------
'[Alert] A Special user has performed actions',
'select ''{''||a.action_name||''} used by {''||a.username||''} using IP {''||p.ip_address||''} with error code {''||a.returncode||''}'' from dba_audit_trail a, pfclatk_audit p where a.username in ('||pfclatk.getfactorc('SPECIAL-USER')||') and a.sessionid=p.sessionid and p.audit_type=''LOGON''');
-- ---------------------------------------------------------------------------
-- enable the policy
-- ---------------------------------------------------------------------------

In this example the policy is created and is aimed at detecting if a defined special user has connected and then all statements that they execute. You can specify what a special user is. It could be a performance monitoring user or a third party connection user or what ever you choose. The policy is split into two sections; the first creates the collection rules in this case a logon trigger and also all statements. You specify the special users with factors that are used here. An example factor is:



The second half of the policy specifies the event filter. This checks if the event has occurred and this can be run on specified intervals such as ever few minutes, half a day or once a day or...

The PFCLATK toolkit takes care of running the filters. Enabling or disabling a policy turns on the lower level audit or trigger or....

So, this toolkit and service is great as we can help customers get a useful audit trail up and running very fast so if you have not got an audit trail and do not know who and what is going on in your database talk to us so that we can help you reduce your risk and worry.

So, back to the main part of the story. If we have a simple to use and comprehensive audit trail where we can easily specify what we want to audit and we can easily enable and disable individual events then this fact alone allows us to design and implement adaptive audit trails. Everyone (even us non Americans) have heard of the DEFCON levels, where DEFCON 5 is relatively safe and DEFCONE 1 could mean the outbreak of nuclear war. The USA has never been at DEFCON 1 as far as I know but was at DEFCON 2 during the Cuban missile crisis. We can imagine similar levels of audit trail in a database.

Audit level 5 would mean its safe, no sign of attacks and we should capture lighter audit. The heavier the audit the more audit data is created and then its more storage and more processing and more costs. So imagine that we are at AUDIT LEVEL 5 and we detect a lower level of attack signs. We can then increase the AUDIT LEVEL to 4 and turn on or enable more policies that capture more audit data. We can go right up to AUDIT LEVEL 1. Imagine that we know there is an attack we can then audit everything at AUDIT LEVEL 1. This would be onerous normally but necessary if we know there is a major issue.

The key idea here is we create some AUDIT LEVEL policies that use the same type of audit rules and events/filters/factors as above and we use these to detect potential changes in AUDIT LEVELS. The toolkit can handle an alert being detected automatically and the payload can be to enable or disable audit. So we have an adaptive audit system that could be used if needed. I would recommend that you set up a comprehensive audit first and enable it and use it before thinking about adaptive audit of course.

Because we can create a policy based audit where we can use events to take actions this means that we could/can also implement adaptive security using the same techniques and triggers (not database triggers). We can have different levels of database security and use audit levels to trigger changes in the actual Oracle database security. Of course you should not even consider this until you have a good level of Oracle security implemented already. We could have a situation where the database runs with a lower level of security (I don't mean no security) and if there is an attack detected we can raise the security level. For instance under normal security we allow connections from most IP addresses and most applications but if we decided to raise the security level we may only allow access to the database from critical applications and block others.

We can also link these ideas of adaptive audit and adaptive security across the whole estate of databases. If one database detects a possible AUDIT LEVEL that could mean an attack then it can signal all or groups of databases and they could increase their audit level or security level.

This is a really interesting area and can be powerful to implement.

The challenges of securing data in an Oracle database

I will be doing a talk at an even in Eight Members Club Bank, 1 Change Alley, London,EC3V 3ND on the 14th June 2022. The event runs from 8am to 10am. The event is free to attend and to register go to - (broken link) Northdoor, the organisers website. I hope to see you there!!

In this talk I will use the Oracle database as the example as that is my area of focus and expertise but the talk is general to all databases so even if you use SQL Server or DB2 or Postgres or DB2 or... the ideas and discussions are just as valid for you to get something useful from it.

This talk is based on many years of working with customers to assess the security of the data held in their databases. Remember, our task is to secure the data we hold and process and to not just simply try and secure the database software itself; the problem space is more complex than that.

I will discuss the main threats to data held in an database from the complexity of the database stack; the lack of a strategy in most companies to secure data in a database and a lack of evidence of actual security of data in a database when I come and review it.

Legacy, default installations and the move to put databases in the cloud do not improve the picture of the core database security and data security itself. I will discuss why this picture persists across the industry in my experience and then the best part of the talk; what can companies do to make data more secure in an Oracle database and how to do it cost effectively in terms of time and money and use of or lack of skilled people.

I will expand on all of this at the talk in London on the 14th June. Please come along and list to myself and also Noam Markfeld who will speak about securing and masking sensitive data and masking.

To register please visit - (broken link) this link.