MS SQL Server - How to physically DELETE row data

Author

How to physically DELETE row data

Ken Bass

2005-04-27, 11:23 am

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).

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

Any ideas on this subject will be greatly appreciated.

TIA

ken bass

Rick Sawtell

2005-04-27, 11:23 am

"Ken Bass" <anonymous@nowhere.com> wrote in message
news:1vbv61t2104n43q
m2nag43tmop6krbijl3@
4ax.com...
> 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).
>
> Also, does something like this exist for other database engines, like
> Oracle, perhaps?
>
> Any ideas on this subject will be greatly appreciated.
>
> TIA
>
> ken bass
>

Well...

Not a pretty solution, but..

Create triggers or use stored procedures on your tables. When a delete
occurs,
first update the row data with some arbitrary value like '0' (You should
ensure that it is the same length as the original values to help keep
forwarding pointers to a minimum). Then delete the row. Keep in mind that
this may cause speed issues on indexed columns..

Also, ensure that the Recovery Model is set to SIMPLE to eliminate potential
issues with transaction logs.

My .02 cents anyhow, maybe someone else has a more elegant solution.

Rick Sawtell
MCT, MCSD, MCDBA

Michael C#

2005-04-27, 11:23 am

Yeah that's about it. Just fill the row with some meaningless data (0's and
'XXXXXX's for instance) using UPDATE and then delete... As far as the
cleaning up at the hardware level, one of the things about Relational
databases is that it abstracts a lot of that from the user -- which means
you have less control over stuff like that. Even if you update the data
with meaningless stuff right before a delete, there's no guarantee that
"foobar" (to use your example) won't be on a sector of the storage device
somewhere (possibly in a sector that was previously used for temporary
storage or some such)... Have you considered encrypting his sensitive data?

"Rick Sawtell" <r_sawtell@hotmail.com> wrote in message
news:%23GG5RA0SFHA.3308@TK2MSFTNGP14.phx.gbl...
>
> "Ken Bass" <anonymous@nowhere.com> wrote in message
> news:1vbv61t2104n43q
m2nag43tmop6krbijl3@
4ax.com...
>
>
>
> Well...
>
> Not a pretty solution, but..
>
> Create triggers or use stored procedures on your tables. When a delete
> occurs,
> first update the row data with some arbitrary value like '0' (You should
> ensure that it is the same length as the original values to help keep
> forwarding pointers to a minimum). Then delete the row. Keep in mind
> that
> this may cause speed issues on indexed columns..
>
> Also, ensure that the Recovery Model is set to SIMPLE to eliminate
> potential
> issues with transaction logs.
>
> My .02 cents anyhow, maybe someone else has a more elegant solution.
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>

David Portas

2005-04-27, 11:23 am

You can't really solve this problem entirely with SQL Server. Depending
on your storage solution, data may be stored in controller cache or on
multiple physical drives. No matter how you overwrite it in the
database it may still exist on magnetic media, particularly in a SAN
environment for example.

The solution is physical security. Ensure that the storage is located
in a physically secure environment where access can be controlled and
monitored.

--
David Portas
SQL Server MVP
--

Ken Bass

2005-04-27, 8:23 pm

The issue for my client is more of a legal one, like this:

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.

Yes, I realized 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. I just didn't know if maybe somehow this functionality got
put into SS or some other db engine.

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

ken

On 27 Apr 2005 09:36:25 -0700, "David Portas"
< REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote:

>You can't really solve this problem entirely with SQL Server. Depending
>on your storage solution, data may be stored in controller cache or on
>multiple physical drives. No matter how you overwrite it in the
>database it may still exist on magnetic media, particularly in a SAN
>environment for example.
>
>The solution is physical security. Ensure that the storage is located
>in a physically secure environment where access can be controlled and
>monitored.
>
>--
>David Portas
>SQL Server MVP

David Gugick

2005-04-27, 8:23 pm

