Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jan 12, 2010

Creating a standard audit trail in Oracle Database 11g

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date