Oracle provides the ability to audit your database activities on a multitude of level which providing the administrator the ability to find suspicious activity. This article covers the display of all auditing which is currently defined in an Oracle RDBMS. This procedure will work regardless of OS type.
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
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 Dec 17 07:11:59 2009
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 display all auditing which is currently defined in your ORACLE RDBMS.
————————————————————–
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
———————————————————————–
———————————————————————–
Beginning of PL/SQL Block output
———————————————————————–
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 ‘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_audit_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_opts)
20 loop
21 dbms_output.put_line(v_stmt.stmt);
22 end loop;
23
24 end;
25 /
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
———————————————————————–
This completes viewing of all auditing occurring within an Oracle RDBMS.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com