Getting started with standard auditing in Oracle Database 11g

Advertisements

Database auditing is big business. Are you ready for the challenge to pass an audit? This article begins with an introduction to Oracle's 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. Let's first get a level-set of what auditing is, at least from Oracle's perspective. Within the 2-Day+ Security Guide, Oracle defines auditing as the monitoring and recording of selected user database actions. Within Oracle, you can:

    * Use standard auditing (using initialization parameters and the AUDIT and NOAUDIT) to audit SQL statements, privileges, schemas, objects, and network and multitier activity.
    * Look at activities within the Oracle database that are always audited regardless of whether you want to mess with turning on auditing such as administrator level privileged connections, database startups & shutdowns.
    * Implement what is known as fine-grained auditing that enables you to create policies that define specific conditions that must be met for auditing to occur�enabling the security administrator to audit access based on the content and triggering events.

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. Let's 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 don't know how many times I've implemented my own specific fine-grain auditing to prove my own actions and point, yes point the finger, towards someone else. This just doesn't 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 they've either deleted, inserted, or selected or tables they've dropped, created, or altered. Moreover, let's 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, it's 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 don't 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:

    * DB � Enables Database Auditing where records will primarily be written to the sys.aud$
    * OS � Enables auditing records to be written to the operating system where you must also specify AUDIT_FILE_DEST which tells Oracle where to write the audit record.
    * NONE � Will disable standard auditing
    * DB, EXTENDED � does all that the type of DB does, plus populating the SQL bind and SQL text CLOB-type columns of the sys.aud$ table when available
    * XML � will write to the operating system audit records in XML format
    * EXTENDED � will write to the operating system audit records in XML format but also populates the SQL bind and SQL text CLOB-types columns of the sys.aud$ table when available

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 we've enables operating system auditing, it is always nice to see exactly what we've 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 we've 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 11.1.0.6.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 11.1.0.6.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.