Home » Should developers be able to query production databases?

Should developers be able to query production databases?

Solutons:


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.

Related Solutions

What is the “You have new mail” message in Linux/UNIX?

Where is this mail? It's likely to be in the spool file: /var/mail/$USER or /var/spool/mail/$USER are the most common locations on Linux and BSD. (Other locations are possible – check if $MAIL is set – but by default, the system only informs you about...

How can I find the implementations of Linux kernel system calls?

System calls aren't handled like regular function calls. It takes special code to make the transition from user space to kernel space, basically a bit of inline assembly code injected into your program at the call site. The kernel side code that "catches" the...

Is a composite index also good for queries on the first field?

It certainly is. We discussed that in great detail under this related question: Working of indexes in PostgreSQL Space is allocated in multiples of MAXALIGN, which is typically 8 bytes on a 64-bit OS or (much less common) 4 bytes on a 32-bit OS. If you are not...

Explaining computational complexity theory

Hoooo, doctoral comp flashback. Okay, here goes. We start with the idea of a decision problem, a problem for which an algorithm can always answer "yes" or "no." We also need the idea of two models of computer (Turing machine, really): deterministic and...

Building a multi-level menu for umbraco

First off, no need pass the a parent parameter around. The context will transport this information. Here is the XSL stylesheet that should solve your problem: <!-- update this variable on how deep your menu should be --> <xsl:variable...

How to generate a random string?

My favorite way to do it is by using /dev/urandom together with tr to delete unwanted characters. For instance, to get only digits and letters: tr -dc A-Za-z0-9 </dev/urandom | head -c 13 ; echo '' Alternatively, to include more characters from the OWASP...

How to copy a file from a remote server to a local machine?

The syntax for scp is: If you are on the computer from which you want to send file to a remote computer: scp /file/to/send username@remote:/where/to/put Here the remote can be a FQDN or an IP address. On the other hand if you are on the computer wanting to...

What is the difference between curl and wget?

The main differences are: wget's major strong side compared to curl is its ability to download recursively. wget is command line only. There's no lib or anything, but curl's features are powered by libcurl. curl supports FTP, FTPS, HTTP, HTTPS, SCP, SFTP, TFTP,...

Using ‘sed’ to find and replace [duplicate]

sed is the stream editor, in that you can use | (pipe) to send standard streams (STDIN and STDOUT specifically) through sed and alter them programmatically on the fly, making it a handy tool in the Unix philosophy tradition; but can edit files directly, too,...

How do I loop through only directories in bash?

You can specify a slash at the end to match only directories: for d in */ ; do echo "$d" done If you want to exclude symlinks, use a test to continue the loop if the current entry is a link. You need to remove the trailing slash from the name in order for -L to...

How to clear journalctl

The self maintenance method is to vacuum the logs by size or time. Retain only the past two days: journalctl --vacuum-time=2d Retain only the past 500 MB: journalctl --vacuum-size=500M man journalctl for more information. You don't typically clear the journal...

How can I run a command which will survive terminal close?

One of the following 2 should work: $ nohup redshift & or $ redshift & $ disown See the following for a bit more information on how this works: man nohup help disown Difference between nohup, disown and & (be sure to read the comments too) If your...

Get exit status of process that’s piped to another

bash and zsh have an array variable that holds the exit status of each element (command) of the last pipeline executed by the shell. If you are using bash, the array is called PIPESTATUS (case matters!) and the array indicies start at zero: $ false | true $...

Execute vs Read bit. How do directory permissions in Linux work?

When applying permissions to directories on Linux, the permission bits have different meanings than on regular files. The read bit (r) allows the affected user to list the files within the directory The write bit (w) allows the affected user to create, rename,...

What are the pros and cons of Vim and Emacs? [closed]

I use both, although if I had to choose one, I know which one I would pick. Still, I'll try to make an objective comparison on a few issues. Available everywhere? If you're a professional system administrator who works with Unix systems, or a power user on...

How do I use pushd and popd commands?

pushd, popd, and dirs are shell builtins which allow you manipulate the directory stack. This can be used to change directories but return to the directory from which you came. For example start up with the following directories: $ pwd /home/saml/somedir $ ls...