Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

So I've worked in many different workplaces as a developer and my level of access to the database has been varied. I typically don't have production db access.

Most of the time I have access to the test database, but it varies. Sometimes I can do and change database and data as I please, but usually there are other arrangements. Like I may only have read access to the data.

I worked in one place where a DBA team would manage the databases, we couldn't make changes at all unless we submitted a form with the sql script for them to "inspect". They typically didn't have much to do with the project itself so most of the time, it was just to press F5.

Honestly, I can understand why prod needs to be locked down but I prefer to have as much database access in the dev and test environments. I think most devs are reasonably capable of knowing their way around a database. But I would like to hear opinions though? How much database access should developers have? Can we be trusted to not break anything up there?

share|improve this question
6  
Probably you meant to ask "How much production database access should developers have?" –  Mayank Mar 25 '11 at 6:39
    
@Mayank You hit the nail on the head. There should always be a testing server to 'play' with new concepts. The production server should only see the revised/tested/proven releases. I'd say the same for websites (even though most web devs don't use one). –  Evan Plaice Mar 25 '11 at 9:12
    
@Mayank - Yeah, I would like to hear about access for production databases, but would also like to hear opinions about access in different environments such as DEV, INT, TEST, PREPROD etc. too –  RoboShop Mar 25 '11 at 23:36
    
Though it's been marked as a duplicate, the related question programmers.stackexchange.com/questions/246618/… is actually an interesting extension of this. –  Eamon Nerbonne Jul 3 at 15:24
add comment

9 Answers

Devs need Read access to all databases including prod. Sometimes the problem is the data on Prod is not what they expected to have and they need to see the data that is causing the problem becasue they can't reproduce it on dev.

Devs should not have production data write rights or rights to create objects. Nothing should be going to prod that is not part of an official release. All too many times, people do a quick fix on prod that either doesn't work, causing prod to be even more mucked up or works but they forget to put the code into the dev/QA/Staging servers and even worse into the source control repository and the code gets overwritten about a month later in the next offical release.

I prefer devs to have full database QA rights because deploying to another server helps them see if there are any gaps in their deployment process (oops forgot that I changed that table in order to do thus and such and oops I forgot that I did that change using the GUI and not in a script in source control which is how any database structural changes need to happen).

When you have a new Enterprise-type client who will have their own set of servers, permissions may be eased before going live. This is because so much needs to happen and the few people who can make it happen on prod get backlogged and sometimes even need to take time off. In particular the people who are importing data from another system, might get tasked to put them on prod before launch if the dataload is going to take a long time. These people tend to be data specialists and there is a higher comfort level with allowing them temporary access to prod than the average application dev. This is not a luxury you have when going to an already live production server.

One of the most critical things about limiting production rights to the database is that the devs then need to ensure their work is in a form that can be deployed by someone else. This tends to improve the quality of the work because they aren't trying to make fixes on the fly becasue they forgot something or something didn't work becasue they did it differently on prod than deve when relying on memory alone. You also lose those "oops I deleted the whole user table by accident because I forgot to hightlight a where clause" type of accidents when prod deployments are purely using scripts that are run as a whole not one command at a time as is typical when devs run things on prod. A team with limited rights to prod databases is more likely to store database changes in source control as well.

share|improve this answer
    
+1 for the comment about forgetting to put things in source control. I think regardless of the access rights and who does the migration to different environments, it should be as automated as possible to ensure all builds are built from source control code. You should try your best to limit as much as possible any process that requires remoting into the server yourself to mess with the code or database. –  RoboShop Mar 25 '11 at 23:46
7  
"Devs need Read access to all databases including prod" is no-no, at lest in my previous job. The prod data contains customer financial records and transactions, which is confidential. –  ohho Mar 28 '11 at 1:25
1  
@ohho, that is a valid exception, but it must make it really diffiucult to troubleshoot a problem you can't immediately reproduce on dev. –  HLGEM Mar 28 '11 at 13:12
add comment

