Getting started with standard auditing in Oracle Database 11g
December 17, 2009
Database auditing is big business. Are you ready for the challenge to pass an audit? This article begins with an introduction to Oracles standard auditing that we can build on to answer that question.
Auditing of an Oracle database has became big business. So much so, that entire companies have dedicated complete solutions to the process. Lets first get a level-set of what auditing is, at least from Oracles perspective. Within the 2-Day+ Security Guide, Oracle defines auditing as the monitoring and recording of selected user database actions. Within Oracle, you can:
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. Lets 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 dont know how many times Ive implemented my own specific fine-grain auditing to prove my own actions and point, yes point the finger, towards someone else. This just doesnt 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 theyve either deleted, inserted, or selected or tables theyve dropped, created, or altered. Moreover, lets 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.
When thinking about auditing an Oracle database, its 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.
Setting up or enabling the standard audit trail is quite easy and is outlined in the 2-Day+ Security Guide as follows:
1. Start Database Control
2. Log in as SYS with SYSDBA privileges
3. Click Server to display the Server subpage
4. Click Initialization Parameters in the Database Configuration section
5. Click SPFile to display the SPFile subpage. If you dont use an SPFile just continue to the next step
6. Enter audit_trail in the Name field to find the AUDIT_TRAIL parameter and then click Go
7. Enter the type of auditing you want in the Value field where value can be:
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 privileges
Sqlplus / as sysdba
2. Check the current settings
SQL> show parameter audit NAME TYPE VALUE ---------------------- ----------- ------------------------------ audit_file_dest string /opt/app/oracle/admin/db11/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB
3. 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
SQL> SHUTDOWN SQL> STARTUP
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.
1. audit_sys_operations - 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 value
2. audit_syslog_level this initialization parameter enables SYS and standard OS auditing records to be written to the system using the SYSLOG utility
Now that weve enables operating system auditing, it is always nice to see exactly what weve 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.
1. Being good DBAs weve locked this account and any attempts will be now be met with the account is locked message:
[oracle@ludwig adump]$ sqlplus scott/tiger SQL*Plus: Release 184.108.40.206.0 - Production on Thu Dec 10 06:51:43 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. ERROR: ORA-28000: the account is locked
2. And now, because of auditing, this attempt will not go unnoticed and will be reported in the audit_file_dest location:
[oracle@ludwig ~]$ cd $ORACLE_HOME/adump [oracle@ludwig adump]$ more ora_3817.aud Audit file /opt/app/oracle/admin/db11/adump/ora_3817.aud Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 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: oracle@ludwig (TNS V1-V3) Thu Dec 10 06:48:46 2009 SESSIONID: "280057" ENTRYID: "1" STATEMENT: "1" USERID: "SCOTT" USERHOST: "ludwig" TERMINAL: "pts/1" ACTION: "100" 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.