Call: +44 (0)1904 557620 Call

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "Kevin Mitnik: New book "The art of intrusion""] [Next entry: "Ben talks about 10g flashback"]

Amis blog talks about logging data in the same table

I saw an interesting post on the Amis Blog today written by Andre Crone titled "Create logging data in the same table" where Andre talks about the problem of logging every update on a table but storing the results in the same table. As Andre points out this cannot be done with one trigger as the mutating table problem rears its head. He goes on to discuss how the problem can be solved with three triggers and a package. Andre gives some example code as well including a sample test to show that it works.

This is a useful post for those wishing to consider alternate auditing methods for monitoring application data but be aware of some of the issues. Trying to put the audit records in the same table can cause complexities. There is also a couple of interesting comment on the Amis blog for this post, the first that suggests that DBMS_WM.ENABLE_VERSION might be an alternative and another commenter suggests that Andres solution is complex.

An interesting post and idea. Holding audit with the data can have good points and bad points. It really depends on the implementation, application, and quantity of data and how often it is amended to make a good qualative decision. Of course Fine Grained Audit is a better solution option in 10g where it now supports update, insert and delete as well as the select statements supported in 9i. Logging and auditing are important aspects of any application design nowadays especially with the regulations that now exist.