Getting started with standard auditing in Oracle Database 11g

auditing is big business. Are you ready for the challenge to pass an audit?
This article begins with an introduction to Oracle’s standard auditing that we
can build on to answer that question.

of an Oracle database has became big business. So much so, that entire
companies have dedicated complete solutions to the process. Let’s first get a
level-set of what auditing is, at least from Oracle’s perspective. Within the
2-Day+ Security Guide, Oracle defines auditing as the monitoring and recording
of selected user database actions. Within Oracle, you can:

  • Use standard
    auditing (using initialization parameters and the AUDIT and NOAUDIT) to audit
    SQL statements, privileges, schemas, objects, and network and multitier
  • Look at
    activities within the Oracle database that are always audited regardless of
    whether you want to mess with turning on auditing such as administrator level
    privileged connections, database startups & shutdowns.
  • Implement what
    is known as fine-grained auditing that enables you to create policies that
    define specific conditions that must be met for auditing to occur—enabling the
    security administrator to audit access based on the content and triggering

It would
seem logical, in this day and age, why we need to audit databases. Nevertheless,
it does stand to reason that many of us still need to be convinced that
malicious intent runs rampant throughout the enterprise as well as from
external threats. Probably the biggest reason to understand and implement
auditing is to satisfy an audit. Let’s face the facts, regulatory requirements
(Sarbanes-Oxely, HIPAA, Basel II, etc.) are all very real and we must be able to
prove compliancy in order to avoid hefty fines and extended litigation. In
addition to regulatory requirements, there is a strong need for accountability
within the enterprise. I don’t know how many times I’ve implemented my own
specific fine-grain auditing to prove my own actions and point, yes point the
finger, towards someone else. This just doesn’t go for DBA practices,
procedures, and tasks. Auditing of user activity, either through SQL*Plus, a
programming tool, or end-user applications does wonders for pinpointing where
problem users are and the data they’ve either deleted, inserted, or selected or
tables they’ve dropped, created, or altered. Moreover, let’s not forget that
usage patterns can tell a lot as well. How many employees do you think you have
that ONLY work between the hours of 10am to 2pm? Auditing can help prove use, misuse, or non-use of
company resources. Auditing is a powerful tool.

thinking about auditing an Oracle database, it’s easiest to first take a look
at standard auditing and see what it has to offer. Setup is easy and at most
requires just a reboot of the database. Standard auditing will provide
information about the operation being audited, the user performing the
operation, and a data and time associated with the operation. Placement of the
auditing records can either be placed within the database (called a database
audit trail and uses the DBA_AUDIT_TRAIL view/sys.aud$ table), on the operating
system in a set of files (called an operating system audit trail), or in the
DBA_COMMON_AUDIT_TRAIL view, which is a combination of standard and
fine-grained audit log records. Also included for standard auditing is a set
of views that can be used to track suspicious activities.

up or enabling the standard audit trail is quite easy and is outlined in the
2-Day+ Security Guide as follows:

Start Database

Log in as SYS
with SYSDBA privileges

Click Server
to display the Server subpage

Initialization Parameters in the Database Configuration section

Click SPFile
to display the SPFile subpage. If you don’t use an SPFile just continue to the
next step

audit_trail in the Name field to find the AUDIT_TRAIL parameter and then click

Enter the type
of auditing you want in the Value field where value can be:

  • DB – Enables Database
    Auditing where records will primarily be written to the sys.aud$
  • OS – Enables
    auditing records to be written to the operating system where you must also
    specify AUDIT_FILE_DEST which tells Oracle where to write the audit record.
  • NONE – Will
    disable standard auditing
    does all that the type of DB does, plus populating the SQL bind and SQL text
    CLOB-type columns of the sys.aud$ table when available
  • XML – will
    write to the operating system audit records in XML format
    will write to the operating system audit records in XML format but also
    populates the SQL bind and SQL text CLOB-types columns of the sys.aud$ table
    when available

8.  Click Apply

9.  Restart the Oracle instance

While setting up standard auditing from the control panel
is quite acceptable, this is one of the tasks that can also easily be
accomplished through the standard PL*SQL interface.

1.  Log in as SYS with SYSDBA

Sqlplus / as sysdba

2.  Check the current settings

SQL> show parameter audit
———————- ———– ——————————
audit_file_dest string /opt/app/oracle/admin/db11/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB

Set the type
of auditing you want by setting the audit_trail parameter

SQL> alter system set audit_trail=OS scope=spfile;
System altered.

4.  Set the auditing destination

SQL> alter system set audit_file_dest=’/opt/app/oracle/admin/db11/adump’ scope=spfile;
System altered.

5.  Restart the Oracle instance


Also note that there are two additional parameters
audit_sys_operations and audit_syslog_level that you should consider setting if
you are concerned about the SYS account activity.

– this initialization
parameter tells Oracle to turn on auditing of the SYS connections, and users
connecting with the SYSDBA or SYSOPER privilege. It has either a TRUE or FALSE

– this initialization parameter enables SYS and standard OS auditing records to
be written to the system using the SYSLOG utility

Now that
we’ve enables operating system auditing, it is always nice to see exactly what
we’ve accomplished. While this is not an exhaustive example, it does touch the
surface of what we are trying to accomplish here. Suppose now that we have an
un-authorized access attempt (failed login) to our database through the use of
the SCOTT/TIGER account.

Being good
DBAs we’ve locked this account and any attempts will be now be met with ‘the
account is locked’ message:

[[email protected] adump]$ sqlplus scott/tiger
SQL*Plus: Release – Production on Thu Dec 10 06:51:43 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ORA-28000: the account is locked

And now,
because of auditing, this attempt will not go unnoticed and will be reported in
the audit_file_dest location:

[[email protected] ~]$ cd $ORACLE_HOME/adump
[[email protected] adump]$ more ora_3817.aud
Audit file /opt/app/oracle/admin/db11/adump/ora_3817.aud
Oracle Database 11g Enterprise Edition Release – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/app/oracle/product/11.1.0/db_1
System name: Linux
Node name: ludwig
Release: 2.6.18-8.el5
Version: #1 SMP Thu Mar 15 19:57:35 EDT 2007
Machine: i686
Instance name: db11
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 3817, image: [email protected] (TNS V1-V3)

Thu Dec 10 06:48:46 2009
RETURNCODE: “28000” COMMENT$TEXT: “Authenticated by: DATABASE” OS$USERID: “oracle”

Now that
you have the settings changed, you can start monitoring the activity on the SYS
account. Remember standard auditing can monitor and record various user database actions such as SQL
statements, privileges, schemas, objects, and network and multitier activity. Now is the time to dig in, see
how far standard auditing can take you to gain compliancy and then fill in the
gaps. The issue becomes whether you can provide auditors everything they need
to pass your audit. If you can monitor all database traffic and take
appropriate action then you will pass, otherwise you are in for a lot of work. But
more on that latter.


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles