I recently was involved in a discussion that I didn't think I would have in 2021. It was about the setup of database users for a Web application. Come and let's dive into some problems, which we should rectify once and for all!
IT and application security and Data Protection are important in today's world. High fines for non-compliance and huge reputation risks combined with an ever increasing number of cyber attacks requires software development and operation teams to consider their overall setup. One important part is managing database access.
Principle of Least Privilege
The Principle of Least Privilege is a basic principle in IT security. Essentially all parts of the system should only have those permissions which they are required to perform their function. This is a kind of an airbag if things go wrong and you see it implemented in different flavors and colors throughout different domains:
- Do not run services as root/admin
- Partition the network by using firewalls
- Hashing passwords
- Relieving your process of root privileges as soon as possible
- ...
Security (and data protection depending on security) are addressed in multiple layers. If one is breached hopefully the next one holds. Why is this necessary?
Because with our current methods software will have bugs and some of them will lead to security problems!
I have once been told by an admin that when I requested to not give administrative privleges to my application, I should drop my job because I was seemingly unable to perform in my role because I would not trust my application. Such statements demonstrate a serious lack of knowledge and problem awareness because the opposite is true: Because I followed good practices I can be more confident that my application is architected well and this means I did my job well!
The lesson is simple: If you design a system, think about what permissions are really necessary and whether you can compartmentalize your system further to assign less privileges to different parts.
Data Protection
Besides "pure" security, data protection has gained a lot attention in the last years. By latest with the GDPR regulations in 2016 companies had to reconsider handling of data. If I have to be involved in basic discussions about those regulations in 2021 then something, unfortunately, is really wrong; there can be hefty fines for non-compliance. However, this article won't be about data privacy and legal requirements as I am no lawyer. We will focus on basic application security instead.
Design Goals
What can or do we want to achieve from an architectural point of view? For me these are some valuable goals:
- Added layers of security,
- Enforcement of architectural constraints, e.g., for modularization,
- Traceability of database schema changes,
- Traceability of production data changes,
- No strange errors due to "on-the-fly" manual updates.
Minimal Setup
Again I am no lawyer. These would be my guidelines, which I would be confident for basic IT security standards, but it needs individual evaluation in your setting and especially for compliance in data protection regulation:
- An administrative, technical user for deployments (fully automated scripts; scripts versioned in repository, logs of execution)
- One or more technical users (see discussion about Microservices below) with minimal rights (no DROP, CREATE, ... nor special privileges; e.g., check the recommendations by MariaDB on slide 6)
- Developers have their own accounts (no account sharing, especially no access to the deployment account)
- Developers by default have no permissions in PROD environments
- Developers are given SELECT rights on production environments only by request, time-limited, and in a documented manner for support that cannot be done otherwise
- Data updates on production environments are always performed by scripts (also for support) which are documented (e.g., in tickets)
By restricting database access permissions for the application users, impact of security vulnerabilities can be reduced, e.g., if some SQL injection (I dare to write this in 2021) is possible. Also different modules of the application can have different permissions. For example, administrative consoles in the application can be granted write permissions to certain tables while the rest of the application only has read permissions. Also modularization can be enforced by granting permissions only to certain modules/components of the application; thereby enforcing architectural boundaries.
Having full automation of database schema and data updates leads to better traceability because these can be versioned artifacts. Also executing changes only via scripts guarantees that different stages (e.g., DEV, TEST, and PROD database) are in sync if they have the same scripts applied. Manual errors are thereby minimized leading to quicker and more reliable deployments.
Such a concept, no matter how you design it, needs to be documented and some software is required for supporting and enforcing the underlying processes (e.g., Flyway for automatic database update and migration scripts). Especially when shielding developers from data they might require for application support, it is necessary to enable them to perform their job quickly, i.e., have an automated process to assign them the necessary permissions as quickly as possible!
Practical Problems
There are some typical challenges and questions to be addressed and decided in most software systems. Some of those are described in the following sections.
Connection Pools
Connections pools are an essential mechanism to increase application performance. Because database connections are not created every time but are reused from the pool instead, the application can much sooner start to execute database queries because no new database connection needs to be established. However, all connections in a pool must be the same, i.e., they must have the same permissions. In my experience many software designers opt for one pool with access to all tables and sometimes even administrative privileges to cover all use cases of the application. When critical permissions and/or critical data is involved, it might be better to have different pools with different permissions in order to better compartmentalize the application. For example, there might be many read-only use cases or the system is modularized enough to have different pools and permissions. Some Web applications provide an update mechanism, which also requires permissions to change the database schema. However, this is a rarely executed use case that it would be better to only use administrative rights for this use case and not for standard operations.
Essentially: be aware that no one limits you to one connection pool per application although you need to balance the number of database connections, pool efficiency, and data security.
Web Hosting Providers
In my experience many Web hosting providers will offer you a (MySQL) database but only one user with all permissions for that database. In such cases it is impossible to follow basic security measures. Especially with people who do some part-time PHP development, this scenario will likely result in easily exploitable Web applications.
Impact of Microservice Architectures
By enforcing service boundaries with deployment units, microservices also partition the data. Services shall be as independent as possible from other services. Very often so-called read models are created, which are essentially data replications (although possibly involving data aggregation and transformation) to read-only data copies in the dependent microservice. In the figure above microservice A reads data, whose master is microservice B. The table C is replicated to a read-only copy in microservice A.
The available data replication mechanisms are manyfold and address different quality attributes of the software, e.g., event sourcing, database replication, pull feed. However, the result is the same: An independent copy of the data that is to be used read-only by the service.
This read-only copy has many implications: The downside are additional space requirements, data up-to-dateness, and the problem of securing multiple copies. The latter is especially troublesome if access rights to data are complex, e.g., access regulations to Swiss bank customer data is non-trivial to say the least. The upside is that services are more resilient and that the data is protected because even if the database of microservice A is breached, no data in the master table C can be updated. We can further enforce this by partitioning microservice A by giving write permissions to the replica C only to the replication component, and allowing only reads to the main service logic.
Impact of DevOps
DevOps has been an ongoing trend for modern "development and operations". Usually DevOps environments are highly automated with regards to deployments and also database updates and migrations. The main problem is to forbid and restrict developers from accessing production data: Because developers are also administrators, in general they have access to all data. This has to be countered by tooling that enforces support and approval processes, e.g., by "break glass" tools that allow privileged access only on demand and approval.
Helpful Guidelines
There a lots of online resources regarding IT security, Web security and so on. Here are some which you hopefully find helpful:
- Database Security Cheat Sheet by OWASP
- Database Security Threats - MariaDB Security Best Practices by Jonathan Day (MariaDB)
- Improving Database Security in Web-based Environments by Francesco Di Tria, Ezio Lefons and Filippo Tangorra
The Bright Outlook
In preparation of this article, I conducted a LinkedIn survey. Although only 10 people replied, 9 said that it is a bad idea to use administrative accounts for database connections from your Web application. The one differing opinion was by someone with a non-technical role. I know that this is far from any statistical significance due to the small sample size. But it still gives me hope that basic guidelines are followed better today and that hopefully good practice usage will increase in the future!
Conclusions
I hope that this article helped you to either better understand basic IT security problems relating to database access and possitble architectural implications. Perhaps you can send this article to help someone else or your organization to address those or similar problems! I am sure that many software architects had similar discussions in the past. If you have similar experience, I would be happy if you share them - perhaps under one of the social media postings!
If you liked this article, please subscribe below and share it on social media to spread the word!