第 15 章 用户与权限管理

📖 本篇简介：MySQL 是一个多用户数据库系统，谁能连进来、能看哪些库、能改哪些表，全靠用户与权限体系控制。本篇假设你已经能用 root 登录 MySQL、会写基本 DDL/DML。学完之后，你将能按业务需要创建最小权限账号、用 GRANT/REVOKE 精确授权、回收权限，并说出生产环境有哪些安全红线。本示例基于 MySQL 8.0。

目录

• 1 MySQL 账号体系概述
• 2 创建用户 CREATE USER
• 3 查看 修改 删除用户
• 4 GRANT 授权详解
• 5 REVOKE 撤权
• 6 权限粒度层级
• 7 权限速查表
• 8 安全实践建议
• 9 常见坑与注意事项
• 10 练习题

1 MySQL 账号体系概述

MySQL 的账号不是一个单纯的"用户名"，而是 用户名 + 来源主机 的组合。也就是说，'app'@'localhost' 和 'app'@'192.168.1.%' 是两个完全不同的账号，可以配不同的密码和权限。

所有账号信息存在系统库 mysql 的 user 表里。登录时 MySQL 会按"最匹配主机"的原则找到对应账号，再校验密码。

-- 本示例基于 MySQL 8.0
-- 查看当前有哪些用户（需要 root 或有 mysql.user 权限的账号）
SELECT user, host, account_locked FROM mysql.user ORDER BY user;

-- MySQL 8.0 默认使用 caching_sha2_password 认证插件
-- 查看某个用户用的认证插件
SELECT user, host, plugin FROM mysql.user WHERE user = 'root';

一个生产库通常至少有这几类账号：

| 账号类型 | 用途 | 权限特点 |

|---|---|---|

| root | 超级管理员 | 仅本机登录，绝不对外开放 |

| 应用账号 | 业务程序连接 | 只给业务库的增删改查 |

| 只读账号 | 报表/分析 | 只有 SELECT |

| 运维账号 | DBA 部署/迁移 | 有限的 ALL，可建库 |

1.1 连接时的账号匹配规则

MySQL 收到连接请求后，会按"主机匹配度最高"的原则在 mysql.user 里挑一条记录来校验密码。比如同时存在 'app'@'localhost' 和 'app'@'%'，从本机连进来时优先用前者，从其他机器连进来才用后者。这就是为什么同一个用户名在不同密码下都能登录成功——它其实命中的是不同的账号记录。

-- 排查"为什么我这个密码能/不能登录"时，先确认命中了哪条记录
SELECT user, host, plugin FROM mysql.user WHERE user = 'app';

2 创建用户 CREATE USER

MySQL 8.0 推荐先用 CREATE USER 建账号，再用 GRANT 授权（早期版本"授权即建号"的写法在 8.0 已不推荐）。

-- 创建一个只能从本机登录的应用账号
CREATE USER 'app'@'localhost'
    IDENTIFIED BY 'App@Strong#2026';   -- 密码尽量复杂，包含大小写数字符号

-- 创建一个允许从指定网段登录的账号（% 是通配符）
CREATE USER 'app_rw'@'10.0.0.%'
    IDENTIFIED BY 'Rw@Strong#2026';

-- 创建一个只读账号，限制登录过期时间为 90 天
CREATE USER 'report'@'10.0.0.%'
    IDENTIFIED BY 'Report@2026'
    PASSWORD EXPIRE INTERVAL 90 DAY;

-- 创建一个锁定状态的账号（建好先锁着，审批通过再解锁）
CREATE USER 'temp'@'%' IDENTIFIED BY 'Tmp@2026' ACCOUNT LOCK;

主机部分的写法规则：

| 主机写法 | 含义 |

|---|---|

| localhost | 只能本机通过 socket/127.0.0.1 登录 |

| 127.0.0.1 | 只能从 127.0.0.1 登录 |

| 10.0.0.% | 10.0.0.0/24 网段可登录 |

| 10.0.0.0/255.255.255.0 | 子网掩码形式 |

| % | 任意主机都能登录（极不安全，生产慎用） |

3 查看 修改 删除用户

-- 修改密码（MySQL 8.0 推荐用 ALTER USER）
ALTER USER 'app'@'localhost' IDENTIFIED BY 'NewApp@2026';

-- 修改当前登录账号自己的密码（不需要知道旧密码以外的权限）
ALTER USER USER() IDENTIFIED BY 'MyNewPass@2026';

-- 解锁被锁定的账号
ALTER USER 'temp'@'%' ACCOUNT UNLOCK;

-- 查看某个账号已经被授予了哪些权限
SHOW GRANTS FOR 'app'@'localhost';

-- 删除用户（同时会自动回收它的所有权限）
DROP USER 'temp'@'%';

注意：改密码、删用户后，该账号已经建立的连接不会立刻断开，新密码只对之后新建立的连接生效。要踢掉旧连接，需要用 KILL <进程ID>。

3.1 查看当前登录身份

排障时经常需要确认"我现在是以哪个账号、从哪台机器连进来的"：

-- 当前登录用户和来源 IP
SELECT CURRENT_USER(), USER();
-- CURRENT_USER() 表示实际命中的账号记录，USER() 表示客户端实际连接信息

-- 当前会话拥有的全部权限
SHOW GRANTS;

CURRENT_USER() 和 USER() 不一样：前者是 MySQL 根据主机匹配选中的账号，后者是客户端发起连接时声明的账号名。当 'app'@'localhost' 和 'app'@'%' 同时存在时，这个区别特别有用。

4 GRANT 授权详解

GRANT 用来把权限授予账号。语法骨架：

GRANT 权限列表 ON 库.表 TO '用户'@'主机' [WITH GRANT OPTION];

-- 给应用账号授予 shop 库所有表的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE
    ON shop.*
    TO 'app'@'localhost';

-- 给只读账号授予整个 shop 库的只读权限
GRANT SELECT ON shop.* TO 'report'@'10.0.0.%';

-- 授予某一张表的全部权限（含索引、建约束等）
GRANT ALL PRIVILEGES ON shop.orders TO 'app_rw'@'10.0.0.%';

-- 列级权限：只允许修改 user 表的 nickname 一列
GRANT UPDATE (nickname) ON shop.user TO 'app'@'localhost';

-- WITH GRANT OPTION：允许该账号把自己拥有的权限再转授给别人（慎用）
GRANT SELECT ON shop.* TO 'analyst'@'10.0.0.%' WITH GRANT OPTION;

-- 授权后刷新权限（一般 GRANT 会自动生效，FLUSH 用于手动改过 mysql.user 后）
FLUSH PRIVILEGES;

ON 库.表 的通配规则：

| 写法 | 含义 |

|---|---|

| *.* | 所有库所有表（超级权限） |

| shop.* | shop 库下所有表 |

| shop.orders | 只针对 orders 这一张表 |

| shop.tmp_% | shop 库下以 tmp_ 开头的表 |

4.1 常用业务角色模板

实际项目里，下面这几套账号模板可以直接套用：

| 角色 | 权限组合 | 用途 |

|---|---|---|

| 应用读写 | SELECT,INSERT,UPDATE,DELETE ON 业务库.* | 后端服务连接 |

| 报表只读 | SELECT ON 业务库.* | 数据分析跑报表 |

| 迁移运维 | ALL ON 业务库.* 但不含 GRANT | DBA 部署数据 |

| 开发测试 | ALL ON 测试库.* | 开发环境随便造 |

-- 一键生成"应用读写"账号的模板
CREATE USER 'app_rw'@'10.0.0.%' IDENTIFIED BY 'Rw@Strong#2026';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_rw'@'10.0.0.%';

-- 一键生成"报表只读"账号的模板
CREATE USER 'report'@'10.0.0.%' IDENTIFIED BY 'Report@2026';
GRANT SELECT ON shop.* TO 'report'@'10.0.0.%';

4.2 权限的传递与回收细节

WITH GRANT OPTION 让账号能把自己的权限转授出去，形成授权链。回收时要注意：先收 GRANT OPTION，再收具体权限，否则会报错或收不干净。

-- 正确的撤权顺序
REVOKE GRANT OPTION ON shop.* FROM 'analyst'@'10.0.0.%';
REVOKE SELECT ON shop.* FROM 'analyst'@'10.0.0.%';

5 REVOKE 撤权

REVOKE 与 GRANT 对应，把权限收回来。

-- 收回 app_rw 对 orders 表的删除权限
REVOKE DELETE ON shop.orders FROM 'app_rw'@'10.0.0.%';

-- 收回只读账号对整个 shop 库的所有权限
REVOKE SELECT ON shop.* FROM 'report'@'10.0.0.%';

-- 收回"转授权限"
REVOKE GRANT OPTION ON shop.* FROM 'analyst'@'10.0.0.%';

-- 查看撤权后的结果
SHOW GRANTS FOR 'app_rw'@'10.0.0.%';

一个常见误区：REVOKE 的权限列表必须和当初 GRANT 的颗粒度一致，否则收不掉。比如当初给的是 ON shop.*，你用 ON shop.orders 去 REVOKE 是收不掉的。

6 权限粒度层级

MySQL 权限分四个层级，从大到小：

| 层级 | 写法 | 典型权限 |

|---|---|---|

| 全局层级 | ON *.* | FILE、PROCESS、RELOAD、REPLICATION SLAVE 等 |

| 数据库层级 | ON shop.* | CREATE、DROP、ALTER、INDEX |

| 表层级 | ON shop.orders | SELECT、INSERT、UPDATE、DELETE |

| 列/例行层级 | UPDATE (col) / EXECUTE ON PROCEDURE | 列级 UPDATE、存储过程执行权 |

设计原则是最小权限：应用账号只给它真正用到的库和表，绝不给 *.*，更不要随便给 SUPER、FILE、GRANT OPTION。

-- 反面教材：给应用账号超级权限，一旦程序被注入后果不堪设想
-- GRANT ALL PRIVILEGES ON *.* TO 'app'@'%';   -- 千万不要这样

-- 正确做法：精确到库、精确到常用 DML
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'10.0.0.%';

7 权限速查表

| 权限 | 作用 | 适用层级 |

|---|---|---|

| SELECT | 查询数据 | 库/表/列 |

| INSERT | 插入数据 | 库/表/列 |

| UPDATE | 更新数据 | 库/表/列 |

| DELETE | 删除数据 | 库/表 |

| CREATE | 建库/建表 | 库/表 |

| DROP | 删库/删表 | 库/表 |

| ALTER | 修改表结构 | 库/表 |

| INDEX | 建/删索引 | 库/表 |

| GRANT OPTION | 转授权限 | 库/表/过程 |

| ALL PRIVILEGES | 除 GRANT OPTION 外的全部 | 视层级 |

| 命令 | 作用 |

|---|---|

| CREATE USER u@h IDENTIFIED BY 'p'; | 建账号 |

| ALTER USER u@h IDENTIFIED BY 'p'; | 改密码 |

| GRANT ... ON db.* TO u@h; | 授权 |

| REVOKE ... ON db.* FROM u@h; | 撤权 |

| SHOW GRANTS FOR u@h; | 查看权限 |

| DROP USER u@h; | 删账号 |

| FLUSH PRIVILEGES; | 重载权限表 |

记忆口诀：先建号、再授权、撤权要对齐、删除用 DROP。日常运维里最常用的就是 SHOW GRANTS 和 GRANT/REVOKE 三件套。

8 安全实践建议

1. root 只允许本机登录：root'@'localhost'，不要建 root'@'%'。

2. 禁用远程 root：任何能从外网连到 3306 还能用 root 的做法都是事故温床。

3. 密码强度：开启 validate_password 组件，强制长度、大小写、数字、特殊字符。

4. 账号按用途拆分：应用读写、报表只读、运维各用各的账号，互不混用。

5. 最小权限：能给列级不给表级，能给表级不给库级。

6. 定期审计：定期跑 SELECT user,host FROM mysql.user; 和 SHOW GRANTS，清理离职/废弃账号。

7. 不要在代码里硬编码 root 密码，用配置中心或环境变量。

8. 定期做账号审计：每季度导出一次 mysql.user 和 SHOW GRANTS，对照在职人员清单核对，清理离职账号和废弃临时账号。权限管理是个持续动作，不是一次性配置。

-- MySQL 8.0 安装 validate_password 组件并设置强度策略
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = 'STRONG';   -- 强策略
SET GLOBAL validate_password.length = 12;        -- 最少 12 位

8.1 账号与权限的存储结构

