MySQL8新特性-Mandatory Roles

12月 26th, 2017

强制角色:Mandatory roles
可以指定强制性的角色作为mandatory_roles系统变量的值。服务器将一个强制性的角色授予所有用户,所以它不需要明确授予任何帐户。
[mysqld]
mandatory_roles=’role1,role2@localhost,r3@%.example.com’
或者
SET PERSIST mandatory_roles = ‘role1,role2@localhost,r3@%.example.com’;
不过需要注意的是:
As of MySQL 8.0.4, setting mandatory_roles requires the ROLE_ADMIN privilege, in addition to the SYSTEM_VARIABLES_ADMIN or SUPER privilege normally required to set a global system variable.
Roles named in the value of mandatory_roles cannot be revoked with REVOKE or dropped with DROP ROLE or DROP USER.
SET PERSIST sets the value for the running MySQL instance. It also saves the value to be used for subsequent server restarts; see Section 13.7.5.1, “SET Syntax for Variable Assignment”. To change a value only for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST.

我们做一个示范:

首先建一个角色并授权:
CREATE ROLE ‘app_developer’;
GRANT ALL ON test.* TO ‘app_developer’;

然后我们把这个参数设成这个角色:
SET PERSIST mandatory_roles = ‘app_developer@%’;

然后我们创建一个用户 并不赋权
create user ‘test2’@’%’ identified by ‘test2’;
FLUSH PRIVILEGES;

我们在其它的窗口上用新加的这个帐号进去。然后可以直接set这个role
test2@information_schema 01:15:07>set role app_developer;
Query OK, 0 rows affected (0.00 sec)

也可以进行查询
test2@information_schema 01:15:15>show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| test |
+——————–+
2 rows in set (0.00 sec)

test2@information_schema 01:15:19>use test;
Database changed
test2@test 01:15:23>select * from test.a;
+——+
| id |
+——+
| 1 |
+——+
1 row in set (0.01 sec)

然后如果我们把这个参数改成空的,相应的权限也就没有了。 这个不用退出会话就会生效
test2@(none) 01:17:03>set role app_developer;
ERROR 3530 (HY000): `app_developer`@`%` is not granted to `test2`@`%`

需要注意的是 当一个角色被set了之后是不可以被删除的
dbadmin@(none) 01:20:25>drop role app_developer;
ERROR 4527 (HY000): The role `app_developer`@`%` is a mandatory role and can’t be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.

标签:
目前还没有任何评论.
您必须在 登录 后才能发布评论.