Rich, Questions: 1. Does that mean I will need a trigger on each of my 1500 tables? 2. How will I know which among the privileged users did the alter. 3. What about objects other than tables. Say, drop sequence, etc. 4. Will will also increase my maintenance workload.
When I say "tagged", it would be great if everything the user does gets tagged, i.e. alter table, syn, seq, ind, "select wages from ", etc. Ray >From : Richard Huntley <[EMAIL PROTECTED]> Reply-To : [EMAIL PROTECTED] To : Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject : RE: recording SQLPlus activity Date : Tue, 25 Jun 2002 12:45:18 -0800 Ray, Just some thoughts on plan (b)...Inspiration for these thoughts can be credited to Joe Testa who mentioned a clever way of restricting DML by application via the dbms_application_info package. To accomplish what you've outlines, how about this: 1. If developers login using SQL*Plus from their individual PC's rather than from the box acting as the DB server, you could create a trigger on logins that grabs the MACHINE value from v$session and dump it into a table. If you login as the same USER from different workstations, the MACHINE field of v$session will distinguish the two. 2. See #1, trigger fired on logon, so everyone gets tagged. 3. Apply triggers to the tables that will read an id from the table mentioned in #1 that holds the MACHINE value and if that id exists in a table containing only the ids of developers that should be allowed to alter/drop a table, then drop out of the trigger, else raise an exception. Then, to track (audit) all DML, etc...turn on object auditing to log all the changes that occur. HTH, Rich -----Original Message----- Sent: Tuesday, June 25, 2002 2:24 PM To: Multiple recipients of list ORACLE-L Craig, This relates to SQLPLUS. A majority of our developers use sqlplus. There are 2 choices: (a) separate environments for each developer using GRANTS, etc. (b) one single application owner account, where all the developers work. Now, (b) is several hundred times more efficient, and I am looking for input on how to make (b) work for me, not (a). What I would like, ideally speaking: 1. Users log into SQLPlus into the same account. They get tagged. All actions are recorded, especially DDL. Who, When, What SQL, 2. No direct SQLPlus access, i.e. not without being tagged. 3. Restrictions: Only specific users (identified by tags) are to be allowed alter/drop table, etc. Thus, everyone works in the same area, but I'm watching and controlling. 1. PUPBLD does not cut it since its not at the object level 2. Redo logs: One problem is that if everyone is working in the same user, we cant tell "who". 3. Audit: what audit can I turn on? thanks. Ray >From : "Craig Munday" <[EMAIL PROTECTED]> Reply-To : [EMAIL PROTECTED] To : Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject : RE: recording SQLPlus activity Date : Mon, 24 Jun 2002 18:08:20 -0800 Ray, Why would you want to record every SQL statement that your developers issue? Are they just using SQL*Plus or some other language? Cheers, Craig. -----Original Message----- Sent: Tuesday, 25 June 2002 10:53 AM To: Multiple recipients of list ORACLE-L I have just been moved to a group with several hundred developers, and to say the least the environment is chaotic. Without putting limits on my developers (such as via READONLY user, etc.), is there some way that every command that a developer executes using SQLPlus gets recorded (by userid and time)? Ray _________________________________________________________________ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Gordon INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
