| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.
 |
 |