Oracle-L: Re: Script to Turn Off All Oracle Auditing Options?

From: <Charlie_Mengler_at_HomeDepot.com>
Date: Tue, 04 Mar 2003 17:35:15 -0800
Message-ID: <F001.005602DF.20030304173515@fatcity.com>

I see yet another poor soul still supporting V7.3.4.5 as I am doing on V2.6 Solaris.

You should be warned that on a couple of different occasions (I'm a slow learner)
when I launched a SQL script which changed AUDITing on hundreds of objects, Oracle went "bonkers". It appearded as though the memory resident portion of
the data dictionary got scrambled. For example Oracle started spewing errors
that it could not find the PART table. Needless to say this caused considerable
problems for my end users.

So now whenever I need to changing AUDITing, I run the script immediately before I am/can bounced the database; just to be safe.

HTH & YMMV HAND!

                                                                                                                                 
                      "Sam Bootsma"                                                                                              
                      <sbootsma_at_gbrownc        To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>             
                      .on.ca>                  cc:                                                                               
                      Sent by:                 Subject:  Script to Turn Off All Oracle Auditing Options?                         
                      root_at_fatcity.com                                                                                           
                                                                                                                                 
                                                                                                                                 
                      03/04/2003 01:50                                                                                           
                      PM                                                                                                         
                      Please respond to                                                                                          
                      ORACLE-L

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?

I am new to this company, and new to the database. Any help you can provide is appreciated.

Thanks,

Sam Bootsma
sbootsma_at_gbrownc.on.ca

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: Charlie_Mengler_at_HomeDepot.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). Received on Tue Mar 04 2003 - 19:35:15 CST