How can I securely destroy some data using sql ser

Question

One of my clients wants me to perform a periodic "real" destruction of some of his old data, and I'm evaluating the best way to do it.

The data is in a table, and I want to destroy some of the rows contained in it.

I could do it manually by deleting/exporting the database on another computer/degaussing the hard drive/reimporting the saved data, but I need an automatic solution.

Is there an equivalent to the delete (as in delete * from foo) command which would perform a secure destruction of the data (using DoD secure wipe, or something like that?)

Do you see other ways to perform this automatic deletion?

Btw, I know the odds of someone retrieving some of the data I've destroyed using the sql delete command are very small, but some of my clients require it. So please don't turn this question into a global debate on the topic of data disposal procedures !

Edit : the problem I want to address is not "How should I destroy the data so it cannot be recovered" but rather "How can I convince my clients that their data cannot be recovered".

Answer 1

Basically, no. The standard operation won't do it, and if it did the data could still be reconstructed from transaction logs etc. Probably the closest you can come is to do it externally, copying and purging the database to another device, then doing a high-quality scrub delete on the old device, but as a security guy I'm not sure I'd even want to say that was a sssured delette.

Secure delete is a difficult problem. You might do better with a cryptographic approach, like Radia Perlman's "ephemerizer".

Answer 2

Actually, chances of retrieving the data destroyed with DELETE are quite big, close to 100% :)

Data that you delete are kept in the transaction log, it's a part of how the transactions work. In other case, you either would not be able to ROLLBACK a transaction, or a COMMIT would take forever (like in old versions of PostgreSQL).

Best you can do without messing with the datafiles is:

1. Delete your data.
   • Perform multiple UPDATEs on the table to destroy old data.
   • Perform several large transactions and commit them for the trasaction log to be truncated. How many exactly depends on your log size.
   • CleanSweep space on disk occupied by old transaction logs.

Answer 3

Well, I'm just playing here but you try this, it will be reasonably secure.

Don't use a typical backup.

Script out the schema, if you haven't already.

Script out all the data so that all the current can be inserted with an script with many INSERT statements. The deleted data won't show up in this file, obviously. Of course, you will want to use Bulk Insert and all that to get the data back in there.

Now use sdelete to delete all the data files and logs associated with the database. Now, restore from the insert script. :)

By the way, your question and the edit you made, saying you don't want a solution but a reason why not to contradicts your whole question. Anyway, a good reason no to do it is that no one is doing it. If you want to do something in computing (other than creating some brand new sort of application or something like that) that no one else is doing, it is probably a bad idea. There are no academic or DoD papers to my knowledge that describe a method to do this.

The bigger problem is what information will be "leaked" from the records you deleted to records that were not deleted. Note, here I mean "leak" in the sense of information flow.

Although, to be honest, the method I outlined above would essentially accomplish your goal.

Answer 4

From Books Online:

Delete operations from a table or update operations that cause a row to move can immediately free up space on a page by removing references to the row. However, under certain circumstances, the row can physically remain on the data page as a ghost record. Ghost records are periodically removed by a background process. This residual data is not returned by the Database Engine in response to queries. However, in environments in which the physical security of the data or backup files is at risk, you can use sp_clean_db_free_space to clean these ghost records.

This should zero-out your "free" data pages. It can also be used if Instant Initialization was used, but you decided you want to zero-out pages instead.

To answer your updated question, "How can I convince my clients that their data cannot be recovered", that BOL entry states it clearly, "Ghost records are periodically removed by a background process."

Answer 5

Delete the data. Do a simple backup and restore on a new hard drive and burn the old drive.

Destroying objects is the only way to really convince people that 'things' are really gone.

Answer 6

Use some form of encryption to store the data fields in the table.

When you decide to "delete", re-encrypt the data you will continue to use with a new key. Discard the old key, and delete the rows encrypted with the old key. Shrink.

Even if someone recovers the rows, w/o the old key no one will be able to restore the data. Just make sure the old key is really discarded - you can have it on a single usb stick only, and destroy the stick, etc.