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 privilege and user in the database they suspect is causing a problem, thus they only wish to monitor that privilege. 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 privilege 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 Mar 6 10:22:512010

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 create table privilege by the user LJCATT, execute the following command to begin the auditing process for our specified object: audit create table by {schema.object_name};

SQL> audit create table by ljcatt;

Audit succeeded.

SQL>

5. Perform a select from the views DBA_PRIV_AUDIT_OPTS to see the audit policies defined by the command in step 4 and as you can see the privilege ‘create table ‘ by LJCATT is setup for auditing.

SQL> select user_name, privilege from DBA_PRIV_AUDIT_OPTS;

USER_NAME PRIVILEGE
—————————— —————————————-
LJCATT CREATE TABLE

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 privilege auditing. To only remove auditing on LJCATT privileges replace the line

for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘||user_name as stmt from
sys.dba_obj_audit_opts)

with

for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘||user_name as stmt from sys.dba_obj_audit_opts where USER_NAME=’LJCATT’)

———————————————————————–
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 ‘||privilege||’ by ‘|| user_name as stmt from
sys.dba_priv_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>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘|| user_name as stmt from
sys.dba_priv_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_PRIV_AUDIT_OPTS;

SQL> select * from DBA_PRIV_AUDIT_OPTS;

no rows selected

SQL>

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

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