RE: Script to Turn Off All Oracle Auditing Options?

To create the audit trail views, run cataudit.sql. To delete the audit trail views, run catnoaud.sql.

To find out which are the active options, use this statement: SELECT * FROM sys.dba_priv_audit_opts;

or for audit options on a specific object: SELECT * FROM sys.dba_obj_audit_opts WHERE owner = 'SCOTT' AND object_name LIKE 'EMP%'; default object audit options:
SELECT * FROM all_def_audit_opts;  

Use the noaudit command to disable auditing.

Reference: Oracle 7 Server Administrator's Guide - Auditing Database Use  

 -----Original Message-----

We are running Oracle 7.3.4.5.0 on an IBM/AIX RISC System/6000: Version 2.3.4.0.0.  

I have a database here with about 600 Oracle users defined in dba_users. However, likely one half or more of the defined users no longer connect to the database. I want to purge out the users that do not use the database. My plan is this:  

Turn on session auditing (See below for potential problem) After a couple of months, determine which users have never logged into the database
Lock or drop the accounts of these users  

I was about to turn on auditing when I realized there was a potential problem. Currently the AUDIT_TRAIL parameter is not defined in the init.ora file, so there is no auditing being performed. Potential Problem: I do not know what auditing options are already defined. For all I know, there may be an option that audits all selects and all DML on all tables for all users
(pretty much worst case scenario). If such an option exists, it will put a
very heavy load on the database, and I will get in trouble with users and management in the morning.  

I want to be able to find out what options are currently set for auditing. Ideally, I would like scripts that will run through all possible auditing options and turn them all off. This way, I can turn on the option(s) I want turned on, and not worry about pre-existing options. Can anybody tell me what data dictionary views hold the auditing options? Also, our system has a synonym named DBA_SYS_AUDIT_OPTS, but no corresponding view. Is there supposed to be a view for this?  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).