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 user account they suspect is causing a problem, thus they only wish to monitor that account. 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 user account 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 definitions 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 Fri Jan 15 19:18:09 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. In this procedure we will audit the account LJCATT, execute the following command to begin the auditing process for your specified user account: audit session by {user_name};
SQL> audit session 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 account is setup for auditing.
SQL> select user_name, privilege from dba_priv_audit_opts;
USER_NAME PRIVILEGE
— —-
LJCATT ALTER SYSTEM
LJCATT AUDIT SYSTEM
LJCATT CREATE SESSION
SQL>
6. Once you have completed your analysis, you want to remove this audit policy, execute the following PL/SQL block.
————————————————————–
Beginning removal of Oracle auditing definitions for a specific user account 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;
end;
/
——————————————————————
End removal of Oracle auditing definitions for a specific user account 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 end;
15 /
PL/SQL procedure successfully completed.
SQL>
——————————————————————
End of OUPUT
——————————————————————
7. Once completed re-execute the following SQL to verify that the auditing definitions have been removed from the system: select user_name, privilege from dba_priv_audit_opts;
SQL> select user_name, privilege from dba_priv_audit_opts;
no rows selected
SQL>
That completes removal of all Oracle auditing for a specific user account in the RDBMS.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com