10

Say there is a SQL database that stores certain records in encrypted. A person wants deletion of a record in a way that even hard drive recovery services cannot recover it without breaking the hard drive.

Is it possible to delete records this way?

schroeder
  • 123,438
  • 55
  • 284
  • 319
savegoodlyfnotendorsinganswer
  • 219
  • 1
  • 8

1 Answers1

15

The more layers you add above the actual data, the harder it will be to securely erase it, because it could have been stored in caches, journals, etc.

Most SQL databases first store information for data modification in a rollback segment to be able to rollback the transaction. Unless a specific database has an option to securely wipe the rollback segment, you should take care of it yourself, and depending on the database, it could be hard if it is even possible.

Furthermore, for security reasons, many professional-grade databases offer a journaling feature. This is a nice recovery feature in case of a crash: you re-install a backup, and replay the journal files registered from the backup date. It guarantees that all records added since that backup are stored in the journal files.

And this does not account for possible caching operations...

Said differently, if you want to securely erase some record, you are better off storing the record directly in a file and preferably not on a SSD drive*. That way, you could rewrite the records many times with a well-known erasure pattern making the original record un-recoverable.

(*) ssd drives can write a new version of a block in a different place. Most offer a secure erasure of the full disk, unsure for the secure erasure of a disk sector.

schroeder
  • 123,438
  • 55
  • 284
  • 319
Serge Ballesta
  • 25,636
  • 4
  • 42
  • 84
  • On top of the issues Serge mentioned, deleting a database record only de-allocates the database pointers, the data still exists and the space is not deallocated from the file system for optional wiping until the database runs a specific program, often called **Vacuum** or **Compress**, to essentially defragment the database file space. Then there's backups, both explicit and system implicit. – user10216038 Feb 20 '21 at 18:22
  • So how the big companies delete when we have the right to delete personal information like Facebook or Google is ordered to delete? – kelalaka Feb 20 '21 at 21:59
  • 4
    @kelalaka I didn't find any definition in the GDPR (for example) of what "erase" means, but I don't think it would be interpreted in the strict sense in use here. A standard database deletion, possibly extending to backups/snapshots, would probably suffice. I doubt any data controller will get dinged for having a user's data still resident in some unused SSD space. – Chris Hayes Feb 20 '21 at 23:55
  • 2
    @kelalaka It would depend on what (if anything) applicable laws say about the matter. They might say nothing beyond "must be deleted" or they may go into detail about secure-erasure processes. – TripeHound Feb 21 '21 at 00:01
  • 3
    @kelalaka The right to delete personal information doesn't mean they have to delete it so it cannot be recovered by forensic means! They aren't even required to delete it from their backups. – Ángel Feb 21 '21 at 02:43
  • _unsure for the secure erasure of a disk sector_ – No SSDs that I am aware of support this. You can mark a sector (well, actually a _flash page_, which is usually larger than the logical sector size) as unused and then issue the TRIM command which usually, [but not always](https://security.stackexchange.com/a/243795/165253), renders it unrecoverable. – forest Feb 21 '21 at 06:48
  • _you are better off storing the record directly in a file_ – Although this is much better than keeping it in a database, filesystems too can stored fragments of the file in various places (a journal and related components, reallocated sectors, filesystem backups, CoW records, sometimes even literally in the free space in an inode). It can be difficult to securely overwrite even an individual file on an HDD (and very difficult on an SSD, as you point out). But if you can be sure that overwriting the file will overwrite all the relevant sectors, you only need one erasure, not multiple. – forest Feb 21 '21 at 06:52