One summer in college, I got an internship at a company that made health information systems. After fixing bugs in PHP scripts for a couple weeks, I was granted access to their production DB. (Hey, they were short on talent.) This database stored all kinds of stuff, including the operating room schedules for various hospitals. It included who was being operated on, when, what operation they were scheduled for, and important information such as patient allergies, malignant hyperthermia, etc.
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.
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. :)
Uh, has anyone on this thread heard of HIPAA? I'm pretty sure having a summer intern get full access to actual patient data shouldn't be possible under a properly implemented set of HIPAA processes, and the same goes for the accidental UPDATE.
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 <<
Phone starts to ring a few seconds later as all the users saw their morning's work disappear.
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... :-)
Did a similar thing, but in a less critical domain (warehouse management). Updated the status of all packages to "NEW", which would have meant that everyone who ever ordered something from that company would have gotten another delivery for free, provided the articles were in stock.
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.