MySQL 8.0 finally allows you to use roles to grant privileges. Most companies are now required to fulfill stricter security requirements, one of those requirements is each user must login using their own username. Roles allow you to grant individual users permissions in one go, rather than trying to maintain permissions per user.

Sidenote: If your users are coming in with a shared user, understand that in the future that kind of access is going away. If you get audited for security, shared users are red flag. The reason being is, if something goes wrong or the wrong activity occurs, it can’t be determined easily who did the wrong thing.

This may seem like a mundane topic over trying to crack password hashes, or discovering the mysql_native_password plugin can be broken easily using rainbox tables, but roles will make managing privileges much much easier. Think of roles as removing a layer of redundancy (having to grant all users all the required permissions). Spending the time to get roles setup means less work controlling user privileges in the future.

Task 7: Identify groups of permissions, create a role, grant the role to individual usernames.

This is a multi stage task.

1. Get a list of current usernames and host and their privileges.
2. Review this list and generate a list of privileges which are required or are in common for the current usernames.
3. Create roles to cover all groups of privileges.
4. Grant this role the privileges identified in step 2.
5. Revoke individual privileges from the user so the user has permissions only via the roles granted to them.

For more details, MySQL roles and MariaDB roles

Until next time.