Preventing Data Fraud and Deletion in SQL Database

Question

We have an application that is installed on premises for many clients. We are trying to collect information that will be sent to us at a point in the future. We want to ensure that we can detect if any of our data is modified and if any data was deleted.

To prevent data from being modified we currently hash table rows and send the hashes with the data. However, we are struggling to detect if data has been deleted. For example if we insert 10 records in a table and hash each row the user wont be able to modify the record without us detecting it but if they drop all the records then we can't distinguish this from the initial installation.

Constraints:

• Clients will have admin roles to DB
• Application and DB will be behind a DMZ and won't be able to connect external services
• Clients will be able to profile any sql commands and be able to replicate any initial setup we do. (to clearify they clients can also drop/recreate tables)
• although clients can drop data and tables, there are some sets of data and tables that if dropped or deleted would be obvious to us during audits beacuse they should always be accumulating data and missing data or truncated data would stand out. We want to be able to detect deletion and fraud in the remaining tables.
• We're working under the assumption that clients will not be able to reverse our code base or hash/encrypt data themselves
• Clients will send us all data collected every month and the system will be audited by us once a year.
• Also consider they client can take backups of the DB or snapshots of a VM in a 'good' state and then do a roll back to that 'good' state if they want to destroy data. we don't want do do any detection of vm snapshot or db backup roll backs directly.

So far the only solution we have is encrypting the install date (which could be modified) and the instance name. Then every minute 'increment' the encrypted data. When we add data to the system, we hash the data row and stick the hash in the encrypted data. Then continue to 'increment' the data. Then when the monthly data is sent we'd be able to see if they are deleting data and rolling the DB back to just after installation because the encrypted value wouldn't have any increments or would be have extra hashes that don't belong to any data.

Thanks

Answer 1

Let's say we have a md5() or similar function in your code, and you want to keep control of the modification of the "id" fields of the table "table1". You can do something like:

accumulatedIds = "secretkey-only-in-your-program";
for every record "record" in the table "table1"
  accumulatedIds = accumulatedIds + "." + record.id;

update hash_control set hash = md5(accumulatedIds) where table = "table1";

After every authorized change of the information of the table "table1". Nobody could make changes out of this system without being noticed.

If somebody changes some of the id's, you will notice that because the hash wouldn't be the same.

If somebody wants to recreate your table, unless he recreates exactly the same information, he woulnd't be capable of making the hash again, because he don't know the "secretkey-only-in-your-program".

If somebody deletes a record, it can be discovered also, because that "accumulatedIds" wouldn't match. The same will apply if somebody adds a record.

The user can delete the record under the table hash_control, but he can't reconstruct the hash information properly without the "secretkey...", so you will notice that also.

What am I missing??