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: Don Granaman <>
Date: Tue, 4 May 2004 20:37:17 -0400
Message-ID: <003b01c432ae$88321b60$6401a8c0@dilbert>

As an answer to all these suggestions, I'll reply to only this one. The majority of the responses seemed to be pointers/RTFMish stuff about standard auditing (e.g SQL> audit table;) This is already in place. The problem is that it simply doesn't have enough detail to tell exactly what happened when. For example, you can tell that someone logged in as a particular user "altered" a particular object, and a few other things, but cannot tell exactly what they did. I can get a *lot* more detail in 9i with even a fairly simple trigger - including the exact SQL. As for the RTFM thing, I did. Chapter 16 of the Oracle9i (9.2) Application Developer's Guide - Fundamentals on event attribute functions was particularly helpful.

As for the stuff mentioned here, and in other similar posts, the problem is that this nightmare duhveloper was at the company before I and had complete control and authority over everything before I came in and inherited this mess. Trying to get the "developers are gods and should not be constrained in any way" (e.g. change control, limited access to production, ad naseum) attitude changed is more difficult that I ever imagined. Her manager and her are pals at and outside work and the manager backs her unquestioningly - even when she is obviously dead wrong and they both know it. No amount of logic or reasoning with these two works. *Seriously*. Its a combination of politics and utter stupidity. I have pushed hard for managed releases, formal change control, no developer access to any production systems, and the rest. So far though, it hasn't really happened. With the last few debacles though, I've been dealing directly with the CTO and founder, have proved beyond any doubt that their "diagnosis" of these recent fiascoes has been 100% wrong and that they even blatantly lied to him. Now he is doing the pushing, so it might actually happen.

Thanks everyone..
-Don Granaman

> Hi Don,
> I cannot think of a paper with a good DDL trigger that captures
> everything. That isn't to say there is not a good example in one of the
> papers on my site. You can also try Daniel Morgans site I
> think, he has examples for quite a lot of things on there related to
> system triggers. Try a search on the c.d.o* as i seem to remember a
> discussion about system triggers recently on there.
> What makes you think this developer and her manager are going to take
> any more notice of a detailed audit log from a trigger? If they totally
> dismiss the audit trail as fiction? I know you already know the answer
> to this but why is she even allowed to alter anything in a production
> database. What about change control, release mechanisms, why is a
> developer debugging "locking problems" by "trying" things?. Why has she
> got privileges in production to do DDL in the first place. I would
> advocate that she only should have read only permissions to investigate
> issues. She should be restricted to test and development databases. This
> sounds like a management issue? - someone needs to justify why this
> person has access to alter the production database and if it is decided
> that she does need access to alter things in production the privileges
> should be removed after use and then given out only when authorised to
> do so.
> Also you intimate that she might change your audit log as you suggest it
> needs to be secured? It would be better to write the log off to the OS,
> either from your trigger or put a trigger on your audit table that
> writes the record off to a file when a line is added, that way you have
> both. You can then copy this to a secure machine using syslog if needed
> as well. Ethans idea of generating trace seems like a good idea, it
> should capture everything, my only worry would be the amount of trace it
> generates and what if she logs in with another user account?? - what
> about archivelogs? and LogMiner? that should give you the proof you
> need.
> Your DDL triggers should be OK, think about writing to the OS, also
> Ethans trace idea is OK but needs to be managed for quantity. Also audit
> this developers privileges, I have a script that prints them out
> hierarchically including all privileges inherited from roles etc. Its at
> and discuss with the manager of
> her manager why she is changing database structure without change
> control!! - in fact if she does everything through change control - her
> SQL will need to be checked before its run and she cannot deny it as
> others will have approved her code first!
> good luck Don.
> kind regards
> Pete
> --
> Pete Finnigan
> Web site: - Oracle security audit specialists
> Book:Oracle security step-by-step Guide - see for
> ----------------------------------------------------------------
> 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 - 19:34:21 CDT

Original text of this message