Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. An administrator has the ability to audit as much as the activities of the entire database all the way to any select, insert, or update on a single table. Care should be taken on what you wish to audit, due to the amount of disk space required to store all of this information. In this article, we will discuss the auditing of connects by a single user account.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Jul 27 19:13:26 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Ensure that audit_trail and audit_sys_operations parameters are set to TRUE.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

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

System altered.

SQL> alter system set audit_sys_operations= TRUE scope=spfile;

System altered.

SQL>

3. Restart the database if you had to edit the init parameters.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

4. Ensure that the new values took effect.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean TRUE
audit_trail string TRUE
SQL>

5. Begin auditing of acccount which you suspect is causing an issue.

SQL> audit connect by ljcatt;

Audit succeeded.

SQL>

6. Logon as the user and perform some actions.

SQL> connect ljcatt/ljcatt
Connected.
SQL>

7. Log back in to oracle as sysdba

SQL> connect sys as sysdba
Connected.
SQL>

8. Perform the following SQL to extract the connection information for the USER LJCATT.

SQL> select userhost, terminal, timestamp, action_name from dba_audit_session wh
ere username=’LJCATT’;

USERHOST TERMINAL TIMESTAMP ACTION_NAME
———— ——– ——— ——————
Mylinux Mylinux1 27-JUL-09 LOGON
Mylinux Mylinux1 27-JUL-09 LOGOFF

9. There is a wealth of information that can be obtained by looking at the various dictionary views which cover the Oracle auditing process. You can easily see if someone is accessing your database in an improper way or manipulating data with a direct connection such as SQL*PLUS instead of through an application. This article covered a brief overview of auditing a single user’s connections; however it is easy to see the power that Oracle auditing can provide.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com