Physically deleting row data

[Ken Bass]

Hi all,

I have a client that is obsessed (actually paranoid) with security.
One requirement he has is that any data deleted from a database be
physically removed. This means not only within the scope of the
database itself, but at the file level itself.

For example, if a row in a table has a value of "foobar", and that row
is deleted, then the string "foobar" must not be found within any of
the database files, or even on the disk. If someone would remove the
disk from the system and look at each individual sector, they would
not find "foobar" anywhere.

Ideally this erasure would be done in real-time, when a DELETE/DROP is
actually performed, although my client would accept a solution where
some utility is executed daily (the database engine needs to stay
online, though).

Does this capability already exist within MySql? If not, can it be
implemented - either by some external utility, or [...gulp...] by some
modification to the source?

Also, does something like this exist for other database engines, like
Oracle, or SS, perhaps?

Any ideas on this subject will be greatly appreciated.

TIA

ken bass

[Aggro]

Ken Bass wrote:

Any ideas on this subject will be greatly appreciated.

I have an idea, but no clue whatsoever does it really work.

Call UPDATE query for that row multiple times, with random data as
values. If the data is written at the same part of the hard drive each
time, that would in theory destroy the data, if executed in a loop
enough times (20 should be enough).

But as I said, I have no idea would this really work, because it might
depend on the MySQL and on the filesystem where they write the data in
case of update happens. But if it works, that would be quite simple to
implement.

[jerry gitomer]

Aggro wrote:

Ken Bass wrote:

Any ideas on this subject will be greatly appreciated.

I have an idea, but no clue whatsoever does it really work.

Call UPDATE query for that row multiple times, with random data as
values. If the data is written at the same part of the hard drive each
time, that would in theory destroy the data, if executed in a loop
enough times (20 should be enough).

But as I said, I have no idea would this really work, because it might
depend on the MySQL and on the filesystem where they write the data in
case of update happens. But if it works, that would be quite simple to
implement.

You will also have to flush the buffers each time you write.

10 writes should be sufficient to prevent recovery by anyone
other than a government agency specializing in
encryption/decryption.

Of course the only absolutely safe method is to use only
non-removable media and to physically destroy the media when the
equipment is sold/trashed or whatever.

HTH

Jerry

[Ken Bass]

Unfortunately, the issue here is more of a legal one:

If a table is used to maintain user information, then when a row is
deleted (presumably to remove all information about one user) there
should be no trace of that user's information left anywhere. Not at
the SQL level, not at the file level, and also not at the disk level.

At that point, if the equipment is subpoenaed by jack-booted
government thugs looking for information about that deleted user, they
can scan all of the disks, but they wouldn't find anythng.

I realize that at the SQL level there really is no control over
this. It is more up to the engine implementation, and how it uses the
filesystem. In some ways, it might be a nice feature to build into
MySql, although I am not sure how much effort that might be.

Thanks, though. All additional information is good to know.

ken

On Wed, 27 Apr 2005 17:30:10 GMT, jerry gitomer <jg******@veriz on.net>
wrote:

Aggro wrote:

Ken Bass wrote:

Any ideas on this subject will be greatly appreciated.

I have an idea, but no clue whatsoever does it really work.

Call UPDATE query for that row multiple times, with random data as
values. If the data is written at the same part of the hard drive each
time, that would in theory destroy the data, if executed in a loop
enough times (20 should be enough).

But as I said, I have no idea would this really work, because it might
depend on the MySQL and on the filesystem where they write the data in
case of update happens. But if it works, that would be quite simple to
implement.

You will also have to flush the buffers each time you write.

10 writes should be sufficient to prevent recovery by anyone
other than a government agency specializing in
encryption/decryption.

Of course the only absolutely safe method is to use only
non-removable media and to physically destroy the media when the
equipment is sold/trashed or whatever.

HTH

Jerry

[Bill Karwin]

Ken Bass wrote:

If a table is used to maintain user information, then when a row is
deleted (presumably to remove all information about one user) there
should be no trace of that user's information left anywhere. Not at
the SQL level, not at the file level, and also not at the disk level.

Simple--just stop making regular backups of your databases. That will
ensure that at some time in the future, all your data will be lost,
without possibility of recovery. But I suppose you want to be able to
have this happen on a schedule that _you_ control. ;-)

But seriously...

For what it's work, you should not rely on the multiple-UPDATE method to
overwrite data if you use InnoDB tables. If I understand it correctly,
InnoDB is a multi-versioning engine, so each UPDATE creates a new
version of the record, leaving past records in the file so that any
outstanding transactions can still read them. So the sequence of bytes
will still exist in the database file for a somewhat indeterminant
amount of time.

What I would do to eliminate data complete is:

1) Use simple SQL DELETE statements to remove the records as per normal.

2) Replicate database db1 to db2 so that all remaining data is preserved.

3) Destructively delete the files for db1 using a specialized tool for
that purpose.
This is in lieu of DROP DATABASE; destroying the data files (for
MyISAM) accomplishes the same thing.
If you use InnoDB, this might take some more configuration, because
the default is to store all data for all InnoDB tables together in one file.

4) Make your applications switch over to using db2. Next time you do
this cycle, do it in the reverse order and have the applications switch
to db1 after you destroy db2.

Destructive delete tools are available for most popular operating
systems. The idea is that they do more than the standard operating
system file deletion; they overwrite the file's data destructively.

See for examples (but feel free to search the internet for other solutions):
http://www.thefreecountry.com/securi...redelete.shtml

However, the solution proposed above has a timing issue. It isn't
practical to do this every time you delete any record. So there exists
the possibility that sensitive data will be seized by the jack-booted
thugs before you do your periodic cleanup cycle.

Regards,
Bill K.