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: Post, Ethan <>
Date: Tue, 4 May 2004 01:29:54 -0500
Message-ID: <>

My solution for this is a combination of the following...

  1. DDL audit trigger throws basic DDL info to alert log, i.e. TRUNCATE BY JIM ON TABLE FOO.
  2. Standard auditing, daily report is sent to me.
  3. Trigger which sets 10046 trace and special trace file identified for Duhveloper.

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 [] Sent: Tuesday, May 04, 2004 3:18 PM
Subject: DDL auditing - *Extremely* detailed

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 = 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.

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 - 01:27:36 CDT

Original text of this message