August 7, 2009 at 9:39 am
Hi all,
This question was asked by our security officer and I never considered it before. Let's say someone deletes an order record; by default it would get logged in the transaction log. Is it possible for someone to access the transaction log and remove the entry of the deleted order so there is no trace of what happened to that order?
Thanks,
Mon
August 7, 2009 at 9:56 am
Not possible
For someone to even begin to edit the log, the database would have to be detached and they'd have to use a hex editor. I doubt there's anyone outside of the SQL dev team who knows the log well enough to edit it with a hex editor (and probably very few inside the dev team). If someone tries and even gets one byte wrong, the DB will fail to reattach or will reattach suspect.
Remember the transaction log is not an audit log. It's job is to ensure database integrity no matter what.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2009 at 10:19 am
GilaMonster (8/7/2009)
Not possibleFor someone to even begin to edit the log, the database would have to be detached and they'd have to use a hex editor. I doubt there's anyone outside of the SQL dev team who knows the log well enough to edit it with a hex editor (and probably very few inside the dev team). If someone tries and even gets one byte wrong, the DB will fail to reattach or will reattach suspect.
Remember the transaction log is not an audit log. It's job is to ensure database integrity no matter what.
Absolutely true. Of course - there are other more "brute force" ways to cover your tracks, such as switching to SIMPLE and then back (which would blow out all committed entries in your log). Naturally, your admin would be able to tell that happened as well.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 7, 2009 at 10:37 am
The transaction log is not an audit log.. I wouldn't want to have to actually do it as I'm sure there are plenty of consistency checks but several companies (including Red Gate) have log readers in various forms and to change that to being a log writer might well be possible (although I am not aware of anyone who has one).
I would also imagine you would need to remove the insert and delete entries for the order, not just the delete entry. LSN's might well also need fixing up etc.
So it's not impossible but I think it would be quite hard to actually do.
James
--
James Moore
Red Gate Software Ltd
August 7, 2009 at 10:46 am
The main problem would be with the LSNs. Remove a entry completely and SQL will believe the LSN chain is broken (and it is). Modify the LSNs and suddenly the LSNs in the log and the ones in the data file (yes, there are some) won't match and the DB will be considered suspect.
To add to that, a single row delete won't have a single row in the transaction log. There'll be the Transaction begin, the commit, maybe a Ghost records cleanup, maybe a change to a page's header or a PFS page, etc.
I expect (though am not 100% sure) that most of the log readers use the undocumented SQL commands to read the log, rather than directly opening the file and decoding the binary file-structure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2009 at 10:51 am
It's possible. It would be extraordinarily difficult to do without leaving traces, but it is possible.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2009 at 11:57 am
Thanks everyone!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply