PeteFinnigan.com Limited Products, Services, Training and Information

Auditing

Home/Auditing

Auditing The Oracle Database

This is my presentation to the UKOUG last December on Auditing The Oracle Database.

Slide 1 - Auditing The Oracle Database

PFCATK – A Toolkit to Help

Slide 2 - Legal notice

Auditing The 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

Do people use Oracle audit trails?
A bit of history
The Focus - The PFCLATK toolkit
An overview
Deployment
Hacking
Audit results

Slide 5 - State of the (Audit Trail) Nation

Extensive experience visiting customer sites
Performing security audits
Reacting to incidents, breaches or attacks
I see one common theme
No audit trails OR
Very limited audit trails OR
Of those that do have audit trails very few use them interactively
Sometimes people collect audit because they have to (regs)
I have even seen some sites collect audit and delete it (regs)
How many people here have audit trails enabled in their database to audit the database? – i.e. not apps or data

Slide 6 - History of the 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

Slide 7 - My Sample Application Architecture

Oracle Linux
Oracle SE1 Database
Applications (Front Facing Website, back office customer processing)

Slide 8 - Demo Hacking

Demo:

Enable secconf.sql to get standard audit
Run audit.sql to see audit configuration
Test some SQL Injection as an attacker
SQL Injection attack as unauthenticated web user
SQL Injection attack as database user with just CREATE SESSION
Access data as a DBA with %ANY% rights
View audit trail generated

Slide 9 - 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 10 - 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 25 pages currently
Version 1.6.1.0 currently
Layered audit

Slide 11 - PFCLATK Block Overview

Free PL/SQL and SQL based toolkit – 14k lines of code.
Audit the database engine itself

Slide 12 - PFCLATC – “A”udit “T”oolkit “C”entralised

PFCLATK can be deployed to each “target Oracle Database”
PFCLATC is an additional layer to add centralisation of those audits automatically
Simple configuration to link each target with the central storage
Uses links and a PUL mechanism
Audit trails are check summed
Audit trails are PUL’d
Audit trails purged from the target
Manage target space needed for audit trails (limited by PUL)
The toolkit also audits the PFCLATC target (if required)
Central reporting possible over many databases

Slide 13 - PFCLATK Architecture

The PFCLATK toolkit is designed to be deployed to a target or central database
When enabled simply adding target link details to the ATC database starts the PUL process automatically

Slide 14 - Database Engine Audit

Sites often have application level audit trails
In the application layer itself
Sometime also in the database (RLA in Oracle E-Business Suite for instance)
Sites sometimes have audit enabled at the operating system level
Auditing of the database is often
Application related
Regulation related
Audit is needed at the database engine layer to capture abuse against the database itself

Slide 15 - Alerts

React in real time to attacks
SQL Injection
Privilege abuse
Error conditions
React in
Real time where possible
Semi-real time if not possible
Other reactions can be slower or not at all
Alerts are configured in policies along with raw audit collected – post filtering is more powerful than unified audit pre-filtering because we can filter across more domains

Slide 16 - Separated Schemas and Roles

Schemas
ATKD – The owner of tables, views, sequences
ATK – The owner of the main API. Also runs jobs that payloads and filters based on
Roles
ATK_ADMIN - Any user granted this role can set up PFCLATK rules, policies, jobs, filters, credentials and factors
ATK_REPORT – Any user granted this role can view the alerts and alert details and audit trail details

Slide 17 - Combine All Elements to Create a Design

Create a cohesive design and use all elements we have covered in the diagram
Work from a design/policy
Implement core designs to capture actions required
Then add security on the audit
Ensure the audit remains enabled or if disabled that action is captured and alerted and escalated
Solution should ideally be modular for easy implementation
The design should be simple though and that’s what we have encapsulated in PFCLATK

Slide 18 - 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

Slide 19 - Configuration

The user configurable settings are simple and at the top of atk.sql

Slide 20 - 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 21 - 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 22 - Alert Jobs

Alerts are jobs that run to parse the collected audit trails
Each policy can include raw collect and also alert jobs (filters)

Slide 23 - 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 24 - Prevent Delete of Audit

There is a danger that someone deletes audit or changes audit or disables triggers
Because we use multi-layer audit this is harder to achieve
Also we can be alerted to delete and changes and use tools such as LogMiner to get data back
The toolkit includes audit of audit, security of audit, audit of security of audit
Security of the tool, checksums, obfuscate, permissions and more

Slide 25 - Configure and Deploy

Edit atk.sql
Edit required settings needed for the toolkit
Edit conf.sql
Add connection details
Demo deployment
Run atk.sql

Demo: Configure and deploy

Slide 26 - PFCLATC

If we also would like to set up PFCLATK as a centralised audit then:
Edit atk.sql configuration section and make the script ATC
Deploy to a suitable centralised database
Use the crl.sql script to create a link from the PFCLATC database to each target database
The “PUL”ling of audit trails will now start from each target to the PFCLATC instance
The pul reports will show progress (p.sql, p2.sql, p3.sql and p4.sql)

Slide 27 - Demo Hacking

Demo:

All ATK policies are enabled

Test some SQL Injection as an attacker

SQL Injection attack as unauthenticated web user

SQL Injection attack as database user with just CREATE SESSION

Access data as a DBA with %ANY% rights

Slide 28 - Reports

A few sample reports exist that highlight issues
Audit_report.sql
Car.sql
Alerts are viewed via the ATKD.PFCLATK_ALERTS table
Alert details in ATKD.PFCLATK_ALERT_ROWS
tr.sql shows high level summary of alerts

Demo: look at the audit trails

Slide 29 - Extend the Toolkit

Two levels of extension are possible
1 – Areas of the database can be audited automatically
Policies can be created via SQL or PL/SQL for users
Policies can be created via SQL or PL/SQL for roles
These tools can be exposed as part of the PFCLATK toolkit
2 – New policies and rules can be defined
Policies can be defined for areas of an application
Policies can be defined for areas of the core database
New jobs can be added that generate raw audit trail data
New jobs can be defined as filters that generate alert records and alerts

Slide 30 - Issues and Future

Sys audit on some objects cannot be enabled other than as SYSDBA
Multitennant has similar issues
Standby databases cause issues with triggers
Extend to generate 12c unified audit policies
Enhancements and areas to extend can include
Parsing of listener log via SQL – PL/SQL
udit in other areas such as password function
Read the alert log, trace files
Add automated policy generation
Add internal security checks
Protection layers
Extend to generate FGA policies
More to do, clean up the code, refactor the code better
Reduce privileges needed as much as possible
Create a separate API needed by report user not the full API as now
Reduce the noise on the toolkit itself

Slide 31 - Availability

Get a copy?
Must be willing to deploy and try it as we would like feedback
Email pete@petefinnigan.com
Its not available as a general download
Is it free?
The PL/SQL API toolkit is currently free (not sure if it always will be)
Free does not mean GPL or similar toxic licenses
We reserve the right to make it commercial
Most likely we will add an admin dashboard (perhaps as part of PFCLScan, perhaps not) and reports and support will be available
We also do consultancy to design and deploy audit trails using this toolkit (already)

Slide 32 - Conclusions