Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.9Mb
PDF (A4) - 30.9Mb
PDF (RPM) - 29.7Mb
HTML Download (TGZ) - 7.7Mb
HTML Download (Zip) - 7.7Mb
HTML Download (RPM) - 6.6Mb
Man Pages (TGZ) - 142.3Kb
Man Pages (Zip) - 201.5Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb


Pre-General Availability Draft: 2017-04-29

7.3.4 Using Roles

A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them.

A user account can be granted roles, which grants to the account the role privileges. This enables assignment of sets of privileges to accounts and provides a convenient alternative to granting individual privileges, both for conceptualizing desired privilege assignments and implementing them.

The following list summarizes role management capabilities provided by MySQL:

  • CREATE ROLE and DROP ROLE enable roles to be created and removed.

  • GRANT and REVOKE enable privilege assignment and revocation for user accounts and roles.

  • SHOW GRANTS displays privilege and role assignments for user accounts and roles.

  • SET DEFAULT ROLE specifies which account roles are active by default.

  • SET ROLE changes the active roles within the current session.

  • The CURRENT_ROLE() function displays the active roles within the current session.

For descriptions of individual role-manipulation statements, see Section 14.7.1, “Account Management Statements”. The following discussion provides examples of role usage. Unless otherwise specified, SQL statements shown here should be executed using an account with administrative privileges, such as the root account.

Consider this scenario:

  • An application uses a database named app_db.

  • Associated with the application, there can be accounts for developers who create and maintain the application, and for users who interact with it.

  • Developers need full access to the database. Some users need only read access, others need read/write access.

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:

CREATE ROLE 'app_developer', 'app_read', 'app_write';

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 7.2.5, “Specifying Role Names”.

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

GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

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:

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';

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:

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

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.)

To verify the privilege assignments, use SHOW GRANTS. For example:

mysql> SHOW GRANTS FOR 'dev1'@'localhost';
+-------------------------------------------------+
| Grants for dev1@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`        |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+-------------------------------------------------+

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:

mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
+----------------------------------------------------------+
| Grants for dev1@localhost                                |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |
+----------------------------------------------------------+

Unless you are displaying information for your own account, the USING clause requires the SUPER privilege.

Verify each other type of user similarly:

mysql> SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';
+--------------------------------------------------------+
| Grants for read_user1@localhost                        |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user1`@`localhost`         |
| GRANT SELECT ON `app_db`.* TO `read_user1`@`localhost` |
| GRANT `app_read`@`%` TO `read_user1`@`localhost`       |
+--------------------------------------------------------+
mysql> SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';
+------------------------------------------------------------------------------+
| Grants for rw_user1@localhost                                                |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `rw_user1`@`localhost` |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost`               |
+------------------------------------------------------------------------------+

Granting roles to a user account does not automatically cause the roles to become active within sessions for the account. For example, if you connect to the server as rw_user1 and invoke the CURRENT_ROLE() function, the result is NONE (no active roles):

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+

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 for each account to all its assigned roles, use this statement:

SET DEFAULT ROLE ALL TO
  'dev1'@'localhost',
  'read_user1'@'localhost',
  'read_user2'@'localhost',
  'rw_user1'@'localhost';

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

mysql> SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

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

mysql> SET ROLE NONE; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
mysql> SET ROLE ALL EXCEPT 'app_write'; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `app_read`@`%` |
+----------------+
mysql> SET ROLE DEFAULT; SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

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

The privileges assigned to a role can be modified. 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:

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';

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):

mysql> SHOW GRANTS FOR 'app_write';
+---------------------------------------+
| Grants for app_write@%                |
+---------------------------------------+
| GRANT USAGE ON *.* TO `app_write`@`%` |
+---------------------------------------+

Because revoking privileges from a role affects the privileges for any user assigned the modified role, rw_user1 now has no modification privileges (INSERT, UPDATE, and DELETE are no longer present):

mysql> SHOW GRANTS FOR 'rw_user1'@'localhost'
       USING 'app_read', 'app_write';
+----------------------------------------------------------------+
| Grants for rw_user1@localhost                                  |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                   |
| GRANT SELECT ON `app_db`.* TO `rw_user1`@`localhost`           |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost` |
+----------------------------------------------------------------+

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

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

GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

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

To remove roles, use DROP ROLE:

DROP ROLE 'app_read', 'app_write';

Dropping a role revokes it from every account to which it was granted.

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:

CREATE USER 'u1';
CREATE ROLE 'r1';
GRANT SELECT ON db1.* TO 'u1';
GRANT SELECT ON db2.* TO 'r1';
CREATE USER 'u2';
CREATE ROLE 'r2';
GRANT 'u1', 'r1' TO 'u2';
GRANT 'u1', 'r1' TO 'r2';

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):

mysql> SHOW GRANTS FOR 'u2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for u2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`%`      |
| GRANT SELECT ON `db1`.* TO `u2`@`%` |
| GRANT SELECT ON `db2`.* TO `u2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `u2`@`%` |
+-------------------------------------+
mysql> SHOW GRANTS FOR 'r2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for r2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `r2`@`%`      |
| GRANT SELECT ON `db1`.* TO `r2`@`%` |
| GRANT SELECT ON `db2`.* TO `r2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `r2`@`%` |
+-------------------------------------+

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:

CREATE USER 'old_app_dev'@'localhost' IDENTIFIED BY 'old_app_devpass';
GRANT ALL ON old_app.* TO 'old_app_dev'@'localhost';

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:

  • Without using roles: Change the account password so the original developer cannot use it, and have a new developer use the account instead:

    ALTER USER 'old_app_dev'@'localhost' IDENTIFIED BY 'new_password';
    
  • Using roles: Lock the account to prevent anyone from using it to connect to the server:

    ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;
    

    Then treat the account as a role. For each developer new to the project, create a new account and grant to it the original developer account:

    CREATE USER 'new_app_dev1'@'localhost' IDENTIFIED BY 'new_password';
    GRANT 'old_app_dev'@'localhost' TO 'new_app_dev1'@'localhost';
    

    The effect is to assign the original developer account privileges to the new account.


User Comments
Sign Up Login You must be logged in to post a comment.