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 at the database level

Oracle security Tips by Donald Burleson

For complete details on Oracle auditing, see my my book "Oracle Privacy Security Auditing", and you can buy Oracle at this link and get instant access to Oracle auditing scripts.

Auditing Oracle Data access at the Oracle Source


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


A ?detective? monitoring approach is used by many successful companies because Oracle allows you to know what actually happened when a breech occurs.  Simple auditing based only on preventative measures will not provide this level of insight.  Just like a human detective, the detective monitoring approach observes all aspects of Oracle data  access and keeps complete logs of all Oracle database activity.  To be fully safe and compliant, you must keep a complete audit trail for the Oracle data source and have a complete who, where, what, and when record of access and updates.


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

Auditing Privilege/Permission and Logon Events


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


While many Oracle 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 Oracle database.  User authentication and access management is done by the application server and the individual users are not exposed to the Oracle database.


The ?who? aspect of Oracle 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 Oracle 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 Oracle data source and the permission to view and access Oracle data is granted via the application.  Because the application controls all Oracle database access, you don't have to be concerned about back-door access with non-application interfaces such as Crystal Reports or ODBC.


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

Auditing DDL Events

Managing changes to the schema definition of your Oracle database is critical.  You must have a complete record of all changes to your Oracle database system infrastructure and understand the potential security risks associated with each change.  This includes knowing that a table has been dropped or permissions have been changed inappropriately. Many open source solutions such as SCCS are inadequate and many Oracle managers use third-party products designed specifically to track schema changes, such as Merant PVCS (Serena), Kintana and Oracle Software Configuration Manager. 


If the organization has a policy of placing everything in the version control system, the changes made are automatically recorded. But what happens when someone makes an emergency change without using the proper procedure? The setup fails. This is a classic case of a system where the integrity can be guaranteed only when everyone follows the rules and no one bypasses them.   

Auditing DML Events

All auditing solutions must track changes to any of the Oracle data items, right-down to the column level.  It's not enough to know that a particular financial record was changed; you must also know exactly what has changed in the Oracle data content.  This includes the access method (?how? Oracle was changed), the before and after values, and the exact time and user ID. 

Auditing SELECT Events

Many Federal regulations mandate that you keep a complete record of access to private and confidential information.  For large active Oracle databases, Oracle is not uncommon to have daily viewing logs that are larger than the whole Oracle database and you must be able to easily run reports against this huge volume of Oracle data.  For example, the new HIPAA regulations allow any medical patient to request to know who has accessed their Oracle data in the past, and this simple query might involve accessing trillions of bytes of audit information.   When you have multiple, simultaneous requests for these reports, an improperly designed audit system might become crippled under the weight of the Oracle data volume. 

Auditing Execution and Modification of Stored Procedures

Many Oracle database shops encapsulate their Oracle database access inside code snippets called 'stored procedures?.  When using stored procedures an end-user takes-on the privileges required to execute the procedure, but only for the duration of the execution of the procedure.  In Oracle databases such as Oracle, stored procedures are written in an interpreted procedural language called PL/SQL.  As every Oracle manager should know, any language that is parsed and executed line-by-line is subject to injection attacks.  Hence, special audit procedures must be employed for any Oracle database that uses stored procedures.


Hardly a week passes without a report of a company suffering major losses due to an information security breach.  Let's take a look at the types of exposures faced by companies and see how an enterprise auditing solution can prevent the threats. 


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy Oracle direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



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.