Detecting database tampering, is it possible?

Long time listener, first time caller.

'Say you have a database table that is responsible for logging user activity. The integrity of this log is important, so you want to be able to detect if someone has modified any data from the table. To make things more interesting, also consider the fact that your system may be operated by an evil SQL admin who has complete control over this wretched system. yikes...

How would you safeguard your data?

How would you detect if someone has tampered with your data?

You have unlimited tools at your disposal. (i.e. hashing, encrypting, etc.)

Solution 1:

If you really must detect that tampering has occurred, then add a checksum field to the table. The checksum for each new row must include the checksum of the prior row. Then to verify the content, walk through the dataset computing the checksum as you move forward. If the calculated checksum doesnt match the value in the table then some value has been tampered.

-Mike

Solution 2:

If the "evil admin" has no access to the application that populates the database, a extra column on each table consisting of a cryptographic signature for the rest of the columns will do the job. The "no access" condition is needed such that they can't just extract your private key and sign their fake data.

Edit: Ah, as the commenters point out, I didn't consider the admin just deleting a row. For this, you'll need one extra row with a cryptographically signed row count that you update each time (or a signed hash of the rest of the table content, or last access time, or whatever indicator you choose).

Solution 3:

Create a shadow table that hashes every filed with a key/salt only you and the application knows about. If you want to check for data tampering, rehash the user table and compare against the shadow table.

Solution 4:

If you really want to be secure Use - Write once Read Many Media for that table.