Is a "delete" really 100% gone?
-
OK, this may be an "out there" question but here goes. When a row in a table is deleted (via EM, SQL, how doesn't matter...), is that row guaranteed to be physically gone from the database? That is, if someone were to get a hold of the MDF file and pick it apart, would they be able to "find" that data or would be be gone. I told you this might be "out there"...
Here's why I ask. Within the DoD world we have to meet certain levels of accreditation for specific systems. One system I'm working on had just such a requirement -- deleted data must be immediately gone. No waiting for a system cleanup, "trash collection", or anything like that.
Anyone got a clue about this? TIA...
-
Depends on how it is removed. If done with a truncate the answer is no, only the poitner in sysindexes to the first page is set to 0 (invalid). If done via DELETE then the data is removed from the data page but depending on you recovery settings a copy of the data potentially exists in the Transaction Log until you clear the log.
-
As always, unless you've melted the hard drive down, it's still there.
SQL Server may 'release' the datapage, but until something else is written over the top of it, it's still there on the hard disk, and probably still there in the physical database files. SQL Server only releases the data pages it's not using when you call DBCC SHRINKDATABASE telling it to truncate free space. Even still, you've got to ask the OS or a 3rd party program to write over the blocks.
But you already encrypt your data right? So released datapages shouldn't be easy to recover anyhow.
Whichever DoD you are working for should already have guidelines on what database/os/scrubber programs are to be used, configuring, and auditing of their status. Check with those guys in systems.
SQL Server only has a C2 certification anyhow ( http://www.microsoft.com/technet/security/news/c2eval.mspx ). There are better (and more expen$ive) data base systems out there with higher certifications.
Julian Kuiters
juliankuiters.id.au -
"That is, if someone were to get a hold of the MDF file and pick it apart". Kind of outlandish. If someone would be capable of getting the mdf file, then you're into deeper problems, even if the deleted items can't be recovered.
Oracle provides better security, BTW.
Of course, the old delete data still exists in the backups. I would rather steal those if i want old data 😀
-
Overwriting the data with random garbage (several times if you are that paranoid!) gets rid of it whether or not you then delete the record but the comments on transaction log and old backups are valid. What is the threat against which you are trying to protect?
-
Thanks for all the info. I'll be sure to check out the links that were provided. BTW, I am aware of Oracle being better in security but we have a system written in SQL Server and we're trying to get "the powers that be" to accredit our IIS/SQL/ASP system and not force us to re-write it in Solaris/Oracle/whatever. As for the question "What is the threat against which you are trying to protect?" what we're looking at doing is having our DB contained data of mixed classification records. The issue is that if a record classified SECRET is deleted and a record classified CONFIDENTIAL (lower classification) gets written in the space where the old record was, the possibility exists that latent SECRET data is now part of a CONFIDENTIAL record (which is bad). Even if the old data was encrypted, our accreditation requirement says "no info, including encrypted info, produced by a prior action is to be available to any subject that obtains access to an object that has been released back to the system. There must be no residual data from the former object". Windows apparently takes care of this for us WRT memory space, it's just the SQL Server issue that we're unsure of. Yes, these rules stink, but rules are rules...
-
"There must be no residual data from the former object". IMHO, the only place a residue can be found, is on the harddrive. This means that 'a copy of a mdf' file is not the place to search for residue's. You need a stolen hard drive, sectorscanning and some deeplevel i/o knowledge on MSS. Not an obvious scenario. If someone has OS access , then sectorscanning is not the softspot to aim for. 😉
But there must be people around here much more into this than this little DBA 😉
-
Since best practices SQL Admin/Development say do not allow direct access to tables, you may have already (or could) perform deletes through stored procedures which replace all the data in the row with garbage (overwrite the SECRET record with random data) before deleting it. That way when the CONFIDENTIAL record is written in the space of the old record any "slack" bytes just contain random (non-SECRET) data.
Transaction log issues corrected by data-recovery model selection.
-
Sorry, went and test myself. The data is still there even after a delete. The pointer on the page to the record is invalidated is all. But even more interesting was the fact when I changed the data the old data was still present in the file. Further more when I changed the data yet again both the original, second and last versions were present in the file. So overwriting it does not help either. ANd it doesn't make a difference which recovery model I am using.
-
old data still present where? original page (invalidated pointer) and changed data written to a different page?
-
Presumably this is BLOB data. Does that make a difference?
-
Seems to be the case it is moved to another page (the entire page) from what I can tell even thou I don't see a page number change. Even tried truncating free space from file to see what would happen and on my small test it all still existed. I could find the entire version history as I knew what I was looking for.
Basically I created a table with the following
TestDel
IDX int identity(1,1) not null primary key,
S1 varchar(10) not null,
S2 char(10) not null,
S3 text not null
Then added several lines of data.
Stopped SQL and copied the file for testing. Looked at and found data by search for key values from each row and text (text is on seperate data pages).
Then started and deleted a row.
Stopped and again in notepad search (found deleted row).
Started and altered a row in all 3 fields with something different from all others.
Stopped and again notepad. Found ordiginal data along with other data on that table as original and then found new data on another part with same other data.
Tried that all again 3 times and found all there each time.
Then tried truncate free space (not shrink) and again found. Then tried moved data pages to front of file with shirnk. Again all data found.
Note this DB was 1Mg DB and 1 Mg Log test so no outside interference.
-
If you decide to go the route of replacing your secret data with "garbage data" before deleting the record, there is something else you must consider. If your "garbage data" is much larger than your secret data, you may cause a page split, in which case your "garbage data" will be written to a new location. Therefore, you would need to be sure that the "garbage data" is the same size as the secret data.
Steve
-
Note in the test it was all data. I was complete taken off guard by test results. Doesn't mean it won't eventually go away but testing shows it remains for a lot longer than thought.
-
Hey Antares686, did you try doing a reindex?
Steve
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply