You didn't screw up here. The entire infrastructure, org chart, and policies that allowed you to accidentally modify a production database containing critical medical information screwed up.
Blaming yourself here is like blaming yourself for being hurt after being told to drive a car with no seatbelt or brakes.
Sure there's plenty of blame to spread around, but I still would have felt terrible if someone had been hurt or killed.
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.
In a case like that, where clicking on the wrong thing could result in death? I would never allow production database access for anything other than the running app.
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.
In the past, I've set up big MOTD style messages that say "PROD" in fancy ASCII graffiti when I ssh/connect a DB client/whatever to production. I think I will set one of those up now for my current setup.
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 :)
Maybe you could set a translucent menu bar, then script something to change the top 22px of the desktop background based on the VPN connection status. It's hacky, but it'd work.
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.)
Most VPNs can be configured to only route certain subnets over them (so all work related networks, for example) instead of everything. This is very simple to do with OpenVPN; can't speak as to the Mac builtin solution.
This is essentially what I do - Black on White for production, White on Black for development. If I'm running development commands on a Black on White screen, something doesn't feel right. It isn't a life-or-death application, so this is enough.
And that's why you can't connect directly from my desktop networks to the production environments, but you can connect to the dev facilities. Firewalls: they're not just to protect against outside threats.
In my work, developers only have read access to production servers (for checking logs etc). If you want to make a change to a production system, you need to go formally request it through OTRS. So this sort of situation can't really arise. You can of course still cock-up live systems through asking the sysadmins to do something stupid, but then the problem is stupidity, not carelessness.
He could have easily revoked any UPDATE and DELETE commands from your privs list. INSERT, CREATE, SELECT is (usually) plenty fine and any database migrations that need to happen should typically be reviewed by him then run by him.
He's not entirely innocent. It's more like blaming himself for being hurt after being told to drive a car with no seatbelt or brakes, while knowing that the car has neither, fully understanding what could happen, agreeing to it anyway, then driving 80 mph down a residential street while still groggy from waking up.
Regardless, I am very glad they are now out of business. :)
Blaming yourself here is like blaming yourself for being hurt after being told to drive a car with no seatbelt or brakes.