Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the development team can't even connect to the production instance.

One of the test instances is a copy of production restored from a production backup once a week, so there aren't any problems with developers actually seeing the data.

What good reasons are there for not allowing developers to query production (except for simply not wanting them to have access to read sensitive data)?

@gbn, can you provide a reference to what those 2 terms mean?
–
LarsHJan 6 '12 at 21:31

10

@LarsH: First line support is typically designed to resolve 90+% of all problems with fundamental basic checks: right URL, username, password, clicked on the OK button, etc. Second line support is provided by advanced, more experienced staff which could take research, experimentation, and/or various advanced personal communication strategies, etc. Break glass access refers to fire alarm trigger controls of the 1950s+ where one must break glass to activate. Presumably a serious problem exists if a regular person is willing to break glass to hit the switch.
–
wallykJan 6 '12 at 23:03

18 Answers
18

It really depends on whether the developer has any support responsibilities. If they are on the hook for third line support then they will probably need to look at the production database to do this.

Generally it's a bad idea to do anything on a production server unless it's really necessary to do it there.

For most development purposes, mirrors or snapshots of the production database will be adequate, and probably better than the live production database. If you are doing anything involving integration then you will want stable database environments where you can control what's in them. Anything involving reconciliation will also need the ability to look at a controlled point in time.

If the problem is that you don't have production mirror environments or any means to put a copy of production data somewhere for your developers then this is a somewhat different question. In that case your developers really need at least one mirror environment. If you can't see what the problem is in the data then it's kind of hard to troubleshoot it.

+1 for Generally it's a bad idea to do anything on a production server unless it's really necessary to do it there. The burden of proof (so to speak) should be on justifying granting access, no justifying denying it.
–
JNK♦Jan 6 '12 at 16:29

Yep, the only things I do in the production database are exporting data and fixing support stuff.
–
Ben BrockaJan 6 '12 at 17:44

4

Well said. A developer that also does support probably needs it. Sometimes it also makes sense to give a developer enough permission to make their own snapshot. But a developr touch production should be the exception, not the rule.
–
TimothyAWisemanJan 6 '12 at 17:55

As a developer and sysadmin both, I concur. When I develop, I need to make a realistic data set that has live data I can test against, so I replicate a subset into a development database. This assures that "actual data" can be used against new code, possibly exposing issues I didn't think of. As an admin that has had to do the occasional "cleanup" or "move-around" of data, touching the production system should only be done when it's warranted, not desired. I'm only in there when there's no other way around a problem.
–
Avery PayneSep 13 '12 at 23:04

Only those who absolutely need access to this information should have it. In a well-organized company, developers are not among those people. Furthermore, your company will fail PCI and SOX compliance if its developers can access production systems with this data.

The reasons for this are obvious. A developer's development work goes through many hands before it goes live. What's to stop a malicious developer with direct production access from stealing your production data or bringing your live database to its knees?

"But that goes for the DBAs too! They could do that!" Exactly. You want as few superusers as is responsibly possible.

Yes.

Developers should have access to production systems.

At my company we have four teams that deal with production databases. They are:

Developers, who design and write the schema and code for the databases. They have no access to the databases in production. They do, though, sometimes sit with the Administrators or Support people and help them look at something in live.

Administrators, who deploy, monitor, and manage the databases in production.

Support people, who investigate time-sensitive production problems and provide feedback to the developers so they can develop fixes.

Business Intelligence people, who extract data from productions databases using either regularly refreshed copies of those databases or carefully written and QA-ed extracts (usually designed by the Administrators).

It's appropriate to grant your developers production access when you have certain deficiencies in these other groups.

For example:

You have no support team. Who's gonna know where to look to debug that time-sensitive production issue? Your developers. Grant them "break the glass" access.

You have no BI team. Your admins don't have or want anything to do with reports or extracts. Who's gonna troubleshoot the report that your execs see every morning? Your developers. Grant them limited access to debug these reports and extracts.

You have no admin team. You're in a very small or startup company, so say hello to the "accidental DBA". Your developers double as your administrators, and thus need full access to production.

