My DBA is Sick – Database User Management Tips for SysAdmins

User management is one of the day-to-day tasks that are performed by database administrators. If your database administrator is not available and you have been appointed to perform his tasks, you may have been asked to fill and perform user management tasks as well. Let’s take a look at what is the most important when managing access to your database.

The details will differ depending on the scale of your environment. Larger the database infrastructure, more likely user management is automated to some extent. Ansible playbooks that run and ensure all of the required users are available and have correct privileges, scripts that create or modify users across the whole infrastructure. On the other hand, in smaller setups, it is more likely that you will be making changes by hand, logging into databases and executing direct statements that will create or alter existing users. No matter how you will achieve that, there are a couple of best practices you probably would like to stick to.

Make Sure All Users Have a Proper Password

Users without a password are a security threat, that’s no-brainer. What you want is a password that is decent – simple passwords like ‘1234qwerty’ or ‘pa$$word’ will not do. Some of the database systems can assist you in enforcing proper passwords. For example, MySQL can be configured to enforce password strength. You can configure exact requirements in terms of the password length, existence of lowercase or uppercase characters, special characters and so on. You can even require a dictionary search to ensure password is not a common word. In MySQL world, if you are not the only person who has access to creating new users, you may want to run regular checks using pt-show-grants, a tool that dumps the users. It allows you to easily track users without passwords.

Make Sure All Users Have Only Those Privileges That They Require

Privileges are designed to limit the access to the database on per-user (per-database, per-table or even per-column basis, all depends on the database system in question) basis. It is quite important to use it to your advantage. If you have to create a user that has to have access to a subset of data, do not grant him access to all of it. If a user has to execute SELECT queries, there’s no reason to grant him write access to the data. This is very important to keep in mind that no matter how secure your database is, if someone gains access to the web server, he’ll be able to access database credentials used by the application. If your regular pattern is to grant too many privileges, this will seriously affect the security of your database. Again, just like in the previous section, pt-show-grants will be useful to track the users created in your MySQL database and make sure that they do not have extensive privileges.

Don’t Ever Use Superuser for Applications to Connect To

This is sort of similar to what we discussed in the previous section. Superuser does have extensive privileges and it will become a security threat when used in the application. There is more to this than just a security, though. Please keep in mind that the database is configured to accept a limited number of connections from the application. This is quite important to keep in mind that if you ran out of available connections, you won’t be able to connect to the database. Quite straightforward, isn’t it? Yet there’s a feature in MySQL that allows for one connection from the superuser even if all of the connections, as per limits, are used. This allows the administrator to make a connection and change the configuration even if the application cannot connect to the database. Using superuser by the application makes this feature pointless – application will use all available connections and that one additional slot for the superuser, leaving you without easy ways to connect to the database and reconfigure it.

Make Sure Users Are Limited to a Required Host

When creating a user you should think where that user connects from. Ideally, you would be able to identify a host or a subnet used by that user for database connections. This will allow you to reduce a risk that a given application user would be used to access the database from an unauthorized location. If you create a user account dedicated to the application access to the database, it probably should not attempt to access your database from some other location like a bastion host or a backup server.

How to Use ClusterControl for Database User Management?

 ClusterControl comes with a built-in user management interface.

As you can see above, you can easily see the users that have been created in your system. You can as well create more of them:

If you need, you can easily edit the existing user:

It is also possible to check the inactive users.

If a user stays inactive, you may want to revoke its privileges to remove unnecessary access paths to the database.

User management plays an important role in database security, we hope this short blog will help you to understand better its most important aspects.