Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DDL auditing - *Extremely* detailed

RE: DDL auditing - *Extremely* detailed

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Tue, 4 May 2004 08:33:37 -0400
Message-ID: <42BBD772AC30EA428B057864E203C99911401F@MSGBOSCLF2WIN.DMN1.FMR.COM>

Why would any other method be better than Oracle auditing?

-----Original Message-----

From: Don Granaman [] Sent: Tuesday, May 04, 2004 4:18 PM
Subject: DDL auditing - *Extremely* detailed=20

I need to create a DDL auditing trigger (initially, for a = 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.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Tue May 04 2004 - 07:31:16 CDT

Original text of this message