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.

Chris B. Behrens
  • 671
  • 1
  • 6
  • 12
  • 26
    Developers should not have write access to the production databases, or preferably, _any_ access. – Michael Hampton Jan 07 '15 at 20:49
  • 12
    @MichaelHampton - It's me and him. I'm also a developer. What do you suggest? – Chris B. Behrens Jan 07 '15 at 21:08
  • 10
    Separate user accounts for each role (dev vs ops/DBA). And an abundance of caution. – Michael Hampton Jan 07 '15 at 21:31
  • Where I work I don't have access to production - which is good. I do have access to a staging server. If you are using SQL Management Studio there are tools (I use SqlPrompt) that can colour the tabs and I try to ensure I have one instance of management studio per server (local dev, staging) – Nigel Ellis Jan 08 '15 at 09:42
  • 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 09 '15 at 04:08
  • @ThorbjørnRavnAndersen - yeah, that's what is ideal. It's just tough prying pennies out of management BEFORE problems. – Chris B. Behrens Jan 09 '15 at 15:01
  • @ChrisB.Behrens sounds like you could use the "oops, I messed up the production database" as an example of what can happen, then. – Thorbjørn Ravn Andersen Jan 09 '15 at 18:04
  • 1
    Just have separate user/passwords to those databases. – neutrinus Jun 02 '16 at 20:39
  • Update - we eventually did ALL OF THE ABOVE. We separated the environments so that Staging and Production were on different boxes, AND we automated deployment. Much better situation now. – Chris B. Behrens Jun 02 '16 at 21:36

6 Answers6

34

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.

Oli
  • 1,799
  • 19
  • 27
  • 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 08 '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 08 '15 at 16:58
  • Where do I get one of those simultaneous-turn dual-key contraptions and does it come with USB? – Lilienthal Jan 08 '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 09 '15 at 01:16
  • @user24313 I can't see any reason to give the prod account access to dev. Quite the opposite, really. If it has both but the other only has part, people will just use the full access one for convenience. – jpmc26 Jan 09 '15 at 01:19
  • @jpmc26 I agree. If dev doesn't have to have prod access, they should not. In my experience, once the genie's out of the bottle though, it's hard to put it back in without a massive fire. I liken the dual accounts to a checklist. There's nothing directly to stop someone from doing the work and ticking all the boxes after the fact, but it's a much harder conversation about why you "forgot" when you're supposed to check the boxes off one at a time. Outside of no access (which may be best) all we can do is practice risk reduction strategies. – user24313 Jan 09 '15 at 02:10
  • 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 09 '15 at 08:19
  • s/attacking/attaching/ – Oli Jan 09 '15 at 08:35
33

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.

Ben Thul
  • 3,024
  • 17
  • 24
  • 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 07 '15 at 23:06
  • 11
    Now you're thinking with portals. :) – Ben Thul Jan 07 '15 at 23:08
  • 1
    +1 automation is your friend. You can also benefit greatly from continous integration tools like TeamCity, CruiseControl and Octopus Deploy. – squillman Jan 07 '15 at 23:14
  • 5
    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 08 '15 at 11:22
  • Restoring a development backup to a production environment hardly ever makes sense. But restoring a production backup to production or staging makes a lot of sense. The mistake may have been as simple as specifying the wrong destination after specifying the correct backup to be restored. – kasperd Jan 08 '15 at 14:47
  • 1
    Yeah, it was exactly that. The new SQL 2012 Restore Database dialog is a little different from 2008 R2, which I think was the source of the error. – Chris B. Behrens Jan 08 '15 at 17:34
  • 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 09 '15 at 08:34
  • @OliverS: That's all good in theory. But at the end of the day, the OP has a business need to do that. You and I can be pragmatists and say "you're wrong for wanting to do that!" or we can help them to do what they want to do in the safest way possible. I've chosen the latter. Is there a better way? Probably. Does the OP know they're not doing things 100% correctly? Also probably. But we're all paid to do a job, not to adhere to a set of rules to the detriment of getting business done. – Ben Thul Jan 09 '15 at 17:35
  • 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 09 '15 at 17:45
  • 1
    @OliverS: Ah... that's a horse of a different color. You said "nobody should have access to production by default" which I read as "the default policy should be that nobody should have access to production". That said, your proposed safeguard doesn't prevent accidents; it just makes you have to do one more thing to make them. And, for the record, I agree with at least making it difficult. Lastly, your objection doesn't really have anything do to with my answer; the automated solution that I'm proposing could run under an elevated account once it's been tested. – Ben Thul Jan 09 '15 at 17:51
  • 1
    @BenThul I just feel that your answer is not complete without that part. It is basic hygiene to have different accounts on a production machine and doing sys admin tasks like restoring a DB should always have the burden of doing something special like retrieving a password. I will remove my down vote. – OliverS Jan 10 '15 at 07:38
  • @BenThul Cannot remove the down vote until the answer is edited. Sorry. – OliverS Jan 10 '15 at 07:39
13

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. :)

robhol
  • 141
  • 3
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.)

200_success
  • 4,771
  • 1
  • 25
  • 42
  • 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 08 '15 at 09:59
  • 1
    @Falco That's exactly what I'm suggesting though. Inconvenient but feasible. – 200_success Jan 08 '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 08 '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 08 '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 09 '15 at 15:34
  • @Falco - I've spent a **LOT** of time in "realistic big environment[s]": and there is *never* a reason for Devs to have direct access to Prod - not using the same creds as they use for Dev, and not the same as Stage. – warren Jan 12 '15 at 20:22
1

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.

warren
  • 18,369
  • 23
  • 84
  • 135
1

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.

Massimo
  • 70,200
  • 57
  • 200
  • 323