| 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
My solution for this is a combination of the following...
Using this I get alerted via email when a DDL event occurs in production. I can review a daily report to see who did what when. I can go get the trace file if I need the actual SQL.
-----Original Message-----
From: Don Granaman [mailto:granaman_at_cox.net]
Sent: Tuesday, May 04, 2004 3:18 PM
To: oracle-l_at_freelists.org
Subject: DDL auditing - *Extremely* detailed
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_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
 |
 |