一、角色的概念

角色：一组权限的集合，使用角色的目的就是为了方便对权限的管理

二、角色的使用过程

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 库名@'%';

示例

应用程序使用名为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;