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