RE: DDL auditing - *Extremely* detailed

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: