DDL auditing - *Extremely* detailed
- From: "Don Granaman" <granaman@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 May 2004 13:18:25 -0700
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 =
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=>TRUE),
I'll have a classic "Homer moment".
-Don Granaman (AKA OraSaurus, Asleep@xxxxxxxxx)
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.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
