PeteFinnigan.com Limited Products, Services, Training and Information

Design

Home/Design

A Design Pattern To Secure Your Database

This is a presentation I have at the UKOUG conference in Birmingham in 2025. This discusses how you might protect data in your Oracle database using standard features of the database.

Slide 1 - A Design Pattern To Secure Your Database

Slide 2 - Legal notice

A Design Pattern to Secure Your 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

Background
A pattern
How does AUDSYS.AUD$UNIFIED work
Design a ReadOnly table
Implement the design
Add more features
Extend the design

Slide 5 - Background

Slide 6 - Design Pattern

We can use a design pattern to secure data
What is a design pattern in this context
We want to use a consistent model to secure data in the same way across the application / database
Ideally generate based on parameters
We want to use the same model base and change based on requirements or what we are protecting
Add remove features
Allow different access paths

Slide 7 - Standard Privileges

As a standard data is held in tables and we can grant
SELECT or READ to read data
INSERT to add data
UPDATE to change data
DELETE to delete data
This is not very granular
The owner can access all the data
Any direct grants can access the data
Users with “ANY” rights can access the data
No limits such as only on Tuesday from machine A or IP address xx.xx.xx.xx or …

Slide 8 - Cost Options

We can use cost options such as Database Vault (DV) or Virtual Private Database (VPD) to achieve similar security
Using a realm around our data
This allows access to our data for direct grants and owner
Blocks sweeping “ANY” access by default
Can use VPD to limit data actions based on rules/policy
Can use DV command rules to further limit

Slide 9 - Cannot Use Cost Options

If you use Standard Edition (SE) or on EE and do not want to purchase DV then different solutions needed
Database Vault and Virtual Private Database are built into the SQL engine at a low level and are much harder to bypass
The solutions using DV and VPD are much more succinct to set up
What I want to is see if we can provide similar security to DV and VPD or indeed Oracles hard coded internal solutions by just using standard features of the database to lock down data access
I want to stop the DBA or others accessing the data or thwart any attempts to bypass the security

Slide 10 - A Pattern

Slide 11 - A Pattern

A pattern design can be used to protect data or other things such as a privilege or external interface or more
I saw that Oracle protects AUDSYS and UNIFIED_AUDIT_TRAIL and state that this data is READ ONLY
This is not strictly true as
INSERT is allowed
In some circumstances DELETE – to purge audit
No UPDATE is allowed
I want to use the ideas of the unified audit trail as a pattern to follow but without DV or VPD or Oracles own internal code

Slide 12 - Normal Privileges are Not Enough

Standard simple privileges on a table are not enough
A DBA or other grantees can use sweeping rights to bypass direct grants with
SELECT/READ ANY TABLE
INSERT/DELETE/UPATE ANY TABLE can be used to “guess” data
A DBA or other grantees can add grants
GRANT themselves access to a table
A DBA or other grantees can remove or disable protections that we add

Slide 13 - Multiple Patterns / Models

Multiple models can be created, we will choose one as an example
The same ideas I present can be used to create different models
i.e. protect access directly to a table without a view
Extend the ideas to other things like protect a privilege such as ALTER USER
We can protect existing applications and even third party code
Ensure upgrades are not broken. i.e. put back scripts and re-apply scripts

Slide 14 - AUDSYS.AUD$UNIFIED

Slide 15 - How Does AUDSYS.AUD$UNIFIED Work?

We will use AUDSYS.AUD$UNIFIED as an example
Core table that holds Oracle Unified Audit data
Sql text and binds are stored in a secure file
The table is partitioned even though we do not have a license
SYS creates the table in the AUDSYS schema
No one can log into the AUDSYS user
The AUD$UNIFIED table is READ ONLY; not true as
The C code allows INSERT bypassing database permissions
DBMS_AUDIT_MGMT can be used to purge by DELETING records
Limited fields due to the syslog RFC line length are written to SYSLOG
If an attacker manages to defeat the protection SYSLOG can be used to verify data in the table

Slide 16 - Cannot Connect to AUDSYS

Cannot unlock and add a password to allow access
Cannot proxy
Because Oracle do not want us to connect we have to assume we could change data if we did?
AUDSYS is dictionary protected so “ANY” privileges are blocked
Granting SELECT ANY DICTIONARY does allow access

Slide 17 - Cannot Change AUDSYS.AUD$UNIFIED

We cannot truncate, delete or update or insert into the table
The error is not standard
We want to achieve the same

Slide 18 - How Does Oracle Protect AUDSYS.AUD$UNIFIED?

Reported on my Twitter: Oracle uses a flag in memory in the SGA that can be changed via gdb. The flag is not exposed by X$ views

