MySQL8.0 角色管理

一、mysql8.0角色管理(role)

01啥是角色？

官方文档的第一句话，就开门见山的告诉了我们角色是什么东西。

不难看出，MySQL角色是"权限的集合"，我们可以像账号那样，回收或者授予一个角色的权限。

我们可以将某个角色赋予某个用户，然后这个用户就拥有了这个角色下的所有权限，这种方法使得我们可以定义不同的角色来分配给不同的用户。例如我们可以定义"开发者角色"，"只读角色"，"读写角色"等等，不同的角色对应不同的权限。这样，对于开发人员张三、李四，我们只需要将"开发者角色"授予他们的个人账号即可，对于某些查询用户，只需要授予"只读角色"，对于业务账号，要授予数据库的"读写角色"

02角色支持的操作有哪些？

支持的操作，也就是角色的管理，下面是角色管理支持的语法：

1、CREATE ROLE和DROP ROLE创建和删除角色。

2、GRANT和REVOKE分配或回收用户帐户和角色的权限。

3、SHOW GRANTS显示用户帐户和角色的特权和角色分配。

4、SET DEFAULT ROLE指定默认情况下哪些帐户角色处于活动状态。

5、SET ROLE更改当前会话中的活动角色。

6、CURRENT_ROLE（）函数显示当前会话中的活动角色。

7、当用户登录到服务器时，系统变量essentially_roles和activate_all_roles_on_login允许定义强制性角色和自动激活授予的角色。

光是罗列，不太容易理解，来看几个例子吧：

创建角色、删除角色：

mysql> create role "role_ro","role_rw","role_dev";
Query OK, 0 rows affected (0.07 sec)

mysql> create role "role_test"@"localhost";
Query OK, 0 rows affected (0.01 sec)

mysql> drop role "role_test"@"localhost";
Query OK, 0 rows affected (0.01 sec)

mysql> drop role "role_ro","role_rw","role_dev";
Query OK, 0 rows affected (0.00 sec)

分配权限给特定角色：

#创建两个角色，role_ro和role_rw
mysql> create role "role_ro","role_rw";
Query OK, 0 rows affected (0.00 sec)

#授予role_ro select权限，授予role_rw增删改查权限
mysql> grant select,show databases on yeyz.* to "role_ro";
Query OK, 0 rows affected (0.01 sec)

mysql> grant select,insert,update,delete,show databases on yeyz.* to "role_rw";
Query OK, 0 rows affected (0.01 sec)

#创建两个用户yeyz_ro和yeyz_rw
mysql> create user yeyz_ro@'%' identified by 'yeyz'; 
Query OK, 0 rows affected (0.01 sec)

mysql> create user yeyz_rw@'%' identified by 'yeyz';
Query OK, 0 rows affected (0.01 sec)

#将两个角色role_ro和role_rw,分别对应的映射到用户yeyz_ro和yeyz_rw上
mysql> grant 'role_ro' to yeyz_ro@'%';
Query OK, 0 rows affected (0.01 sec)

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

分类好了权限，我们来对权限进行查看：

#查看账号权限，发现只能查看到角色和usage权限
mysql> show grants for yeyz_ro@'%';
+--------------------------------------+
| Grants for yeyz_ro@%                 |
+--------------------------------------+
| GRANT USAGE ON *.* TO `yeyz_ro`@`%`  |
| GRANT `role_ro`@`%` TO `yeyz_ro`@`%` |
+--------------------------------------+
2 rows in set (0.00 sec)

#使用using语法查看账号所拥有的的角色的权限
mysql> show grants for yeyz_ro@'%' using 'role_ro';
+----------------------------------------------+
| Grants for yeyz_ro@%                         |
+----------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO `yeyz_ro`@`%` |
| GRANT SELECT ON `yeyz`.* TO `yeyz_ro`@`%`    |
| GRANT `role_ro`@`%` TO `yeyz_ro`@`%`         |
+----------------------------------------------+
3 rows in set (0.00 sec)

