Is a "delete" really 100% gone?
-
Made no difference. I replace 1 chracter per 1 chracter and was still intact just not visible directly.
I have the test files I just worked with if anyone wants to take a look themselves. Just shoot me a message with your email and I will be glad to zip and forward with notes on each copy.
-
Just tried. Made no difference.
-
Stewart Joslyn may have hit on something - not BLOB, but TEXT data types. See: BOL "text, ntext, and image Data When text in row Is Set to ON" & "text, ntext, and image Data When text in row Is Set to OFF". TEXT data types may/may not be treated similar to BLOBs - storing data in other pages using pointers instead of with the current row's data.
hoo-t: didn't want to bog milz down with all the details, just point him to a possible solution although you are right about sizes.
-
Ok I see what stewart is saying and can verify that is right.
If the data is overwritten by all 0s for instance first that will change but this is only so far the case with BLOBs. However when the data is deleted if you had overwritten with all 0s that data woudl still be left behind. So text can be made safer. Note: has to write 0's at minimum the same number of characters over text data to make all go away.
-
Don't worry about bogging me down with details -- the more the better. Stirred up quite the hornet's nest I did, eh?
-
David, Thanks. Having worked in a government installation, I know where he is coming from. I don't remember specifics, but in sensitive data processing, when you delete a record, it must be overwritten multiple times to be considered properly deleted. There are serious consequences for not following those guidelines. Therefore, I wanted to be sure that he knew of all possible "gotchas".
Steve
-
hoo-t: "overwritten multiple times" - like PGP software wipes free space on hard drives.
milz: "hornet's nest" - that's life. When you got problems to solve, here's a great place to go. Lots of experience and willing to experiment on new things.
-
I enjoy the low level questions like this that don't have a easily identifiable white paper on them. Makes for fun to see how much I and how many ways I can test it.
-
Now I am curious on how Oralce does it. Anyone got an up and running copy of Oralce for Windows they can test with. If not may have to install mine to see what happens.
Anyone know of some server setting that they may have come across that changes the behavior I have described in testing. Brain isn't finding one, if there is, right now.
-
A further thought occurred to me in the middle of the night (sad!) - If you have varchars or a clustered index (and perhaps in other circumstances) data is likely to move physically simply when you do an update or insert : delete is a relatively trivial problem given the requirement specified! A minimum restriction would have to be no variable length fields and no clustered indexes (at least unless you never are going to delete records and only are going to add to the end of the index - ie clustered on an identity field).
Antares - does this scenario apply to your tests and offer an explanation for the retained deleted data?
-
Might, I won't have a chance until Thuesday thou to do further testing. Anything speciifc as far as table sturucture you want me to test. I will try with a table of just chars, a table of just varchars, a table of text, mixing the indexing up and anything else I can think of. Let me know if anythign speciifc to test. ALso I was testing a very very small DB, will try to grab a large DB from work to a test machine and see if size matters.
-
Just found this post by David Burrows in another thread (Administration."Ghost Cleanup"? Configuration...)
Quote from David Burrows:
To confirm your observations, found this on TechNet
quote:
When you delete rows, pages, or extents in your database, SQL Server can mark those objects as "ghosts" (meaning that deletion is pending) and clean them up later by using a background task. This process is called "ghost cleanup." Ghost cleanup improves the performance of the DELETE command because SQL Server doesn't have to deal with physical cleanup right away.No solution though. Is it possible that someone did a lot of deletes, the server has a lot or cleaning up to do or the server was busier than usual.
end quote
Seems to apply to this discussion!
Steve
-
Hi all,
Good discussion! I just want to add that we usually back up databases and those backups are very readable if you open them in Notepad. Another thing: the backup does contain a copy of your deleted data and if somebody restores it on another server... I would password-protect backups
Regards,Yelena Varsha
-
It would seem to me that the only way to guarantee that data genuinely is deleted immediately is to overwrite it with garbage. The problem comes down to ensuring that we overwrite the same physical blocks. Perhaps you need an extended stored procedure to get the pointers from the server and do the work - way beyond my knowledge even to specify, let alone code! Even then, there is still the problem of the server deciding to reorganise the data pages to fit in an insert (clustered) or a change to field width (variable width fields). It's probably simpler, safer and cheaper to adopt a different database!
-
A MS site with a bunch of security info:
http://www.microsoft.com/technet/security/prodtech/dbsql/default.mspx
including a topic on "Standards, Regulations, and Government Issues" and "Additional Security Resources"
For the instance of the whole data/log file (.mdf or .ldf) getting into the wrong hands, you could use Win2k's encryption?
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply