How do you limit access to the production environment in the company you work for?

Question

In the company I work for, devops engineers (currently only 2 members, who are me and another coworker) are the only people who have access to the production database.

So when any other developers need to execute a MySQL query on the production database. They would send the query to the 2 engineers to let them execute it.

Here are the situations when we need to execute commands to the production database:

1. The database contains corrupted data, which produce bugs. They execute commands to fix the bugs.

2. A bug is reported. And they want to see the current values inside the database.

3. One of our customers wants to modify his/her data. But our web application doesn't have the ability to do the modification. So we have to send MySQL commands directly to the database to complete the customer's requirement.

4. The QA team created test accounts on the production environment. And they want to change the account's status so that they can do other tests.

This creates a lot of interruptions for me and the other coworker. When we develop programs during the daytime, we often have to switch context just to execute some queries.

I don't think this is a good architecture for the company. How do you control the permissions to the production environment in your company?

Our production database consists of sensitive customer information. If the data were leaked out, our company might be fined for millions of dollars.

Answer 1

If your question is how to manage database changes consider something like Flyway. This lets you control your changes via tracked config files in your repository and apply them via an automated & controlled process - use your normal code review & promotion steps.

If the question is "how do I get devs to stop bugging me to run arbitrary SQL commands" then you may want to consider scripting out a way to automate it or giving them a 3rd party UI to use with an account that's locked down to prevent changes & restricts them from seeing any sensitive tables. YMMV depending on the layout of your DB.

Answer 2

You could embed database schema and data changes into source code control by using a concept called database migrations. These can then be executed on dev and staging environments as part of a partially automated deployment process.

For example in my environment (PHP Web application), I am using Doctrine Schema for schema updates, Yii2 migrations for data changes. The respective commands are part of a 7 line bash script that runs all necessary commands to deploy a change in each environment

Answer 3

I see a first problem, DevOps is about building teams able to handle an application from build to exploitation.
So your devs should have access to the databases, you quoted various cases which are the reality for a lot of people and the major drawback which is turning you and your colleague in a bottleneck as well as impeding your own work.

Other answers address well the schema change or planned changed which should indeed be integrated as part as the application delivery process, but they don't allow to quickly fix the live access need, when a dev may need to dump the DB to understand what caused the bug and how to fix it for exemple.

Things like ProxySQL you already quoted in a comment could be ok for MySQL databases, just configuring MySQL to log things could be a good approach also, MySQL offers a commercial audit plugin which may answer the problem of letting your devs access the database and fulfill your CISO requirements of keeping track of what is done.

If you have more than just Mysql DB and need to audit their access, configuring each system to audit log users actions and not application actions could be cumbersome. Keeping the things closed could be even worse, one dev will one day integrate a DB shell in an application to circumvent this road block and it will eventually go live in production without proper access control and expose all datas, I strongly advise you to ask your company to review this policy.

There's one commercial solution that I know which can help (and allow auditing more than just DB requests) which is strongDM, it also allow to audit ssh and rdp sessions, as if your devs needs access to DB, they probably also need access to the machines hosting the applications for debugging purposes.

Answer 4

... me and another coworker ... are the only people who have access to the production database.

That's a good starting position.
All too often, DBA's find themselves trying to close the stable door after the horse has run off.

So when any other developers need to execute a MySQL query on the production database ...

Question:
Why are Developers running anything against the Production database?

How do you control the permissions to the production environment in your company?

Role-Based access control.

Users are given access to each database as and when their job role requires it and Roles are used to give them access to the tables within each database. The process by which these accounts are created and roles granted is centrally-managed and strictly audited.

Developers should never have "hands-on", update access outside of their Development databases. Everything else should be scripted, tested, audited and released through pre-prepared, controlled (and, preferably, automated) channels.