注意到，使用一般的show grants方法只能查看到账号拥有哪些角色，关于角色的具体权限，可以使用using的语法来进行查询。

二、角色管理

来看看角色管理部分的关键内容。

01创建角色

新创建的角色暂时是被锁定的，没有密码。该角色的属性可以被拥有create user权限的用户来修改。处于锁定状态下的账号，不能被用来对服务器进行验证，也就是无法直接登录服务器，解锁之后的角色，就可以登录服务器了。

昨天的文章中，我们创建了两个角色，并将角色分配给两个账号，如下：

角色1：role_ro

权限：select

账号：yeyz_ro

角色2：role_rw

权限：select，update，insert，delete

账号：yeyz_rw

当我们使用yeyz_ro的账号去登录数据库的时候，可以发现：

1、账号可以登录

2、无法执行任何查询操作,甚至连我们的数据库yeyz都看不到。

如下：

192:~ root# /usr/local/mysql_8.0/bin/mysql -uyeyz_ro -pyeyz -h127.0.0.1 --socket=/data/mysql_5306/tmp/mysql.sock --port=5306
........
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

mysql> use yeyz;
ERROR 1044 (42000): Access denied for user 'yeyz_ro'@'%' to database 'yeyz'

这个原因是由于当前没有角色被"激活"导致的，如下：

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

那么如何"激活"角色呢？

02如何激活角色？

使用set default role语法可以激活用户进行身份认证时所需的角色，具体的方法如下：

mysql> set default role 'role_ro' to yeyz_ro@'%';
Query OK, 0 rows affected (0.00 sec)

将role_ro这个角色设置为yeyz_ro账号的默认激活角色，这样，就可以使用yeyz_ro用户来访问对应的数据库了，该用户将拥有role_ro这个角色的权限。如下,再次用yeyz_ro登录MySQL服务，查看当前的角色：

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

进行相关操作，

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yeyz               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use yeyz;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| test_tbl0      |
+----------------+
1 row in set (0.00 sec)

mysql> select * from test_tbl0;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
+------+----------+
3 rows in set (0.00 sec)

如果我们进行delete操作，则会报错，因为role_ro只有select权限。

mysql> delete from yeyz.test_tbl0;
ERROR 1142 (42000): DELETE command denied to user 'yeyz_ro'@'127.0.0.1' for table 'test_tbl0'
mysql> 

这种"激活"角色的方法可以让用户拥有角色所拥有的权限，但是不难看出来，每次给新建用户绑定一个角色，在新建用户登录之前，都得将该用户激活一下，从操作上看不是特别方便，如何让所有的指定的角色都即时生效呢？

MySQL提供了一个系统参数来解决这个问题，该参数是：

mysql> show variables like '%activate%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login         | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

该参数是默认关闭的，直接打开即可。

03

多个角色之间如何切换？

我们知道，当我们创建一个用户的时候，可以给它绑定多个角色，那么如何在多个角色之间进行切换，我们一把。

首先，创建账号yeyz_ro_and_rw,并将上面的role_ro和role_rw两个角色绑定在该用户上，并指定role_ro为默认角色：

mysql> create user yeyz_ro_and_rw@'%' identified by 'yeyz';
Query OK, 0 rows affected (0.02 sec)

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

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


mysql> set default role 'role_ro' to yeyz_ro_and_rw@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
192:~ root# /usr/local/mysql_8.0/bin/mysql -uyeyz_ro_and_rw -pyeyz -h127.0.0.1 --socket=/data/mysql_5306/tmp/mysql.sock --port=5306
...
mysql> use yeyz;
Database changed

mysql> select * from test_tbl0;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
+------+----------+
3 rows in set (0.00 sec)

mysql> insert into test_tbl0 values (4,'zhaoliu');
ERROR 1142 (42000): INSERT command denied to user 'yeyz_ro_and_rw'@'127.0.0.1' for table 'test_tbl0'
mysql> 