Actually I would disagree with the assertion that developers don't need access to production data. If you're working with any integration project then you really need to see the grot in the data in order to identify and deal with data issues. SOX does not require that developers can't see production data - I've worked on SOX regulated projects and this was never a requirement for that in my experience. All that needs to be masked for PCI compliance is banking and credit card details - at least in the UK.
–
ConcernedOfTunbridgeWellsJan 6 '12 at 20:55

1

Having said that, I did apply for a job at one of the Gnomes of Zurich once and swiss banking confidentiality regulations really do prohibit this.
–
ConcernedOfTunbridgeWellsJan 6 '12 at 20:57

5

@ConcernedOfTunbridgeWells - I agree, developers absolutely need access to non-sensitive production data, particularly in integration projects. Developing against real data is invaluable. They don't need, however, access to production systems. That's why many companies provide sanitized copies of production data for their developers to use.
–
Nick ChammasJan 6 '12 at 21:09

1

@ConcernedOfTunbridgeWells - Regarding PCI requirements, I refer you to PCI DSS Requirement 6.4.2: "There is a separation of duties between personnel assigned to the development/test environments and those assigned to the production environment."
–
Nick ChammasJan 13 '12 at 6:41

Just because they can't change the data doesn't mean they can't affect the server. A poorly written query could bring the production environment to its knees, and potentially cause other issues (like tempdb overflows):

SELECT *
FROM BigTable A, OtherBigTable B
ORDER BY Somecolumn

That's a recipe for disaster. Notice that this is a cartesian product with an order by, which means it will be sorted in tempDB.

@user606723 - If you could trust users then why would you need security at all?
–
JNK♦Jan 6 '12 at 17:01

7

@user606723 - I agree you need to trust them some. That doesn't mean they should get the keys to the kingdom. I trust my mom but I wouldn't let her work on my production database.
–
JNK♦Jan 6 '12 at 17:07

2

@user606723: off course there is trust between the teams of the same project, because there's no team without trust. But it's the DBA who spends time to read logs, to fire up Profiler, to check the monitoring tools to see what happened, and that darn beer was so good with his friends :-). See? If the guilty developer will wear for one night the shoes of a DBA, he/she might be more responsible. But I've never seen this happening in my past experience.
–
MarianJan 6 '12 at 17:22

The principle is "least privilege" and "need to know": do developers pass this test?Especially when Auditors or Sarbannes-Oxley come knocking.

Then, my next assumption: developers are stupid. So if they do need say for 3rd line support, who then needs it? Web monkeys typically don't but database types yes if they are expected to support it.

Then, is access needed permanently? They can have "break glass" access using a SQL login or alternate Windows account that requires a sign off. In our case, it was the data owner (some tech savvy business person hopefully) and the IT manager to approve it.

I have seen developers test against or run queries on production and take it down because of ignorance. Saying that, developers should take responsibility for their actions: if they do take a server down, they should suffer accordingly. I got someone demoted after one incident...

These assume a reasonably size shop of course. The more hats folk wear the less separation of duties you can have

Also, is there an environment that developers can run queries against recent data? In my last shop, prod was restored each night to a test server to provide this.

+1 to performance comment. I do not know that all developers know the cost of the queries they write. I'm a developer and I try hard to get it right and have still botched it. Throw in a tool like Hibernate where it is easy to mess things up and it gets even trickier.
–
Chris AldrichJan 6 '12 at 21:38

On an usual 24/7 OLTP environment a normal developer shouldn't be allowed in production. Period! If, from time to time, a particular reason appears, than permissions could be granted upon request. But on a usual basis no.

A massive ERP database with lots of sensitive company and customer information? Probably not (both for security and performance reasons).

A departmental 5 MB database with an Access front-end that tracks contributions to the donut and pizza funds? Not going to make a whole lot of difference, at least for read-only access.

Granted, the first example is much more common than the second, but these are differences you should be aware of if you're in charge of making these types of policy decisions. But on the flip-side, it's amazing how quickly a 5 MB donut-and-pizza-fund database can scope-creep its way to a 50 GB part-numbers/customer-credit-card-numbers/who-knows-what-else database if you let it.

I work as a developer for a very large company. All of our developers that will be doing any sort of support (basically all of them) have access to relevant production databases. I can only speak for my specific team, but I will tell you why we have access.

We need to realtime access to keep an eye on our daily processing. (While we have a dashboard, we need to be able to keep an in-depth eye on things. While it'd be nice to have this feature on our dashboard, we've found that to be unpractical.)

We need realtime access to investigate any production failures because delays can have a huge impact. (I am not going to discuss our failures on here. They come in all sorts)

We often need to do custom reports for business users and this information needs to be up to date. (dba's don't have time to do this and we don't have time to wait for them. non-ideal, for sure though.)

We need to do verification of production DDL/DML deployments/patches. (DBAs deploy them, but only we know how it should be structured. We know more about our database structure than the DBAs. We might be strange here, but out database is very complicated because our business is very complicated.)

Performance is a concern. We do have occurrences of developers causing slowdowns. However, these are isolated instances and our SQL is so performance driven that it's rare our developers don't understand the impact of their queries.

1. Shouldn't that be satisfied by a report or extract that is designed by devs, QA-ed by someone, and then deployed by your DBA? 2. What kind of production failures? 3. You write the report against your development database and your DBA deploys it for you, just like with any other code. 4. Shouldn't your DBA be doing that for you? Who deploys code changes to production? Your developers?
–
Nick ChammasJan 6 '12 at 17:40

2

This doesn't justify prod access. number 4: use tools like red gate to prepare script correctly. 3: use a day old data on non-prod 1. what no reporting or dashboard?
–
gbnJan 6 '12 at 17:41

I had to look up "pithy". Thanks for the compliment as "pithy" is actually a good thing.
–
Jason SebringJan 7 '12 at 5:32

2

I have an issue with the differnce between a core-trusted team and an offshore team. If you don't trust your offshore team to be as competent as your in-house team, then you shouldn't be using them.
–
HLGEMApr 25 '12 at 17:38

You should not trust your offshore team with mission-critical code if you don't own the actual offshore company and have actual direct contact with the managers there that you actually know first hand if you have a project of any scale. Otherwise, you are being naive with your head in the sand waiting to be screwed over.
–
Jason SebringSep 24 '14 at 23:26

For this question to be asked one must presume that they currently do not have access. If one's organization is developing software and this is for troubleshooting a customer issue and the customer supplies a copy of their database, then 'yes'. Otherwise I would advocate keeping developers out of production and have alternate enivironments created for their research needs. Once the toothpaste is out of the tube, it is hard put it back in.

Depends what "production system" means for you. Our "production system" is a research platform in active development, in contrast to the "development system" that we use for creating new features on that platform.
–
Lightness Races in OrbitJan 7 '12 at 15:25

It depends to the DBA and how he or she is confident with the developer. Usually developers are given query (read) privileges to the production databases. As a rule of thumb, developers should only work with test/dev databases.

I would only trust a lead developer with that kind of privilege. That way, if anyone shoots themselves in the foot, you can blame one person because there should only be one foot with a bullethole in it. All other developers in test/dev databases should be a rule of thumbs, fingers, and toes. +1 !!!
–
RolandoMySQLDBAJan 6 '12 at 17:42

I agree that the burden of justification should be on the ones requiring access. Typically in environments where I have consulted, I have had access to production systems where it was a small environment and I was the support person. I have had access to backups, etc. where I was support for the support, and indirect access (through a dedicated support developer) to production data.

The big thing is: You need this access when you are on the hook for keeping everything running smoothly and you have to answer the finance guy's question about something not working. You can't always work from even day-old data in that case. On the other hand, the more access the worse it is. Typically as a consultant I tend to avoid getting this sort of access unless it is needed. Since I am working on financial databases, the last thing I want is to be accused of entering my own invoices :-D.

On the other hand, if you don't need access you shouldn't have it. I don't really buy the sensitive data argument since the developer is probably on the hook for making sure this is handled correctly (and it is very hard to verify without looking at what has actually been stored when a bug report comes in). If you can't trust the developer to look at the data the developer's app is storing, you shouldn't hire the developer to write the app. There are too many ways the developer could obfuscate the data and email it away and you can never be sure. MAC controls help here but they are still pretty complex to implement.

The big issue from my side has to do with write access. If a developer has no access then, a fortiori, the developer has no write access. If you want to verify the integrity of the books, you want to keep write access to as few people as you can. Audit trails are far easier to validate if the developers have no access. If the developer has read access, then you always have some question as to whether there has been some privilege escallation attach that can give write access (maybe in-stored-procedure SQL injection?). I have often had full access to client billing info when I have had access to staging environments. If there is a staging environment that works though, I will usually actively ask not to have access to production unless it is necessary.

So this isn't perfect, of course. A developer could still build back-doors into the application that may not be readily detectable, but this approach is a reasonable approach, given the fact that backup data is available from a day prior it seems to me that this is the concern they have.

Hope this helps.

Edit: Just adding that on the larger environments I have worked in, I have had access to full backup data often ranging from a few days old to a few months old for the finance system. This has always been good enough for my work and the only times it has broken down have been when the finance guys needed an ability to test with newer data so they could match against production.

Not having access is a good thing and a way to protect developers and others from not accidentally corrupting the data or viewing it. This also protects companies from breaking the law (i.e. Hipaa violations and privacy concerns)

A developer never really need access to a production environment it is just easier from the developers view point if a tough bug cannot be reproduced.

However, a developer can put in mini-dumps or log files and use the PDB symbol files to re-create the bug.

If the data does need to be brought down to a test environment then it is typical for some kind of process to scrub the data which can create extra work.

Depending on the database software that is used in what you are calling production a new licence could be required for the developer to access the database, which is a large expense to simply have read access.

If your company is not providing you with the tools to debug or research production issues it is not because you do not have access to the production data.

Yes. In some cases, it makes sense to allow some subset of users, including developers some level of access to query production data. However, the proper limitations must be in place for two reasons. First, as a DBA, you must do your best to insure the level of service needed by all users. Also, you want to prevent unintentional bad queries like mass deletions or vilolations of business rules. It should go without saying that proper security controls must be in place.

Whatever the reasons you might have for not allowing ad hoc queries directly to database tables, there can be a case made for allowing queries to views and stored procedures. Using database permissions, you can prevent SELECT queries agains tables directly and even limit which views and stored procedures a given user has access to. Not only does this method give flexibility to your user base, it also protects your data integrity and realiability when implemented correctly.

The challenge is that most software applications are data driven. So when you are trying to fix a problem in the application, you really need to see the data that is driving it. So the developers really need some form of access.

Using SQL logins to only give them read only access to tables is great. BUT, what prevents them from creating a query with 20 joins or doing SELECT * from a table with millions of records? These queries could accidentally kill the performance of your database and storage.

My company Stackify came up with a clever way to solve this. Developers can run the query through our software and we use the query plan to make sure it is just a SELECT statement and that the estimated cost of the query is low and it will return just a few records. This way they can't do much harm. We also audit all the queries they run.

Some might regard this as spam because the intention seems solely to promote your product. OTOH, it is relevant to the question and properly disclosed, so I personally think it is worthwhile.
–
Jack Douglas♦Aug 30 '12 at 7:35

In our company we maintain read-only slaves of production databases that are not relied on by production services. We grant developers access to those for access to production data. If there is sensitive data (Customer Info, payment info, Etc.) we restrict replication of those tables and maintain a sample data table on the slave server.

I'm not sure why everyone assumes developers are stupid and do not know anything. I get a sample of a ton of different roles where they messed up and should not be "in production". I've DBAs, Sys Admins, Network Admins, Developers, etc... all mess up.

No one (dev, dba, sa) has access to any server or database in any environment with there normal network login. They all have specific "admin" accounts that must be used. Yes, typically the dba and sa are using theirs more often, but even they should tread lightly. I 've burned by everyone.

So, on a good day, no IT role needs access. However, the sh!t hits the fan, all hands on deck and we need the right people solving the problem. This usually lead by the developer who knows the application and guide the dba and sa to certain points. It's just the unneeded delay or requesting and approval.

In addition, approval is never followed by any type auditing anyway, so approval means nothing.

Not sure what environments you are speaking about, but in any company that has to adhere to serious regulations such as the higher tier PCI, SOX, SISR, etc. SA level log in and access NEEDS to be logged. In our case, not only do we log it, but we also Splunk it up so no one can edit it after the fact.
–
Ali RazeghiFeb 15 '13 at 22:42