MySQL 把账号和权限存在 mysql 系统库的几张表里，理解这张图能帮你排查"为什么我授权了却不生效"：

| 系统表 | 存什么 |

|---|---|

| mysql.user | 全局账号、密码、全局权限 |

| mysql.db | 数据库级权限 |

| mysql.tables_priv | 表级权限 |

| mysql.columns_priv | 列级权限 |

| mysql.procs_priv | 存储过程/函数权限 |

权限校验是按"全局 → 库 → 表 → 列"逐层叠加的，最终权限是各层的并集。

-- 直接查系统表看某个账号的库级权限（少用，优先用 SHOW GRANTS）
SELECT db, user, host, Select_priv, Insert_priv, Update_priv
FROM mysql.db WHERE user = 'app';

8.2 密码策略与密码过期

MySQL 8.0 提供了一整套密码管理能力，适合企业级合规要求：

-- 让某个账号的密码过期，下次登录强制改
ALTER USER 'app'@'localhost' PASSWORD EXPIRE;

-- 密码改了之后不能立刻重用最近 5 次
ALTER USER 'app'@'localhost' PASSWORD HISTORY 5;

-- 账号锁定：连续输错 3 次密码锁 1 天
CREATE USER 'ops'@'10.0.0.%' IDENTIFIED BY 'Ops@2026'
    FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;

8.3 角色 Role（MySQL 8.0 新特性）

当账号多了，逐个 GRANT 很烦。MySQL 8.0 引入角色（Role）：把一组权限打包成一个角色，再把角色授予账号。

-- 1. 创建角色
CREATE ROLE 'shop_read', 'shop_write';

-- 2. 给角色授权
GRANT SELECT ON shop.* TO 'shop_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_write';

-- 3. 把角色授予账号
GRANT 'shop_read' TO 'report'@'10.0.0.%';
GRANT 'shop_write' TO 'app'@'10.0.0.%';

-- 4. 账号登录后激活角色（或设为默认激活）
SET DEFAULT ROLE ALL TO 'report'@'10.0.0.%';

这样以后权限调整只要改角色，所有挂这个角色的账号自动生效，管理成本大幅下降。

8.4 审计账号的日常命令清单

| 操作 | 命令 |

|---|---|

| 列全部账号 | SELECT user,host FROM mysql.user; |

| 看某账号权限 | SHOW GRANTS FOR 'u'@'h'; |

| 看当前会话权限 | SHOW GRANTS; |

| 删账号 | DROP USER 'u'@'h'; |

| 改密码 | ALTER USER 'u'@'h' IDENTIFIED BY 'p'; |

| 踢连接 | KILL <id>; |

9 常见坑与注意事项

坑 1：建号时忘了写主机部分，结果变成 `'app'@'%'`。

CREATE USER 'app' IDENTIFIED BY '...' 等价于 'app'@'%'，意味着全世界都能连。生产环境必须显式写明主机或网段。

坑 2：以为 GANK 后立即对已连接会话生效。

GRANT/REVOKE 只影响新建立的连接，已经连上的会话权限不变。要让对方立刻感受到，需要 KILL 掉旧连接。

坑 3：8.0 里还在用 GRANT 隐式建号。

MySQL 8.0 已经不支持"GRANT 不存在的用户自动创建"（需要 sql_mode 配合，且不推荐）。必须先 CREATE USER 再 GRANT。

-- 8.0 下这句会报错：You must have privileges to create user
-- GRANT SELECT ON shop.* TO 'nobody'@'localhost' IDENTIFIED BY 'x';
-- 正确分两步：
CREATE USER 'nobody'@'localhost' IDENTIFIED BY 'Xx@2026';
GRANT SELECT ON shop.* TO 'nobody'@'localhost';

坑 4：密码里有特殊字符导致命令行登录失败。

!、# 在 shell 里有特殊含义，命令行传密码时要么用 --password=xxx 加引号，要么干脆只敲 -p 让它交互式输入。

坑 5：把 DROP/ALTER 权限给了应用账号。

应用被 SQL 注入后，攻击者 DROP TABLE 都能执行。应用账号只给 SELECT/INSERT/UPDATE/DELETE，结构变更走 DBA 工单。

坑 6：用 `%` 通配主机却误以为很安全。

