Creating a standard audit trail in Oracle Database 11gJanuary 12, 2010 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:
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 [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
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:
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 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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
