Should developers be able to query production databases?

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.

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.

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.