'app'@'%' 意味着任意 IP 都能尝试连接，哪怕有密码，也暴露在暴力破解风险下。必须限定到具体网段。

坑 7：忘记回收离职人员的临时账号。

开通容易收回难，临时账号往往被遗忘。建议每季度跑一次账号审计，对照在职名单清理。

10 练习题

练习 1：创建一个账号 crm_app，允许从 172.16.0.% 登录，密码 Crm@2026#Sec，并只给它 crm_db 库的 SELECT/INSERT/UPDATE 权限。

<details>

<summary>参考答案</summary>

CREATE USER 'crm_app'@'172.16.0.%' IDENTIFIED BY 'Crm@2026#Sec';
GRANT SELECT, INSERT, UPDATE ON crm_db.* TO 'crm_app'@'172.16.0.%';
SHOW GRANTS FOR 'crm_app'@'172.16.0.%';

</details>

练习 2：上面那个账号后来不需要 INSERT 权限了，怎么收回？

<details>

<summary>参考答案</summary>

REVOKE INSERT ON crm_db.* FROM 'crm_app'@'172.16.0.%';

注意 REVOKE 的粒度要和当初 GRANT 一致，都是 crm_db.*。

</details>

练习 3：'app'@'localhost' 和 'app'@'%' 是同一个账号吗？为什么？

<details>

<summary>参考答案</summary>

不是。MySQL 账号由"用户名+主机"共同决定，它们是两个独立账号，可以有不同密码和权限。登录时 MySQL 按主机匹配度选择用哪一个，localhost 比 % 更具体、优先级更高。

</details>

练习 4：为什么不建议给应用账号 GRANT ALL PRIVILEGES ON *.*？

<details>

<summary>参考答案</summary>

违背最小权限原则。一旦应用被 SQL 注入或代码泄露，攻击者可以 DROP DATABASE、读任意库、甚至用 FILE 权限读写服务器文件。应只给业务库的常用 DML 权限。

</details>

练习 5：改了某个账号的密码，为什么它还能连着旧密码继续操作？

<details>

<summary>参考答案</summary>

改密码只影响之后新建立的连接，已经建立的会话仍然有效。要立刻踢掉，需要 SHOW PROCESSLIST 找到连接 ID，再 KILL <id>。

</details>

练习 6：MySQL 8.0 的角色（Role）解决了什么问题？怎么用？

<details>

<summary>参考答案</summary>

账号多了以后逐个 GRANT 维护成本高。Role 把一组权限打包，再授予多个账号，改权限只改角色即可。用法：CREATE ROLE → GRANT 权限给角色 → GRANT 角色给账号。

CREATE ROLE 'rw';
GRANT SELECT,INSERT,UPDATE ON shop.* TO 'rw';
GRANT 'rw' TO 'app'@'10.0.0.%';

</details>

练习 7：为什么不建议给应用账号 FILE 权限？

<details>

<summary>参考答案</summary>

FILE 权限允许 MySQL 读写服务器本地文件（LOAD DATA INFILE / SELECT ... INTO OUTFILE）。一旦应用被注入，攻击者可以读服务器敏感文件、写 webshell，等于拿了服务器的文件系统控制权。应用账号绝不能给 FILE、PROCESS、SUPER 这类全局权限。

</details>

练习 8：GRANT SELECT ON shop.* TO 'a'@'%'; 之后为什么还要 FLUSH PRIVILEGES？什么时候不需要？

<details>

<summary>参考答案</summary>

用 GRANT/CREATE USER 等正规语句授权后，MySQL 会自动把权限加载到内存，不需要 FLUSH。FLUSH PRIVILEGES 只在你直接用 SQL 改了 mysql.user 这类系统表（绕过 GRANT）时才需要，让修改重新生效。养成用 GRANT 而不是直接改系统表的习惯，就基本用不到 FLUSH。

</details>

本篇小结：权限管理的精髓就两条——账号按用途拆分、权限按最小粒度授予。记住"用户名+主机"才是完整账号，授权后已建立的连接要 KILL 才生效；账号多了用 Role 统一管理。

>

安全红线：root 绝不远程登录，应用账号绝不给 FILE/SUPER 等全局权限。

下一篇预告：第 16 章 备份与恢复