6.3.4 Using Roles

Consider this scenario:

To avoid granting privileges individually to possibly many user accounts, create roles as names for the required privilege sets. This makes it easy to grant the required privileges to user accounts, by granting the appropriate roles.

To create the roles, use CREATE ROLE:

Role names are much like user account names and consist of a user part and host part in 'user_name'@'host_name' format. The host part, if omitted, defaults to '%'. The user and host parts can be unquoted unless they contain special characters such as - or %. Unlike account names, the user part of role names cannot be blank. For additional information, see Section 6.2.5, “Specifying Role Names”.

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

Now suppose that initially you require one developer account, two user accounts that need read-only access, and one user account that needs read/write access. Use CREATE USER to create the accounts:

To assign each user account its required privileges, you could use GRANT statements of the same form as just shown, but that requires enumerating individual privileges for each user. Instead, use an alternative GRANT syntax that permits granting roles rather than privileges:

The GRANT statement for the rw_user1 account grants the read and write roles, which combine to provide the required read and write privileges.

The GRANT syntax for granting roles to an account differs from the syntax for granting privileges: There is an ON clause to assign privileges, whereas there is no ON clause to assign roles. Because the syntaxes are distinct, you cannot mix assigning privileges and roles in the same statement. (It is permitted to assign both privileges and roles to an account, but you must use separate GRANT statements, each with syntax appropriate to what is to be granted.)

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:

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.example.com'

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

SET PERSIST sets the value for the running MySQL instance. It also saves the value to be used for subsequent server restarts; see Section 13.7.5.1, “SET Syntax for Variable Assignment”. To change a value for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST.

Setting mandatory_roles requires the ROLE_ADMIN privilege, in addition to the SYSTEM_VARIABLES_ADMIN or SUPER privilege normally required to set a global system variable.

Mandatory roles, like explicitly granted roles, do not take effect until activated (see Activating Roles). At login time, role activation occurs for all granted roles if the activate_all_roles_on_login system variable is enabled, or only for roles that are set as default roles otherwise. At runtime, SET ROLE activates roles.

Roles named in the value of mandatory_roles cannot be revoked with REVOKE or dropped with DROP ROLE or DROP USER.

If a role named in mandatory_roles is not present in the mysql.user system table, the role is not granted to users. When the server attempts role activation for a user, it does not treat the nonexistent role as mandatory and writes a warning to the error log. If the role is created later and thus becomes valid, FLUSH PRIVILEGES may be necessary to cause the server to treat it as mandatory.

SHOW GRANTS displays mandatory roles according to the rules described in Section 13.7.6.21, “SHOW GRANTS Syntax”.

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. To show role privileges as well, add a USING clause naming the granted roles for which to display privileges:

Verify each other type of user similarly:

SHOW GRANTS displays mandatory roles according to the rules described in Section 13.7.6.21, “SHOW GRANTS Syntax”.

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. For example, because thus far in the preceding discussion 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:

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

The effective user for stored program and view objects is subject to the DEFINER and SQL SECURITY attributes, which determine whether execution occurs in invoker or definer context (see Section 24.6, “Access Control for Stored Programs and Views”):

Just as roles can be granted to an account, they can be revoked from an account:

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 itself, but any account granted that role. Suppose that you want to temporarily make all application users read only. To do this, use REVOKE to revoke the modification privileges from the app_write role:

As it happens, that leaves the role with no privileges at all, as can be seen using SHOW GRANTS (which demonstrates that this statement can be used with roles, not just users):

Because 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 (INSERT, UPDATE, and DELETE are no longer present):

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 use of roles makes it unnecessary to modify privileges for individual accounts.

To restore modification privileges to the role, simply re-grant them:

Now rw_user1 again has modification privileges, as do any other accounts granted the app_write role.

To remove 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.

As has been hinted at earlier for SHOW GRANTS, which displays grants for user accounts or roles, accounts and roles can be used interchangeably. You can treat a user account like a role and grant that account to another user or a role. The effect is to grant the account's privileges and roles to the other user or role.

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, each of u2 and r2 have been granted privileges from a user (u1) and a role (r1):

The preceding example is illustrative only, but interchangeability of user accounts and roles has practical application, such as in the following situation: Suppose that a legacy application development project began before the advent of roles in MySQL, so all user accounts associated with the project are granted privileges directly (rather than granted privileges by virtue of being granted roles). One of these accounts is a developer account that was originally granted privileges as follows:

If this developer leaves the project, it becomes necessary to assign the privileges to another user, or perhaps multiple users if development activies have expanded. Here are some ways to deal with the issue: