Why would any other method be better than Oracle auditing? -----Original Message----- From: Don Granaman [mailto:granaman@xxxxxxx] Sent: Tuesday, May 04, 2004 4:18 PM To: oracle-l@xxxxxxxxxxxxx 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@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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------