MySQL 8新特性--角色

原创

Expect_lei 2022-09-29 11:47:44 博主文章分类：MySQL 8 ©著作权

文章标签 mysql 数据库 oracle 文章分类 运维

©著作权归作者所有：来自51CTO博客作者Expect_lei的原创作品，请联系作者获取转载授权，否则将追究法律责任

3.角色

从MySQL8.0开始支持角色，和Oracle中的角色一样，角色就是权限的集合。
MySQL中当前支持的关于角色的相关操作和变量：

CREATE ROLE和DROP ROLE
GRANT和REVOKE
SHOW GRANTS
SET DEFAULT FOLE
SET ROLE
CURRENT_ROLE()
mandatory_roles和activate_all_roles_on_login系统变量

下面将详细的介绍关于角色的相关操作，说明

3.1.创建角色并赋权限

创建三个角色，分别是数据库管理员角色，读角色，写角色

mysql> create role 'dba_role','read_role','write_role';
Query OK, 0 rows affected (0.08 sec)

–赋权限

mysql> grant ALL ON *.* TO 'dba_role';
Query OK, 0 rows affected (0.18 sec)
mysql> grant SELECT ON *.* TO ' read_role';
Query OK, 0 rows affected (0.12 sec)
mysql> grant INSERT,UPDATE,DELETE ON *.* TO 'write_role';
Query OK, 0 rows affected (0.17 sec)

–将角色赋给相应的用户，如：

grant dba_role to ' lei '@ ' %';

3.2.定义强制性角色

所谓的强制性角色，就是该角色会赋给数据库中的所有用户。可以通过变量mandatory_roles指定，如下：

[mysqld]
mandatory_roles='dba_role,test_role@localhost,read_role@%.test.com'

或在数据库运行期间设置

SET PERSIST mandatory_roles='dba_role,test_role@localhost,read_role@%.test.com'

注：PERSIST会在下次数据库重启后继续生效，如果不想这么做可以使用GLOBAL。
具体示例：

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

–报错了，提示了权限不足
注：修改该参数需要ROLE_ADMIN权限

mysql> SET PERSIST mandatory_roles='dba_role,test_role@localhost,read_role@192.168.1%';
ERROR 1227 (42000): Access denied; you need (at least one of) the ROLE_ADMIN, SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation’

mysql> show variables like '%manda%';
+-----------------+------------------------------------------------------+
| Variable_name   | Value                                                |
+-----------------+------------------------------------------------------+
| mandatory_roles | 'dba_role,test_role@localhost,read_role@192.168.1%'; |
+-----------------+------------------------------------------------------+
1 row in set (0.00 sec)

–激活角色

SET DEFAULT ROLE ALL TO 'lei_test'@'localhost';

后台日志有条警告信息：提示没有对应的角色

[Warning] [MY-010968] [Server] Can't set mandatory_role: There's no such authorization ID test_role@localhost.

–创建一个用户

mysql> create user 'lei_test'@'%' identified by 'lei';
Query OK, 0 rows affected (0.19 sec)

–以localhost方式登录看看，具有了dba_role角色了

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `dba_role`@`%` |
+----------------+
1 row in set (0.10 sec)

设置完mandatory_roles后不会立刻生效，还需要激活角色才可以，关于激活角色下面会说。
mandatory_roles指定的角色无法REVOKE或删除(DROP ROLE或DROP USER)。
如果mandatory_roles指定的角色不存在，那么该角色不会对用户生效，mysql会将该警告信息记录在日志中。如果后面该角色被创建并执行了FLUSH PRIVILEGES，那么该角色会对用户生效。

3.3.激活角色

默认情况下，将一个角色赋给一个用户或mandatory_roles，该角色是不被激活的，也就是不会生锈的。除非用户手动激活才可以。
正常没有激活的角色，显示如下：

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+

激活指定用户的角色

SET DEFAULT ROLE ALL TO 'lei_test'@'localhost';

使用该用户登录数据库，查看当前角色

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `dba_role`@`%` |
+----------------+
1 row in set (0.10 sec)

当然也可以设置当用户登录数据库时，角色就自动激活。通过设置activate_all_roles_on_login变量即可，如：

mysql > set global activate_all_roles_on_login=on;
mysql> SET ROLE NONE; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
mysql> SET ROLE ALL EXCEPT 'app_write'; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `app_read`@`%` |
+----------------+
mysql> SET ROLE DEFAULT; SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

3.4.回收角色或角色权限

这个不用说太多了，和oracle一样，直接使用REVOKE命令即可。需要注意的是mandatory_roles中指定的角色是无法REVOKE的。

REVOKE INSERT ON *.* FROM 'role_write';

查看角色的定义：

mysql> SHOW GRANTS FOR 'role_write';
+---------------------------------------+
| Grants for app_write@%                |
+---------------------------------------+
| GRANT WIRTE,DELETE ON *.* TO `role_write`@`%` |
+---------------------------------------+