4

I know that to securely delete files on a system I have to apply some kind of secure deletion (overwritting empty clusters, for example).

But when I want to delete some information on a database (Oracle or MySQL), is it completely deleted?

I mean, I have a DB running, and I don't want to delete the entire database, just some records, but I want to be sure that those records can't be restored. Is the dbms delete function enough?

I consider that there exists no other copy of the information (there's no database backup).

I've read something related to this about Microsoft Access here, and they say that after deleting the data it remains in the db until that space is used (or the db is "compacted & repaired"). This happens also on Oracle and MySQL?

Thanks in advance.

Community
  • 1
BrainSCAN
  • 85
  • 2
  • 6

1 Answers1

6

There are two ways to answer this. The first way follows your line of thinking, the other does not.

While it's hard to be sure for any specific database, due to slack space (i.e. not shrinking the data file when stuff is deleted), journaling, and other database features, I'd suspect that the following is reasonably sufficient for a system like InnoDB:

  • Do an UPDATE on the rows to replace all data with blank data of the same length. So, if you've got Polynomial in a cell, you replace it with AAAAAAAAAA.
  • Delete the rows as normal.
  • Add a bunch of new rows with large-ish blobs of dummy data.
  • Delete those rows.
  • Take down the database and run a shrink / compact / re-index operation.

This isn't guaranteed to do the job, because database implementations have all sorts of weird behaviour, but for most cases it'll at least make it more difficult to recover the data. In fact, in a high usage environment I'd suspect that emptied out row slackspace gets overwritten pretty fast.

But here's the more complete answer - your threat model in this case appears to be that someone has captured the database file. So why not encrypt it? Full-disk encryption is computationally cheap (and LUKS is easy to set up) and would help prevent someone from getting at the database file from a stolen disk.

Polynomial
  • 132,208
  • 43
  • 298
  • 379
  • Thank you very much for your help. About the threat model, I just wanted to find a way to be sure that once a data is deleted, it can't be recovered at all (to protect privacy). – BrainSCAN Mar 06 '15 at 11:51
  • There's no way to recover it from the database itself - there's no `UNDELETE` or similar. The only way to get at the deleted data is to read the database file on disk and extract latent data. – Polynomial Mar 06 '15 at 11:58
  • Doesn't MySQL explicitly support secure deletion? – forest Aug 14 '18 at 22:19
  • @forest Not that I'm aware of, and I couldn't find anything in the docs. – Polynomial Aug 20 '18 at 21:19
  • @Polynomial Hm, I just remember a `secure-deletion` USE flag for the Gentoo MySQL ebuild which claimed to enable a feature for secure deletion. Perhaps it's an out-of-tree patch. – forest Aug 29 '18 at 01:02