Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Auditing Traps

Don Burleson


Burleson is the co-author of the bestselling book "Oracle Privacy Security Auditing: Includes Federal Law Compliance with HIPAA, Sarbanes-Oxley & The Gramm-Leach-Bliley Act GLB", by Rampant TechPress.

The Auditing Traps

The common auditing traps are well-known in the IT security field and are taught in almost every business school in the USA.  Given the wide knowledge of these exposures it is surprising how often they are disregarded by the IT manager.

·        Cover all layers - Protecting the application layer is only part of the solution.  A comprehensive auditing solution must check for access at the web cache layer (cached HTML data), application server caches, database caches and backdoors, and access violations at the server level. 

·        Exclude privileged users - Another common trap is allowing privileged employees to bypass security and audit mechanisms.  Your Systems Administrator and DBA have no business touching your auditing mechanism, and while they may be responsible for the integrity of the data, a third-party must be used to perform all auditing collection, administration and reporting duties.  There have been many serious lawsuits where a dishonest DBA entered a database and changed financial data, disclosed confidential information and violated Federal data access regulations.

·        Not knowing how it happened –Finding a security violation and never being able to determine the cause creates a huge legal liability for any corporation, and this is very common among IT shops that choose to use piecemeal solutions to their privacy and auditing mechanism. In one case, a user was found to be committing fraud, but without an audit trail of exactly what transpired the organization had no way of understanding the scope of the damage.

·        Non-uniform audit rules –Another of the common traps is applying different rules to auditing of different systems.  This is often the result of the limitations of the application code.  For example, you may be able to add a complete auditing solution to the system that was developed in-house, but you do not have the same luxury when using an ERP product (SAP. PeopleSoft) because you cannot touch the application code.

With all of these exposures and threats the savvy IT manager must be able to cover themselves from even the most unlikely scenario.  Here are some of the common ways that the IT manager ensures that they have a compliant, robust and comprehensive solution.

Segregation of Auditing Duties

While general security and auditing are passive activities, a comprehensive solution to auditing requires real-time reporting of active attempts to bypass security.  Remember, smart shops close all back-door data access (e.g. ODBC) and enforce data access via the application layer.  However, we must still create an alert mechanism for all data access attempts at all layers, whether malicious or benign. 

This segregation of duties is critical because it is considered malfeasance to give the "Keys to the Kingdom" to anyone charged with maintaining the servers and databases.  In many cases disgruntled employees may view confidential information for personal gain and sometimes create mechanisms to disclose the information if they are terminated from employment (see horror stories later in this paper).

Now, let's change focus and examine how the IT manager can satisfy their due diligence requirements while satisfying their auditing challenges.

Security, Privacy and Auditing Misconceptions

After interviewing dozens of IT managers, a common set of misconceptions arise about compliance with Federal regulations for security and privacy.  This is not surprising, given that the legalese of the actual laws is almost indecipherable, but the IT manager needs to know about the realities of these important new laws. Some of the most common misconceptions include:

·        Prevention alone is sufficient –Traditional security measures focused on "perimeter" security (e.g. firewalls) are an important component of mitigating the risks of inappropriate data access or changes.  But with most error and fraud occurring from within the organization, it's important to have the ability to understand exactly what is happening to the data .  A complete record of data access and change provides this "detective" capability which augments existing security.

·        Application access, privilege controls and logging are enough - This is a very serious misconception because it ignores the other important access areas.  As we see, all data access must be audited directly at the data source. 

·        Preventing fraud is the only goal - Many IT managers fail to account for the possibility of human error, which is more prevalent than fraud, in their auditing plan.  A comprehensive solution must account for legitimate errors by end-users and IT staff.

Now that we see the common misconceptions let's examine the importance of auditing data at the data source, the database management layer.

Auditing Data at the Source

All IT managers know that simple triggers and code extensions can be used to enforce security and privacy at the application layer.  The real problem is securing the data source and all intermediate repositories and providing the ability to understand the root cause of the violation.

There are many challenges involved with auditing at the database level.  If you want to understand "how" a violation happens, you must audit all events of interest,.  These events include privileged access by IT personnel, the auditing of all changes to the data, auditing all viewing of confidential data, and recording all changes to the database infrastructure, both by DDL and changes to executable database procedures.  Let's take a close look at each auditing requirement.

Auditing Privilege/permission and Logon events

You must have a complete record of the users who have data access including; "who" is attempting to get it; "what" they have rights to do with the data, "why" they are changing the data, and "when" the data was viewed or changed. 

While many databases such as Oracle provide primitive logon triggers for determining logon events, they don't work with many modern ERP products (SAP, Oracle Applications, PeopleSoft) because they use pre-spawned connections to the database.  User authentication and access management is done by the application server and the individual users are not exposed to the database.

The "who" aspect of data auditing can be confounded if you use a tool such as SAP or Oracle applications that pre-spawns anonymous connections to Oracle.  The application controls user access and authenticated users are directed into the database under the control of the application (Figure 8).









Figure 8 - External application authentication

In these types of architectures an end-user has no direct privilege against the data source and the permission to view and access data is granted via the application.  Because the application controls all database access, you don't have to be concerned about back-door access with non-application interfaces such as Crystal Reports or ODBC.

 However, it is critical to audit the activity of privileged users, including DBAs, who have direct access to the database and can access or modify the application's underlying data.

Get the Oracle auditing book, click here

For an excellent Oracle auditing product, click here



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.