Oracle 11g Security - Getting started with standard auditing

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 statement, 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 meat 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. But it does stand to reason that many of us still need to be convinced that malicious intent runs ramped throughout the enterprise as well as from external threats. But 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 tools, 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. And 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 you can 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 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

1. Click Apply
2. Restart the Oracle instance

While setting up standard auditing from the control panel is quite acceptable, this is one of tasks that can also easily be accomplished through the standard PL*SQL interface.

1. Log in as SYS with SYSDBA privileges

Sqlplus / as sysdba

1. 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

1. Set the type of auditing you want by setting the audit_trail parameter

SQL> alter system set audit_trail=OS scope=spfile;

System altered.

1. Set the auditing destination

SQL> alter system set audit_file_dest='/opt/app/oracle/admin/db11/adump' scope=spfile;

System altered.

1. 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

1. 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: "2800

0" 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.