search 2013 adfgs
作者:Sky.Jian | 可以任意转载, 但转载时务必以超链接形式标明文章原始出处 和 作者信息 及 版权声明
链接:http://isky000.com/database/mysql-8-role | del.icio.us | Twitter it

MySQL 的权限管理一只以来都是大家所诟病的主要问题之一,最大的问题在于权限系统太过于简单粗暴,对于稍微复杂一些的权限维护就会很复杂。

MySQL 8终于在这一方面做了一点改进,给权限系统增加了 Role(角色),总算可以给咱们的权限管理维护人员减低一些工作量了。

所谓Role,简单来说就是一个权限集合,这个集合有一个统一的名字,就是Role名。当我们有多个用户需要相同权限的时候,就可以为多个账户创建以恶搞Role,然后再奖Role授予每个账户。这样,当这些用户需要增加或者调整某个权限的时候,并不需要每个用户单独授权,只需要针对Role调整权限就可以达到目的。这在很大成都上方便了权限的维护和管理。

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

下面是简单的测试用例

mysql> create database mysql8role;
Query OK, 1 row affected (0.00 sec)

mysql> use mysql8role; 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 mysql8role.t1 to 'priv_t1';
Query OK, 0 rows affected (0.00 sec)

然后我们再建一个账户,并将role的权限赋给他

mysql> create user 'role_u1'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

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

接着我们用用户role_u1登录

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

## 需要加using "role名"才会展开权限


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

然而此时并不能直接获得t1表的查询权限, 你需要手动进行选择哪些role在账户连接上来时被激活,如下:

mysql> select * from tmysql8role.t1;
ERROR 1142 (42000): SELECT command denied to user 'role_u1'@'localhost' for table 't1'

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

--- 重新登录生效

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

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


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



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


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


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


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

MySQL8中增加了两个系统表来维护Role信息,一个是mysql.default_roles表,用于展示账户使用的默认role信息,一个是role_edges,用于展示已创建的role信息

mysql> select * from default_roles;
+------+----------+-------------------+-------------------+
| HOST | USER     | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+----------+-------------------+-------------------+
| %    | role_u1 | %                 | 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   | %       | role_u1 | N                 |
+-----------+-----------+---------+----------+-------------------+
1 row in set (0.00 sec)

我们可以通过系统提供的函数显示当前账户有哪些role:

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `priv_t1`@`%`  |
+----------------+
1 row in set (0.00 sec)

看完了要说点啥么?