Oracle Security Software Controlled Audit
Oracle Security Tips by
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
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
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
Periodic management reports of all access
Please se the above entry; it’s along the same
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
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
Disaster recovery plan periodically tested
Please see above point.
Emergency data access assured in case of
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
||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.