我要评论版本说明：角色（role）是 mysql 8.0.0 引入的特性，mysql 5.7 及更早版本不支持角色。角色在内部实现上是一个没有登录权限的特殊用户（account_locked=y 且 password_expired=y）。以下命令仅适用于 mysql 8.0+。
1. 创建角色
-- 创建单个角色 create role 'app_read'; -- 批量创建多个角色 create role 'app_write', 'app_admin';
查看已创建的角色
角色在 mysql.user 表中体现为 account_locked='y' 且 password_expired='y' 的记录：
select user, host from mysql.user where account_locked = 'y' and password_expired = 'y';
返回结果：
+-----------+------+ | user | host | +-----------+------+ | app_admin | % | | app_read | % | | app_write | % | +-----------+------+ 3 rows in set (0.00 sec)
2. 给角色授权
给角色授权与给用户授权的语法完全一致。下面是一个最常见的例子：
-- 创建角色 create role 'app_read'; -- 授予 db_test 库的只读权限 grant select on db_test.* to 'app_read';
查看角色拥有的权限
-- 查看角色拥有的权限 show grants for 'app_read';
返回结果：
+-----------------------------------------------+ | grants for app_read@% | +-----------------------------------------------+ | grant usage on *.* to `app_read`@`%` | | grant select on `db_test`.* to `app_read`@`%` | +-----------------------------------------------+ 2 rows in set (0.00 sec)
3. 给用户分配角色
将角色授予用户使用 grant ... to 语法：
-- 先创建用户 create user 'zhangsan'@'%' identified by 'zspass123!'; -- 将角色分配给用户 grant 'app_read' to 'zhangsan'@'%';
查看用户被授予了哪些角色
show grants for 'zhangsan'@'%';
返回结果：
+----------------------------------------+ | grants for zhangsan@% | +----------------------------------------+ | grant usage on *.* to `zhangsan`@`%` | | grant `app_read`@`%` to `zhangsan`@`%` | +----------------------------------------+
注意：角色分配给用户后，默认并不会自动生效（即角色内的权限不会立即对用户生效），还需要激活。
4. 激活角色
4.1 为什么需要"分配"和"激活"两个步骤
| 步骤 | 类比 | 说明 |
|---|---|---|
| 分配角色（grant ... to） | 给你一把钥匙 | 只是声明你拥有这个角色，但权限尚未生效 |
| 激活角色（set role） | 用钥匙开门 | 真正让角色中的权限在当前会话中生效 |
这样设计的好处：
- 最小权限原则：用户可以拥有多个角色，但只激活当前任务需要的角色。比如一个 dba 平时只用只读角色，只有在需要修改时才激活写入角色。
- 灵活切换：无需重新分配角色，只需
set role即可临时切换权限组合。 - 安全隔离：高权限角色默认不激活，降低误操作风险。
4.2 激活前后的对比
-- ========== 激活前 ========== -- 查看当前激活的角色，登录刚才分配角色的用户zhangsan select current_role();
返回结果：
+----------------+ | current_role() | +----------------+ | none | +----------------+
此时 zhangsan 虽然被分配了 app_read 角色，但角色未激活，不具备 app_read 中的 select 权限。
-- ========== 激活后 ========== -- 激活指定角色 set role 'app_read'; -- 再次查看当前激活的角色 select current_role();
返回结果：
+----------------+ | current_role() | +----------------+ | `app_read`@`%` | +----------------+ 1 row in set (0.00 sec)
此时 zhangsan 真正拥有了 app_read 角色中的 select on db_test.* 权限。
4.3 激活的几种方式
方式一：手动激活（当前会话有效，重新登录后失效）
-- 激活指定角色 set role 'app_read'; -- 激活所有被授予的角色 set role all; -- 停用所有角色（恢复到无角色状态） set role none; -- 恢复默认角色 set role default;
方式二：设置默认角色（每次登录自动激活）
-- 为 zhangsan 设置默认角色，登录时自动激活 set default role 'app_read' to 'zhangsan'@'%'; -- 激活所有被授予的角色 set default role all to 'zhangsan'@'%'; -- 不设置默认角色（登录后需手动 set role） set default role none to 'zhangsan'@'%';
查看默认角色设置
-- 查看默认角色设置，查询需要权限，在root用户查询 select * from mysql.default_roles;
返回结果：
+------+----------+-------------------+-------------------+ | host | user | default_role_host | default_role_user | +------+----------+-------------------+-------------------+ | % | zhangsan | % | app_read | +------+----------+-------------------+-------------------+ 1 row in set (0.00 sec)
方式三：全局强制激活（对所有用户生效）
-- 查看当前策略 show variables like 'activate_all_roles_on_login';
返回结果：
+-----------------------------+-------+ | variable_name | value | +-----------------------------+-------+ | activate_all_roles_on_login | off | +-----------------------------+-------+ 1 row in set, 1 warning (0.00 sec)
全局开启：所有用户的所有角色登录时自动激活
set global activate_all_roles_on_login = on;
持久化：写入配置文件 my.cnf / my.ini
[mysqld] activate_all_roles_on_login = on
注意：开启此选项后，无需再为每个用户单独执行 set default role。
5. 角色与用户的关系
5.1 角色本质上是用户
在 mysql 内部，角色就是一个 account_locked='y' 且 password_expired='y' 的特殊用户：
-- 可以看到角色和用户都在同一张表中
select user, host, account_locked, password_expired
from mysql.user
where user in ('app_read', 'zhangsan');
返回结果：
+----------+------+----------------+------------------+ | user | host | account_locked | password_expired | +----------+------+----------------+------------------+ | app_read | % | y | y | | zhangsan | % | n | n | +----------+------+----------------+------------------+ 2 rows in set (0.00 sec)
因此角色可以嵌套继承——一个角色可以授予给另一个角色：
-- 创建基础角色 create role 'base_read'; grant select on db_test.* to 'base_read'; -- 创建高级角色，继承基础角色 create role 'senior_dev'; grant 'base_read' to 'senior_dev'; -- 角色授予角色 grant insert, update, delete on db_test.* to 'senior_dev'; -- 将 senior_dev 授予用户，用户将同时拥有 base_read 和 senior_dev 的权限 grant 'senior_dev' to 'zhangsan'@'%';
5.2 用户能否像角色一样授予给其他用户？
可以。原因在于 mysql 的实现中，角色和用户 没有本质区别 ——都是 mysql.user 表中的一行记录，区别仅在于 account_locked 标志位。而 grant … to … 操作的是 mysql.user 表里的授权关系， mysql 并不会校验被授予的对象是"角色"还是"用户" 。所以将一个普通用户授予另一个用户在语法上完全合法：
grant 'zhangsan'@'%' to 'dba_user'@'%';
6. 回收角色
6.1 撤销用户的角色
-- 先查看用户当前拥有的角色 show grants for 'zhangsan'@'%';
返回结果：
+---------------------------------------------------------+ | grants for zhangsan@% | +---------------------------------------------------------+ | grant usage on *.* to `zhangsan`@`%` | | grant `app_read`@`%`,`senior_dev`@`%` to `zhangsan`@`%` | +---------------------------------------------------------+ 2 rows in set (0.00 sec)
撤销用户的某个角色
revoke 'app_read' from 'zhangsan'@'%';
6.2 撤销角色的权限
查看角色权限
show grants for 'app_write';
撤销角色中的某条权限
revoke delete on db_test.* from 'app_write';
6.3 删除角色
删除角色会同时从所有拥有该角色的用户中移除：
-- 删除单个角色 drop role 'app_read'; -- 批量删除 drop role 'app_write', 'app_admin';
6.4 管理角色需要的权限
| 条件 | 说明 |
|---|---|
| create user | 创建和删除角色需要此权限（角色本质是用户） |
| role_admin | 执行 grant role 和 revoke role 需要此权限 |
| with admin option | 在授予角色时附加此选项，允许被授予者继续将该角色传递给其他用户 |
-- 示例：授予角色管理员所需的权限 grant create user, role_admin on *.* to 'role_admin_user'@'%' with grant option;
7. 完整示例流程
-- ============================================ -- 场景：为项目设置角色体系 -- ============================================ -- step 1: 创建角色 create role 'role_read', 'role_write', 'role_dba'; -- step 2: 给角色授权 grant select on db_app.* to 'role_read'; grant select, insert, update, delete on db_app.* to 'role_write'; grant all privileges on db_app.* to 'role_dba'; -- step 3: 创建用户 create user 'zhangsan'@'%' identified by 'zspass123!'; create user 'lisi'@'%' identified by 'lspass123!'; create user 'wangwu'@'%' identified by 'wwpass123!'; -- step 4: 分配角色给用户 grant 'role_read' to 'zhangsan'@'%'; grant 'role_write' to 'lisi'@'%'; grant 'role_dba' to 'wangwu'@'%'; -- step 5: 设置默认角色（登录即自动激活） set default role 'role_read' to 'zhangsan'@'%'; set default role 'role_write' to 'lisi'@'%'; set default role 'role_dba' to 'wangwu'@'%'; -- step 6: 验证 show grants for 'zhangsan'@'%' using 'role_read'; show grants for 'lisi'@'%' using 'role_write'; show grants for 'wangwu'@'%' using 'role_dba';
到此这篇关于mysql 角色管理的实现的文章就介绍到这了,更多相关mysql 角色管理内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论