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.