可以看到，role_ro拥有select的权限，所以账号yeyz_ro_and_rw可以对yeyz数据库下面的表test_tbl0进行select操作，但是执行insert操作的时候报错。也容易理解，因为角色role_ro没有insert权限。

此时将用户的角色切换成role_rw，再次执行：

mysql> insert into test_tbl0 values (4,'zhaoliu');
ERROR 1142 (42000): INSERT command denied to user 'yeyz_ro_and_rw'@'127.0.0.1' for table 'test_tbl0'
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `role_ro`@`%`  |
+----------------+
1 row in set (0.00 sec)

mysql> set role 'role_rw';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_tbl0 values (4,'zhaoliu');
Query OK, 1 row affected (0.01 sec)

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

可以看到，再次执行insert操作的时候，执行成功了，原因是将角色切换到了role_rw,而role_rw拥有insert权限。

04强制角色定义

强制角色，顾名思义，就是用户账号强制绑定的一个角色，如果我们在创建用户的时候，想要给用户赋予一定的权限，那么可以通过设置一个强制角色，来给所有新生成的用户都赋予这个角色的权限。强制角色一般是需要定义在my.cnf文件中的，假设我们要定义一个强制角色，拥有对yeyz库的select权限，有两种方法：

第一种是可以在配置文件中写下：

[mysqld]
mandatory_roles='role_ro'

这样，所有新创建的账号就有了该角色的权限。

第二种是运行过程中输入下面命令：

set persist mandatory_roles = 'role_ro';

这样，强制角色就永久生效了。

有以下几点需要注意

1、这里的永久生效，是指即使MySQL服务器重启了，那么该配置也会生效。相当于改了my.cnf配置文件，需要区别于MySQL5.7版本的set global语法。

2、强制角色也需要使用set default role的方法进行"角色激活"，或者通过修改参数activate_all_roles_on_login的方法进行"激活"才可以生效。

3、强制角色不能通过revoke的方法或者drop的语法进行权限回收或者角色删除

4、不能将包含system_user权限的角色列在强制角色列表中。

5、如果只是在配置文件中指定了角色为强制角色，但是实际上该角色不存在于mysql.user表里面，则后续创建的账号不会继承该角色的权限。如果后续人工在MySQL实例中对强制角色进行了补充，则需要进行flush privileges操作以确保设置生效。

05撤销角色，回收角色权限

类似将角色授予账户一样，我们可以使用revoke的方法从账户中撤销角色，下面我们演示从账号yeyz_ro中撤销角色role_ro角色:

mysql> show grants for yeyz_ro@'%' using 'role_ro';
+----------------------------------------------+
| Grants for yeyz_ro@%                         |
+----------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO `yeyz_ro`@`%` |
| GRANT SELECT ON `yeyz`.* TO `yeyz_ro`@`%`    |
| GRANT `role_ro`@`%` TO `yeyz_ro`@`%`         |
+----------------------------------------------+
3 rows in set (0.00 sec)

mysql> revoke 'role_ro' from yeyz_ro@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> show grants for yeyz_ro@'%' using 'role_ro';
ERROR 3530 (HY000): `role_ro`@`%` is not granted to `yeyz_ro`@`%`
mysql> show grants for yeyz_ro@'%';
+-------------------------------------+
| Grants for yeyz_ro@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO `yeyz_ro`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)

我们看到，使用了revoke 的语法回收了yeyz_ro的角色role_ro之后，相应的yeyz_ro账号的权限也变少了。

回收角色权限：

我们可以通过revoke的方法回收一个角色的权限，例如role_rw这个角色的增删改查权限，我们可以回收掉它的删除权限，如下：

mysql> show grants for yeyz_rw@'%' using 'role_rw';
+-------------------------------------------------------------------+
| Grants for yeyz_rw@%                                              |
+-------------------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO `yeyz_rw`@`%`                      |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `yeyz`.* TO `yeyz_rw`@`%` |
| GRANT `role_rw`@`%` TO `yeyz_rw`@`%`                              |
+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> revoke delete on yeyz.* from  role_rw;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for yeyz_rw@'%' using 'role_rw';
+-----------------------------------------------------------+
| Grants for yeyz_rw@%                                      |
+-----------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO `yeyz_rw`@`%`              |
| GRANT SELECT, INSERT, UPDATE ON `yeyz`.* TO `yeyz_rw`@`%` |
| GRANT `role_rw`@`%` TO `yeyz_rw`@`%`                      |
+-----------------------------------------------------------+
3 rows in set (0.00 sec)

可以看到，当我们回收了角色的权限之后，绑定该角色的账号的权限也会相应变少，所以这个操作是联动的。

三、角色和账号区别

01角色和账号的区别

我们知道，我们创建角色，可以给角色赋予一定的权限。我们也可以创建账号，给账号赋予一定的权限。二者都是用来访问数据库的，那么他们之间的区别有哪些呢？

区别1：CREATE ROLE创建角色，它默认情况下是被锁定的权限标识符，而CREATE USER创建账户，默认情况下被解锁的权限标识符。也就是说，CREATE ROLE创建的角色是一种预先进行的操作，一旦我们把它绑定到账户上，后续激活角色即可使用既定账号。

区别2：权限上的差异，如果我们拥有CREATE ROLE和DROP ROLE权限，则我们被允许使用CREATE ROLE和DROP ROLE语句；如果我们拥有CREATE USER权限，则我们被允许使用ALTER USER，CREATE ROLE，CREATE USER，DROP ROLE，DROP USER，RENAME USER和REVOKE ALL PRIVILEGES等语句。

区别3：角色可以绑定到账号上，这意味着我们可以用某一个全局的角色，来对所有账号进行批量管理。

02账户和角色的互换性

角色可以绑定到用户，当然，MySQL8.0还支持将用户绑定到角色，在一定程度上来讲，账户和角色具有互换性，如下：

#创建一个角色r1，一个账号u1
mysql> create role 'r1'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'u1'@'%';
Query OK, 0 rows affected (0.01 sec)
#分别授予select权限，r1有yeyz数据库的权限，u1拥有yeyz2数据库的权限
mysql> grant select on yeyz.* to 'r1'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on yeyz2.* to 'u1'@'%';
Query OK, 0 rows affected (0.01 sec)

#--------------将u1和r1互相绑定-----------
mysql> grant 'u1' to 'r1';
Query OK, 0 rows affected (0.01 sec)

mysql> grant 'r1' to 'u1';
Query OK, 0 rows affected (0.00 sec)


#查看二者的权限，发现u1和r1分别拥有了对方的权限。
mysql> show grants for 'u1' using 'r1';
+---------------------------------------+
| Grants for u1@%                       |
+---------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%`        |
| GRANT SELECT ON `yeyz`.* TO `u1`@`%`  |
| GRANT SELECT ON `yeyz2`.* TO `u1`@`%` |
| GRANT `r1`@`%` TO `u1`@`%`            |
+---------------------------------------+
4 rows in set (0.00 sec)

mysql> show grants for 'r1' using 'u1';
+---------------------------------------+
| Grants for r1@%                       |
+---------------------------------------+
| GRANT USAGE ON *.* TO `r1`@`%`        |
| GRANT SELECT ON `yeyz`.* TO `r1`@`%`  |
| GRANT SELECT ON `yeyz2`.* TO `r1`@`%` |
| GRANT `u1`@`%` TO `r1`@`%`            |
+---------------------------------------+
4 rows in set (0.00 sec)

在一定程度上，一个激活的角色和一个账号之间可以进行互相替代，只要权限是相同的，我们可以认为用角色去访问数据库和用账号去访问数据库的结果是一样的。因为，角色的本质，就是权限的集合。

角色和账号之间的互换性给我们提供了一个思路，假如某个数据库处于开发阶段，其上有多个账号，某个开发人员的账号权限，完全可以将一个模板开发账号跟该开发人员账号进行绑定，这样，如果要同一管理所有开发人员的账号，只需要修改模板开发账号即可，给快速的管理权限带来了方便。