Ken Bass wrote:
> The issue for my client is more of a legal one, like this:
>
> 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.
>
> Yes, I realized 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. I just didn't know if maybe somehow this functionality got
> put into SS or some other db engine.
>
> Thanks, though. All additional information is good to know.
>
> ken

You also have to consider that deleted data resides in the t-log, not to
mention all backups (before the deletion). I would assume that you keep
backups of data and log files available on the network in case of
recovery needs (as well as tape). If so, how do you currently secure
those files from browsing?

--
David Gugick
Imceda Software
www.imceda.com

Michael C#

2005-04-28, 9:23 am

"jack-booted government thugs"? Your customer doesn't happen to live on a
compound in Idaho and run a militia does he?

As I said, SQL Server provides one layer of abstraction which prevents you
from doing what you're asking. Windows provides another layer of
abstraction, which is even out of SQL Server's control, and prevents you yet
again from doing what you're asking. The only way to ensure that you
physically erase information completely after a database delete would
involve performing a low-level format of the hard drive after deleting a
record and then re-installing the OS and SQL Server. That's a lot of work
to ensure that the record for "Wonka, Willy" was completely and thoroughly
erased.

Some form of encryption might be your best bet, since any residual bits left
on the hard drive after a delete would be incomprehensible to anyone without
the decryption algorithm. This will affect the system's overall performance
(I imagine it would slow it down considerably, and would make LIKE 'NAME%'
type queries almost impossible without Scanning every single row of an Index
and/or table). But it's a heckuva lot less work than performing a low-level
format after deleting each record.

"Ken Bass" <anonymous@nowhere.com> wrote in message
news:p5vv61h51n6r7gn
3scqa1u1774og8ev0qg@
4ax.com...
> The issue for my client is more of a legal one, like this:
>
> 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.
>
> Yes, I realized 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. I just didn't know if maybe somehow this functionality got
> put into SS or some other db engine.
>
> Thanks, though. All additional information is good to know.
>
> ken
>
> On 27 Apr 2005 09:36:25 -0700, "David Portas"
> < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote:
>
>

Hugo Kornelis

2005-04-28, 8:23 pm

On Wed, 27 Apr 2005 14:09:07 -0700, Ken Bass wrote:

>The issue for my client is more of a legal one, like this:
>
>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.
>
>Yes, I realized 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. I just didn't know if maybe somehow this functionality got
>put into SS or some other db engine.
>
>Thanks, though. All additional information is good to know.
>
>ken

Hi Ken,

Others have already mentioned encrypting as an alternative way to get
your client the desired security. Encrypting inside SQL Server has some
problems (like the slow searching, as pointed out by Michael C#, but
also the fact that you might still have unencoded data in the temp
database or in the Windows swap file). But have you considered
encrypting complete drives?

BestCrypt (www.jetico.com) allows you to create a very large file, then
open it as if it's a disk. You need the password to open the file, then
you can use it as if it were a regular disk. Without the password, the
data in the disk is indecipherable. BestCrypt also offers the feature to
encrypt the Windows swap file. That means that if the "jack-booted
government thugs" take out your hard drive and try to analyze it, even
the contents of the swap file won't help them.

I've never tried to store SQL Server's data and log files in a BestCrypt
drive, so I have no idea how it affects performance. You'll have to try
that out for yourself.
(Oh, and don't forget to move tempdb's data and log partition to the
encrypted disk as well!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Gert-Jan Strik

2005-04-29, 1:23 pm

In addition to the other replies: if you inline replace the real data
with garbage date, then make sure you do not update any clustered index
column, because that would cause the row to be moved. This could also
limit the potential clustered index columns that you might want to use.

Another thing would be to periodically reindex the tables with
fillfactor 100 and padindex 100. After that you can reindex them again
with a more reasonable fillfactor/padindex.

Gert-Jan

Ken Bass wrote:
>
> 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).
>
> Also, does something like this exist for other database engines, like
> Oracle, perhaps?
>
> Any ideas on this subject will be greatly appreciated.
>
> TIA
>
> ken bass

Sponsored Links