[MySQL]New in MySQL 8.0.0 - Role

The role feature is a long-expected one. This can be confirmed by its worklog number (WL#988) that indicates it is a very early demand with a high appeal.
The so-called role is a set of permissions. The set has a uniform name, that is, the role name. You can grant a uniform role permission to multiple accounts, and modifications to the role can be achieved through modifying the role directly without the need to grant permissions for every account. This greatly facilitates the O&M and management.
The role can be created, modified and deleted and the changes will apply to the account it belongs to.
The following is a simple example. Create a test table as follows.
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)

mysql> use testdb; create table t1 (a int, b int, primary key(a));
Database changed
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,2);
Query OK, 1 row affected (0.00 sec)


Create the role with the query permission of table t1:
mysql> create role priv_t1;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on testdb.t1 to 'priv_t1';
Query OK, 0 rows affected (0.00 sec)

Create an account and grant the role permission to it.
mysql> create user 'rw_user1'@'%' identified by 'xxx';
Query OK, 0 rows affected (0.00 sec)

mysql> grant 'priv_t1' to 'rw_user1'@'%';
Query OK, 0 rows affected (0.00 sec)

Log in as rw_user1.
---- View permissions

mysql> show grants;
+---------------------------------------+
| Grants for rw_user1@%        |
+---------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`%`  |
| GRANT `priv_t1`@`%` TO `rw_user1`@`%` |
+---------------------------------------+
2 rows in set (0.00 sec)

## You need to add using “role name” to unfold the permissions.

mysql> show grants for 'rw_user1'@'%' using priv_t1;
+-------------------------------------------------+
| Grants for rw_user1@%                           |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`%`            |
| GRANT SELECT ON `testdb`.`t1` TO `rw_user1`@`%` |
| GRANT `priv_t1`@`%` TO `rw_user1`@`%`           |
+-------------------------------------------------+
3 rows in set (0.00 sec)

But at this time, you cannot directly get the query permission of table t1. You need to manually select the roles to be activated when the account is connected, as follows:
mysql> select * from testdb.t1;
ERROR 1142 (42000): SELECT command denied to user 'rw_user1'@'localhost' for table 't1'

mysql> SET DEFAULT ROLE ALL TO 'rw_user1'@'%';
Query OK, 0 rows affected (0.00 sec)

--- Log in again to activate the permission.

mysql> select user();
+--------------------+
| user()             |
+--------------------+
| rw_user1@localhost |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from testdb.t1;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

-- For the SET ROLE syntax, refer to the official documents:
-- http://dev.mysql.com/doc/refman/8.0/en/set-default-role.html

Modifications to the role permissions will apply to the corresponding account directly:
--- Add insert permission
--- login as root

mysql> grant insert on testdb.t1 to 'priv_t1';
Query OK, 0 rows affected (0.00 sec)

--- login as rw_user1

mysql> insert into testdb.t1 values (2,3);
Query OK, 1 row affected (0.00 sec)

--- Delete insert permission
--- login as root

mysql> revoke insert on testdb.t1 from 'priv_t1';
Query OK, 0 rows affected (0.00 sec)

--- login as rw_user1

mysql> insert into testdb.t1 values (3,4);
ERROR 1142 (42000): INSERT command denied to user 'rw_user1'@'localhost' for table 't1'

Two system tables are added to maintain the role information. One is the mysql.default_roles table, used for displaying the information about the default role used by the account, and the other is the role_edges table, used for displaying the information of the created roles.
mysql> select * from default_roles;
+------+----------+-------------------+-------------------+
| HOST | USER     | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+----------+-------------------+-------------------+
| %    | rw_user1 | %                 | priv_t1           |
+------+----------+-------------------+-------------------+
1 row in set (0.00 sec)

mysql> select * from role_edges;
+-----------+-----------+---------+----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER  | WITH_ADMIN_OPTION |
+-----------+-----------+---------+----------+-------------------+
| %         | priv_t1   | %       | rw_user1 | N                 |
+-----------+-----------+---------+----------+-------------------+
1 row in set (0.00 sec)

The newly added function is used to display the role used by the current account:
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `priv_t1`@`%`  |
+----------------+
1 row in set (0.00 sec)

For how-tos, click Official Documents
If you are interested in the specific implementation, you can refer to commit 19ff587febb635f4518a78bdd5dffbfd9058c9aa