DML results in ORA-12081
But we want INSERT and occasional DELETE
Could create a view “WITH READ ONLY” but we would need INSERT/DELETE via the base table
Could also use a READONLY tablespace BUT same issue

Clearly stops DML but it would not work for this model as we want INSERT and some DELETE
Might be possible to create constraint rules that allow SELECT and INSERT but block DELETE and UPDATE
Other options
Triggers, VPD, Label, RAS

Slide 19 - Design a Read Only Table

Slide 20 - Build a READONLY Table like AUD$UNIFIED

Our solution is not perfect, better than nothing
Access to SYSDBA or DBA could removed it
Enough layers make it harder and we should audit

Slide 21 - Security in the Design

Slide 22 - High Level Design Sketch

Slide 23 - Implement the Design

Slide 24 - Implement the Solution

Create a user, no create session
Create a table and data
Create a read only view

Slide 25 - Build Security

Create a ROLE to allow read access
Stop connecting via schema
Stop password change or allowing proxy
Didn’t really need the logon trigger if we cannot add a password

Slide 26 - Protect Data

Create the API
Allow INSERT and DELETE only via the API
Prevent CREATE on the schema

Slide 27 - Add More Security

Prevent Grants
Prevent DROP
Prevent Truncate
Audit what happens

Slide 28 - Testing

Slide 29 - Testing Continued

Slide 30 - Add More Features

Slide 31 - Can We Block EXECUTE ANY PROCEDURE?

We need to know the name of the caller
We need to know if they have been granted EXECUTE ANY PROCEDURE
Grant a role ATK_ROLE
Test for this role granted when procedure executed
Need to prevent granting of ATK_ROLE

Slide 32 - Detect Disable Trigger

A lot of security is built around triggers
We must stop them being disabled

Slide 33 - Example Summary

It is much more complex to use standard features than DV or VPD or…
We need multiple layers to implement the security
We need security of the security to prevent anyone bypassing it
We could even have security of security of security
Cleary it would be useful to script all of this
We can also rationalise

Slide 34 - What is Missing

What improvements can we make?
As we use system triggers (LOGON, DDL, etc) then these can be stopped from firing by setting _system_trig_enabled parameter
Changing parameters does not fire an ALTER trigger
We could use lockdown profiles to limit this
Also ensure that the lockdown profile cannot be reset
Also ensure lock down profile privileges are not available
We can use the same ideas on existing products/applications code and tables
Ensure upgrades and re-apply are available
Trap GRANTS to prevent bypass via grants
Audit everything

Slide 35 - Extend the Design

Slide 36 - API: Issues / Items to fix with this model

The areas to secure can include
Access to system privileges such as ALTER USER
Access to any DDL
Access to dangerous logic provided by Oracle; network API, File API, jobs, encryption
Access to Java or C
Access to vulnerable packages fixed in security patches
Access to Undocumented packages
Access to Internal packages
Access to deprecated packages
Access to dynamic packages – ones that use dynamic code

Slide 37 - API: As a Design Pattern

Identify the dangerous thing. i.e. use of ALTER USER…
Create a separate schema to own the PL/SQL API that allows access
Create the PL/SQL API
Grant the privilege or create access to the resource to the API schema
Create an API to limit how that privilege is used – i.e. enforce least use / rights
Choose carefully definer / invoker and consider CBAC / Inherit
Grant just the API to the other users who need access to the dangerous rights
Add context based controls
The accessible by clause from 12c or who_called_me before that

There are differences dependent on what we protect.
We can include a DDL trigger for ALTER USER but not use of a link

Slide 38 - API: As a Design Pattern (2)

Add additional controls to the schema to prevent bypass
Password locked, schema only, limit with DDL triggers, logon triggers…
Filter inputs, white list and black list
We can also include a prolog / epilog – see later slides – to filter and protect access to the API
The API should include
The best of all PL/SQL secure coding
No comments
Obfuscate / wrap
Separate the common security code into a separate package
This allows template approach
The developers don’t get to see the security code details

Audit or log all access to the API or changes to the API

Slide 39 - Using Resources Securely

Resources such as DIRECTORY, Links, Libraries etc present a problem in how to securely use them – the API model can be used
Create dynamically
This means they “live” for a short period of time but the owner has the rights to create many of them
The access rights need to be built and torn down
The resource can be closed
Multi-threaded access is a mess – DB is multi-access
Statically pre-create
The owner does not need rights to create
The resource “lives” continuously
An access API can exist but could be bypassed
Limit access to the schema, lock, login trigger

Slide 40 - Conclusions