May the source be with you, but remember the KISS principle ;-)
Contents Bulletin Scripting in shell and Perl Network troubleshooting History Humor

Oracle Audit and Security

News See also Recommended Links Recommended Papers Security Audit Checklists SQL Injection FAQs
NSA Guides NIST guides Oracle Developer Network Papers Unix security Commercial Tools   Humor Etc

Oracle is a huge product and because of this complexity auditing Oracle is a huge subject. The watchword here is simplicity and caution. The simpler the audit is, the more likely it is that the results will be analyzed and be of some use. It is important to define what configuration settings are being checked for so that simple scripts can be written to automate most of the checks.

A simple basic set of audit actions should be active all the time. The ideal minimum is to check:

If an employee is suspected of inappropriate actions or if an attack has been suspected then more detailed audit can be turned on for specific tables.

The standard audit commands allow all system privileges to be audited along with access at the object level to any table or view on the database for select, delete, insert or update. Audit can be run for either successful or unsuccessful attempts or both. It can be for each individual user or for all users and it can also be done at the session level or access level. At action level a single record is created per action and at session level one record is created for all audit actions per session.

A default installation of Oracle has audit turned off by default and Oracle does not come with any standard default audit settings or reports to analyze any audit trail produced.

Top Visited
Past week
Past month


Oracle Security Handbook by Marlene Theriault & Aaron Newman 656 pages
ISBN 0-07-213325-2

[Nov 30, 2005]  Oracle Database Management System Security Standard

[Nov 16, 2005] Introduction to Simple Oracle Auditing

All of the sample SQL listed in this paper can be downloaded from the author's Web site at

The task of auditing an Oracle database does not have to be limited only to the audit commands; other techniques can be employed as well. Here are some of the main methods that can be used to audit an Oracle database:

A Simple Oracle Host-Based Scanner
by Pete Finnigan
As with any large software package, the default installation of Oracle does not provide for the most secure system out of the box. Indeed, some aspects of the default installation are remarkably insecure. This paper will explore the scanning of an installation of Oracle's RDBMS and, in doing so, will investigate some common security deficiencies.

Oracle Security Chapter 7. Developing a Database Security Plan

There are many steps to securing your system and its data. But one of the first--and one that too few organizations take--is the development of a security policy that outlines and maps out the enforcement of a security plan. We've included this chapter as the first one in the "Implementing Security" part of this book because we believe that the creation of security policies and the implementation of a security plan must precede the more operational steps of securing your system and database.

In this chapter:
About the Security Policy and Security Plan
Types of Accounts
Standards for Accounts
Standards for Usernames
Standards for Passwords
Standards for Roles
Standards for Views
Standards for the Oracle Security Server
Standards for Employees
Sample Security Plan Index
Sample Security Plan Checklist

Oracle FAQ Oracle Security

Hackproofing Oracle Application Server: A guide to securing Oracle 9i
A detailed report on security vulnerabilities in Oracle 9 by independent security analyst David Litchfield. Very useful information about security and PL/SQL, JSP, XSQL, TNS Listener, and more. (Jan. 10, 2002)


An Overview of Oracle Database Security Features

Oracle Security Server Guide

Sun/Oracle Best Practices (January 2001) -by Bob Sneed  In this paper, Best Practice concepts are first defined, then specific high-impact technical issues common with Oracle in the Solaris[tm] Operating Environment are discussed.

Pal's Linux RDBMS Library -- probably the best resource

Oracle Security

Security Alerts The latest Oracle security alerts and info at OTN.


An Overview of Oracle Database Security Features
Lorraina Hazel, CNE
May 13, 2001


The intent of this paper is to give a new user of Oracle database software, or anyone considering the use of Oracle or an Oracle application, a basic understanding of the security capabilities of Oracle database software. It is beyond the scope of this paper to cover all of the countless security features and options available in Oracle. This paper covers Oracle 8i release 3, unless otherwise noted. Although the newest version, Oracle 9i is expected to be available during the Spring of 2001, Oracle 8i is currently the most widely used. Oracle database software has many sophisticated security features which make it an excellent database system for practically any application. Data confidentiality, integrity, and availability can all be well protected with a properly designed Oracle database.

For those organizations that require a very high level of security, such as medical facilities and e-commerce sites, the more sophisticated capabilities of the Oracle Advanced Security option should be considered. This paper includes a brief description of this optional add-on product.


Oracle allows for various types of authentication. Oracle-based authentication allows for Oracle database accounts with user-ids and strong password management (see Profiles below). Oracle passwords are encrypted with a modified DES algorithm for each database connection. Oracle passwords are stored in an encrypted format in the data dictionary.(11) Each session key is unique and is not re-used. All passwords are encrypted, including user passwords whether across the network or local connections, server to server passwords, and even database administrator passwords when the database is down.(1) Oracle also supports host-based authentication which is based on the operating system’s user accounts which are then passed on to Oracle. Additional authentication options are available for those that choose the Oracle Advanced Security Option (see below).


Oracle makes use of profiles to allow the administrator to place specific restrictions and controls on a number of system resources, password use and various Oracle products. These profiles can be defined, named, and then assigned to specific users or groups of users. There are two types of profiles, which are system resource profiles and product profiles.

