Oracle-L: RE: recording SQLPlus activity

From: Gesler, Rich <RGesler_at_lexington.com>
Date: Thu, 27 Jun 2002 06:23:25 -0800
Message-ID: <F001.0048A4AA.20020627062325@fatcity.com>

Ray,

With several hundred developers, it seems to me that a few, well defined environments are needed. May be there are key projects or functional teams of developers that can be grouped together? This way the project would have some sort of service level associated with it instead of the demands of individuals(backup schedule/availability). Also, some of the control issues can and should be placed at the project lead role. For instance, coordinating a refresh from production is a nightmare if you poll every developer for timing.
At this development/sandbox level have a schema that owns all objects. Create public synonms for all objects. Every developer logins in under their own schema. I would let them do DDL in their own schema but some sort of change control may be necessary to modify the owner's schema. This can be a big bottle-neck for development, so maybe certain developers are given the owner's schema password.
Then a QA environment would unite all of the projects into an integrated environment. Everything should be locked down here. Set it up just like production. Not only are you looking for good integration/negative testing here, you should be getting an idea on performance and sizing information.

It comes down to how much control is needed for a development environment. Development is either paralyzed by everyone running over one another or harsh DBA, centric change management. There is a happy medium if all parties discuss, agree, publish and implement.

HTHs,
Rich Gesler
<>< Saved by Grace

-----Original Message-----
Sent: Thursday, June 27, 2002 9:18 AM
To: Multiple recipients of list ORACLE-L

Ray,
Answers:
4. Seems to me, no matter which route you take, if you're really serious about logging (tagging)
EVERYTHING, then plan on your maintenance workload increasing SIGNIFICANTLY. No clean, easy way
to go about capturing everything for several hundred users with 1500 tables.

The only way I'm aware of to capture DETAILED information about changes is via triggers. Object auditing will let you know some object in the DB has changed, but won't point out what the specific changes were. Oracle 9i has something called Fine-Grained Object Auditing via the DBMS_FGA package, but there is some work involved with creating your own procedures to enforce the audit policies you'd like to add. With Auditing, you're just tagging, not blocking anyone from actually making changes. It's even more difficult in your case to know what user performed an alter or other change given the users use the same account. As I mentioned previously, by capturing the MACHINE and SID values of v$session, if you're using triggers you could record that info on login, then when you wanted to see what specific changes were made, just tie it back to using the SID and you know who it is by looking at what machine they logged in from.
Ramon, looks like he's on the right track with his DDL trigger.

It seems that for such a large number of developers and so many DB objects, tagging needs to be scaled back to
only the most important things. To get at the level of detail you're looking for, you'll probably end up having to break it apart and handle the DDL and DML separately via triggers.

Alternatives to consider:

In keeping with not having a separate account for each user...is it possible to
1. Have an account that owns all of the 1500 tables and other objects, then a scaled down account, that accesses
the objects but doesn't actually own anything on it's own. Some tables could have update on specific columns, some logging tables with delete, etc... All controlled through the account that owns no objects but accessed by all the developers. Changes are then only made via change request that you implement on the account that actually owns

the objects. This way, tagging could be scaled back greatly.

2. If you're lucky enough to be running on some UNIX OS, you could create an account on the box itself that kicks them straight into an Oracle session, doesn't allow them to host out to the OS and when they exit, drops the connection from the box. A real easy way of tagging everything would be using the "script" command, which logs everything that appears

on screen to a file, typescript by default.

Good luck!

-----Original Message-----
<mailto:rgordon_1_at_hotmail.com> ]
Sent: Tuesday, June 25, 2002 5:44 PM
To: Multiple recipients of list ORACLE-L

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 <rhuntley_at_mindleaders.com>
Reply-To : ORACLE-L_at_fatcity.com
To : Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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:

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.

PUPBLD does not cut it since its not at the object level
Redo logs: One problem is that if everyone is working in the same user, we cant tell "who".
Audit: what audit can I turn on?

thanks.

Ray

>From : "Craig Munday" <Craig.Munday_at_ecard.com.au>
Reply-To : ORACLE-L_at_fatcity.com
To : Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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 <http://mobile.msn.com>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com>  
-- 
Author: Ray Gordon 
  INET: rgordon_1_at_hotmail.com 

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: 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). 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gesler, Rich
  INET: RGesler_at_lexington.com

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: 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 Thu Jun 27 2002 - 09:23:25 CDT