What is a simple way to tell if a row of data has been changed?

Question

If I have a row of data like:

1, 2, 3

I can create a checksum value that is the sum of all of the columns, 1 + 2 + 3 = 6. We can store this value with the row in the 4th column:

1, 2, 3, 6

I can then write a program to check to see if any of the values in the columns changed accidentally if the sum of the columns don't match the checksum value.

Now, I'd like to take this a step further. Let's say I have a table of values that anyone has read/write access to where the last column of data is the sum of the previous columns as described earlier.

1, 2, 3, 6

Let's say someone wants to be sneaky and change the value in the third column

1, 2, 9, 6

The checksum is easy to reproduce so the sneaky individual can just change the checksum value to 1 + 2 + 9 = 12 so that this row appears not to be tampered with.

1, 2, 9, 12

Now my question is, how can I make a more sophisticated checksum value so that a sneaky individual can't make this type of change without making the checksum no longer valid? Perhaps I could create a blackbox exe that given the first three values of the row can give a checksum that is a little more sophisticated like:

a^2 + b^2 + c^2

But while this logic is unknown to a sneaky user, he/she could still input the values into the black box exe and get a valid checksum back.

Any ideas on how I can make sure all rows in a table are untampered with? The method I'm trying to avoid is saving a copy of the table every time it is modified legitimately using the program I am creating. This is possible, but seems like a very unelegant solution. There has to be a better way, right?

Answer 1

Although your question is based on malicious entries to a database the use of the MOD11 can find inaccurate or misplaced values.

The following MySQL statement and SQLfiddle illustrate this

SELECT id, col1, col2, col3, col4, checknum,
9 - MOD(((col1*5)+(col2*4)+(col3*3)+(col4*2) ),9) 
AS Test FROM  `modtest` HAVING checknum =Test

Answer 2

Users should not have unconstrained write access to tables. Better would be to create sprocs for common CRUD operations. This would let you control which fields they can modify, and if you insist you could update a CRC() checksum or other validation.

This would be a big project, so it may not be practical right now - but it's how things should be done.

Answer 3

You seem to be asking, "how can I give a program a different set of security permissions to the user running it?" The way to do this is to make sure the program is running in a different security context to the user. Ways of doing this vary by platform.

If you have multiple machines, then running a client server architecture can help. You expose a controlled API through the server, and it has the security credentials for the database. Then your user can't make arbitrary requests.

If you're the administrator of the client machine, and the user isn't then you may be able to have separate processes doing something similar. E.g. a daemon in unix. I think DCOM in windows lets you do something like this.

Another approach is to expose your API through stored procedures, and only grant access to these, rather than direct access to the table.

Having controlled access to a limited API may not be enough. Consider, for example, a table that stores High Scores in a game. It doesn't matter that it can only be accessed through a ClaimHighScore API, if the user can enter arbitrary values. The solution for this in games is usually complicated. The only approach I've heard of that works is to define the API in terms of a seed value that gave the initial game state, and then a set of inputs with timestamps. The server then has to essentially simulate the game to verify the score.

Answer 4

Using basic math your checksum is invalid:

a^2 +b^2 +c^2  

a=0,b=0,c=2  = checksum 4
a=2,b=0,c=0  = checksum 4

If you want a set of "read-only" data to the users, consider using materialized views. A materialized view will compute the calculation a head of time i.e. your valid data and serve that to the users, while your program can do modifications in the background.

Further this is the reason why privileges exist, if you only supply accounts that cannot modify the database for instance read-only access, this mitigates the issue of someone tampering with data. Also you cannot fully prevent a malicious user from tampering with data only make them jump through several hoops in hopes they get bored / blocked temporarily.

There is no silver bullet for security, what you can do is use a defense in depth mindset that would consist of the following features:

Extensive Logging,
Demarcation of responsibilities,
Job rotation,
Patch management,
Auditing of logs (goes together with logging, but someone actually has to read them),
Implement a HIPS system (host intrusion prevention system),
Deny outside connections to the database

The list can go on quite extensively.