System resource profiles can be used to put user limits on certain system resources such as CPU time, the number of data blocks that can be read per session or program call, the number of concurrent active sessions, idle time, and the maximum connection time for a user. Use of this option can ensure that users don’t unintentionally or maliciously "hog" system resources. Additionally, system resource profiles can be used to define and enforce password rules such as password life, grace logins, and account lockout after a defined number of failed login attempts. Perhaps the most impressive feature of this type of profile is the ability to have password complexity checked against a custom password complexity function. The database administrator can write a custom PL/SQL function to define just how complex a password must be, such as number of alpha, numeric and/or numeric characters, length, and how different a new password must be from the previous one.(3, 4)

Product profiles can be used to prevent users from accessing specific commands or all commands in Oracle SQL, SQL*Plus, SQL*ReportWriter, and PL*SQL. Use of this option allows the administrator to do such things as prevent user access to the operating system (SQL*PLUS HOST command), and to prevent unauthorized copying of data from one table to another (SQL*PLUS COPY command).(3)


By default, new Oracle users are not given any privileges. New users must be given privileges before they can logon or execute any database operation. Users can not do anything unless they have been given the specific privilege to do so. There is an impressive number of privileges that can be given, around 100 in all. There are two types of privileges available to be granted to users. They are system and object privileges.

System privileges allow a user to create or manipulate objects, but do not give access to actual database objects. System privileges allow a user to execute commands such as ALTER TABLE, CREATE TABLE, EXECUTE ANY PROCEDURE, and DELETE TABLE.

Object privileges are used to allow access to a specific database object, such as a particular table or view. Object privileges that are given at the view level are especially impressive. This allows for an administrator to give users access to a chosen sub-set of columns or rows in a table, rather than the entire table.(1) Oracle also allows for the user of the GRANT privilege which allows a user to GRANT their privileges to another user or role (see Roles below) for objects that they own.(3, 5)


Roles are used to ease the management task of assigning a multitude of privileges to users. Roles are first created and then given sets of privileges that can be assigned to users and other roles. Users can be given multiple roles. It is much easier to create sub-sets of privileges that are organized into roles and then assign the role to one or more users. Roles can be protected with passwords. Roles that are protected with passwords require that a password be provided before activating a role unless it is the user’s default role. The password feature can be useful in situations where a user needs access to data through an application but it is not desirable to give the user direct access to the data through the use of a report writing tool, etc. The password can be supplied by the application, thus preventing the user to even need to know the password.(5)

Oracle has three default roles which have certain privileges already assigned. The Connect Role is useful only for occasional users because it only allows user login and the ability to create their own tables, indexes, etc. The Resource Role is similar to the Connect Role, but allows for more advanced rights such as the creation of triggers and procedures. The Database Administrator Role is granted all system privileges needed to administer the database and users.(4)

Database Availability Features

Oracle 8i has a number of features that allow it to support mission critical applications that require reliability, continuous operation and recoverability in the event of a system failure. Some of these features are as follows: (6)

Database Encryption

At first thought, it might seem desirable to encrypt a confidential database. However, if the proper user access controls are in place than an encrypted database would only provide protection from unauthorized users and, of course, the database administrator who has unlimited access. It must be remembered that encryption has a high cost in over-head due to the processing power needed to execute the complex encryption/decryption algorithms. For these reasons, Oracle 8i does not provide full database encryption.(1) However, Oracle 8i does provide a special PL/SQL package which can be used to encrypt and decrypt data using DES and Triple DES. This can be used to do partial database encryption.(10)


There are three standard types of auditing available in Oracle, including SQL statement-level, privilege-level, and object-level auditing. Audit records can be written to the standard Oracle audit table, to an operating system audit trail (dependent on operating system used), or to an external file. The three basic types of auditing can be done by user, successful or non-successful attempts, and by session or access time intervals. The standard auditing is useful but is at the table level. It can not be used to audit at the record or column level.

In order to address the limitations of the standard package auditing, Oracle now has the ability to audit through custom audit programming. Oracle allows for custom audit programs through the use of database and event triggers. Database triggers are PL/SQL programs that are stored within a table and are executed before, after, or instead of certain commands when they are executed. For example, this could allow for the writing of an audit record each time a change is made to a particular row in a database. Event triggers can be used to write Oracle audit messages on the events such as login, logoff, and other database events.

Through the use of the standard auditing capabilities and the custom trigger programs, it is possible to audit an endless number of possibilities such as: (3, 4, 5)

Protecting Data Integrity

Oracle provides several features to ensure data integrity whether in the case of system failure, human error, or malicious attacks. These features include redo log files, rollback segments, and LogMiner.

All data changes are recorded in at least two redo log files that are maintained by Oracle. In the event of a system failure or data corruption, the last good backup and the redo log should be restored to bring the system back to the state it was before the corruption or data loss.

Oracle uses rollback segments to record the state of the database prior to each change. In the event of a system failure or corruption event, the rollback segment can be used to back-out any uncommitted changes to restore the database to the state it was prior to the last uncompleted transaction(s).(3)

Oracle has a SQL-based log file analyzer utility called LogMiner. LogMiner can be used to analyze the redo log files and rollback segments when a more sophisticated restoration process is required.(6)

Oracle Advanced Security Option

The Oracle Advanced Security Option (OAS) includes features which ensure secure communications when accessing a database even over the internet. RSA RC4 (40, 56, 128, and 256 bits available) and Triple DES can be used for data encryption over a network. OAS uses the MD5 cryptographic checksum and Secure Hash Algorithm (SHA) for securing the integrity of network messages.(9, 10)

