| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: recording SQLPlus activity
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.
1. 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.
3. 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.
OR
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-----
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:
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.
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
-- Please see the official ORACLE-L FAQ: 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: Richard Huntley INET: rhuntley_at_mindleaders.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 - 08:18:21 CDT
 |
 |