Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In this article we will show how to audit use of a system privilege by a particular user in the Oracle RDBMS. This procedure will work on any OS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Nov 14 13:28:31 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>

3. Check the current setting of the initialization parameter AUDIT_TRAIL with the command: show parameter {initialization_parameter}
The initialization parameter AUDIT_TRAIL controls auditing at the database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure we want the definition to be set to DB.

SQL> show parameter AUDIT_TRAIL

NAME TYPE VALUE
———————————— ———– ——————————
Audit_trail string DB

SQL>

4. If the parameter is not set to DB and you are using a SPFILE, execute the command: alter system set audit_trail=DB scope=spfile; and bounce the database. If you are using an init file, shutdown the database, add the string AUDIT_TRIAL=DB and start the database.

NOTE: If you do not know if you are using an SPFILE or INIT file, search ORACLE SPFILE vs INIT File

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

System altered.

SQL>

5. Restart your database if you had to change the audit_trail parameter.

6. In this procedure we will audit the creation of tables by the user LJCATT. To perform this auditing, we will use the command: audit create table by {user_name};

SQL> connect / as sysdba
Connected.
SQL> audit create table by ljcatt;

Audit succeeded.

SQL>

7. Perform a select on the view DBA_PRIV_AUDIT_OPTS, to verify that auditing is enabled.

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME PRIVILEGE
—————————— —————————————-

LJCATT CREATE TABLE

SQL>

8. Logon as the user you are auditing and create a table.

SQL> connect ljcatt
Enter password:
Connected.
SQL> create table test_audit(col_a varchar2(30));

Table created.

SQL>

9. Logon with sysdba privileges and verify that the create table action was recorded in you DBA_AUDIT_TRAIL view. As seen below the audit record for the create table privilege was recorded.

SQL> connect / as sysdba
Connected.
SQL>
SQL> select username, action_name from dba_audit_trail;

USERNAME ACTION_NAM
———- ———-
LJCATT CREATE TABLE

This completes auditing of a system privilege by a user account within the Oracle RDBMS.

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