6.3.4 最小权限与角色管理实践

在运维实践中，最小权限原则（PoLP）要求用户仅拥有完成工作所需的最小权限，避免过度授权带来的数据泄露与误操作风险。本节围绕 MySQL 的权限粒度、角色管理与落地流程给出可操作的方案，并提供可执行示例、排错与练习。

最小权限设计要点#

按职责分离权限：将账号划分为只读、读写、DDL、运维、审计等角色，避免“一号通吃”。
最小授权对象：优先授权到库/表/列级，避免全局权限；仅在必要时使用 *.*。
限制敏感权限：SUPER、FILE、PROCESS、RELOAD、SHUTDOWN 等权限仅授予运维管理员。
禁用危险操作：在生产环境避免授予 DROP、ALTER 等高风险权限给业务账号。
分环境隔离：开发、测试、生产分离，生产环境账号权限更严格。

角色管理实践（MySQL 8.0+）#

1. 创建角色#

CREATE ROLE 'role_readonly';
CREATE ROLE 'role_rw';
CREATE ROLE 'role_ddl';
CREATE ROLE 'role_dba';

2. 为角色授权（含命令说明）#

-- 只读角色：仅允许查询
GRANT SELECT ON appdb.* TO 'role_readonly';

-- 读写角色：允许增删改查
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'role_rw';

-- DDL角色：允许结构变更（生产环境需严格审批）
GRANT ALTER, CREATE, INDEX, DROP ON appdb.* TO 'role_ddl';

-- DBA角色：系统级权限（谨慎）
GRANT PROCESS, RELOAD, REPLICATION CLIENT ON *.* TO 'role_dba';

关键命令解释
- GRANT SELECT ON appdb.*：授权到库级，避免全局 *.*。
- PROCESS：查看线程信息，用于排查阻塞。
- RELOAD：刷新权限与日志，等同 FLUSH 相关操作。

3. 绑定角色到用户（含来源限制）#

CREATE USER 'app_ro'@'10.%' IDENTIFIED BY 'StrongPass!';
GRANT 'role_readonly' TO 'app_ro'@'10.%';
SET DEFAULT ROLE 'role_readonly' TO 'app_ro'@'10.%';

CREATE USER 'app_rw'@'10.%' IDENTIFIED BY 'StrongPass!';
GRANT 'role_rw' TO 'app_rw'@'10.%';
SET DEFAULT ROLE 'role_rw' TO 'app_rw'@'10.%';

4. 角色继承与组合#

-- 组合授权：开发账号拥有读写+DDL（仅限非生产）
CREATE USER 'app_dev'@'10.%' IDENTIFIED BY 'StrongPass!';
GRANT 'role_rw', 'role_ddl' TO 'app_dev'@'10.%';
SET DEFAULT ROLE ALL TO 'app_dev'@'10.%';

5. 验证权限是否生效（可执行检查）#

-- 登录后查看当前用户权限
SHOW GRANTS;

-- 验证默认角色
SELECT CURRENT_ROLE();

预期效果
- SHOW GRANTS 应仅显示该角色允许的权限范围。
- CURRENT_ROLE() 显示默认启用的角色名称。

权限回收与审计（含流程命令）#

-- 回收角色
REVOKE 'role_ddl' FROM 'app_dev'@'10.%';

-- 删除账号
DROP USER 'legacy_user'@'%';

-- 刷新权限（可选）
FLUSH PRIVILEGES;

审计建议
- 开启 general_log（短期排查）或 audit_log（长期审计）。
- 定期导出权限列表备审计。

-- 导出所有账号与权限（示例）
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'app_rw'@'10.%';

常见问题与排错#

1. 角色未生效#

现象：SHOW GRANTS 没有权限，操作报错 ERROR 1142
排查与修复：

-- 检查角色是否设置为默认角色
SELECT DEFAULT_ROLE_USER, DEFAULT_ROLE_HOST FROM mysql.default_roles
WHERE USER='app_rw' AND HOST='10.%';

-- 重新设置默认角色
SET DEFAULT ROLE 'role_rw' TO 'app_rw'@'10.%';

2. 授权成功但无法登录#

现象：ERROR 1045 (28000): Access denied
排查与修复：

-- 检查来源是否匹配
SELECT user, host FROM mysql.user WHERE user='app_rw';

-- 若来源不匹配，新增正确来源
CREATE USER 'app_rw'@'10.1.%' IDENTIFIED BY 'StrongPass!';
GRANT 'role_rw' TO 'app_rw'@'10.1.%';
SET DEFAULT ROLE 'role_rw' TO 'app_rw'@'10.1.%';

3. 误授全局权限#

现象：SHOW GRANTS 出现 ON *.*
修复：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_rw'@'10.%';
GRANT 'role_rw' TO 'app_rw'@'10.%';
SET DEFAULT ROLE 'role_rw' TO 'app_rw'@'10.%';

练习（含预期结果）#

创建只读角色并授予到表级
- 目标：仅允许访问 appdb.orders 表。
- 预期：对 appdb.users 执行 SELECT 失败。

CREATE ROLE 'role_orders_ro';
GRANT SELECT ON appdb.orders TO 'role_orders_ro';
CREATE USER 'orders_ro'@'10.%' IDENTIFIED BY 'StrongPass!';
GRANT 'role_orders_ro' TO 'orders_ro'@'10.%';
SET DEFAULT ROLE 'role_orders_ro' TO 'orders_ro'@'10.%';

验证权限最小化
- 目标：orders_ro 执行 INSERT 报错。

-- 使用 orders_ro 登录执行：
INSERT INTO appdb.orders(id) VALUES(1);

预期报错：ERROR 1142 (42000): INSERT command denied

回收权限并确认

REVOKE 'role_orders_ro' FROM 'orders_ro'@'10.%';
SHOW GRANTS FOR 'orders_ro'@'10.%';

预期效果：权限列表中不再包含 SELECT ON appdb.orders。

最佳实践建议#

角色模板化：为不同业务线提供标准化角色模板，统一授权策略。
账号生命周期管理：创建、变更、回收均需工单或审批记录。
限制登录来源：绑定账号来源网段，减少暴露面。
只读账号优先：运维查询优先使用只读账号，避免误操作。

通过角色化授权与最小权限实践，可显著降低生产风险，同时提升权限管理的可追溯性和可维护性。