RE: Data auditing: triggers vs application code

  • From: "Kennedy, Jim" <jim_kennedy@xxxxxxxxxx>
  • To: <dubey.sandeep@xxxxxxxxx>, "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
  • Date: Wed, 21 Sep 2005 14:29:43 -0700

And how will the application code guarentee that they will audit changes made 
to the table via some other mechanism? (eg sqlplus)  Answer: They can't.  The 
trigger getting disabled is a security thing.  Don't allow that many peoiple 
the ability to disable the trigger.  Someone did.  Someone could change the 
Java code or write Java code in the application that went around the trigger.  
How is that any more secure than in the database next to where the change is? 
(it isn't, it is less secure.)  With thousands of line of Java code how will 
you know what sections aren't audited and should be vs which are?  The trigger 
is the most performant since you have to have the information.


From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Sandeep Dubey
Sent: Wed 9/21/2005 1:36 PM
To: Igor Neyman
Cc: oracle-l
Subject: Re: Data auditing: triggers vs application code

Igor- Application will be based on J2EE. Delete to a live table and insert to 
audit table will be part of one single transaction one thread. I don't think it 
will be an extra trip. Moreover the point being putforward is we will deploy 
multiple application servers (10s or more) to do scaling. 

Here back in past the trigger got somehow disabled - packaged called from 
trigger was invalidated, later fixed the packge but could not check the 
triggers. Application ran fine without complaining only later to found no audit 
records. I think that mindset of application architect is pushing against going 
with triggers. It's not though easy to disable the part og Java code that does 
the audit -you need to actually go and remove the code portion. 
Stephane - Thanks for your comments
Thanks, Sandeep
On 9/21/05, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote: 

        Regarding your 2nd point:
        In high transaction environment making extra trip from application to 
database to log data in adit table will cause more overhead than using trigger 
to populate audit table. 
        Now, if you are afraid that someone could disable auditing triggers, 
aren't you afraid that someone could disable "auditing part" of application? 
        Igor Neyman


        From: oracle-l-bounce@xxxxxxxxxxxxx [mailto: 
oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx> ] On 
Behalf Of Sandeep Dubey
        Sent: Wednesday, September 21, 2005 3:52 PM
        To: oracle-l
        Subject: Data auditing: triggers vs application code
        I need to keep track of history of data change in the live tables. Two 
methods are being compared.
        1. Create triggers on the live table. For each insert/update/delete 
insert a row in the audit table. 
        2. Let the application take care of inserting the data in the audit 
table itself.
        The cons against using triggers in the order of severity are:
        1. If somehow the triggers are disabled in production, the application 
goes through without noticing it and no audit data will be captured.
        2. In a high transaction environment triggers have overhead.
        As a database person, I am inclined to use triggers. But I fail to 
guarantee that trigger will never get disabled. If it is disabled somehow 
application SHOULD stop. (It is impractical to check the status of all 
underlying triggers before each transaction). 
        I would like to hear how you guys handle data auditing  in your system.

Other related posts: