[Previous entry: "Lateral SQL Injection and Conferences and security training"] [Next entry: "Slides from OUG Scotland DBA SIG on Oracle Forensics available"]
05/01/2008: "Conditionally firing triggers"
Post to del.icio.us
Post to Furl
I saw a post on the BAR Solutions blog today titled "Triggers…" that was very interesting as I have had the same issue in the past for different reasons. The blog post was around an issue where triggers became disabled, or rather not re-enabled after an upgrade script that turned them off didnt successfully complete and therefore the triggers didnt get re-enabled.
The author presents a solution based on a semaphore flag that conditionally allows the trigger to fire based on whether the semaphore is set or not.
I posted a comment to the blog which is repeated here:
"nice post and interesting. I have come across the same issue but from a security angle. I wanted to have triggers that would conditionally fire based on certain circumstances (user, group of users (role), time..). I came up with a similar solution but simpler. I used the “when” clause of the trigger to detect which user/role fired the trigger. in this way it was possible to control when the triggers fired. This meant triggers could always remain enabled but not fire for certain cionditions. I did some extensive testing and the performance “loss” due to the when clause was much less than executing checks in the body of the trigger. Running a trace shows that a loss less background work is done in the trigger. The losses i saw were shown at a high level in as a 3% impact for executing the when clause compared to 37% for executing the body. I wrote a presentation that is called “does vpd, fga and audit really cause a performance impact” - there is a link on my Oracle security white papers page including some sample code.
Other areas we looked at were the OF clause as well."
I thought it worth a mention here as this is a common issue where for security reasons triggers are used as part of an audit trail but need to fire conditionally based on user, role, time.... These are facilities that are available for other Oracle audit solutions such as FGA but not out of the box for triggers. Nice post though and useful.
