Study Notes on the new feature ROLE of MySQL 8.0

The Role function can be said to be an expected feature, which can be seen from its Worklog number (WL #988). This is an early and demanding requirement.

The so-called Role can be considered as a set of permissions. This set has a unified name, that is, the Role name. You can assign a unified Role permission to multiple accounts, the modification of permissions can be implemented directly by modifying the Role, without having to GRANT permissions one by one for each account, which greatly facilitates O & M and management.

Role can be created, modified, and deleted, and applied to the account to which it belongs.

Here is a simple example. Create the following Test table

Mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)

Mysql> use testdb; create table t1 (a int, B int, primary key ());
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 a Role with the query permission for 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 on with 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)

# Grant permissions only by adding using "role name"

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)

However, you cannot directly obtain the query permission for table t1. You need to manually select which role will be activated when the account is connected, as shown below:

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 on again to take effect

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, see the official documentation:
Http://dev.mysql.com/doc/refman/8.0/en/set-default-role.html

Modifying role permissions directly applies to the corresponding account:

--- 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 mysql. default_roles, which is used to display the default role information used by the account and the other is role_edges, which is used to display the created role information.

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)

Add a function to display the role used by the current account:

Mysql> select current_role ();
+ ---------------- +
| Current_role () |
+ ---------------- +
| 'Priv _ T1' @ '%' |
+ ---------------- +
1 row in set (0.00 sec)