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 a heavily used system auditing process can produce a large amount of data, thus it should be used sparingly. This article covers the removal of audit definitions from an Oracle RDBMS, without disabling your ability to perform auditing. 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 definitions can be seen in three views: DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS. We can use these views to generate the appropriate commands to remove the current audit definitions from our database.
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 Tue May 11 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>
4. Execute the following PL/SQL block to list all auditing being performed.
———————————————————————–
Beginning of PL/SQL Block
———————————————————————–
set serveroutput on
declare
v_ct number;
begin
–this block removes all auditing from an oracle RDBMS system.
for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;
for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt from
sys.dba_stmt_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;
for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;
end;
/
———————————————————————–
end of PL/SQL Block
———————————————————————–
———————————————————————–
output
———————————————————————–
audit all by LJCATT
audit all by LJCATT
audit CREATE SESSION by LJCATT
audit CREATE TABLE by LJCATT
audit all on LJCATT.TEST
PL/SQL procedure successfully completed.
SQL>
———————————————————————–
Ending of PL/SQL Block output
———————————————————————–
5. Once you have validated that you do wish to remove all of these auditing options, execute the following PL/SQL block:
—————————————————————————–
Beginning removal of Oracle auditing definitions PL/SQL Block
————————————————————————–
set serveroutput on
declare
v_ct number;
begin
–this block removes all auditing from an oracle RDBMS system.
for v_stmt in(select ‘noaudit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;
for v_stmt in(select ‘noaudit ‘ ||audit_option||’ by ‘ ||user_name as stmt from
sys.dba_stmt_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;
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 PL/SQL Block
——————————————————————————-
——————————————————————————-
OUPUT
——————————————————————————-
SQL> set serveroutput on
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 –this block removes all auditing from an oracle RDBMS system.
8
9 for v_stmt in(select ‘noaudit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
10 loop
11 execute immediate(v_stmt.stmt);
12 end loop;
13
14 for v_stmt in(select ‘noaudit ‘ ||audit_option||’ by ‘ ||user_name as stmt
from sys.dba_stmt_au
dit_opts)
15 loop
16 execute immediate(v_stmt.stmt);
17 end loop;
18
19 for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt
from sys.dba_obj_audit
_opts)
20 loop
21 execute immediate(v_stmt.stmt);
22 end loop;
23
24 end;
25 /
PL/SQL procedure successfully completed.
SQL>
——————————————————————————-
End of OUPUT
——————————————————————————-
6. Execute the PL/SQL block from step 4 to validate that all Oracle auditing definitions have been removed from the RDBMS. As you can see no values are returned because they no longer exist.
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 –this block removes all auditing from an oracle RDBMS system.
8
9 for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
10 loop
11 dbms_output.put_line(v_stmt.stmt);
12 end loop;
13
14 for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt
from sys.dba_stmt_audi
t_opts)
15 loop
16 dbms_output.put_line(v_stmt.stmt);
17 end loop;
18
19 for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_o
pts)
20 loop
21 dbms_output.put_line(v_stmt.stmt);
22 end loop;
23
24 end;
25 /
PL/SQL procedure successfully completed.
SQL>
That completes removal of all Oracle auditing from the RDBMS.
Larry J. Catt
oracle@allcompute.com
www.allcompute.com