Tamper Evident SQL Table? I could use some real help with this one...

[zachattack05]

The application I am working on uses SQL to store data. Most of the data is fine the way it is, but one of the tables contains information that would be very tempting for some users to alter.

My original plans were to have the application check the identity column and make sure there are no "breaks" in the sequence to show if a user has deleted a row. But I realized this was a poor choice considering that identity values can be "reset" if so desired and it doesn't address tampering (changing values) in the row.

Is anyone aware of a method that can detect, not necessarily prevent, tampering of a table? I read some other ideas online where a column can contain a hash value based on the row and the row previous to it to detect both removal and alterations. I'm not sure if this is a good solution though and thought I would see if anyone had any ideas.

[scudzilla]

Have you tried using an SQL trigger?

[zachattack05]

I've considered that, but dismissed it, maybe prematurely.

The table will constantly be updated by the application with heavy (sometimes with thousands of) inserts in a single operation.

The trigger shouldn't trigger (hehe that's kinda funny sounding) when the application does the inserting.

I need to detect an unauthorized insert, update or deletion and I'm really not sure how this can be accomplished.

It of course would be up to the end users to physically secure their server machine, I can't do anything about that, so I'm not worried about someone using SSMS while sitting at the server box to mess with things, I can't stop that. But I'm concerned about users downloading SSMS (or some other tool) and just connecting directly to the server from their workstation.

Wait a second...flash of genious moment...maybe...what about this:

The table has zero permissions for insert, update or delete with any user (except admins which is a different issue, and really not fixable) and all changes must be performed through the provided stored procedure which would only allow inserts...that would prevent (to some degree, which is better than just detection) updates and deletes. But I don't see how that would prevent unauthorized inserts.

Is there a way for SQL server to know what is sending the commands? Not the user, but kinda like the javascript that detects browser types on the web, so that the server can actively reject any changes unless they come from the application and not "Billy-Bob's SQL Hacking Tool" or something?

Another issue that just occured to me is that the table is kind of a "hybrid" between just a "log" and a table that requires an index and keys, but does require "cleaning."

The table holds lists that are accessed frequently by other parts of the application, and since those operations are dealing with a table that could hold tens to hundreds of thousands of rows over time, it has to be fast.

There also needs to be a maintainance routine that monitors the table and automagically deletes (or transfers to another table, I haven't decided yet) the rows over x amount of years old (where x is set by the end user). So there would have to be some deleting going on with the table, but again, only by the application, not the users directly without it being detected somewhere.

[scudzilla]

A quick search gave me this. Anyway, I think the column containing a hashed value might be good.

[Momerath]

Don't give users permissions they don't need. The application needs to do the insert, update, etc, but the users do not.

[zachattack05]

I don't want to give the users permissions they don't need but I either end up having to create a login for the application only and that means storing the username and password on the workstations (either in the app code or in supporting files) or, give each user their own password (which is the current plan) and that password and username is used by the application to do the SQL operations.

I don't know how to get around that.