Answers: <p>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.</p>
  
  <p>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</p>
  
Answers: CC BY-SA 4.0
Answers: 2019-02-18T18:32:29.563Z
Answers: 2019-02-18T18:32:29.563Z
Answers: jdog
Answers: <p>I see a first problem, DevOps is about building teams able to handle an application from build to exploitation.<br>
  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.</p>
  
  <p>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.</p>
  
  <p>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 <a href="https://dev.mysql.com/doc/refman/5.5/en/audit-log.html" rel="nofollow noreferrer">audit plugin</a> which may answer the problem of letting your devs access the database and fulfill your CISO requirements of keeping track of what is done.</p>
  
  <p>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.</p>
  
  <p>There's one commercial solution that I know which can help (and allow auditing more than just DB requests) which is <a href="https://strongdm.com/docs/architecture/" rel="nofollow noreferrer">strongDM</a>, 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.</p>
  
Answers: CC BY-SA 4.0
Answers: 2019-02-20T13:28:51.107Z
Answers: 2019-02-20T13:28:51.107Z
Answers: Tensibai
Answers: <blockquote>
    <p>... me and another coworker ... are the only people who have access to the production database.</p>
  </blockquote>
  
  <p>That's a good starting position.<br>
  All too often, DBA's find themselves trying to close the stable door after the horse has run off. </p>
  
  <blockquote>
    <p>So when any other developers need to execute a MySQL query on the production database ... </p>
  </blockquote>
  
  <p>Question:<br>
  Why are Developers running <em>anything</em> against the Production database? </p>
  
  <blockquote>
    <p>How do you control the permissions to the production environment in your company?</p>
  </blockquote>
  
  <p>Role-Based access control. </p>
  
  <p>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. </p>
  
  <p>Developers should <strong>never</strong> have "hands-on", update access outside of their Development databases.  <em>Everything</em> else should be scripted, tested, audited and released through pre-prepared, controlled (and, preferably, automated) channels.</p>
  
Answers: CC BY-SA 4.0
Answers: 2021-05-19T21:42:44.257Z
Answers: Good point about role-based access, and the key takeaway is that privileges should escalate according to need. Querying non sensitive data is fine; running an update or schema change in prod is not.
Answers: Stuart Ainsworth
Answers: CC BY-SA 4.0
Answers: 2019-02-20T11:43:26.070Z
Answers: 2019-02-20T11:43:26.070Z
Answers: Phill  W.
Answers: <p>I've ran into similar issues at other companies and we always had some home grown solution.</p>
  <p>So I've built Kviklet as a generic open-source tool now to manage this better and some first companies are using it, so if someone finds this via google, have a look: <a href="https://github.com/kviklet/kviklet" rel="nofollow noreferrer">https://github.com/kviklet/kviklet</a>.</p>
  <p>The general idea is that you have a portal for production access, submit requests (mostly supports SQL databases so far so the request contains an SQL statement or multiple) and another person approves it. Think Code Reviews/Pull Requests for production access.</p>
  <p>So for anything that can't be solved via running a migration or a good BI setup (which should be the preferred way), this tool can be used to enforce a 4-eyes principle or provide temporary access to one person in a rather simple way.</p>
  <blockquote>
  <p>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.</p>
  </blockquote>
  <p>If you trust your teams enough you can let them peer-review each other, if not you can still review the requests yourself.</p>
  <blockquote>
  <p>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.</p>
  </blockquote>
  <p>For this btw I highly suggest building specific tooling so that at least 90% of the use-cases are covered, there will always be some situations that need direct DB access, but try to keep it as low as possible. Anything automated has a much lower chance of going wrong.</p>
  
Answers: CC BY-SA 4.0
Answers: 2024-05-05T12:24:50.517Z
Answers: 2024-05-05T12:24:50.517Z
Answers: Jascha Beste
Answers: <p>If your question is how to manage database changes consider something like <a href="https://flyway.dborg" rel="noreferrer">Flyway</a>. This lets you control your changes via tracked config files in your repository and apply them via an automated &amp; controlled process - use your normal code review &amp; promotion steps.</p>
  
  <p>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 &amp; restricts them from seeing any sensitive tables. YMMV depending on the layout of your DB. </p>
  
