I need to permanently purge single records from a MySql database (Mssql would be interesting too). By purge I mean unrecoverably send it into oblivion.
I need this either for compliance with EU-GDPR and also for a client who wants to make sure that data is removed from MySQL for good.
As I understand
delete from table where id=1
would remove the data from the table-space and from the indexes but not from the transaction log. To achieve this one needs to perform a shrink. But even than the record would still be existing binary on disk.
What I need to achieve it to really and truly destroy that database record so that even Hacker-Jesus could not recover it.
I already spent some thoughts on that and a first idea was to not delete the record but overwrite the data in the columns with random data. As I understand the Mysql documentation that would overwrite the physical memory of the old data. So if there was a column secret_column with the data secret info I would overwrite is with 'XXXXXXXXXXX' shrink the database and the old value of the column should be gone. Another more secure option would be the synchronously encrypt the secret_column and store the key in another column cypherkey. Then when I want to delete the record I decrypt the column, create a temporary in-memory key, encrypt the data with the temp key and write it back to the database. That should destroy the information for good - but this is impossible to use with indexed columns in respect to performance.
I am well aware of the fact, that any backup of the database would still contain the purged data. That is another miracle to solve.
I would appreciate any ideas or (better) solutions to that question. Thank's a lot.
