User management is one of the key tasks around securing your database.

It provides the rules for who can connect, and what they can do after they connect.

Keeping track of who can do what can become tedious, but is necessary for Data Security.

Today I talk about the MySQL 8.0 feature of user roles to help with user management.

The trouble with users

Everyone wants access to your data.

Whether a new app or service is being developed, or the devs want to test some queries on a read replica.

Or the business analyst needs to run some reports.

When someone leaves the company, or the service is dropped, you remove the user to avoid issues later.

As the Database Administrator, it is your job to make sure they can get what they need without putting the data at risk.

So, exercising the Principle of Least Privilege, you grant access to each request.

Historically in MySQL, user management has been a real pain.

You create the user, and you specify the privileges for that user.

Then you repeat for the next user, even if the privileges are the same.

This can lead to mistakes in a user getting the wrong privileges.

Perhaps the user was granted too few permissions on the safer side, and you had to re-issue.

Or perhaps they had too many and you didn’t realize until they accidentally dropped the production table.

MySQL 8.0 introduced User Roles

The good news is that MySQL 8.0 introduced support for user roles.

MySQL 8.0 has been out for a couple of years. And even though I’m late in talking about this feature, I’m still many years ahead of how long it took to get the feature into MySQL!

Because user roles has been a SQL standard for a long time.

User roles allow the database administrator to specify a specific set of grants for each type of access.

For instance, if you have a dev that needs to execute read queries, you might have a ‘read_only’ role on all databases.

Or your microservice application has limited write and read capabilities on a specific schema, but each service has its own database user.

So you have a ‘app_read_write’ role.

And you might have those business analysts that also need read access for their reporting queries, so they get the ‘read_only’ role.

Once the role’s privileges are set, then you can create the users and grant that role to the user rather than the privilege string.

Users can even have multiple roles to allow for more complicated privilege requirements without complicating user grants.

Conclusion

User management can be a tedious operation for many database administrators.

But it is an important function for protecting the data.

For a long time in MySQL, you had to have potentially long privilege strings assigned to each user.

And this was error prone.

As of MySQL 8.0, however, you can assign your privileges to roles. And then assign users to those roles.

Overall, I think roles simplifies user management for MySQL Data Guardians.