Tonie Huizer

16 September 2024

• Flyway
• CI pipelines

The Importance of Access Checks and Controls in Database Development

The incident

During routine monitoring of the deployment environments, it was discovered that databases related to a local Flyway development project had been incorrectly installed into a test server at a remote site, which was managed for a different customer. The first-response team assumed that an automated Flyway deployment process had been executed on a server to which it shouldn’t have had access, so they raised a Security incident, and the IT team were called in to investigate.

We soon determined that, in fact, a developer had manually run a setup script on the wrong server. The incident had nothing to do with Flyway itself and could have happened in any other type of database development project, in similar circumstances. We recommended that the organization should pin down access permissions, for both developers and automated Flyway processes, to prevent recurrence, and set about establishing a plan to achieve this. While the measures we recommended are broadly applicable to database development for any RDBMS, the proposed implementation examples in this article are for SQL Server.

What went wrong?

All developers have legitimate admin-level access to their local dev servers. However, one developer also had authorized access to a remote test server, through a different login. Unfortunately, this login also had admin permissions which meant he was able to accidentally create databases on the remote test server. As part of the initial setup the developer ran a script to create a Flyway development environment, forgetting that he was still connected to the remote test server through his alternate login.

The development team follow a DevOps process that I’ve described in detail elsewhere. It automatically provisions dedicated development databases, using SQL Clone, whenever a developer creates a branch in Git and links it to a backlog task (2). The team use Flyway to track their development changes and generate migration scripts (3). These migration scripts are reviewed before entering an automated Azure DevOps Delivery Pipeline for testing and deployment (4).

The whole process is largely automated, but there is still a one-time manual setup required in the database provisioning step (2), where each new developer must run a script to set up their shadow database for the Flyway Desktop projects (required for migration script generation and validation). It was these shadow databases that were discovered in the remote test environment.

The initial response was to delete the rogue databases, but the core issue to be fixed was lax access control mechanisms, which gave more permissions for the role the developer performed on the remote test server than were necessary. However, we also saw opportunities for other process improvements to prevent any possible recurrence of this issue.

Preventative Measures and Improvements

Firstly, and fundamentally, while the developer had legitimate access to the remote test server, there was a failure to follow Principle of Least Privilege (PoLP). This states that users, programs, or processes should have only the minimal level of access — or permissions — necessary to perform their tasks. Applying this principle will reduce the risk of accidental or malicious damage by limiting the potential impact of a security breach.

Secondly, if the process step being performed manually by the developer had been automated, the human error could have been avoided in the first place.

Finally, as a first line of defense, the developer could have received some clear indication in their database IDE that they had accidentally connected to the wrong server. This did not happen.

Restrict developer-role access to non-development environments

The developer did not require Admin rights on the remote test server but was using a login that had them and so was able to create databases on the remote test server. Developers should only have access to non-development environments when it is essential to their role and should be using credentials appropriate to that role. To enforce this, the following measures would be implemented:

1. Identify the Separate Roles in the development, testing and deployment process, if this is not yet clear. Then create separate security roles, each with their own access logins and credentials
2. Limit Role-based Permissions: use these role-based access controls (RBAC) to limit permissions based on the user’s role. Developers should have admin access to development environments only.
3. Controlled Deployment Operations: Review and validate access controls for production environments (acceptance, staging, and production). Verify they are strictly controlled and can be performed only by authorized personnel using the correct role for the task or, preferably, through automated processes to reduce the risk of human error.
4. Enforce Process Compliance: Establish clear access control guidelines that define who can perform what role, and regularly audit these access controls to ensure compliance and prevent any unauthorized actions.

Automate manual steps in setting up development environments

We recommended automating the entire development environment provisioning process, including the deployment of Flyway Desktop’s shadow databases, which will be spun up on-demand alongside the other databases on the personal instance. This will prevent manual errors during database setup, and by ensuring the process can only run on development environments, we remove the possibility of accidental execution on the wrong environment. Plus, it makes the onboarding of new developers easier, faster and in-line with the process.

Since all environments ran SQL Server, we proposed using SQL Clone to automate provisioning of the entire database environment, including shadow databases.

• Automated Environment Setup: During the creation of a new development environment, shadow databases can be automatically cloned from a master image. This idea is not new, Chris Unwin wrote about it before.
• Efficient Housekeeping: In our SQL Clone implementation, we run automatic cleanup of old or unused clones. By integrating this with the housekeeping process for other databases, unused shadow databases will be removed automatically once they are no longer needed.
• Faster Provisioning: Since SQL Clone leverages disk virtualization, provisioning new shadow databases takes only a few seconds and consumes minimal storage, making it ideal for environments that require frequent resets.

By incorporating SQL Clone into the CI/CD pipeline, shadow databases can be generated, maintained, and cleaned up automatically, ensuring a more reliable and streamlined deployment process. For more details on provisioning multiple database environments, see this guide.

Indicate the connected environment in the database development IDE

Since all developers use SQL Server Management Studio (SSMS) as their database IDE, we also saw an opportunity to implement SQL Prompt tab coloring as a ‘first line of defense’. SQL Prompt tab coloring is a feature that can visually distinguish between different database environments in SSMS. You assign a tab color to servers and databases in each environment. When connecting to an environment from SSMS, the tab color provides immediate visual confirmation that you’re connected to the intended environment, or serve as a warning otherwise!

The plan was to:

• Assign tab colors to all existing databases and servers in the local development and deployment environments
• Set up a default “Undefined” environment for all other servers, including remote servers, to avoid any misconfigured connections
• Ensure that the color schemes are documented and communicated to all team members.

Notice that any server or database not assigned to a environment in SQL Prompt falls into the “Undefined” category and will appear in a distinct yellow color. This acts as a safeguard for new servers as well as remote or unfamiliar servers. It signals that the developer may be connected to the wrong environment or that a new environment is not properly categorized, and they should proceed with caution.

Conclusion

Errors like the one experienced are preventable with the right measures in place. By restricting access permission using Principle of Least Privilege, revising the DevOps process, automating database management tasks, and using visual cues in their development IDEs, organizations can significantly reduce the risk of similar incidents.

Implementing these improvements will not only enhance the reliability of the deployment process but also foster a culture of diligence and accountability within the development team. While mistakes are inevitable, it’s crucial to learn from them and improve processes to prevent future recurrence.