Answers: CC BY-SA 4.0
Answers: 2019-02-20T02:18:34.733Z
Answers: My question is about `how do I get devs to stop bugging me to run arbitrary SQL commands`. I think I can use [ProxySQL](https://proxysql.com/) to mask sensitive customer data so that other developers can read the production database.
Answers: Brian
Answers: CC BY-SA 4.0
Answers: 2019-02-20T13:23:28.310Z
Answers: Are you trying to keep them from *messing it up* or *seeing sensitive data*? The former you can give them RO access with something like mysql-web-ui. The second will require RBAC as stated in Phil W's answer.
Answers: TheFiddlerWins
Answers: CC BY-SA 4.0
Answers: 2019-02-18T20:32:56.997Z
Answers: 2019-02-18T20:32:56.997Z
Answers: TheFiddlerWins
Body: <p>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.</p>
  <p>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.</p>
  <p>Here are the situations when we need to execute commands to the production database:</p>
  <ol>
  <li><p>The database contains corrupted data, which produce bugs. They execute commands to fix the bugs.</p>
  </li>
  <li><p>A bug is reported. And they want to see the current values inside the database.</p>
  </li>
  <li><p>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.</p>
  </li>
  <li><p>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.</p>
  </li>
  </ol>
  <p>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.</p>
  <p>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?</p>
  <p>Our production database consists of sensitive customer information. If the data were leaked out, our company might be fined for millions of dollars.</p>
  
Tags: <permissions>
Title: How do you limit access to the production environment in the company you work for?
UserDisplayName: Brian
ThreadText: # How do you limit access to the production environment in the company you work for?
  
  (Asked by: username_0 on 2019-02-18)
  
  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.
  
  **Tags:** permissions
  
  
  ---
  
  ## 5 answers
  
  ### Answer by username_6 on 2019-02-18. Score: 5 | Accepted answer
  
  If your question is how to manage database changes consider something like [Flyway](https://flyway.dborg). 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.
  
  > **Comments:**
  > **username_0**: My question is about `how do I get devs to stop bugging me to run arbitrary SQL commands`. I think I can use [ProxySQL](https://proxysql.com/) to mask sensitive customer data so that other developers can read the production database.  
  > **username_6**: Are you trying to keep them from *messing it up* or *seeing sensitive data*? The former you can give them RO access with something like mysql-web-ui. The second will require RBAC as stated in Phil W's answer.  
  
  ---
  
  ### Answer by username_1 on 2019-02-18. Score: 4
  
  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 by username_2 on 2019-02-20. Score: 4
  
  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](https://dev.mysql.com/doc/refman/5.5/en/audit-log.html) 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](https://strongdm.com/docs/architecture/), 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 by username_3 on 2019-02-20. Score: 1
  
  > ... 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.
  
  > **Comments:**
  > **username_4**: Good point about role-based access, and the key takeaway is that privileges should escalate according to need. Querying non sensitive data is fine; running an update or schema change in prod is not.  
  
  ---
  
  ### Answer by username_5 on 2024-05-05. Score: 0
  
  I've ran into similar issues at other companies and we always had some home grown solution.
  
  So I've built Kviklet as a generic open-source tool now to manage this better and some first companies are using it, so if someone finds this via google, have a look: <https://github.com/kviklet/kviklet>.
  
  The general idea is that you have a portal for production access, submit requests (mostly supports SQL databases so far so the request contains an SQL statement or multiple) and another person approves it. Think Code Reviews/Pull Requests for production access.
  
  So for anything that can't be solved via running a migration or a good BI setup (which should be the preferred way), this tool can be used to enforce a 4-eyes principle or provide temporary access to one person in a rather simple way.
  
  > 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.
  
  If you trust your teams enough you can let them peer-review each other, if not you can still review the requests yourself.
  
  > 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.
  
  For this btw I highly suggest building specific tooling so that at least 90% of the use-cases are covered, there will always be some situations that need direct DB access, but try to keep it as low as possible. Anything automated has a much lower chance of going wrong.
  
  
  ---
  
  