31

Just recently, I had a developer accidentally try to restore a database to production, when he should have been restoring to a staging copy. It's easy to do, given that the db names are similar, i.e., CustomerName_Staging versus CustomerName_Production.

Ideally, I'd have these on entirely separate boxes, but that is cost prohibitive, and, strictly speaking, it doesn't prevent the same thing from happening if the user connects to the wrong box.

This is not a security problem, per se - it was the correct user working with the staging database, and if there is work to be done on the production database, it would be him as well. I'd love to have a deployment officer to separate out those concerns, but the team isn't big enough for that.

I'd love to hear some advice in terms of practice, configuration and controls on how to prevent this.

  • 25
    Developers should not have write access to the production databases, or preferably, any access. – Michael Hampton Jan 7 '15 at 20:49
  • 12
    @MichaelHampton - It's me and him. I'm also a developer. What do you suggest? – Chris B. Behrens Jan 7 '15 at 21:08
  • 10
    Separate user accounts for each role (dev vs ops/DBA). And an abundance of caution. – Michael Hampton Jan 7 '15 at 21:31
  • 2
    I would strongly advise that you have your production environment on a separate box. Otherwise staging and production have to share resources - disk, cpu, etc. - and if staging is monopolizing a resource your production environment may suffer. – Thorbjørn Ravn Andersen Jan 9 '15 at 4:08
  • 1
    Just have separate user/passwords to those databases. – neutrinus Jun 2 '16 at 20:39
32

If this is something you see yourself doing often, automate it. And since you're both developers, writing some code should be in your wheelhouse. :) Seriously though... by automating it, you can do things like:

  • Verify that you're restoring on the correct server (i.e. no dev -> prod restores)
  • Verify that it's the right "type" of database (in your case, "staging" and "production")
  • Figure out what backup(s) to restore automatically by looking at the backup tables in msdb

Et cetera. You're only limited by your imagination.

  • 1
    That's an interesting idea...we already have code that manages db restores (for automated testing). We could put an abstraction layer in between that only ever pointed at staging so that restoring to production was an entirely different process... – Chris B. Behrens Jan 7 '15 at 23:06
  • 11
    Now you're thinking with portals. :) – Ben Thul Jan 7 '15 at 23:08
  • 4
    For automated jobs that affect production, I like to add in a manual step that requires the user to type in the word "production" to reduce the possibility that they think they're looking at e.g. the staging equivalent. – Joe Lee-Moyet Jan 8 '15 at 11:22
  • 2
    I downvoted as nobody should have access to production by default. You have to have a special process to retrieve a prod password. It is inconvenient but really the minimum. – OliverS Jan 9 '15 at 8:34
  • 1
    @BenThul Adding a different account for prod access and making it one more step inconvenient is still the correct solution to me. The business need is not to have the DEV save 2 minutes but to restore the DB which can be perfectly moved to a prod account. – OliverS Jan 9 '15 at 17:45
32

I disagree with the assumption in the question —this is security— but I also disagree that automation is going to save the day on its own. I'll start with the problem:

You shouldn't be able to accidentally do anything to production!

That includes doing automated things accidentally.

You're confusing system security with concepts like "who's allowed to do what". Your development accounts should only be able to write to their copies, the version control server and the dev database. If they can read/write production, they can be hacked and exploited to steal customer data or (as you've demonstrated) can be mishandled into losing customer data.

You need to start by sorting your workflow out.

  • Your developer accounts should be able to write to their own copies, version control and perhaps pull from version control into a testing environment.

  • Backup users should only be able to read from production and write to your backup store (which should be aptly protected).

  • Doing any other read/write on production should require special and inconvenient authentication. You shouldn't be able to slip into it or forget you're logged in. Physical access control is useful here. Smart cards, flip-switches to "arm" the account, simultaneous-turn dual-key access.

    Accessing production shouldn't be something you need to do every day. Most of the work should be on your testing platform and out-of-hours deployments made to production after careful scrutiny. A little inconvenience won't kill you.

Automation is part of the solution.

I'm not blind to the fact that the full turnaround (uploading to VCS, checking coverage, pulling to test server, running automated tests, reauthentication, creating a backup, pulling from VCS) is a long process.

That's where automation can help, per Ben's answer. There are many different scripting languages that make running certain tasks much, much easier. Just make sure you don't make it too easy to do stupid things. Your reauthentication steps should still be pronounced (and if dangerous) they should be inconvenient and hard to do without thinking.

But alone, automation is worse than useless. It'll just help you make bigger mistakes with less thought.

Suitable for teams of all sizes.

I noticed you pointing out the size of your team. I'm one guy and I put myself through this because it only takes one person to have an accident. There is an overhead but it's worth it. You end up with a much safer and much more secure development and production environment.

  • 2
    In addition, one thing I like to do is use two named accounts per user. One is for normal user login, operation, day to day work, etc., while the second account (usually with some kind of suffix like a + or an underscore) has full rights to prod and dev that the user requires. That way, the user must make an active decision to push to prod as opposed to dev. This is similar to bullet 3 described above, but doesn't require significant additional infrastructure or expense to demonstrate the value. – user24313 Jan 8 '15 at 16:15
  • 3
    It's also important to avoid getting into the habit of doing anything but prod maintenance in your prod account. To that end, make sure prod can't see source code, can't start an IDE, etc. – Eric Lloyd Jan 8 '15 at 16:58
  • Where do I get one of those simultaneous-turn dual-key contraptions and does it come with USB? – Lilienthal Jan 8 '15 at 22:12
  • Something else that might help is fully automating (one or two click) procedures in staging and dev, but not fully automating production deployments. Having to manually remote into the box to do anything to production but not to other environments is a significant difference in convenience, as you suggest. (You could still script out any steps involved and use that script on all environments; what I mean is that you would have to manually fire off execution of said scripts for production.) That of course can be done in addition to the type of authentication procedures you recommend. – jpmc26 Jan 9 '15 at 1:16
  • 1
    @Lilienthal It was a metaphor for high security theatre but you could emulate that cheaply attacking USB sticks to each developer and then having your automation check for at least two of their serial numbers when running dangerous things. In bigger teams you could then log to see who is interfering with production and hold the right people responsible when it all goes wrong. – Oli Jan 9 '15 at 8:19
12

One of my coworkers has an interesting approach to this. His terminal color scheme for production is fugly. Gray and pink and hard to read, which is theoretically supposed to ensure that whatever he writes, he really intended to write.

Your mileage may vary... and I probably don't have to say that it's hardly bulletproof on its own. :)

  • 2
    I also use a big red background color on terminals/db connections to production servers, as well as bright red wallpapers for admin-accoungs on PCs... – Falco Jan 8 '15 at 10:00
  • Yeah, I was thinking that. Make production fire-engine red... – Chris B. Behrens Jan 8 '15 at 15:02
  • Color coding helps. Just as in an IDE. – Thorbjørn Ravn Andersen Jan 9 '15 at 4:10
1

Developers shouldn't know the password to the production database. The prod password should be random and not memorable — something like the result of keyboard mashing (Z^kC83N*(#$Hx). Your dev password can be $YourDog'sName or correct horse battery staple or whatever.

Sure, you could find out what the password is, especially if you're a small team, by looking at the client application's configuration file. That's the only place where the prod password should exist. That ensures that you would have to make a deliberate effort to get the prod password.

(As always, you should have point-in-time backups for your production database. For example, with MySQL, archive the binary logs as incremental backups. For PostgreSQL, archive the write-ahead logs. That is your protection of last resort for any kind of disaster, self-inflicted or otherwise.)

  • I can't fully agree with this, because in any realistic big environment there are fairly regularly instances where developers/admins need to access the production database. Sure in a perfect world with a faultless system this should never happen, but in most systems I know you have to fix some production critical data by hand... So I'm with Oli, production login should be inconvenient, but feasible – Falco Jan 8 '15 at 9:59
  • 1
    @Falco That's exactly what I'm suggesting though. Inconvenient but feasible. – 200_success Jan 8 '15 at 10:02
  • The problem with your approach is only, if there is an emergancy and production is down, time counts. So your devs should know where to find the password and get it fast. If they have to ask around, search the repository and config files and try around you're losing valuable time = money. So I'd rather have the password in a place where everyone knows where to look, but it is still inconvenient, but fast if need be – Falco Jan 8 '15 at 10:16
  • 2
    @Falco Since prod environments should closely mirror dev environments, the configuration file would be in the analogous place on the prod server as it is on the dev machines. Any competent developer should know where to look, and if they don't know where to look, then you want that delay — precisely for preventing damage of the type stated in the question. – 200_success Jan 8 '15 at 10:21
  • Not knowing passwords does not hinder accidents. Quite to the contrary, it creates motivation to just make the password lookup only once, and after that the developer may start using bash history or may even create an alias to connect to the database. And then, accidents are more likely to happen. – k0pernikus Jan 9 '15 at 15:34
0

The short answer is RBAC - role-based access control.

Your permissions to all environments need to be different - and as annoying as things like things like UAC are, you need them: especially for PROD environments.

There is NEVER a reason for devs to have direct access to Prod - regardless of how small the organization/team is. Your "Dev" may also wear the "Stage" and "Prod" hats, but he needs to have different credentials and processes to hit different environments.

Is it annoying? Absolutely. But does it [help] prevent borking your environments? Absolutely.

0

A quick and simple solution: use two different user accounts, one for your normal development work which has only access to the development database, and a different one for actually operating on the production database, with full access to it. This way, you will have to actively change the account you are using before you can make any change in production, which should be enough to prevent accidental mistakes.

The same approach can be used if you have two websites, or two servers, or two whole environments: one user account for development with no access (or at least no write access) to production, another user account for working on the production system(s).


This is the same approach as a sysadmin having a standard non-admin account for routine work (reading email, web surfing, tracking tickets, filing timesheets, writing documentation, etc.) and a distinct full-admin account to be used when actually operating on servers and/or Active Directory.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.