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