Creating a standard audit trail in Oracle Database 11g

In
this article we set up and create a standard audit trail through the use of
Oracle’s Enterprise Manager.

Up to
this point, for auditing we’ve focused on setting up some defaults and in
particular creating an audit trail through the use of SQL*Plus. This article,
as I thought it would be a nice tie-in, will actually go through the tutorial
at the end of the last chapter of the 2-Day Security Guide and create an audit
trail using the Oracle Enterprise Manager. I particularly like using SQL*Plus,
mostly because I grew up doing DBA work way before Oracle Enterprise Manager,
but have grown to like the simplicity of OEM; when it works.

For this
particular tutorial, we’ll go through three simple steps of:

1. 
Enabling
standard auditing

2. 
Enabling
auditing for SELECT statements against a table (OE.CUSTOMERS)

3. 
Test to make
sure the auditing is working by selecting from the table and checking the audit
trail (DBA_AUDIT_TRAIL)

Enable
standard auditing

Log into Oracle Enterprise Manager as the sys user:

  • User Name: SYS
  • Password:
    <password-for-sys>
  • Connect As:
    SYSDBA

After logging in as the
sys user you should be placed in the Home subpage for the instance. In
this example, my instance is db11.

Click Server to
display the Server subpage.

Click Server to display the Server subpage

Click Initialization
Parameters
in the Database Configuration section to bring up the
Initialization Parameters page.

Click Initialization Parameters in the Database Configuration section to bring up the Initialization Parameters page

Click SPFile
to display the SPFile subpage.

Click SPFile to display the SPFile subpage

From
the SPFile subpage, you can modify various parameters. Enter AUDIT_TRAIL in
the Name and then click Go to produce a filtered
list of all of the parameters that have AUDIT_TRAIL in them; only one in this
case but this is only to state that you could just as easily enter the first
few letters of the parameter you are searching for and have a longer list to
scroll through until you find the AUDIT_TRAIL parameter.

From the SPFile subpage, you can modify various parameters

Depending on whether or
not you’ve been following along with the last couple of articles, the value for
audit_trail (isn’t that funny how it is lowercase but we searched on upper
case) may already be set to DB—enabling database auditing and directing all
audit records to the database audit trail (SYS.AUD$) unless they are of the
type that are always written to the OS.

If your value is not DB then just use the drop-down list to select DB and then click Apply.

If your value is not DB then just use the drop-down list to select DB

If you needed to set the
value of AUDIT_TRAIL from some other value than DB, you will need to restart
the Oracle Database instance. Here are those steps and screen shots just incase
you’ve never experienced the procedure.

From the screen you’re
on, click the Database Instance link at the top of the page.

Click on the Home subpage
tab to display the database control home page.

Click the Shutdown button
under the General section to initiate the shutdown process.

Click the Shutdown button under the General section to initiate the shutdown process.

From the Startup/Shutdown/Specify
Host and Target Database Credentials
page, enter the host credentials
(owner of the oracle instance; usually oracle) and the database credentials for
the SYS user.

Startup/Shutdown/Specify Host and Target Database Credentials

From the Startup/Shutdown
Confirmation
page, click Yes to confirm you actually want to
shutdown the instance.

Startup/Shutdown Confirmation

The Startup/Shutdown
Activity Information
page will display, confirming that a shutdown is in
process. You can refresh this page and wait for the instance to be shutdown.

Startup/Shutdown Activity Information

Click Startup once
the shutdown completes.

Enabling auditing for SELECT statements against a table (OE.CUSTOMERS)

For this tutorial, the sec_admin
user is used and so it is essential you make sure this user actually exists. If
the sec_admin user does not exist, you will have to go back to page 4-4 of the
2-Day Security Guide and follow the instructions on how to create.

The first thing to do is
grant SELECT privileges to the sec_admin on the OE.CUSTOMERS table. The simplest way to do this is to log into
SQL*Plus and issue the grant statement as follows:


[oracle@ludwig ~]$ sqlplus oe/********
SQL*Plus: Release 11.1.0.6.0 – Production on Sat Jan 9 10:15:55 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant select on customers to sec_admin;
Grant succeeded.

Setting up the auditing
of the OE.CUSTOMERS table is done through the sec_admin user. Log in to
Database Control as user sec_admin
and perform the following steps.

Log in to Database Control

From the Home subpage,
click Server to display the Server subpage.

From the Home subpage, click Server

Click Audit Settings under
the Security section to bring up the Audit Settings page.

Click Audit Settings under the Security section to bring up the Audit Settings page

Towards the middle of the
page, select the Audited Objects subpage. You
may note that there are some items already being auditing within my database.
You may have some or you may not but the search facility (entering a
schema/object name/object type) will help you find them if you would like to
investigate.

select the Audited Objects subpage

Click Add.
to display the Add Audited Objects page and add a new object to be
audited.

Click Add. to display the Add Audited Objects page

To add an object, enter the following information:

  • Object
    Type: choose
    Table from the drop down list
  • Table: OE.CUSTOMERS.
  • Available
    Statements: choose
    SELECT and then move it into the
    Selected Statements list

To add an object, enter the following information

Click OK
to add the object and if all goes well, the following confirmation will
appear.

confirmation will appear

As stated before, you can
go back and search for the audited objects. In this case, I’ve entered OE in
the Schema and clicked the Search button—showing the newly added object.

you can go back and search for the audited objects

To activate the newly
added audited object, shut down the database instance and then restart it as
shown above.

Test to make sure the auditing is working by selecting from the table and checking
the audit trail (DBA_AUDIT_TRAIL)

The easiest way to test the auditing of the object we just enabled is to log into SQL*Plus and issue a SELECT statement against the OE.CUSTOMERS table and then check the view DBA_AUDIT_TRAIL. Perform the following—noting that a select on the DBA_AUDIT_TABLE was performed before and after the actual select on the OE.CUSTOMERS.


[oracle@ludwig ~]$ sqlplus sec_admin/8Birdman
SQL*Plus: Release 11.1.0.6.0 – Production on Sat Jan 9 11:54:00 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 132
SQL> col obj_name for a15
SQL> SELECT username, owner, obj_name, action, timestamp FROM dba_audit_trail WHERE username = ‘SEC_ADMIN’;

USERNAME OWNER OBJ_NAME ACTION TIMESTAMP
————— ———- ————— ———- ———
SEC_ADMIN 100 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 100 09-JAN-10

8 rows selected.

SQL> SELECT count(*) FROM oe.customers;

COUNT(*)
———-
0

SQL> SELECT username, owner, obj_name, action, timestamp FROM dba_audit_trail WHERE username = ‘SEC_ADMIN’;

USERNAME OWNER OBJ_NAME ACTION TIMESTAMP
————— ———- ————— ———- ———
SEC_ADMIN 100 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 101 09-JAN-10
SEC_ADMIN 100 09-JAN-10
SEC_ADMIN OE CUSTOMERS 103 09-JAN-10

9 rows selected.

And that is all there is to setting up auditing through Enterprise Manager. While Oracle strongly recommends enabling auditing, and this is a VERY simplistic example, monitoring is a much broader issue that spans the enterprise. What seems like a simple task (starting to audit) is really laden with many questions. You may say you’re auditing your database but proving its effectiveness is a whole other story. You’re on your way; just research what really needs to be audited and how you will monitor the audited items. Turning on auditing is easy; knowing what to do with it is entirely different.

»


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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles