role(解读：MySQL 8.0新特性之ROLE)

解读：MySQL 8.0新特性之ROLE

Role功效可以说是一个渴望已有的功效，这从它的Worklog号(WL#988)就可以看出来，这是个相当早并且呼声很高的需求了。

所谓Role，可以以为是一个权限的聚集，这个聚集有一个一致的名字，就是Role名，你可以为多个账户赋予一致的某个Role的权限，而权限的修正可以直接经过修正Role来完成，而无需每个账户逐一GRANT权限，大端庄便了运维和办理。

Role可以被创建，修正和删除，并作用到其所属于的账户上。

举个简便的例子。创建如下测试表

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)

创建Role，拥有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)

创建一个账户，并将role的权限赋给它

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)

以rw_user1登录

---- 查察权限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)

## 必要加using "role名"才会掀开权限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)

但是此时并不克不及直接取得t1表的查询权限， 你必要手动举行选择哪些role在账户毗连上去时被激活，如下:

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)--- 重新登录奏效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)-- SET ROLE语法参阅官方文档：--
http://dev.mysql.com/doc/refman/8.0/en/set-default-role.html

修正role的权限，会直接作用到对应的账户上:

--- 增长insert权限

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

--- 删除insert权限

--- 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'

增长了两个体系表来维护Role信息，一个是mysql.default_roles表，用于展现账户使用的默许role信息，一个是role_edges，用于展现已创建的role信息

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)

新增函数用于体现如今账户使用的role：

mysql> select current_role();

+----------------+| current_role() |

+----------------+| `priv_t1`@`%` |

+----------------+1 row in set (0.00 sec)