MySQL8角色管理

一、角色的概念

角色：一组权限的集合，使用角色的目的就是为了方便对权限的管理；
角色在MySQL8.0版本之后存储在mysql.role表中。
Mysql5.7模拟角色：https://blog.csdn.net/omaidb/article/details/120022948

二、角色的使用过程

https://juejin.cn/post/6844903655200538638

-- 角色创建
CREATE ROLE

-- 角色删除 
DROP ROLE 

-- 为用户和角色分配权限
GRANT 

-- 为用户和角色撤销权限
REVOKE 

-- 显示用户和角色的权限
SHOW GRANTS 

-- 指定哪些帐户角色默认处于活动状态
SET DEFAULT ROLE 

-- 更改当前会话中的活动角色
SET ROLE 

-- 显示当前会话中的活动角色
CURRENT_ROLE() 

1、创建角色

-- 创建角色
create role 角色1;

2、授予角色权限

-- 将权限授予角色
-- all表示所有权限
-- *.*表示所有数据库的所有表
grant all on *.* to 角色1;

-- 查看角色的权限
show grants for 角色1;

3、将角色授予用户

-- 将角色授予用户
grant 角色1 to 用户名;

-- 查看用户权限
show grants for 用户名;

4、将用户的角色撤销

-- 将用户的角色撤销
revoke 角色1 from 库名@'%';

5、删除角色

-- 角色删除 
DROP ROLE 

示例

应用程序使用名为scott的数据库。
app_dev用户需要完全访问数据库；
app_r用户需要只读访问；
app_rw用户需要读/写访问。

1、创建用户app_dev,app_r,app_w

-- 创建app_dev,app_r,app_w角色
CREATE ROLE 'app_dev','app_r','app_w';

2、按需为不同的角色分配不同的权限

-- 按需为不同的角色分配不同的权限

-- 为app_dev角色授予指定数据库的所有权限
GRANT ALL ON 指定库名.* TO 'app_dev';

-- 为app_r角色授予指定数据库的只读权限
GRANT SELECT ON 指定库名.* TO 'app_r';

-- 为app_w角色授予指定数据库的插入，修改，删除权限
GRANT INSERT,UPDATE,DELETE ON 指定库名.* TO 'app_w';

-- 应用权限
FLUSH PRIVILEGES;

3、按需创建多个用户

-- 创建开发用户
CREATE USER 'app_dev1'@'localhost' IDENTIFIED BY 'dev1pass';

-- 创建app只读用户1
CREATE USER 'app_r1'@'localhost' IDENTIFIED BY 'appr1pass';

-- 创建app只读用户2
CREATE USER 'app_r2'@'localhost' IDENTIFIED BY 'appr2pass';

-- 创建app读写用户
CREATE USER 'app_rw1'@'localhost' IDENTIFIED BY 'apprw1pass';

4、按需将不同的角色分配给不同的用户

-- 将app_dev角色权限授予开发人员app_dev1
GRANT '角色名' TO '用户名'@'localhost';

授予角色权限给指定用户：

-- 将app_dev角色权限授予开发人员app_dev1
GRANT 'app_dev' TO 'app_dev1'@'localhost';

-- 将app_r角色权限授予开发人员app_r1
GRANT 'app_r' TO 'app_r1'@'localhost';

-- 将app_r和app_w角色权限授予开发人员app_rw1
GRANT 'app_r','app_w' TO 'app_rw1'@'localhost';

-- 应用权限配置
FLUSH PRIVILEGES;

下面这段PostgreSQL 命令 “SET ROLE ALL EXCEPT ‘app_w’” 并不适用于 MySQL

-- 将当前会话的角色设置为除了名为 'app_w' 的角色之外的所有角色
SET ROLE ALL EXCEPT 'app_w';

四、强制性角色

通过在mandatory_roles系统变量的值中命名角色，可以将角色指定为强制性角色。服务器将强制角色视为授予给所有用户，因此不需要显式地授予任何账户。

-- 在MYSQL服务器运行时设置强制角色
-- SET PERSIST 对系统变量的更改在服务器重启后依然生效
-- mandatory_roles指定连接到服务器所需的角色
SET PERSIST mandatory_roles='role1,role2@localhost,r3@%.example.com'

要在服务器启动时指定强制角色，请在服务器my.cnf配置文件中定义mandatory_roles

[mysqld]
# 指定连接到服务器所需的角色
mandatory_roles='角色1,角色2'

五、检查角色权限

-- 查看分配给账户的权限
SHOW GRANTS FOR '用户名'@'localhost';

-- 查看app_dev分配给指定账户的权限
SHOW GRANTS FOR '用户名'@'localhost' USING '角色名';

SHOW GRANTS FOR '用户名' @'localhost' USING '角色名1','角色名2';

-- 查看app_r和app_w组分配给app_rw用户的权限
SHOW GRANTS FOR 'app_rw' @'localhost' USING 'app_r','app_w';

六、激活权限

新创建的角色是锁定账户，以防止用户直接使用该角色。

-- 应用权限配置
FLUSH PRIVILEGES;

-- 查看是否激活所有角色
SHOW variables LIKE 'activate_all_roles_on_login';

临时激活权限

-- 为当前会话临时设置一个角色
-- 该会话将具有admin角色权限，而其他角色权限将被取消。
SET ROLE 角色名;

-- 查看当前会话的默认角色
SELECT CURRENT_ROLE();

-- 退出当前角色并返回默认角色的权限
SET ROLE NONE;

-- 查看当前会话的默认角色
SELECT CURRENT_ROLE();

-- 将当前会话的角色设置为默认角色
SET ROLE DEFAULT;

查看默认角色

-- 查看当前会话的默认角色
SELECT CURRENT_ROLE();

持久化激活权限

# 激活所有用户
[mysqld]
activate_all_roles_on_login=on

查看用户权限是否激活

-- 查看用户权限是否激活
SHOW GRANTS FOR 用户名@localhost;

七、撤销角色

撤销用户的角色授权

-- 撤销用户隶属的角色授权
REVOKE 角色名 FROM 用户名;
REVOKE role1, role2 FROM john;

强制性角色不能被撤销

系统变量mandatory_roles中命名的角色不能被撤销。

撤销角色权限

-- 撤销角色权限
-- 插入角色指定数据库.下所有表 的插入，修改，删除权限
REVOKE INSERT,UPDATE,DELETE ON 数据库名.* FROM 角色名;

-- 查看角色权限
SHOW GRANTS FOR 角色名;

-- 查看指定隶属于该角色的用户权限
SHOW GRANTS FOR 用户名@'localhost' USING 角色名1，角色名2;

自动为所有用户授予所有角色

MySQL 8.0 的语句 SET PERSIST mandatory_roles=ALL; 用于将系统变量 mandatory_roles 的值设置为 ALL，并使更改在服务器重启时持久化。

mandatory_roles 系统变量指定了必须启用的角色列表，以便任何客户端成功连接。将其设置为 ALL，表示要求所有在服务器实例中定义的角色必须启用才能成功连接客户端。

请注意，只有具备 SUPER 特权的用户才能执行此语句。

-- 自动为所有用户授予所有角色
SET PERSIST mandatory_roles=ALL;