How much database access should developers have?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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

Answer 7

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.

Answer 8

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.

Answer 9

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.