This blog post discusses roles in MySQL 8.0, which are named collections of privileges. Like user accounts, roles can have privileges granted and revoked as required.

Typically, we have multiple users with the same set of privileges. Previously, the only way to grant and revoke privileges to multiple users was to change the privileges of each user individually, which was time-consuming. To make it easier, MySQL provided a new object called role. A role is a named collection of privileges.

Here are the primary SQL commands that we will be discussing in relation to managing MySQL roles:

The mandatory_roles and activate_all_roles_on_login system variables enable defining mandatory roles and automatic activation of granted roles when users log in to the server.

Below are examples of role usage.

How to create roles and grant privileges

Suppose an application uses a mytestdb database. To interact with the mytestdb database, it is required to create accounts for developers who generally would need full access to the database. In addition, one needs to create accounts for users who need only read access and others who need both read/write access (depending upon the requirement).

To avoid granting privileges to each user account individually, you create a set of roles and grant the appropriate roles to each user account.

To create the roles, use the CREATE ROLE statement:

The role name is similar to the user account that consists of two parts, the user and host: “user_name@host_name.”

If you omit the host part, it defaults to ‘%,’ which means any host.

To assign privileges to the roles, execute GRANT statements using the same syntax as for assigning privileges to user accounts:

Create user

When you need to create:
1. One developer account with full access
2. Two user accounts that need read-only access
3. And one user account that needs read/write access

Use CREATE USER to create the accounts:

Assigning roles

To assign roles to users, use a GRANT statement.

The above statements grant the app_developer role to the user account ‘dev1@’localhost’, app_read role to ‘read_user1’@’localhost’, ‘read_user2’@’localhost’, and app_read, app_write roles to ‘rw_user1’@’localhost’ respectively, thus granting single (or) multiple roles to single (or) multiple users based on the requirement.

How to define mandatory roles

It is possible to specify roles as mandatory by naming them in the value of the mandatory_roles system variable. The server treats a mandatory role as granted to all users so that it need not be granted explicitly to any account.

To specify mandatory roles at server startup, define mandatory_roles in your server my.cnf file:

To set and persist mandatory_roles at runtime, use a statement like this:

Here, each user on the system uses three roles automatically. When setting the system variable, the value of mandatory_roles must be a string, so we encapsulate the entire role list in single quotes and use backticks to quote individual role components.

A role cannot be added to the mandatory_roles list that has the SYSTEM_USER privilege. This is a security measure to ensure that not all of the sessions on the system are system sessions automatically.

How to check role privileges

To verify the privileges assigned to an account, use SHOW GRANTS.

For example:

However, that shows each granted role without “expanding” it to the privileges the role represents. Alternatively, use the pt-show-grants command from the Linux prompt to verify privileges.

To show role privileges as well, add a USING clause naming the granted roles for which to display privileges:

How to activate roles

Roles granted to a user account can be active or inactive within account sessions. If a granted role is active within a session, its privileges apply; otherwise, they do not. To determine which roles are active within the current session, use the CURRENT_ROLE() function.

By default, granting a role to an account or naming it in the mandatory_roles system variable value does not automatically cause the role to become active within account sessions. In the example so far, no rw_user1 roles have been activated; if you connect to the server as rw_user1 and invoke the CURRENT_ROLE() function, the result is NONE (no active roles).

To specify which roles should become active each time a user connects to the server and authenticates, use SET DEFAULT ROLE. To set the default to all assigned roles for each account created earlier, use this statement:

Now, if you connect as rw_user1, the initial value of CURRENT_ROLE() reflects the new default role assignments:

To cause all explicitly granted and mandatory roles to be automatically activated when users connect to the server, enable the activate_all_roles_on_login system variable. By default, automatic role activation is disabled.

Within a session, a user can execute SET ROLE to change the set of active roles. For example, for rw_user1:

From the above, the first SET ROLE statement deactivates all roles. The second makes rw_user1 effectively read-only. The third restores the default roles.

Revoking roles or role privileges

To revoke privileges from a specific role, use the REVOKE statement.

Roles named in the mandatory_roles system variable value cannot be revoked. REVOKE can also be applied to a role to modify the privileges granted to it. This affects not only the role but any account granted that role. REVOKE as below, which revokes the WRITE privileges from the app_write role:

Now, it can be seen that the role has no privileges at all by checking with SHOW GRANTS:

Thus, revoking privileges from a role affects the privileges for any user who is assigned the modified role. Rw_user1 now has no table modification privileges:

In effect, the rw_user1 read/write user has become a read-only user. This also occurs for any other accounts that are granted the app_write role, illustrating how the use of roles makes it unnecessary to modify privileges for individual accounts. To restore modification privileges to the role, simply re-grant them:

Dropping roles

To drop roles, use DROP ROLE:

Dropping a role revokes it from every account to which it was granted. Roles named in the mandatory_roles system variable value cannot be dropped.

How to interchange users and roles

MySQL treats user accounts like roles; therefore, you can grant a user account to another user account, like granting a role to that user account. This allows you to copy privileges from one user to another user.  Suppose you need another developer account for the mytestdb database. This set of statements demonstrates that you can grant a user to a user, a role to a user, a user to a role, or a role to a role:

The result in each case is to grant to the grantee object the privileges associated with the granted object. After executing those statements, user2 and role2 has been granted privileges from a user (user1) and a role (role1):

Conclusion

Roles are normally used in the most common databases, and MySQL had implemented them quite late. Roles are used to distribute privileges in MySQL databases and can help simplify the management overhead and complexity of your access control system.

It’s much easier to ensure that users with the same responsibilities have the same privileges using roles than it is to grant many different privileges directly. By taking the time to create and organize roles ahead of time, your ability to manage user access to different parts of your data will be more straightforward in the long run.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

 

Try Percona Distribution for MySQL today!

Subscribe
Notify of
guest

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Gangadhar Pataabandula

Thanks @Uday Rajarapu, Very Useful.

0
Reply