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 Initialization
Parameters in the Database Configuration section to bring up the
Initialization Parameters page.
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.
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 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.
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.
From the Startup/Shutdown
Confirmation page, click Yes to confirm you actually want to
shutdown the instance.
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.
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 optionsSQL> 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.
From the Home subpage,
click Server to display the Server subpage.
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.
Click Add.
to display the Add Audited Objects page and add a new object to be
audited.
To add an object, enter the following information:
-
Object
Type: choose
Table from the drop down list -
Table:
OE.CUSTOMERS. -
Available
Statements: choose
SELECTand then move it into the
Selected Statements list
Click OK
to add the object and if all goes well, the following 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.
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 optionsSQL> 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-108 rows selected.
SQL> SELECT count(*) FROM oe.customers;
COUNT(*)
———-
0SQL> 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-109 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.
