Oracle provides the ability to audit your database activities on a multitude of level, providing the administrator the ability to find suspicious activity. In this article we will show how to audit activities on a certain object within the database. 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 Wed Apr 07 20:31:43 2010

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 table ljcatt.test for any select, update, delete or insert operations that may occur. To do this we will user the following command: audit select, update, delete, insert on {schema}.{object_name} by access;

SQL> audit select, update, delete, insert on ljcatt.test by access;

Audit succeeded.

SQL>

7. Perform a select on the database view dba_obj_audit_opts and you can see that auditing is enabled for the object test with actions DEL – delete, INS – insert, SEL – select, and UPD – update being monitored.

SQL> select * from dba_obj_audit_opts;

OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— —————–
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
— — — — — — — — — — — — — — — — —
LJCATT TEST TABLE
-/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-

SQL>

8. Now connect as ljcatt and perform an insert, update, select and delete from your table.

SQL> connect ljcatt
Enter password:
Connected.
SQL>
SQL> insert into ljcatt.test(testing) values(‘testing’);

1 row created.

SQL> update ljcatt.test set testing=’testing_2′ where testing=’testing’;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from ljcatt.test;

TESTING
———-
testing_2

SQL>

SQL> delete from ljcatt.test;

1 rows deleted.

SQL> commit;

Commit complete.

SQL>

9. Perform the following select from the dba_audit_objects and you can see the audit trail from above actions.

SQL> column username format a10
SQL> column action_name format a10
SQL> column obj_name format a6
SQL> select username, action_name, obj_name from sys.dba_audit_object where username=’LJCATT’;

USERNAME ACTION_NAM OBJ_NA
———- ———- ——
LJCATT INSERT TEST
LJCATT UPDATE TEST
LJCATT SELECT TEST
LJCATT DELETE TEST

SQL>

This completes the auditing of a single object within the Oracle RDBMS.

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