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 most cases the DBA knows which object in the database they suspect is causing a problem, thus they only wish to monitor that object. However, in a heavily used system, the auditing process can produce a large amount of data and should be discontinued once it is obsolete. This article covers the removal of audit definitions for a specific object on an Oracle RDBMS. This procedure will work on any OS.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire 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 AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definition for user accounts are stored in views:
DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS.

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

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Feb 27 06:38:462010

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>

4. In this procedure we will audit the table account LJCATT.TEST_AUDIT, execute the following command to begin the auditing process for our specified object: audit insert, update, delete on
{schema.object_name} by access;

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

Audit succeeded.

SQL>

5. Perform a select from the views DBA_OBJ_AUDIT_OPTS to see the audit policies defined by the command in step 4 and as you can see the object is setup for auditing.

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_AUDIT TABLE
-/- -/- -/- A/A -/- -/- A/A -/- -/- -/- A/A -/- -/- -/- -/- -/- -/-

SQL>

6. Once you have completed your analysis, you want to remove this audit policy, execute the following PL/SQL block.

NOTE: This script will remove all object auditing. To only remove auditing on LJCATT.TEST_AUDIT table replace the line

for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)

with

for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts
where owner=’LJCATT’ and OBJECT_NAME=’TEST_AUDIT’)

———————————————————————–
Beginning removal of Oracle auditing definitions for a specific object PL/SQL Block
———————————————————————–

set serveroutput on

declare

v_ct number;

begin

for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

end;
/

———————————————————————–
End removal of Oracle auditing definitions for a specific object PL/SQL Block
———————————————————————–
———————————————————————–
OUPUT
———————————————————————–

SQL> declare
2
3 v_ct number;
4
5 begin
6
7 for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt
from sys.dba_obj_audit
_opts)
8 loop
9 execute immediate(v_stmt.stmt);
10 end loop;
11
12
13
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>
———————————————————————–
End of OUPUT
———————————————————————–

7. Once completed re-execute the following SQL to verify that the auditing definition have been removed from the system: select * from DBA_OBJ_AUDIT_OPTS;

SQL> select * from DBA_OBJ_AUDIT_OPTS;

no rows selected

SQL>

That completes removal of all Oracle auditing for a specific object in the RDBMS.

Larry J. Catt
oracle@allcompute.com
www.allcompute.com