From oracle-l-bounce@freelists.org Wed Sep 21 16:36:08 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j8LLa7cC009536 for ; Wed, 21 Sep 2005 16:36:07 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j8LLa16H009526 for ; Wed, 21 Sep 2005 16:36:01 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CDB521EE8D1; Wed, 21 Sep 2005 16:35:48 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15470-01; Wed, 21 Sep 2005 16:35:48 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 17F581EE5E6; Wed, 21 Sep 2005 16:35:48 -0500 (EST) x-mimeole: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5BEF4.28A89EEE" Subject: RE: Data auditing: triggers vs application code Date: Wed, 21 Sep 2005 14:29:43 -0700 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Data auditing: triggers vs application code Thread-Index: AcW+7D0Dx8RJ6Fc9QoO11Gqd+0YkewAB1gkg From: "Kennedy, Jim" To: , "Igor Neyman" Cc: "oracle-l" X-OriginalArrivalTime: 21 Sep 2005 21:33:51.0615 (UTC) FILETIME=[28D320F0:01C5BEF4] X-archive-position: 25779 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jim_kennedy@mentor.com Precedence: normal Reply-To: jim_kennedy@mentor.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-4.4 required=5.0 tests=AWL,BAYES_00, HTML_FONTCOLOR_BLUE,HTML_MESSAGE autolearn=no version=2.63 ------_=_NextPart_001_01C5BEF4.28A89EEE Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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. Jim ________________________________ From: oracle-l-bounce@freelists.org 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.=20 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.=20 =20 Stephane - Thanks for your comments =20 Thanks, Sandeep =20 On 9/21/05, Igor Neyman wrote:=20 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.=20 =20 Now, if you are afraid that someone could disable auditing triggers, = aren't you afraid that someone could disable "auditing part" of = application?=20 =20 Igor Neyman =20 ________________________________ From: oracle-l-bounce@freelists.org [mailto: = oracle-l-bounce@freelists.org ] = On Behalf Of Sandeep Dubey Sent: Wednesday, September 21, 2005 3:52 PM To: oracle-l Subject: Data auditing: triggers vs application code =09 =20 =09 Hi, =20 I need to keep track of history of data change in the live tables. Two = methods are being compared. =20 1. Create triggers on the live table. For each insert/update/delete = insert a row in the audit table.=20 2. Let the application take care of inserting the data in the audit = table itself. =20 The cons against using triggers in the order of severity are: =20 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. =20 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).=20 =20 I would like to hear how you guys handle data auditing in your system. =20 Thanks =20 Sandeep =20 ------_=_NextPart_001_01C5BEF4.28A89EEE Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =0A= =0A= =0A= =0A=
=0A=
And how will = the application =0A= code guarentee that they will audit changes made to the table via some = other =0A= mechanism? (eg sqlplus)  Answer: They can't.  The trigger = getting =0A= disabled is a security thing.  Don't allow that many peoiple the = ability to =0A= disable the trigger.  Someone did.  Someone could change the = Java code =0A= or write Java code in the application that went around the = trigger.  How is =0A= that any more secure than in the database next to where the change is? = (it =0A= isn't, it is less secure.)  With thousands of line of Java code how = will =0A= you know what sections aren't audited and should be vs which = are?  The =0A= trigger is the most performant since you have to have the =0A= information.
=0A=
Jim
=0A=

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

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

Here back in past the trigger got somehow disabled - packaged = called =0A= from trigger was invalidated, later fixed the packge but could not check = the =0A= triggers. Application ran fine without complaining only later to found = no audit =0A= records. I think that mindset of application architect is pushing = against =0A= going with triggers. It's not though easy to disable the part og Java = code that =0A= does the audit -you need to actually go and remove the code portion. =
=0A=
 
=0A=
Stephane - Thanks for your comments
=0A=
 
=0A=
Thanks, Sandeep
=0A=
 
=0A=
On 9/21/05, Igor =0A= Neyman <ineyman@perceptron.com> =0A= wrote: =0A=
=0A=
Regarding =0A= your 2nd point:
=0A=
In high =0A= transaction environment making extra trip from application to database = to log =0A= data in adit table will cause more overhead than using trigger to = populate =0A= audit table.
=0A=
 
=0A=
Now, if you are = afraid that =0A= someone could disable auditing triggers, aren't you afraid that = someone could =0A= disable "auditing part" of application?
=0A=
 
=0A=
Igor =0A= Neyman
=0A=

 
=0A=
=0A=
=0A= From: oracle-l-bounce@freelists.org [mailto: =0A= oracle-l-bounce@freelists.org] On Behalf Of Sandeep =0A= Dubey
Sent: Wednesday, September 21, 2005 3:52 = PM
To: =0A= oracle-l
Subject: Data auditing: triggers vs application =0A= code

 
=0A=
=0A=
=0A=
Hi,
=0A=
 
=0A=
I need to keep track of history of data change in the live = tables. Two =0A= methods are being compared.
=0A=
 
=0A=
1. Create triggers on the live table. For each = insert/update/delete =0A= insert a row in the audit table.
=0A=
2. Let the application take care of inserting the data in the = audit table =0A= itself.
=0A=
 
=0A=
The cons against using triggers in the order of severity = are:
=0A=
 
=0A=
1. If somehow the triggers are disabled in production, the = application =0A= goes through without noticing it and no audit data will be = captured.
=0A=
2. In a high transaction environment triggers have overhead.
=0A=
 
=0A=
As a database person, I am inclined to use triggers. But I fail =0A= to guarantee that trigger will never get disabled. If it is = disabled =0A= somehow application SHOULD stop. (It is impractical to check the = status of all =0A= underlying triggers before each transaction).
=0A=
 
=0A=
I would like to hear how you guys handle data auditing =  in =0A= your system.
=0A=
 
=0A=
Thanks
=0A=
 
=0A=
Sandeep
=0A= =
 

=0A= ------_=_NextPart_001_01C5BEF4.28A89EEE-- -- http://www.freelists.org/webpage/oracle-l