3

This is a specific scenario. What I need to achieve is this:

  1. If someone insert or delete one or more rows in a specified table outside my application's UI, then it should detect that the table was modified.
  2. It should not use triggers.

This is what it not need to do:

  1. Differentiate if it was deleted or inserted.
  2. Detect who did the alteration or where it was altered.
  3. If the user do the same number of inserts and deletes at the same time. That can technically be counted as alterations and so, don't need to be detected as inserts and deletes.

This is what we think could do the job: Create a table that will store the watched table name, the number of rows it have and a validation key, let's call integrity_tbl. After inserts or deletes, update the number of rows and validation key. Only update the count of rows if validation key is valid. Invalidate the validation key every time the comparison fails. When data integrity verification is needed, count rows in table and compare to integrity_tbl.

Do you think this is a good approach or should we try something else?

We are using Delphi and Firebird for this. As asked, we are using IBX to connect to Database. But the logic is the problem not the way to access the database.

EMBarbosa
  • 1,473
  • 1
  • 22
  • 76
  • Not sure it should be tagged Delphi at all. o.o'' – EMBarbosa Jun 13 '12 at 16:32
  • It's depending on how you calculate your integrity_tbl. – philnext Jun 13 '12 at 16:42
  • @philnext. Not sure what you mean. `integrity_tbl` will store name and number of rows of watched table and a validation key hash just to know if someone altered `integrity_tbl`. We did not decided what specific hash we will use yet cause that is not the worst part of the problem. – EMBarbosa Jun 13 '12 at 17:37
  • @HuguesVanLandeghem I know this seems not logical at all, but it is because triggers can be easily disabled outside of the application. This is to attend a specific law for the software. – EMBarbosa Jun 13 '12 at 17:42
  • 4
    If someone is going to intentionally disable triggers in order to break the law by tampering with your table, can't that person also be expected to modify the integrity table at the same time? – Rob Kennedy Jun 13 '12 at 22:45
  • Could you update your question with information about which Delphi components are you using to access Firebird database? Based on that I will provide specific answer. – LightBulb Jun 14 '12 at 00:20
  • @LightBulb I will do that now. But don't thought it was needed... – EMBarbosa Jun 15 '12 at 13:54
  • @RobKennedy that is why integrity table has a validation key that is defined via software and not triggers. If someone mess it up, software will say: "Hey someone messed this!". I know it is weird requirement, and not heavily secure. Don't you, sometimes, have the impression that people that make the laws don't know nothing about programming? – EMBarbosa Jun 15 '12 at 14:00

2 Answers2

1

Firebird has a very nice mechanism for notifying clients if and when something specific has changed anywhere in the database. The mechanism is called Firebird Events. Read that document to properly understand how it functions.

In general, events are used in triggers or stored procedures. You use keyword POST_EVENT <event_name> to send a specific event to all connected clients. <event_name> can be any string up to 127 characters. Then, in Delphi you need to use component called TIBEvents (or TIBEventAlerter in older Delphi versions). There, you use Events property where you specify one or more <event_name>s you are interested in receiving. After that, you need to write code for TIBEvents.OnEventAlert where you can react to all registered events every time they are triggered.

LightBulb
  • 964
  • 1
  • 11
  • 27
  • Thanks, but it will not work this way. To trigger an event on delete I must use a trigger. That is out of question. Also, if the application is not in use, it cannot register an interest in events. – EMBarbosa Jun 15 '12 at 17:34
  • There is a `Registered` property which you can set to False when you don't want to receive any events. Anyway, I still don't understand the reason why you don't want or can't use triggers. Can you explain that a bit more please? – LightBulb Jun 15 '12 at 18:28
  • It's not that I don't want. It's a law that the software must accomplish. I'm not good translating but is something like: "There's should not be any automatic way outside of the software to deactivate the integrity verification mechanism". So if it need a trigger to work while the application is not running, it will not meet the requirements. – EMBarbosa Jun 15 '12 at 18:49
  • Also, I think you did not understand that the problem is detect the insert/delete made outside the software even if the application is not running. – EMBarbosa Jun 15 '12 at 19:07
0

Calculate an MD5 (or other) checksum or signature from each table, store it somewhere, such as a .ini file, another table (specific to this purpose), etc..

Chris Thornton
  • 15,620
  • 5
  • 37
  • 62
  • This doesn't meet the requirements: "If the user do the same number of inserts and deletes at the same time", it's not counted. The operation of deleting a row and inserting another would change the checksum or signature. (Also, the purpose is auditing and the comments specifically say "no triggers because they could be disabled" - if the user could disable triggers, an .ini file definitely wouldn't be a solution.) No downvote, but it won't work. – Ken White Jun 13 '12 at 22:51
  • @KenWhite The worst problem with this is that calculating the MD5 checksum in the whole table would be a pain after every insert or delete. I'm expecting `count` be a lot faster. – EMBarbosa Jun 15 '12 at 17:39
  • @EMBarbosa: I agree it's a poorly thought out solution. That's why I said it wouldn't work. – Ken White Jun 15 '12 at 17:44