MySQL 账号权限管理之角色详解

前言

本菜鸟 DBA 最近遇到了一个问题：每当有新业务需求，需要创建新数据库作为载体时，我就要手动为乌泱泱一大片开发（其实也就三五个）一个个关联新数据库的权限，感觉很捞，就想着有没有办法能显得高大上一点？果不其然，MySQL 作为最大的开源数据库，这点功能还是有的，那就是角色！

MySQL 角色介绍

什么是角色？

MySQL 角色是一组命名的权限集合。与用户一样，角色也可以被授予或撤销权限。

可以向用户授予角色，从而向该用户授予与每个角色相关的权限。这样可以将权限集分配给用户，并提供一种方便的替代方法，以代替授予单个权限。

创建角色、授予权限

创建：

CREATE ROLE r_1; -- 创建单个角色
CREATE ROLE r_1, r_2, r_3; -- 创建多个角色

角色的授权与用户的授权写法完全一致：

GRANT SELECT ON db1.* to r_1;

检查角色权限

假设有如下用户、角色以及权限：

create user u_x identified by '123';
create role r_x, r_y;
create database dbx;
create database dby;
grant select on dbx.* to r_x;
grant select on dby.* to r_y;
grant r_x,r_y to u_x;

SHOW GRANTS FOR u_x 只会显示关联的角色，不显示角色对应的权限：

如果要查看关联的角色的权限，需要加个 using 字句：

定义强制角色

服务器将强制角色（mandatory_roles）视为已授权给所有用户，因此无需手动将其授予任何用户。

强制角色是通过 mandatory_roles 变量定义的，它其实也是来自于普通的角色。

下面演示强制角色的用法：

create user u_3 identified by '123';
create role r_3;
create database db3;
grant select on db3.* to r_3;
set global mandatory_roles = 'r_3'; -- 将r_3设置为强制角色

注意：设置 mandatory_roles 变量需要有 SYSTEM_VARIABLES_ADMIN + ROLE_ADMIN 或 SUPER + ROLE_ADMIN 权限，否则会报如下错误：

SQL 错误 [1227] [42000]: Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SUPER privileges, as well as the ROLE_ADMIN privilege(s) for this operation
即便是 root 用户，可能也没有足够权限，通过 show grants for `root`@`%` （不一定都是 `root`@`%`，新安装的实例应该是 `root`@`localhost`，我是自己改成了 %）查看当前权限，如果没有就要加了，有上述两个组合中的任意一组权限即可。有同学要问了，怎么加呢？问得好！自己给自己加就行了（不愧是 root），要是嫌麻烦，把三个权限都加上就行，即 grant SYSTEM_VARIABLES_ADMIN, ROLE_ADMIN, SUPER to `root`@`%`。

强制角色默认是未激活状态。如果希望默认激活，需要设置全局变量 activate_all_roles_on_login = ON。

接下来分别演示 activate_all_roles_on_login = OFF 和 ON 的情形：

• activate_all_roles_on_login = OFF，强制角色未激活，因此用户 u_3 关联不到 r_3 的权限，无法访问 db3（截图是用 u_3 连接执行的）

  强制角色和普通角色还有一个区别，下面为 u_3 再关联一个新的普通角色 r_4 作为对照：

  create role r_4;
  create database db4;
  grant select on db4.* to r_4;
  grant r_4 to u_3;

  此时用户 u_3 关联了强制角色 r_3 和普通角色 r_4，强制角色在 show grants 语句中不显示：

    区别见下图，此时即使强制角色未激活，对应的权限也会显示。

• activate_all_roles_on_login = ON，强制角色激活，可以使用强制角色的权限：

  

还有一种手动激活强制角色的方式是 SET ROLE，如图：

回收角色、回收权限

回收的方式与授权类似，只需把 GRANT ... TO ... 改成 REVOKE ... FROM .. 即可。

删除角色

DROP ROLE role1, role2。实测用 DROP USER 也可以。

注意：

1. 强制角色无法直接删除，需要从 mandatory_roles 变量中移除后才能删除，见下图：

2. 删除角色会即时同步到与其关联的所有用户，例如用户关联了 r_1，删除 r_1 后，用户与 r_1 相关的权限立马消失，不会有当前连接着的用户可以继续用这种说法。权限授予、变更也是一样，用户与角色也一样，只要变更权限，都是即时生效。

角色和用户的可互换性

角色和用户的区别

1. 创建时“是否锁定”的属性不同

create user u_a identified by '123';
create role r_a;
select * from mysql.user where user in ('u_a','r_a');

因为角色创建时不需要密码，因此除了密码相关的两个字段不同外，角色和用户唯一不同之处就是 account_locked，即可以把角色看成是锁定的用户。

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 操作。

因此，CREATE USER 的权限更强大。

角色和用户的共性

角色和用户可以互相授予，即允许：

• 将用户授予用户

• 将用户授予角色

• 将角色授予用户

• 将角色授予角色

下面验证这四种授予方式：

create user u_1 identified by '123';
create role r_1;
grant select on db1.* to u_1;
grant select on db2.* to r_1;
create user u_2 identified by '123';
create role r_2;
grant u_1, r_1 to u_2; -- 将用户、角色授予用户
grant u_1, r_1 to r_2; -- 将用户、角色授予角色

查看 u_2 和 r_2 的权限：

角色套娃

角色是支持套娃的，尽管官方文档没有提到这一点。一起来康康吧！

创建3个角色 r_1st,r_2nd,r_3rd 分别授予 db1,db2,db3 的读权限，然后将 r_1st 授权给 r_2nd，将 r_2nd 授权给 r_3rd，创建一个新用户 u_all，将 r_3rd 授权给 u_all：

create role r_1st,r_2nd,r_3rd;
grant select on db1.* to r_1st;
grant select on db2.* to r_2nd;
grant select on db3.* to r_3rd;
grant r_1st to r_2nd;
grant r_2nd to r_3rd;
create user u_all identified by '123';
grant r_3rd to u_all;

查看 u_all 权限，结果如下：

r_1st 和 r_2nd 的权限也拿到了，泰裤辣！

有同学又要问了，套娃有什么用呢？问得好！来假设一个场景：公司有三个业务，每个业务都有许多个数据库，例如 db_业务a_1，db_业务a_2，db_业务a_3，db_业务b_1，db_业务b_2……那么首先可以为每个业务分别创建对应数据库读和写权限的角色，例如业务a就分为 r_业务a_read，r_业务a_write，这是最底层的角色；然后再往上创建开发小哥哥们的角色 r_开发_业务a，有些开发可能负责多个业务，就会有 r_开发_业务abc，这是相对更上一层的角色。这时候，r_开发_业务a 就需要获得 r_业务a_read，r_业务a_write 的权限，同时，r_开发_业务a 要关联给所有业务a的开发小哥账号；同理 r_开发_业务abc 就需要获得 r_业务a_read，r_业务a_write，r_业务b_read，r_业务b_write，r_业务c_read，r_业务c_write 的权限，同时，r_开发_业务abc 要关联给所有同时负责业务abc的开发小哥账号。

总结

MySQL 的角色可以方便地管理用户权限。