Hi ! There's a law here in France, that forces all business touse "cash register software" or ledger software that are "tamper proof" : The laws can be summarized as follow:
Those laws are ensuring that no business is trying to avoid paying VAT on any of the business done (just imagine, as a business owner, you delete or update sale_lines after the sale occured, reduce the value, and so, you can "pay" less VAT... that's a thing that some shop owners used to do !)
I'm a bit disappointed for the second rule : Even if the user doesn't have access to the DB, if at any time, he can pay a hacker that could get into the server, it would grab the DB password in the .env file, and could eventually manage to go into the "sale_lines" table and modify it.
So, the first way I've found could be : sign every record with a hash (sha256 ie.), store this signature into every record. Then, on the "next record", reference the previous record signature. Add an "foreign key" that self-references the same table, previous_record_hash = record_hash or something similar.... That's a first step, but it's not enough : "evil programmer" could still update records, recompute hashs and cascade-update next records !
Next step would be: Let the DB the hash computation with a "generated column". Mysql allows it since a few years ago, and the version I use allows it. That's a bit better, if you try to update the record, since the hash is referenced by the next record, you would have to recompute every next records in the same instruction to allow the update to pass....
Another step could be : use HMAC-like signing functions instead of just hashing : this involves using a private key... But gess where would be the private key ? In an .env file ? In the code ? That's not a good idea... "evil programmer" would have access to the key and then could also re-sign the records ! An alternative could be to make the sign process on another server, with an API call... Passing the "data to sign" to server B from server A, just getting the hmac signature and storing it. There's no constraint in the DB that would avoid tampering the data, but the law requirements are met: if anyone tampers the data, the stored signature would become invalid : checking each row against the "signature server" would show that row signature mismatches the "signature server" version, and the law guys would be happy ?
Having the "previous hash" referenced in each rows shows that no row where deleted by a DBA, but.... If anyone deletes many rows starting from the end and then reinsert new rows.... How would you proove that data has not been tampered !?
In facts, the perfect solution would be an append-only table for the sale_lines data ! But I haven't found anything that was satisfying under mysql features !?
There is no way to be 100% sure that a record hasn't been tampered with, once someone gains access to the database, but here are some tricks that you can check out.
A table trigger that calculates a checksum and stores that and the record id in a seperate table.
A table trigger that stores all updates and deletes from the table.
This post covers how to create and use a trigger in Laravel.
https://tray2.se/posts/using-table-triggers-to-log-changes-in-your-database-tables
Hi @Tray2 ,
Thanks for your ideas, but imagine "evil paid developper" that have gained access to the server : he could temporarly disable the triggers, do his stuff, and re-enable them !
That security is not enough ;)
@Napo7 The thing is that if the evil developer has access they can do whatever they want, and the developer has extensive knowledge about the functionality, then there is't much you can do to prevent tampering.
Another thing I would consider is to store the crucial data in a backup database that the regular developers doen't have access to, and of course incremental backups every hour or so.
@Tray2 The key point is not to "prevent" unauthorized access to the data, but to proove to the administration that the data has not been tampered nor modified.... A "readonly" or append-only table is a key to the solution, like SQL Server 2022 feature provides : any dev or any DBA cannot modify the data, and each data row is signed against the current and previous data, so even when having access to the table files, it would be very hard to tamper the data !
@Napo7 Harder yes, but never impossible.
It seems Azure is building something for sql server: https://www.mssqltips.com/sqlservertip/6889/azure-sql-database-ledger-data-tampering-protection/
Not sure it is complete, but might be worth checking out
@Sinnbeck That Azure features is exactly what is needed :) Now it's still a preview feature, but there might be something interresting to follow !
@Napo7 Be aware that the article is from 2021, so it might have gotten further since. Found this article in the docs :) https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-verify-database?view=sql-server-ver16&tabs=t-sql-automatic
@Sinnbeck Yes, it seems it is included in SQL Server 2022.... Will have to try it and see if it's worth doing the change !
I reply to myself :
Involving another server, we could imagine that every row that is inserted from the software is also submitted to another secured server, from an API. This way, even if an "evil paid programmer" can access the application server and database, he would also have to make a way to the other server and tamper the data there !? That's still not a perfect solution, because he could still make his way to the other server and tamper data there, but that's an added layer of protection...
Another option perhaps :
Mysql offers an append-only table in the "Archive" engine. This table's rows cannot be deleted nor updated. But there's still a "REPLACE" instruction allowed (that's silly!??).
Inserting data in the "archive" table at the same time that in the sale_lines table, in addition to the hashing mechanism would render the tampering harder : evil programmer would have to first find a way to tamper the sale_lines data. If he wants to change any of this data, he would have to also recompute the hashes, update the following rows hashes, and replace the data in the archive table... Still harder but not impossible !
This "archive data" table on the other hand would not allow evil dev to delete records from it...
I would approach this using audit logs. Many DBMS has audit logs plugins that can output every action happened
I think by accessing the audit log you can find about every UPDATE, ALTER, DELETE .... Queries. To make it more safe you can do the following if possible (to be honest never done it for database but I worked on auditing APIs before for sensitive access):
you can use ELK stack for this (Elasticsearch, Logstash,Kibana) or replace the last two with (FluentD & grafana)
if you go with this approach you will need to learn how to write config files for log stash or fluentd to be able to clean the data and forward to elastic search (not so difficult tbh)
Now this doesn't prevent tampering but this will help you proving that nothing happened. It will also tell you when an event happened to your database. (auditable event)
Please or to participate in this conversation.