Re: DDL auditing - *Extremely* detailed
- From: "Don Granaman" <granaman@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 May 2004 07:37:45 -0700
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
----- Original Message -----
From: "Pete Finnigan" <oracle_list@xxxxxxxxxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, May 04, 2004 4:40 AM
Subject: Re: DDL auditing - *Extremely* detailed
> 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 www.psoug.org 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
> http://www.petefinnigan.com/tools.htm 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
> email:pete@xxxxxxxxxxxxxxxx
> Web site: http://www.petefinnigan.com - Oracle security audit specialists
> Book:Oracle security step-by-step Guide - see http://store.sans.org for
details.
>
> ----------------------------------------------------------------
> 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 //www.freelists.org/archives/oracle-l/
> FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
