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 Security Software Controlled Audit Trails

Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.

Software controlled audit trails

In this method, the application software creates an audit trail of the user?s actions. In the case of pre-Oracle9i databases, this is the only way the exact select statements can be captured. However, the ultimate objective is to enable auditing at the database level, not the application or middleware level.

Transaction log audit trail

This is close to mining the information contained in transaction logs, such as Oracle?s archived logs. Log Miner, explained in detail in Chapter 9, provides the exact functionality.

File level audit trail

The requirement here is that access to files be tracked. This is not relevant since there are neither user accessible nor manipulation-enabled files inside an Oracle database.

Record level audit trail

Record level audit trails track changes to individual records inside a table. Conventional audits do not provide the functionality. The task calls for creation of table triggers to capture the changes as they occur, and writing to user-defined audit trails, as described in Chapter 9. The other option is to mine information from the archived redo log files using Log Miner, also described in chapter 9. To track SELECT statements, not changes, the Oracle9i Fine Grained Auditing, explained in Chapter 11 can be used.

Field level audit trail

This calls that changes to specific fields be tracked, not just records. There is no conventional audit facility in Oracle to do so. However using table triggers and using a WHEN clause, the changes to fields can be tracked. This is described in Chapter 9. Log Miner can be used to track changes to fields inside a record, too, as shown in Chapter 9.

For mere selection of data from the fields, not changes, the Oracle 9i Fine Grained Auditing can actually track access to specific fields using the Policy Columns parameter, as explained in Chapter 11.

Write or change data audit trail

Again, all the functionality explained above can be used in this case.

Read, display, print data audit trail

Again, all the functionality explained above can be used in this case.

Automatic display of "last access" to the next user, to allow self-audit by all users.

This requirement states that when the users access the data, they are shown the last time they accessed the same information, similar to how the last login information for the user is shown in UNIX when the user log in. This results in the user himself identifying a possible attack using the userid. However, accesses to Oracle database usually occur transparently, and non-interactively, unlike the UNIX login process. Therefore this functionality does not have a useful place. However, if need be, the users can be allowed to select their own data from the audit tables to examine for possible fraudulent activity. The best course is to build a VPD policy on the AUD$ table to show only the user?s own data and let the user choose from various views based on AUD$ table.

Periodic management reports of exceptions

This is extremely easy to implement in Oracle. A simple auditing mechanism to audit logons, logoffs, and access to objects, which is triggered when the action is unsuccessful can be useful in identifying a possible attack on the database. In Chapter 8, there is a script that creates this management exception report. In Chapter 8, you will also find a script to generate exception reports on the database server errors, some of which may indicate fraudulent activity.

Periodic management reports of all access

Please se the above entry; it?s along the same lines.

Internal periodic audit of audit trails

In Chapter 8, we have provided several scripts to produce reports that can be studied and analyzed to indicate potentially fraudulent and malicious activity. The reports can be examined periodically to ensure compliance.

Policies and procedures in place for Access Monitoring, to detect misuse and violations

Please see the above entries, along the same topic.

External/independent audit of audit trails

This is an interesting challenge for Oracle audits. Conventional audit trails are typically written to a table called AUD$. However, since the table is subject to manipulation by the DBA, the actions of a malicious DBA would violate the integrity of the table. To address this problem, Oracle allows the audit records to be written to Operating System files. This is accomplished by setting the initialization parameter audit_dest to OS, instead of DB or TRUE. Doing this causes the audit trails to be written to small files at the operating system level. If the auditor can secure them, the integrity of the files can be maintained.

Physical Security and Disaster Recovery



Secure computer room

It is good practice to have adequate physical security. You can read all about physical security in Chapter 4.

Secure access to displays and printers

Specifically this calls for limited access to consoles and line printers. However, in today?s technology, limiting printer access may not provide a huge security barrier. However, limiting access to consoles helps stop unauthorized activities such as abnormal shutdowns, etc. The section on Physical Security in Chapter 4 discusses this.

Network security, no external network access

This calls for putting a firewall around all important access points. Sometimes there should be two different firewalls ? one around the entire company network and one around the important servers such as database servers, etc.

Secure destruction of printouts, floppies, etc.

This is a procedural issue. As a general rule all media with HIPAA defined sensitive information should be destroyed ? electronically or physically by shredding.

Secure destruction of obsolete equipment

Procedural issue, again.

Burglar alarm monitored by Police

Part of procedures.

Secure backup, storage and retrieval

The backup tapes should be secured as diligently as the original database. Anyone who has access to the backups can recreate the database and obtain sensitive data.

Multiple backup storage sites

This is a part of the disaster recovery solution ? sending the datafile and archived log backups to multiple locations, in the event of a problem with one site. Another solution is to build a set of standby databases in a geographically different location and use them in the disaster recovery strategy.

Disaster recovery plan in place

This is a procedural issue. The organization should have a written, well-understood disaster recovery plan in place. The plan should be reviewed periodically and dry runs should be made from time to time to validate the activities laid out in the plan.

Disaster recovery plan periodically tested

Please see above point.

Emergency data access assured in case of disaster

Provision of a Standby Database will ensure the access to all data in the event of a disaster.

Data content integrity assured

This calls for application design. Some applications may not be designed to handle exceptions such as power failures very well. This review will help find these out and ensure their validity.


This is an excerpt from the book "Oracle Privacy Security Auditing".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing 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 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.