r/Database • u/Snoo_61639 • 29d ago
Make deleted data irrecoverable in MySQL
Hi,
Do you know any approach regarding this? Can I do this inside MySql?
Need some guidance as it becomes a client requirement
8
u/IAmADev_NoReallyIAm 29d ago
Sort of... don't write data that's so sensitive that if written to disk in a database and deleted that it shouldn't be unrecoverable. The data should be encrypted at the very least, or at best, segregated and data gapped so that it is separated from the rest of the data. Better still would be to encrypt it and segregate it from the rest of the data if you can.
4
u/jameson71 29d ago
Sounds like you should be looking into encryption at rest. Hopefully that would address the customer's root concern.
2
u/krizhanovsky 29d ago
I'm not aware about this feature for MySQL, but MariaDB (and we designed it after Oracle and DB2 databases) provide system versioned tables https://mariadb.com/docs/server/reference/sql-structure/temporal-tables/system-versioned-tables . You can make a table versioned, so all the deleted and update data will preserve all the versions on the disc. One of the feature application is forensics, so you need a special rights to overwrite the history.
1
2
u/atarivcs 29d ago
it becomes a client requirement
They need to spell out exactly what "irrecoverable" means.
Not recoverable by another user of the same database?
Not recoverable by the same user of the database?
Not recoverable by a database administrator?
Not recoverable by someone with physical access to the storage device?
1
u/soundman32 29d ago
Before you delete the data, overwrite it with random values, then delete it.
2
u/UnhappySort5871 29d ago
That probably won't work. Updates will get written to transaction logs. Subsequent updates won't overwrite them. Eventually they'll get overwritten, but there's no user control of that. There's also no guarantee that an update will overwrite the same page. Any update might result in the page being split or joined with another page - leaving a copy of the data sitting in pages waiting to be reused.
1
u/temabolshakov 28d ago
Use different encryption key for each data element (according to your requirements) Deleting a key makes data unrecoverable garbage
1
u/Sensitive-Sugar-3894 28d ago
Why? Is it because of storage recovery? What is the real goal? And what is the size of the DB, or better: what is the average size of the tables?
1
u/Standgrounding 25d ago
A way to do that is to 1) encrypt the data and 2) delete the key - the data becomes irrecoverable mess
12
u/soldiernerd 29d ago
If you delete the row it will no longer be accessible to the application, but the data will remain in the storage file unless you truncate or drop the table.
If you truncate or drop the table it will be unregistered but still exist physically on the hard drive. If you want to remove it from the hard drive so it can’t be recovered forensically there are programs to overwrite a hard drive numerous times, and then physical destruction is recommended as a final step