Data Integrity

Question

Data Integrity

robsimm

Grasshopper

Points: 17
More actions
January 4, 2008 at 7:40 am

#202041

Hi all,
I am attempting to create a database driven web application that provides high levels of integrity of the data both stored and retrieved from a DB.
I have opted to use MSSQL 2005 as my DBMS and was wondering what features are present that could assist me with this problem? (Assume that all measures have been taken to prevent SQL injection).
The problem:
The idea of temporal logic applies to this problem, whereby patterns of behavior should be monitored on both Web Server and SQL server. I want to be able to map a specific T-SQL query pattern/behavior as valid, and that if anything "out of the ordinary" is detected it is either rejected and or flagged (by whatever means) - which should provide higher levels of integrity. There has already been extensive research into temporal logics, but I was wondering how this could be achieved within what SQL Server 2005 has to offer as a DBMS.
I have seen features such as triggers in 2005, which sounds great but I think that's going to produce an extensive amount of logging which would need further filtering and would not provide real-time analysis/response.
Any ideas/comments would be greatly appreciated! :D.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

I am not sure if you are talking about auditing or data integrity. If it is data integrity, you can acheive it by using transactions. Use primary key and foreign keys.

You can also use sp_getapplock to prevent race condition. You could use Encrytion to keep the data safe from prying eyes.

-Roy

robsimm Grasshopper Points: 17 More actions · Answer 2

Apologies for the ambiguity in my above post. I suppose what I am trying to achieve is what is quite commonly achieved with file systems. Imagine there is a text document sitting on a server. To verify that the file is un-changed since it's last authorised write we can quite simply do a hash on the document and verify the hash the next time the document is opened.

With a database this is much more difficult. Database are dynamic by nature and are not fixed (with some exceptions). There are multiple writes/reads occuring at any given time. What I am trying to ensure is that the data has not been tampered with in any way since it's last successfull/valid write (insert). I suppose encryption could ensure this is fulfilled, but was wondering if theres anything similar to the above case scenario.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Have a look at the hash_bytes function. Might do what you want. There's also checksum and checksum_agg

I'm not sure they're sufficient for what you're trying to do, but they might help.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

robsimm Grasshopper Points: 17 More actions · Answer 4

Thanks GilaMonster! Just checked on MSDN and have now found a bunch of useful functions that could help me with this. 🙂