PeteFinnigan.com Limited Products, Services, Training and Information

Security

Home/Security

Adaptive Security in an Oracle Database

This is my presentation to the UKOUG last December on Adaptive Security in an Oracle Database.

Slide 1 - Adaptive Security in an Oracle Database

Slide 2 - Legal notice

Adaptive Security in an Oracle Database

Published by
PeteFinnigan.com Limited
Tower Court
3 Oakdale Road
York
England, YO30 4XL

Copyright © 2025 by PeteFinnigan.com Limited

No part of this publication may be stored in a retrieval system, reproduced or transmitted in any form by any means, electronic, mechanical, photocopying, scanning, recording, or otherwise except as permitted by local statutory law, without the prior written permission of the publisher. In particular this material may not be used to provide training of any type or method. This material may not be translated into any other language or used in any translated form to provide training. Requests for permission should be addressed to the above registered address of PeteFinnigan.com Limited in writing.

Limit of Liability / Disclaimer of warranty. This information contained in this course and this material is distributed on an “as-is” basis without warranty. Whilst every precaution has been taken in the preparation of this material, neither the author nor the publisher shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the instructions or guidance contained within this course.

TradeMarks. Many of the designations used by manufacturers and resellers to distinguish their products are claimed as trademarks. Linux is a trademark of Linus Torvalds, Oracle is a trademark of Oracle Corporation. All other trademarks are the property of their respective owners. All other product names or services identified throughout the course material are used in an editorial fashion only and for the benefit of such companies with no intention of infringement of the trademark. No such use, or the use of any trade name, is intended to convey endorsement or other affiliation with this course.

Slide 3 - Background

Pete Finnigan – Background, Who Am I?

Oracle Security specialist and researcher
CEO and founder of PeteFinnigan.com Limited in February 2003
Writer of the longest running Oracle security blog
Author of the Oracle Security step-by-step guide and “Oracle Expert Practices”, “Oracle Incident Response and Forensics” books
Oracle ACE for security
Member of the OakTable, SYM 42
Speaker at various conferences UKOUG, PSOUG, BlackHat, more..
Published many times, see http://www.petefinnigan.com for links

Slide 4 - Agenda

A bit of history
Designing Audit Trails
We use a toolkit
Simple Policy based audit
Adaptive Audit
Adaptive Security

Slide 5 - A bit of History

A bit of History

Slide 6 - History of the Audit Events, Toolkit and Talks

In 2009 piece of work to help design audit trails
Site had limited staff, little time to design, deploy, maintain any audit trails
I came up with some simple ideas, proof of concepts – to package up audit trails for them; inc policy based audit, IPS and simple firewall
They spent limited time to deploy a useful audit trail
Similar piece of work in 2011 where limited team needed to deploy audit
2012 to 2015 extended the toolkit
I wrote a presentation back in 2012 and presented it just once at a SIG on practical audit trails where I mentioned this toolkit for the first time
This then became the basis of a one day class on the same subject
Reworked that presentation in UKOUG 2015 conference
Customer in 2016 needed an audit trail to deploy quickly
Deployed now to customers in UK, Ireland and Germany
The ideas of audit design came from these pieces of work and talks

Slide 7 - Adaptive

We must have audit to create adaptive audit
We must have security to create adaptive security
We most likely need audit to create adaptive security
Audit is the key element

Slide 8 - Designing Audit Trails

Designing Audit Trails

Slide 9 - Some People Think They Have Designed Audit

I see sites with some audit settings
This is not a WELL DESIGNED audit trail
Usually these random set of parameters in the Oracle database will not catch a good range of events that could be an attack
Some sites have application audit and OS audit
BUT worse; lots of sites have no audit at the database engine level
If there is a breach a lack of audit makes forensic response very difficult

Slide 10 - Design

Before we get started implementing audit trails
Design must be the first step
The final chosen solution implements the design
Therefore until you know the design you cannot specify the right solution – right?
The solution could be “free” or “commercial” solution or even a combination of both – we use our toolkit
Often people buy third party products and implement out of the box with no internal requirements!

Slide 11 - Create Audit Events

Create audit events based on “I Want to know?”

Slide 12 - Build On The Audit Events

Work backwards from the events to decide what raw audit to collect
Then how to work out if event has occurred
Then how to report
Then how to alert
Then how to escalate
When you have this “table” decide on the technical solution that can be implemented and deployed

Slide 13 - Audit Trail Toolkit

Audit Trail Toolkit

Slide 14 - The Goal of the Toolkit

As simple as SQL> @atk and a sophisticated audit trail is up and running
Making it simple for organisations to deploy audit trails simply, with no resources
No design, implement, test etc as we have done it for you already
Used in ATC mode – space also is managed in each target database audited
Simple to configure or not configured at all
A complete solution to know what is happening at the database engine level for sites with limited resources

Slide 15 - PFCLATK – “A”udit “T”rail tool”K”it

Toolkit to aid audit trail deployment easily
Simple pre-configure
Policy based
Alert based
Multiple audit trails sources
Add in factors (input hints)
Separated schema design
Manual 27 pages currently
Version 2.4.0.0 currently
Layered audit

Slide 16 - Elements of Design - Core

The core features of our audit design are
Policies and events (There are 20 policies and 17 events)
Polled jobs and reports as checks (There are 7 job intervals)
Core PL/SQL API Package
Rules/policy/jobs/payload meta data
Audit trails specific to PFCLATK
System Triggers
DDL triggers
DML triggers
PL/SQL based
Checks can be made via polled jobs to test if the audit is valid
Raw data is collected, filters are jobs polled via DBMS_SCHEDULER; results added to alerts and alerts generates escalation

Can be deployed to multiple databases and a central database

Slide 17 - Factors

Some factors are re-defined, some should be edited and more can be added easily
Factors allow the toolkit to be customised for a specific site

Slide 18 - Audit Policies

Policies declare collection of raw data and also events
PFCLATK policies are different to Unified audit – we filter on collected data after storage to look for abuse; Unified audit filters before storage
Core audit, DML, System triggers

Slide 19 - Audit of Audit

A multi-layer approach is needed
Audit of core trail tables such as AUD$
Audit of core audit settings such as AUDIT$
Audit of triggers (Event, DDL and DML)
Audit of custom logs
Audit of audit functionality, packages and other objects
All can be set up as policies in PFCLATK

Slide 20 - Simple Policy Based Audit Trails

Simple Policy Based Audit Trails

Slide 21 - Policies I Will Implement

Policies I Will Implement

Slide 22 - Install the Audit Events and Toolkit

Install the Audit Events and Toolkit

Slide 23 - Some Audit Results From Hacks

Some Audit Results From Hacks

Slide 24 - Audit Trail Report

We catch the NOAUDIT
We catch the password changes
We catch all errors, 942, 1789, 907, 911 etc
We catch the creation of the hack procedure
All events are not caught 100% but the attack was not based on the audit design!!

Slide 25 - Adaptive Audit

Adaptive Audit

Slide 26 - Adaptive Audit

Change the audit trail based on security at the time
Based on a real attack happening – in progress – or a perceived attack happening the audit capture can be increased
This, as with adaptive security would be reduced after the perceived attack
Adaptive Audit is useful because in normal circumstances we would not want to collect excessive audit trails but during an attack we may do so.
For example if the application schema has limited audit normally we increase to audit all access during an attack
These ideas need to be designed carefully to ensure that the audit does not become a ”Denial Of Service”
Example
A schema changes its password from the web server; so audit all actions in the session or audit all actions in the schema by all users

Slide 27 - What is Conditional Audit?

Audit would be more powerful if conditionally applied
Oracles Fine Grained Audit (FGA) attests to this
Oracles Unified Audit from 12c also attests to this but still is underpinned by core audit facilities
Trigger based security can use some element of conditionality
When clause can be used to limit the trigger body to certain conditions such as time based, user based privilege based and SYS_CONTEXT() based and more
OF clause can limit a trigger to certain columns BUT has little value in conditional audit other than protection – see integrity
A trigger body is PL/SQL so any conditional audit can be programmed in
Secure contexts can be used to control granularity in triggers, audit functions and more across the database – set up at logon
Conditional security in core audit is not feasible BUT
We can process the records records after collection via reports/jobs
This is in essence what unified audit is doing

Slide 28 - Adaptive Security

Adaptive Security

Slide 29 - Adaptive Security

Adaptive security is not part of audit trail design but adaptive security could be controlled by audit results and reports
Create multiple levels of security i.e. defcon5, 4, 3, 2,1
Adaptive security is where
Security is changed dynamically based on a real attack happening or perceived real attack happening
Normally this would be to tighten security to defend the database because of the current attack
After an attack the reverse could normally occur to remove the additional restrictions
Examples
Detect change of a password and lock the user whose password was changed
Detect change of audit by a schema (user) and kill the user doing it
These can be implemented in triggers as VP/IPS or specific payloads as jobs

No one is doing this BUT WE COULD
The ultimate – one database is being attacked and it “tells” all others to change audit and security

Slide 30 - Conclusions