Usually (that's means there is the luxury to setup a full environment), the developer access to:

  • Production server
    • None (SA/PM will apply for schema setup, end user will provide init data)
  • UAT server
    • None (SA/PM will apply for schema setup and sample data seeding)
  • Testing/QA server
    • Usually developer will send schema setup script to QA team and QA creates the tables
    • Developers have full access to databases but seldom alter it
    • Developers may help QA colleagues to seed/patch/delete some data
  • Development server/localhost
    • Full access

The main reason behind why developers should not touch production servers is: when anything goes wrong, the operation team is responsible, but not the developers.

share|improve this answer
1  
The developers are always responsible even if they can't touch the system they are the ones that end up fixing it. –  Erin Mar 25 '11 at 14:00
2  
If the fix is a change in the database, it is the responsibility of the development team to produce the fix, and he operation team to apply it. Also, for sanity reasons, I would not allow developers to "alter" in any way (data or structure) the QA environment. Any change to that environment should be as controlled as the Production environment. –  Soronthar Mar 25 '11 at 15:00
1  
If you have an operation team... –  Marcie Mar 28 '11 at 21:12
    
I'd ask for read-only access on the production servers. Makes finding bugs a lot easier. –  Carra Apr 4 '11 at 9:10
    
@Carra: That may have regulatory problems, as production servers can have data that is legally regulated (a medical system in the US must comply with HIPAA, for example). I've never been in a place myself where anybody tried to restrict my access to live confidential data, but they probably exist. –  David Thornley Apr 4 '11 at 13:51
add comment

Well its really a question of "Vampires (Programmers) versus Werewolves (Sysadmins)" as Jeff Atwood has put it here.

share|improve this answer
    
Go Team Edward I guess. –  Joel Etherton Mar 25 '11 at 14:27
2  
@Joel Etherton, for those of us who haven't seen the movie, which one is Team Edward? –  CaffGeek Mar 25 '11 at 16:40
1  
@Chad Its good that you have actually not seen that "Twilight" crap. At least you won't have to pretend to not have seen it, like me. ;) –  Mayank Mar 25 '11 at 16:55
    
@Chad: I haven't seen the movies either, but Edward is the vampire. I know that because of the constant bombardment a while back by Burger King commercials and some stupid "buy our crap with Twilight smeared all over it" campaign. Soy un programador. –  Joel Etherton Mar 25 '11 at 17:14
    
oh, I know it's good I haven't seen it. And I never will. –  CaffGeek Mar 25 '11 at 18:51
add comment

The absolute minimum you need to get the job done.

If all developers are given full DB access, the chance of one getting angry (or drunk, or extremely tired or...) and doing serious damage is much higher then if they can only read from a database.

If your job can mostly be done without DB write access (And by mostly I mean at most a few change requests a week), then you really don't need write access.

share|improve this answer
add comment

There are 2 competing desires in all work environment.

  • The desire to give people access so they can solve problems on their own. This allows them to be fast and self service.
  • The desire to limit and control access to prevent damage, down time or loss of data (intentional or otherwise).

Part of what shapes the balance that is struck (or should anyway) is the expectation set for the developers. In every job I've had where developers had access to everything the expectation was for them to limit themselves. Only access the system if you know what you're doing. That meant you knew what you were doing from both a developer and sysadmin standpoint. If you weren't sure in either area you had no business accessing the systems without someone who did to chaperon you.

To sum up if you don't know, you shouldn't have access to any system other than easily re-buildable dev systems. If you do know, than you know you shouldn't be accessing any system except your easily re-buildable dev systems.

share|improve this answer
add comment

Developers should have full access to dev databases (ideally they should be running a local server, but that's not always possible). They should have access to the build/QA database, but only to the data (should have to get permission/submit a ticket to change the structure). Developers should never have casual access to the production database (unless it's a small company/project and developers also do production support).

share|improve this answer
add comment

I think the key here is what responsibility level the developers have. In a large organization with lots of developers, they will likely only have access to development with read only access to QA/Test.

However, there should be people on the development team with full access to all environments. This is usually the person responsible for making fixes etc. While this is somewhat risky it's a tradeoff between how much you trust the developer, how quickly you want things fixed, and the risks involved in messing up the system or disclosure of information in the system.

I worked in a large IT shop and we did have read access to production for most people. I as the lead developer also had permissions to the production database. I still had to follow the same guidelines as the sysadmins and DBAs in terms of process and paperwork, but I could also make an urgent fix if needed.

share|improve this answer
add comment

There's a related issue that most of us forget - we may not be the only people using the database! We tend to take this for granted but shouldn't. Even small sites might have the business people running third-party tools against the database for their reports. Enterprise sites will almost always have multiple users of the database tables and your 'small' change might break their applications and vice versa.

So this has to be the first question. The second has to be the staff available - I've worked at sites that had sysadmins who were protective of their turf but really weren't that good. (Which is probably why they were so territorial - they knew they would catch a lot of heat if someone looked around.) Sometimes you have to have more access than you would like.

But in an ideal world I agree with the points made by other people. I don't want access to live data since, frankly, I don't want the responsibility. Think about it - if I have operational access and there's a breach then I'll have to spend a lot of time proving that I didn't have anything to do with it. I might have to show that I haven't snarfed the data for personal reasons, etc. A lot of sites take privacy very seriously, esp. government sites.

I don't even want write/admin access to the test group's system. If I can't do something on the production system then I don't want to be able to do it on the test systems. Read access is the exception since it's necessary to help figure out what's going on.

Development systems, both individual and departmental, are a different story. But even here, in practice, it's usually best to run all database changes through a point person instead of having everyone do their own thing.

share|improve this answer
add comment

I totally agree with all the answer, saying "as least privileges as possible for developers on prod databases". But to be honest, who can deny developers the access to the database if they want to get it. With enough will (be it criminal or for good) they will get the needed access.

Who can hold them back from putting a simple SQL editor into the application? This way they can use the database with the privileges of the application. In most cases this is all that is needed. When the database is configured securely, they might not have the privilege to create or delete objects, but they have at least read and write access to the data.

I already here the ops people cry. But be honest to yourself, you are not in complete control of the application deployed in production, unless you write it yourself (and not even then, think about libraries). And to all the developers, as Erin already said, you are also responsible for the production environment, not only the ops people. So use your privileges wisely.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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