| 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: DDL auditing - *Extremely* detailed
Why would any other method be better than Oracle auditing?
-----Original Message-----
From: Don Granaman [mailto:granaman_at_cox.net]
Sent: Tuesday, May 04, 2004 4:18 PM
To: oracle-l_at_freelists.org
Subject: DDL auditing - *Extremely* detailed=20
I need to create a DDL auditing trigger (initially, for a 9.2.0.4 =
database)
that logs *extremely* detailed information about exactly who, what, =
when,
from where, ad infinitum whenever *anyone* makes modifications to a =
schema.
Being able to tell that they had a hangnail on their right thumb when it =
hit
the spacebar between "alter" and "table" might even prove useful.
The motivation is an wildly irresponsible, extremely careless, and =
utterly
dishonest developer running rampant in production - then (as if that =
were
not enough) blaming anyone and anything else when their screwups are
discovered. Unfortunately, her manager is slightly less intelligent =
than
the average hamster and always believes her (or at least aids/supports =
the
attempted blame-shifting) - even when presented with overwhelming =
evidence
to the contrary. What I would actually like is a logon trigger to =
"execute
duhveloper" (preferably, with the "cascade upward" option), but would =
settle
for very robust DDL triggers. In three recent fiascoes, ill-considered =
DDL
that whacked mass quantities of production data was submitted by this =
same
person (Would you drop the primary key constraint, all foreign keys, and =
all
check constraints , even NOT NULL, on a table with a half-billion rows =
to
resolve a "locking problem"? [Mis-diagnosed by DUHveloper]). She =
insisted
on continually denying it in spite of standard audit logs (auditing all =
DDL)
and a ton of other "circumstantial" evidence. [Standard DDL auditing
doesn't provide, for example, the actual statement issued.]
The trigger needs to be safe and this problematic application schema is =
far
from it, so it must be an "on database" trigger with a schema filter =
inside
[e.g. "if ora_dict_obj_owner in ('X','Y') ..."] - if a schema filter is
desired.
Also, the (custom) audit trail table needs to be secure and the user =
issuing
the DDL to fire the trigger should NOT have "select on v$session".
After some RingTFM, I experimented with alter/drop/create/etc triggers,
event attribute functions (ora_login_user, ...), SYS_CONTEXT, USERENV(),
etc. I can get everything I want, but perhaps not all in one place. =
Some
of it (e.g. MODULE & PROGRAM) seem to be only in v$session (and its
ancestors), but not its descendents and cousins. [Corrections =
welcomed!]
I started developing a trigger and a package for this tonight and have =
it
(mostly) working, but it is a bit of a Rube Goldberg contraption with
ora_some_things, USERENV('OTHER THINGS'), SYS_CONTEXT('THIS','THAT') and
"select THE_OTHER_THINGS from V$SESSION where AUDSID =3D
userenv('SESSIONID')" scattered about in a trigger and a package. I =
know
that this can all be obtained more efficiently by "consolidation", but =
hey,
its MY
turn to hack away! (OK, OK - I'll clean it up!)
Does anyone know of any *really* good sources for this? I suspect that
someone (Pete?) already has something like this on a site or in a =
whitepaper
somewhere. I plan to finish the rough draft of this sometime tomorrow
(before I'll see replies from the list), but I wouldn't mind seeing =
others'
incarnations/visions for this sort of thing.
If someone points me to something like
dbms_guide_to_the_universe.all_imaginable_info(HANGNAILS=3D>TRUE),
I'll have a classic "Homer moment".
-Don Granaman (AKA OraSaurus, Asleep_at_the.wheel)
I have been working on a few new "real world business rules"-oriented
programming
constructs. Most promising are the "maybe if" statement and the =
"sometimes
do" loop, but correct implementation is tricky.
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |