A couple of days ago I walked through getting information about data deletion uing the SQL Server Transaction Log. We looked how you could find when and by whom data had been removed from a SQL Server table.
So you’ve got the when, and you’ve got the who, now how do you precede? Remember you could be holding someone’s job or professional reputation in your hands.
As a long time DBA I’m well aware that applications are not always the best written things, and instructions don’t always spell out correctly what an option is going to do.
In the case I was writing about I withheld the name from general knowledge, a single senior manager was made aware that I knew and would be investigating. Why did I do this?
In the case of this particular third party application , there were a couple.
- Deleting data within the app should write data to an internal audit trail, this hadn’t
- Users cannot generate their own SQL queries, so this wasn’t an ad hoc query either
- The single row delete was in it’s own transaction
- This particular app is badly written. We’ve seen it fail to correctly write it’s own ini files on exit
- It has a habit of crashing out for no reason
So a bit like a lot of applications out there. Now I had some time to investigate I started by going through logs on the client Windows machine and trying to replicate on my own.
Eventually I traced it back to a coding error in the application where they processed an Update as:
- Delete old data
- Insert new data
- Write audit log
None of this happened in an encompassing transaction (from studying the Transaction Log). So when the app crashed just after step 1, the row was just left missing and nothing there.
To the user who was logged in at the time, they wouldn’t have been aware of the crash as the GUI doesn’t display app status. The crash also happened at 16:55, which is pretty much home time so they wouldn’t have looked at the record again
Now I had the evidence I could talk to the Senior manager and show that we needed to raise this with the supplier, and that the user was blameless, but that we should recommend to users that they should double check any updates to be sure.
Releasing the name to a less trusted source without this background information would have probably led to the user being blamed for something that wasn’t really their fault
So, just because you have the information it doesn’t mean it’s actionable with adding more context and knowledge around it.