OAS offers additional authentication options. OAS allows for user and database to database authentication through the use of X.509 v.3 certificates. The database to database authentication can be used to enforce the authentication of databases that reference each other. OAS also allows for the use of SSL, RADIUS, Kerberos and CyberSafe, Smart Cards (RADIUS-Compliant),Token Cards (SecurID or RADIUS-Compliant) , Biometric Authentication (Identix or RADIUS-Compliant) and BULL ISM.(1,2) Single sign-on is also supported with digital certificates over SSL.

OAS can be integrated with Oracle’s Internet Directory. Oracle’s Internet Directory is an optional LDAP compliant directory which can be used to centralize and manage users in one directory rather than in multiple databases. Use of the directory also makes single sign-on possible, thus eliminating the need for users to remember multiple passwords for various applications.

The Oracle Company’s Commitment to Security

In 1990 Oracle formed a "hack team" to begin a proactive approach to finding and fixing product vulnerabilities. This team was initially formed to perform a United States government "Orange Book" evaluation of Oracle products. The team is comprised of Oracle staff and external computer security experts. This team conducts extensive security assessments on Oracle products using current hacking tools, known bugs, and their own expert knowledge of the products and computer security. Oracle’s goal is to provide secure products that are secure "out of the box" using the default installation.

In addition to self-evaluation, Oracle has made a major commitment to having its products undergo extensive evaluation by a number of national and international security evaluation organizations, two of which are the US National Computer Security Center, and the International Common Criteria (CC). Oracle products have obtained more security certifications than any other database vendor.(7) For more information on Oracle security certifications, see For information on the current status of security certification see



1. "Database Security In Oracle8i" An Oracle Technical White Paper, November 1999,
URL: (13 May 2001).

2. "Untitled", An Oracle 8i Overview,
URL: (13 May 2001).

3. Theriault, Marlene, and William Heney, Oracle Security, Sebastopol CA: O’Reilly & Associates, Inc., 1998.

4. Koch, George, and Kevin Loney, Oracle8: The Complete Reference, Berkely CA: Osborne McGraw-Hill, 1997.

5. Kreines, David C., and Brian Laskey, Oracle Database Administration, Sebastopol CA: O’Reilly & Associates, Inc., April 1999.

6. "Meeting the Availability Needs of the Mission-Critical Enterprise with Oracle8i" An Oracle Business White Paper, February 1999,
URL: (13 May 2001).

7. Smith, Howard, "Hack Proofing Oracle", Oracle Corporation UK Limited,
URL: (13 May 2001).

8. "Computer Security Criteria: Security Evaluations and Assessment" An Oracle White Paper, October 2000,
URL: (13 May 2001).

9. "Introduction to Oracle Advanced Security", Oracle Advanced Security Administrator’s Guide, Release 8.1.5,
URL: (13 May 2001).

10. "Oracle 8i Release 3 New Features Summary" Features Overview, August 2000,
URL: (13 May 2001).

11. "Oracle 8i Concepts" Controlling Database Access,
(13 May 2001).

* Note: Some of the above URL addresses may require the creation of a free account to login with.


Security Audit

A Simple Oracle Host-Based Scanner by Pete Finnigan

As with any large software package, the default installation of Oracle does not provide for the most secure system out of the box. Indeed, some aspects of the default installation are remarkably insecure. This paper will explore the scanning of an installation of Oracle's RDBMS and, in doing so, will investigate some common security deficiencies.

An Overview of Oracle Database Security Features
The intent of this paper is to give a new user of Oracle database software, or anyone cons idering the use of Oracle or an Oracle application, a basic understanding of the security capabilities of Oracle database software. It is beyond the scope of this paper to cover all of the countless security features and options available in Oracle. This paper covers Oracle 8i release 3, unless otherwise noted. Although the newest vers ion, Oracle 9i is expected to be available during the Spring of 2001, Oracle 8i is currently the most widely used. Oracle database software has many sophisticated security features which make it an excellent database system for practically any application. Data confidentiality, integrity, and availability can all be well protected with a properly designed Oracle database.

By Lorraina Hazel, 03/24/2004

Conducting a Security Audit of an Oracle Database
This paper has been written from the perspective of an external, independent auditor with the task of conducting a security audit on a system based around an Oracle database. The methodology presented in the Federal Information System Controls Audit Manual is described as a foundation for conducting the audit. Specific security issues related to Oracle databases are discussed based on the methodology. The focus of the paper is on auditing access controls to Oracle databases. What should the auditor evaluate and test to enable him to give an informed opinion about the security of an information system based on an Oracle database? A number of issues that the auditor should evaluate are discussed in the paper, with indications of how these issues should be dealt with by the entity being audited.

By Egil Andresen, 03/24/2004

Oracle Row Level Security: Part 2
In part one of this short article series we looked at some of the advantages of Oracle's row level security, what it can be used for, and looked at a simple example of how it works. We'll conclude this series by testing the policies that have been setup, demonstrate a few of the data dictionary views that allow for management and monitoring, cover some other issues and features, and then see if the data can be viewed by hackers or malicious users through the use of trace files.

By Pete Finnigan, 03/22/2004

Oracle Row Level Security: Part 1
In this short paper I want to explore the rather interesting row level security feature added to Oracle 8i and above, starting with version 8.1.5. This functionality has been described as fine grained access control or row level security or virtual private databases but they all essentially mean the same thing. We will come back to this shortly but before we do that lets get to what this paper is about. This paper is meant as an overview; a taster in fact of what row level security can be used for and how it can be used, with some simple examples to illustrate. I want to also discuss some of the issues with row level security. Finally, I also want to show how to view what row level security components have been implemented in the database and also touch on how to view how the actual database queries are altered by the row level security functionality in the oracle optimizer.

By Pete Finnigan, 03/22/2004


Introduction to Simple Oracle Auditing
This article will introduce the reader to the basics of auditing an Oracle database. Oracle's RDBMS is a functionally rich product and there are a number of auditing alternatives available to the reader. Because auditing Oracle is such a huge subject, doing all of it justice would take an entire book, so this paper will cover the basics of why, when and how to conduct an audit. It will also use a couple of good example cases to illustrate how useful Oracle audit can be to an organization.

By Pete Finnigan, 03/22/2004

A Simple Oracle Host-Based Scanner
As with any large software package, the default installation of Oracle does not provide for the most secure system out of the box. Indeed, some aspects of the default installation are remarkably insecure. There is a high dependency on the database administrator (dba) to ensure that the system is correctly configured, thereby avoiding some of these issues.

By Pete Finnigan, 03/20/2004



PDF Hack-Proofing Oracle
This paper addresses how Oracle responds to IT security threats with its own "hack team" and provides suggestions for improving database application security.

Database Security in Oracle8i 2001-01-01 A technical white paper on Oracle security issues. A PDF document.

Database Security 101
Richard D. Newallis 2000-11-19 An introduction to Oracle security issues. A PDF document.

Hack Proofing Oracle Howard Smith 2000-10-24 What Oracle (the company) does to make the software more secure and how the DBA/Developer can improve the security of an Oracle system. A presentation from OpenWorld 2000. A PDF document; free registration is required.

Developing a Database Security Plan Marlene Theriault, William Heney 2000-08-20 A very useful article on what makes a good security policy and how to implement it. A sample chapter from the book Oracle Security, published by O'Reilly.

An Overview of Oracle Database Security Features

Tree Tier Architecture

Securing Oracle Network Traffic Roger Schrag 2001-01-27 A very useful article on Net8 security issues.

Securing Three-tier Systems With Oracle8i John H.Heimann 2001-01-01. An overview of the techniques in Oracle8i that can make a three-tier system more secure. A PDF document.


Protecting Your Database Kevin Loney 2000-11-03 An article from the May 2000 issue of Oracle Magazine on how to protect your database from non-technical attacks.

Anatomy of the Openhack Database Crack Lluis Mora 2000-11-17 A very entertaining ZDNet article on how somebody with minimal Oracle knowledge managed to crack an Oracle database from the Internet. A PDF document.


Fine Grained Access Control



Oracle Developer Network Papers

Commercial Tools

Oracle Security Analyser

Security Analyzer is a simple point-and-click Windows application used by security administrators and auditors to comprehensively define, schedule, execute and enforce security policies for each database in an Oracle 7 or 8 environment.

Key Benefits

The Business Challenge
As corporations open their systems to an extended enterprise made up of customers, suppliers, partners, and contractors, they also increase the likelihood of a cyber attack. Compliance with security policies regarding the proper handling of proprietary information is not always required from these "trusted" outsiders, potentially leaving the corporation open to attacks. Any system downtime caused by information security breaches also can have an immediate and significant impact on customer satisfaction and revenues. Security breaches, either from inside or outside the corporation, are becoming more sophisticated while the volume of business critical data is becoming increasingly difficult to manage.

With the rapid growth of business-to-business e-commerce, companies must implement security at several levels within their computing architecture, not least of which is the database. In client/server database applications, users rarely log into the host operating system. Instead, they log directly into the database, specifying the username and password given to them by the database administrator. Most database systems today do not provide tools to define, enforce and administer corporate security policies.

VigilEnt Security Analyzer for Oracle®
Security Analyzer provides the ability to define, refine, assess and enforce corporate security policy at the database level. Using Security Analyzer, a database can be quickly and easily measured against a pre-defined set of site and application specific security rules and guidelines; direct and indirect security loopholes are identified and resolved.

Additional Benefits of VigilEnt Security Analyzer:

VigilEnt Security Analyzer for Oracle provides the ability to:

Audit     Assess the security issues of the Oracle environment

Secure   Resolve security issues directly.

Detect   Send alerts if database activity indicates a possible intrusion


Recommended Links

Softpanorama hot topic of the month

Softpanorama Recommended

***** Pete Finnigan - Oracle and Oracle security information

Recommended Papers

[Nov 16, 2005] Introduction to Simple Oracle Auditing

All of the sample SQL listed in this paper can be downloaded from the author's Web site at

The task of auditing an Oracle database does not have to be limited only to the audit commands; other techniques can be employed as well. Here are some of the main methods that can be used to audit an Oracle database:

This is really the subject of this paper. All privileges that can be granted to a user or role within the database can be audited. This includes read, write and delete access on objects at the table level. For more detailed audit, the database triggers need to be employed.

These were introduced with Oracle 8 and allow the writing of database triggers that fire when system events take place. These include start- up and shutdown of the database, log-on and log-off attempts, and creation, altering and dropping of schema objects. With the aid of autonomous transactions, these allow a log to be written for the above system events.

This is the second line of defence in trying to understand users' actions at a more detailed row level. Database triggers need to be written to capture changes at the column and row level. It is possible to write complete rows of data before and after the change being made to a log table in the database. The use of this type of logging is very resource intensive, as many extra records are written and stored. The one failing with this method is that read access cannot be captured with normal database triggers.

Fine-grained audit solves the problem of capturing read access. This feature is also based on internal triggers that fire when any piece of SQL is parsed. This is very efficient, as the SQL is parsed once for audit and execution. The feature uses predicates that are defined and tested each time the relevant object is accessed. Fine-grained audit is managed by a PL/SQL package called DBMS_FGA. A PL/SQL procedure is executed every time a "match" is made with the predicate. This method allows the audit to be performed down to the row and column level and to also for read statements. Readers should be forewarned that use of this feature requires programming skills.

Oracle generates many log files and many of them can provide useful information to assist in auditing the database. One good example is the alert log used by the database to record start-up and shutdown as well as any structural changes such as adding a datafile to the database.

This paper is going to explore only the standard built-in audit commands. The other options will be left for future articles.

Some Examples

Because of the myriad of possibilities, auditing an Oracle database can be a daunting task. In order to try and simplify the discussion of what can be done, we will discuss a couple of simple examples that we will explore and work through.

This is a fundamental check to find out who accesses the database, from where and when. Log-on failures can be captured as well as log- ons at strange (anomolous) times of the day.

In a production database, no user should ever change the schema structure. DBAs should make changes for upgrades at specific times; any other changes should be regarded as suspicious. Watching for structural changes can turn up indicators of incorrect use of the database.

A third simple example that could have been employed here is to audit any use of system privileges. However, this example is left to the reader to explore.

The final group of audit commands that can be employed is to audit any data changes to objects themselves. Unfortunately, as the requirements are very application and installation specific, this is beyond the scope of this paper.

Audit within Oracle is broken into three areas: statement auditing such as CREATE TABLE or CREATE SESSION, privilege auditing such as ALTER USER, and object level auditing such as SELECT TABLE.

Basic Configuration

The audit trail can be either written to the database or to the operating system. Writing the audit trail to the operating system is, in some senses, more secure but the implementation is not available on all platforms and is platform specific. In this article we will concentrate on using the database to store the audit trail.

Audit is turned on for writing to the database by adding the following line to the init.ora file. A symbolic link to it can usually be found in $ORACLE_HOME/dbs

audit_trail = db

The database now needs to be restarted. A simple check will show that audit is indeed now turned on.

SQL> select name,value from v$parameter
  2  where name like 'audit%';

NAME                           VALUE
------------------------------ ------------------------------
audit_trail                    DB
audit_file_dest                ?/rdbms/audit


No audit actions are captured yet until audit actions are defined; that is, except for privileged access to the database, starting and stopping of the database, and structural changes such as adding a datafile. These are logged to operating system files in $ORACLE_HOME/rdbms/audit unless audit_file_dest is redefined in the init.ora file. On Windows these events appear in the Event Viewer. To check if any privilege or statement audit actions are enabled, do the following:

SQL> select * from dba_stmt_audit_opts
  2  union
  3  select * from dba_priv_audit_opts;

no rows selected


To find out what objects are being audited, query the view dba_obj_audit_opts.

The Worked Examples

Let us now work through our two example cases and see what can be learned. First, turn on audit for the access attempts to the database:

SQL> audit create session;

Audit succeeded.


The above command will capture access by all users by access and whether successful or unsuccessful. The default for this command is by access.

Note: The format of all audit commands from the Oracle documentation is as follows:

audit {statement_option|privilege_option} [by user] [by 
{session|access}] [ whenever {successful|unsuccessful}]

Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.

For a user to define audit statements, the privilege "AUDIT SYSTEM" needs to have been granted first. The users that have this privilege can be checked as follows:

SQL> select *
  2  from dba_sys_privs
  3  where privilege like '%AUDIT%';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CTXSYS                         AUDIT ANY                                NO
CTXSYS                         AUDIT SYSTEM                             NO
DBA                            AUDIT ANY                                YES
DBA                            AUDIT SYSTEM                             YES
IMP_FULL_DATABASE              AUDIT ANY                                NO
MDSYS                          AUDIT ANY                                YES
MDSYS                          AUDIT SYSTEM                             YES
WKSYS                          AUDIT ANY                                NO
WKSYS                          AUDIT SYSTEM                             NO

9 rows selected.


The above results are for a 9i database, the default users MDSYS, CTXSYS and WKSYS would likely be good targets for attackers, as any audit actions could be turned off as one of these users to hide any actions undertaken.

Now that audit will capture all access attempts, we need to wait for some users to log in to do work. So while they do that, let's set up the audit to capture alterations to the schema. For the sake of brevity, in this example, not all schema object changes will be captured. Changes to tables, indexes, clusters, views, sequences, procedures, triggers, libraries and many more can be captured. In this example, audit will be enabled on an example set. Turning on the audit can be performed as a two-stage process, generate the audit commands and then run them as follows:

set head off
set feed off
set pages 0
spool aud.lis
select 'audit '||name||';'
from system_privilege_map
where (name like 'CREATE%TABLE%'
or name like 'CREATE%INDEX%'
or name like 'CREATE%CLUSTER%'
or name like 'CREATE%SEQUENCE%'
or name like 'CREATE%PROCEDURE%'
or name like 'CREATE%TRIGGER%'
or name like 'CREATE%LIBRARY%')
select 'audit '||name||';'
from system_privilege_map
where (name like 'ALTER%TABLE%'
or name like 'ALTER%INDEX%'
or name like 'ALTER%CLUSTER%'
or name like 'ALTER%SEQUENCE%'
or name like 'ALTER%PROCEDURE%'
or name like 'ALTER%TRIGGER%'
or name like 'ALTER%LIBRARY%')
select 'audit '||name||';'
from system_privilege_map
where (name like 'DROP%TABLE%'
or name like 'DROP%INDEX%'
or name like 'DROP%CLUSTER%'
or name like 'DROP%SEQUENCE%'
or name like 'DROP%PROCEDURE%'
or name like 'DROP%TRIGGER%'
or name like 'DROP%LIBRARY%')
select 'audit '||name||';'
from system_privilege_map
where (name like 'EXECUTE%INDEX%'
or name like 'EXECUTE%PROCEDURE%'
or name like 'EXECUTE%LIBRARY%')
spool off

This will generate a set of audit commands that can be captured to a spool file, which is then run to enable the audit commands.

Another solution would be to audit the actual permissions granted to users by generating the audit commands from the database view dba_sys_privs. While this may seem to be a better solution and potentially involve less audit commands, it would not allow for the case when new permissions are granted to users. In this case, audit would also need to be enabled at the time the privileges are granted.

Now that all of the sample audit is now enabled, the settings can be viewed with this SQL:

  1  select audit_option,success,failure
  2  from dba_stmt_audit_opts
  3  union
  4  select privilege,success,failure
  5* from dba_priv_audit_opts
SQL> /

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER ANY INDEX                          BY ACCESS  BY ACCESS

38 rows selected.


Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table owned by the SYS user called AUD$. This table is, by default, located in the SYSTEM tablespace. This in itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang.

The AUD$ table is rare, as it is the only SYS owned table from which Oracle allows records to be deleted. If the audit trail is turned on and written to the database, then the numbers of records in this table need to be monitored carefully to ensure it doesn't grow too fast and fill the system tablespace. A purging strategy needs to be adopted to keep the size of the table in check and, if needed, to archive off audit trail records for future reference. One tactic could be to copy the records to summary tables that allow specific checks for abuse to be performed offline. These summary tables can be in a separate database for added security. Once copied, sys.aud$ can be truncated.

SYS.AUD$ can be moved to a different tablespace other than SYSTEM but check with Oracle support first, as this action is no longer supported.

Only users who have been granted specific access to SYS.AUD$ can access the table to read, alter or delete from it. This is usually just the user SYS or any user who has had permissions. There are two specific roles that allow access to SYS.AUD$ for select and delete, these are DELETE_CATALOG_ROLE and SELECT_CATALOG_ROLE. These roles should not be granted to general users.

Back to the examples, our users have been logging on and working throughout the day and created some audit records. These audit records can be viewed in a number of ways:

A simple piece of SQL can show details of the connection attempts:

SQL> get check_create_session
  1  --
  2  -- check_create_session.sql
  3  --
  4  col username for a15
  5  col terminal for a6
  6  col timestamp for a15
  7  col logoff_time for a15
  8  col action_name for a8
  9  col returncode for 9999
 10  select     username,
 11     terminal,
 12     action_name,
 13     to_char(timestamp,'DDMMYYYY:HHMISS') timestamp,
 14     to_char(logoff_time,'DDMMYYYY:HHMISS') logoff_time,
 15     returncode
 16* from       dba_audit_session
SQL> /
--------------- ------ -------- --------------- --------------- ----------
SYS             pts/1  LOGOFF   09042003:051046 09042003:051641          0
ZULIA           pts/1  LOGON    09042003:051641                       1017
SYS             pts/1  LOGOFF   09042003:051649 09042003:053032          0
SYS             pts/2  LOGOFF   09042003:052622 09042003:053408          0
ZULIA           pts/1  LOGON    09042003:053032                       1017

There are a number of simple abuses that can be checked for in the area of user access to the database. As examples for this paper we will look at the following:

This can indicate fat fingers or attackers' attempts to gain unauthorized access the database. The following SQL highlights this:

SQL> select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY')
  2  from dba_audit_session
  3  where returncode<>0  
  4  group by username,terminal,to_char(timestamp,'DD-MON-YYYY');

---------- --------------- ------ -----------
         1 BILL            pts/3  09-APR-2003
         3 FRED            pts/3  09-APR-2003
         4 ZULIA           pts/1  09-APR-2003


This shows two possible abuses, the first is the user Zulia attempting to log on and failing four times on the same day. This could be a forgotten password or it could be someone trying to guess his or her password. A change to the SQL as follows gives a bit more detail:

SQL> select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode
  2  from dba_audit_session
  3  group by username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode;

---------- --------------- ------ ----------- ----------
         1 BILL            pts/3  09-APR-2003       1017
         1 EMIL            pts/1  09-APR-2003          0
         1 EMIL            pts/2  09-APR-2003          0
         1 EMIL            pts/3  09-APR-2003          0
         1 EMIL            pts/4  09-APR-2003          0
         3 FRED            pts/3  09-APR-2003       1017
         3 SYS             pts/1  09-APR-2003          0
         1 SYS             pts/2  09-APR-2003          0
         1 SYSTEM          pts/5  09-APR-2003          0
         4 ZULIA           pts/1  09-APR-2003       1017
         1 ZULIA           pts/1  09-APR-2003          0

11 rows selected.


This reveals that the user successfully logged on on the same terminal on the same day. A number of failed log-ons should be agreed as part of these checks and the above SQL run every day. Those users with failure numbers above the threshold should be investigated.

One interesting extension to the above SQL is to find attempts to log in where the user doesn't exist. An audit record is still created in this case. The following SQL illustrates:

SQL> select username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')
  2  from dba_audit_session
  3  where returncode<>0
  4  and not exists (select 'x'
  5     from dba_users
  6     where dba_users.username=dba_audit_session.username)
SQL> /

--------------- ------ --------------------
FRED            pts/3  09-APR-2003 17:31:47
FRED            pts/3  09-APR-2003 17:32:02
FRED            pts/3  09-APR-2003 17:32:15
BILL            pts/3  09-APR-2003 17:33:01


This is probably abuse. All attempts to log on with a user that doesn't exist should be checked each day and investigated.

Checks should be made for any attempts to access the database outside of working hours. These accesses could be genuine overtime work or maintenance but they could just as easily be unauthorized access attempts and should be checked as follows:

SQL> select     username,
  2     terminal,
  3     action_name,
  4     returncode,
  5     to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),
  6     to_char(logoff_time,'DD-MON-YYYY HH24:MI:SS')
  7  from dba_audit_session
  8  where to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') < 
  9 or to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') >
SQL> /

---------- ------ -------- ---------- -------------------- --------------------
SYS        pts/1  LOGOFF            0 09-APR-2003 20:10:46 09-APR-2003 20:16:41
SYSTEM     pts/5  LOGOFF            0 09-APR-2003 21:49:20 09-APR-2003 21:49:50
ZULIA      pts/5  LOGON             0 09-APR-2003 21:49:50
EMIL       APOLLO LOGON             0 09-APR-2003 22:49:12


The above SQL shows any connections before 8:00 AM and after 7:30 PM. Any connections, particularly those made by privileged users such as SYS and SYSTEM, should be investigated. Particular attention can be made to the location from which the access was made. For instance, if privileged access is made from machines that are not in the administrator department, the administrator needs to find out why.

The following SQL looks for users who are potentially sharing database accounts:

SQL> select count(distinct(terminal)),username
  2  from dba_audit_session
  3  having count(distinct(terminal))>1
  4  group by username
SQL> /

------------------------- ----------
                        4 EMIL
                        3 SYS
                        3 ZULIA 

This shows that three users have accessed their accounts from more than one location. A further check could be to add a time component to see if they are accessed simultaneously and also to restrict the check per day. The above SQL gives some idea of the potential without complicating it too much. Again, these accounts and users should be investigated.

The final example checks to find where multiple database accounts have been used from the same terminal. The SQL is again simple and could be extended to group by day and also to print out the users per terminal. This is a simple test to illustrate the abuse idea:

SQL> select count(distinct(username)),terminal
  2  from dba_audit_session
  3  having count(distinct(username))>1
  4  group by terminal
SQL> /

------------------------- ------
                        3 pts/1
                        2 pts/2
                        3 pts/3
                        3 pts/5


This could indicate someone trying to gain access by trying many accounts and passwords, or it could indicate legitimate users sharing accounts for certain aspects of their work. In either case, the admin should investigate further.

There are, of course, many other scenarios that could indicate possible abuses. Checking for those is as simple as the cases depicted above. It will be left to the reader to experiment. Let me know what you find useful.

The second example case that audit actions were set for is to detect changes made to the database schema. This could include new objects being added or attempts to change existing objects within the database.

A simple piece of SQL will show any audit trail items that relate to objects being created or changed as follows:

col username for a8
col priv_used for a16
col obj_name for a22
col timestamp for a17
col returncode for 9999
select  username,
        to_char(timestamp,'DD-MON-YYYY HH24:MI') timestamp,
from dba_audit_trail
where priv_used is not null
and priv_used<>'CREATE SESSION'
SQL> @check_obj.sql

ZULIA    CREATE TABLE     STEAL_SALARY           09-APR-2003 20:07          0
PETE     CREATE PROCEDURE HACK                   09-APR-2003 20:42          0

This simple example shows that the user ZULIA has created a table and the user PETE has been writing PL/SQL procedures. Any changes such as this that are found should be investigated in a production database. Many more specific abuses can be checked for in relation to object and schema changes but, in general, no user should be able to alter the database schema in a production database. As a result, the check can remain pretty simple.

Protecting the Database Against These Abuses

The two examples given are just two of many possible scenarios that could be detected using Oracle's auditing facilities. Turning on and managing audit is one of the first steps to securing the database. Using audit should be part of an overall organization security plan and policy that includes Oracle. The database should be audited regularly for misconfiguration or known vulnerabilities that could allow security breaches to take place.

Because of its complex nature and vast number of different ways it can be used and configured, the best approach to securing Oracle will always be to follow the principle of least privilege. Once the database is part of the overall security plan and is configured correctly and checked regularly, then auditing it should be considered an important part of the strategy.

In general, do not grant any privileges to general users in a production database, remove most of the PUBLIC privileges and delete or lock and change the passwords of any default accounts. Ensure that users obey password policies and that the password management features of Oracle are employed.

It is important that the audit actions are planned from a performance and usability point of view and that the audit trail is managed. It is also important that the audit trail data is understood in terms of detecting abuse.

The author's recent book by the SANS Institute "Oracle security step- by-step - A survival guide for Oracle security" gives excellent guidelines on how to configure Oracle securely.

