Securely delete data from a table

[omacron]

Hi

I don't even know what I am about ask exists or if DB2 does it already. I am being asked to securely delete data from a table. Its a two fold issue; one is to delete only select records from a table, the second is to delete data from a field.

So for example, table Customers

CustID     Name
1234       Bob Smith
1235       John Dole

Now normally if I want to delete a customer a simple delete script;

delete from Customers where CustID = '1234';

If The customer's name has to be deleted but the record has to stay a simple update command;

update customers
set Name = ''
where CustID = '1234';

Although now I am being asked to "securely" delete the data. The problem is the person that is asking me this is familiar with how just deleting a file from a hard drive is not secure.

Trying to google this topic doesn't come up with a lot of data one way or the other. Any help would be great.

thanks

 

[papadba]

The problem is the person that is asking me this is familiar with how just deleting a file from a hard drive is not secure

Which really has nothing to do with "deleting" data content from a column or deleting a row from a table. . .

When values are updated/deleted from a database it is nothing at all like dealing with some "deleted" file.

 

[omacron]

Yeah I know that, but is it secure? It it cached someplace (auto commit turned on)? Is it in memory for a given time? FYI I am ignoring user generate backups.

 

[MarcLodge]

omacron, I believe that the data is still available if you were issuing a 'dirty read' until it is committed. Once that has happened, the row on the table is physically re-written with the new data.

As far as I am aware, that data is not stored or cached anywhere and to all intents and purposes has been deleted from the system. The only way that I know of of retrieving that data is from an indepth analysis of the DB2 log using software written specifically for that purpose.

Marc

 

[omacron]

thanks for information.

 

[Diancecht]

Keeping in mind that you're not making periodic backups ...

Cheers,
Dian

 

[stevexff]

As Marc notes, updating a column will physically rewrite the data for the row, wiping out the value. Deleting a whole row will remove the index entries and update the free space in the page, but in the interests of performance and CPU usage this is unlikely to set the resulting free space to low values. So a determined hacker *may* be able to recover the 'deleted' data by looking at the raw data files, at least until the space is reused. There may be an option you can set at the server/database/tablespace/table level to force the write of low values, but I don't know.

But how far do we want to take this? Modern SANs are RAID devices that will smear datasets across a number of disks, and in many cases will write new blocks on different stripes in preference to updating in place to support resilience and failover (which again leaves the old data lying around on the disk). Is your security wonk worried about that too?

I'd be more worried about the log file, as there are plenty of utilities that can read it, and deletion is one of those activities that has to log the complete row to support backout and recovery, otherwise it wouldn't be much use as a log. These don't require a huge knowledge of DB2 internals to use, and most will generate SQL scripts to put the data back or load it to another table right out of the box.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]