Should developers be able to query production databases?

Question

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

Answer 1

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.

Answer 2

No.

Developers should not have access to production database systems for the following reasons:

1. Availability and Performance: Having read-only rights to a database is not harmless. A poorly written query can:

   1. Lock tables, blocking other critical processes.
   2. Trash your data cache, forcing other processes to re-read data from disk.
   3. Tax your storage layer, impacting other services that share that storage.

2. Security: Your production database may contain sensitive information like:

   • password hashes
   • billing information
   • other personally identifiable information

   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:

1. 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.
2. Administrators, who deploy, monitor, and manage the databases in production.
3. Support people, who investigate time-sensitive production problems and provide feedback to the developers so they can develop fixes.
4. 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.

Answer 3

Performance would be a BIG reason.

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.

Answer 4

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.

Answer 5

I think the answer is, like with many things IT, "it depends".

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.

Answer 6

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.

I've seen many reasons for this:

• SELECT * from a big table leading to:

  • performance issues (cartesian products);
  • blocking issues that eventually brought the site to it's knees;
  • blocking chain that put the replication to a hang;
  • ordering big set of data that filled TempDB drive which ..guess what? Caused complete madness :-)!
  • high blood pressure for the DBA in charge of production for that night;

• reading of sensitive data (a developer shouldn't have access credit card info..or any user personal details);

I'm sure there are even more reasons.

Answer 7

• Security: There might be sensitive information that is sanitized when they make it available to developers.
• Paranoia: Some might think you could still mess up data with just select access.
• Performance: A query takes some resources to perform, and you can't tell me your developers are perfect when they write code.

Answer 8

Couple of items to consider

• Is the data sensitive?
• Are the programmers part of a core trusted team or some offshore team?
• What is the scale of the data being queried upon in terms of impacting performance?
• What is the scale of the project or dollars involved?
• How critical is the uptime?

Smaller dollars needs less process needs quicker flow of development.

Bigger dollars needs more process needs stricter standards of development practices.

Adapt your practices to what you are doing.

Answer 9

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.

1. 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.)
2. 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)
3. 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.)
4. 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.

Answer 10

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.

Answer 11

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.

Answer 12

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.

Data is the most valuable part of most applications!

Answer 13

Performance might be one of the reasons.

Developer queries can often be inefficient, causing excessive locking or resource usage until they are properly tuned.

A production system is not a suitable place for developers to experiment.

Answer 14

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.

Answer 15

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.

This is just one of the things we provide. Check us out at http://www.Stackify.com to learn more about our DevOps support solutions.

Answer 16

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.

Answer 17

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.

Answer 18

No..Never!!

This is why we have development/Test/UAT servers. The data from Production can be copied onto the test environment and the developers can go ahead with their testing. Select queries can be very harmful as well in case of a Production environment. It increases the load and at peak time can bring down the entire performance.

Any information they need should go via the DBA who can run what they want to ( Select) and send them the results. That's how we do in our environment.

Answer 19

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.