[PDF] Oracle 9iAS Security - FAQ
File Format: PDF/Adobe Acrobat - View as HTML
... support for role-based access control (RBAC), including the ... Is there a difference
between principals.xml and the JAAS ... only users from a certain group can run? ... oracle9iAS/pdf/9iAS_faq1.pdf - Similar pages

RBAC Support in Object-Oriented Role Databases

Role Based Access Control (RBAC) in Relational DBMS Systems

Protecting Oracle Databases Whitepaper Aaron Newman, Application Security Inc

Hackproofing Oracle Application Servers David Litchfield, NGSSoftware Insight Security Research



[Dec 15, 2003] Oracle_Database_Checklist Prepared by Pete Finnigan


Oracle security step-by-step – A survival guide for Oracle security – Pete Finnigan - SANS Press – January 2003

Links to many useful papers and presentations about Oracle security –

Oracle security website

Oracle Corporation main page

Customer support site

Security alerts


 This checklist is to be used to audit an Oracle database installation. This checklist is just that “a checklist” and does not contain any specific SQL or shell commands because it is intended to be just a list rather than a “how to” document otherwise. It is also important that the Oracle database is not checked in isolation and the surrounding elements such as the operating system used, the network configuration, web access, application servers and clients are considered.

 Whilst every effort has been made to ensure that this checklist is as complete and comprehensive as possible new issues and vulnerabilities are found every day therefore don’t rely on it to be all encompassing. Regularly check for updates of this list.

 Elements to be considered prior to applying this checklist:

·        Host Operating System – Although this checklist includes items that specifically relate to the operating system hosting the Oracle installation they are included because they have a direct effect on Oracle. It is imperative that the host operating system is secured before any applications (in this case Oracle). The same applies to network components and other applications hosted on the same servers. Please consult other S.C.O.R.E documents ( ), center for internet security (CIS) benchmarks and tools ( ) and SANS step-by-step guides ( ) for more information.

·        Procedural – It is important to also consider physical security of the servers hosting the Oracle database and also to employ security procedures and policies and to develop standards for change and control.

·        Findings and data sensitivity – Establish the sensitivity of the data stored within the Oracle database and establish rules for reporting any security findings back to the organisation. This should take into account availability, confidentiality and the integrity of the data. This is important to be able to place any findings within the correct context when reporting back results of an audit.

·        Practicality of the checklist – This list is the culmination of the knowledge of many Oracle database security practitioners and as such includes every issue thought to be relevant to somebody. To some organisations some items are important to be fixed and to others not relevant because of mitigating circumstances. Oracle can be configured in many differing ways and this affects how it is secured. The list has been provided with severity levels to allow the audit to be conducted to a specified level and also includes OS and Oracle versions relevancies.

·        Oracle database security standards - This checklist could also be used to define a company standard for securing Oracle.

Before using this checklist to review an Oracle database installation it is important to understand the use to which the Oracle database and applications will be put. How the database is used can have a direct effect on how this list is read and interpreted. Oracle is a complicated beast to configure in any multitude of guises and checks and solutions that are relevant for one installation and type of application will conflict with another. Practicality is called for!


FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available in our efforts to advance understanding of environmental, political, human rights, economic, democracy, scientific, and social justice issues, etc. We believe this constitutes a 'fair use' of any such copyrighted material as provided for in section 107 of the US Copyright Law. In accordance with Title 17 U.S.C. Section 107, the material on this site is distributed without profit exclusivly for research and educational purposes.   If you wish to use copyrighted material from this site for purposes of your own that go beyond 'fair use', you must obtain permission from the copyright owner. 

ABUSE: IPs or network segments from which we detect a stream of probes might be blocked for no less then 90 days. Multiple types of probes increase this period.  


Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers :   Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism  : The Iron Law of Oligarchy : Libertarian Philosophy


War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda  : SE quotes : Language Design and Programming Quotes : Random IT-related quotesSomerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose BierceBernard Shaw : Mark Twain Quotes


Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 :  Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method  : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law


Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds  : Larry Wall  : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOSProgramming Languages History : PL/1 : Simula 67 : C : History of GCC developmentScripting Languages : Perl history   : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history

Classic books:

The Peter Principle : Parkinson Law : 1984 : The Mythical Man-MonthHow to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Hater’s Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite

Most popular humor pages:

Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor

The Last but not Least

Copyright © 1996-2016 by Dr. Nikolai Bezroukov. was created as a service to the UN Sustainable Development Networking Programme (SDNP) in the author free time. This document is an industrial compilation designed and created exclusively for educational use and is distributed under the Softpanorama Content License.

The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be tracked by Google please disable Javascript for this site. This site is perfectly usable without Javascript.

Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.

FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available to advance understanding of computer science, IT technology, economic, scientific, and social issues. We believe this constitutes a 'fair use' of any such copyrighted material as provided by section 107 of the US Copyright Law according to which such material can be distributed without profit exclusively for research and educational purposes.

This is a Spartan WHYFF (We Help You For Free) site written by people for whom English is not a native language. Grammar and spelling errors should be expected. The site contain some broken links as it develops like a living tree...

You can use PayPal to make a contribution, supporting development of this site and speed up access. In case is down you can use the at


The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the author present and former employers, SDNP or any other organization the author may be associated with. We do not warrant the correctness of the information provided or its fitness for any purpose.

Last modified: August 05, 2013