Blaming yourself here is like blaming yourself for being hurt after being told to drive a car with no seatbelt or brakes.
What system would you put in place to prevent this? The issue was that I connected to prod when I thought I was connecting to a test DB. We each had different credentials for prod vs everything else, but the SQL client remembered my username and password. Anyone with prod access could have made the same mistake.
* Keep the prod DBs in an isolated VPN that requires a separate login outside of the SQL client. Stay logged out of that except when you explicitly need production access. This keeps you from casually messing with production.
* Don't save production credentials in your SQL client - uncheck the box or whatever you have to do. Probably a good idea for security anyway.
* Some clients will let you change UI for each DB. I know that SQL Server Management Studio will let you change tab and editor background color. So maybe make prod all red (or pink, or something else annoying).
* Only give a few people production logins and require them to audit everything before they run it. Actually, I'm surprised this wasn't already the case for a company dealing with health info.
I'd have an emergency procedure, sure, one where in some dire circumstance somebody could poke a hole in the firewall, change the database configuration, open a sealed envelope, and then look at/change the real data.
But in normal circumstances, anybody who really needed to see prod data would look at a read-only copy. (Or better, would look at an identity-scrambled version of it.) Any anybody who needed to change it would write a bit of code to do the work and take it through the normal review and push process.
Also, sort of related, I'm using MacOS, and in the back of my head I've wanted to create a tool that will change the color of the menu bar (at the top of the screen) to, say, bright yellow, when I'm connected to the VPN so that I don't accidentally visit a porn site while still connected to work.
That said, neither of these systems is even close to fail-proof :)
Another option would be to configure your routes. At a previous job, I set up my home router to connect to the VPN and route 10.* to the VPN interface. Setting this up isn't easy, but it's oh-so-convenient. Reading http://wiki.openwrt.org/doc/howto/vpn.client.pptp will start you on the right track.
Be careful though. This gives anyone on your home network access to work. It almost certainly violates security policy. I only did this because I knew I'd just be chastised if I got caught. (Same goes for running rogue APs at work.)
P.S. You poor soul! I feel your pain.
Regardless, I am very glad they are now out of business. :)
The story reminds me of the day I was introduced to "BEGIN TRANS", "COMMIT" and "ROLLBACK" when someone upgraded the Sybase console and helpfully changed the default setting so we didn't need those pesky semi-colons to finish a query any more. The result was:
DELETE * FROM TABLE x
131054 rows deleted
WHERE a = "foo"
>> Malformed query <<
This stuff is way too easy for us noobs. Thank goodness that with modern technology we've found ways to make sure it doesn't happen any more... :-)
That right there is one of the worst feelings in the world. I imagine that everyone on HN just felt it with you as they were reading it!
Totally agree that the higher-ups were responsible for not putting better roadblocks in place to prevent this type of thing from happening.
We were able to restore the data pretty quickly, but we had to interrupt warehouse workflow for a few minutes. They were surprisingly accommodating, almost amused by my mistake.
I was a little sleepy one morning and accidentally connected to prod instead of testing. I thought, "That's weird, this UPDATE shouldn't have taken so long-oh shit." I'd managed to clear all allergy and malignant hyperthermia fields. For all I knew, some anesthesiologist would kill a patient because of my mistake. I was shaking. I immediately found the technical lead, pulled him from a meeting, and told him what happened. He'd been smart enough to set up hourly DB snapshots and query logs. It only took five minutes to restore from a snapshot and replay all the logs, not including my UPDATE.
Afterwards, my access to prod was not revoked. We both agreed I'd learned a valuable lesson, and that I was unlikely to repeat that mistake. The tech lead explained the incident to the higher-ups, who decided to avoid mentioning anything to the affected hospitals.
If it's any consolation, the company is no longer in business.
Just remember when you screw things up: Your mistake probably won't get anyone killed, so don't panic too much.