🗄️ 第02章 · 用户与权限

CREATE USER GRANT 权限体系

用户与权限¶

数据库不是 root 一个人的玩具。应用要一个只读写自己库的账号，报表要一个只读账号，DBA 要一个管理账号——用户与权限决定了“谁能进来、进来能干什么”。本章讲清楚 MySQL 的账号模型和从零开始的最小权限授权。

用户到底是什么¶

MySQL 的用户不是简单的一个名字，而是 “用户名 + 来源主机” 的组合，写作 'user'@'host'。同一个用户名可以来自不同主机，各自拥有一套独立密码和权限。判断当前连接到底是哪个用户、从哪来，用：

SELECT CURRENT_USER() AS 当前有效用户, @@hostname AS 主机;
-- 结果示例: bangdream@localhost | hostname

易错点：'bangdream'@'localhost'（只走 socket 本机登录）和 'bangdream'@'%'（任意主机）是两个完全不同、互不相干的账号。前者无法远程登录，后者权限给到了全网。给账号授权时一定要明确主机范围。

认证插件¶

MySQL 8 默认认证插件是 caching_sha2_password，它比旧版更安全，但要求客户端或驱动能支持它。mysql_native_password 是老的、仍然兼容的方式，但不推荐新用户使用。查看某账号用哪个插件：

-- 查看当前服务器上非系统内置的用户及其插件
SELECT user, host, plugin FROM mysql.user
 WHERE user NOT IN ('mysql.sys','mysql.session','mysql.infoschema');
-- 示例: bangdream|localhost|caching_sha2_password
--       lovelive|localhost|caching_sha2_password
--       root|localhost|auth_socket

root 在这里是 auth_socket，即“系统 shell 里的 root 用户才能登录”，这是 Ubuntu 的默认安全配置。

创建与删除用户¶

CREATE USER 负责“造人”，GRANT 负责“发证”，两者分开（也可以 GRANT 一条命令同时建号并授权）。

-- 创建用户：账号 bangdream，来源主机 localhost，并设置密码
CREATE USER 'bangdream'@'localhost' IDENTIFIED BY 'GrxzyyrRQjOv0P2P0nXG';

-- 创建可用任意主机连接的账号（仅测试用，生产慎用）
CREATE USER 'report'@'%' IDENTIFIED BY 'only-read-password';

-- 修改已有账号的密码
ALTER USER 'bangdream'@'localhost' IDENTIFIED BY 'new-password';

-- 删除用户（连带清掉它的所有权限）
DROP USER 'report'@'%';

-- 查看某账号当前的权限
SHOW GRANTS FOR 'bangdream'@'localhost';

权限层级与授权¶

MySQL 的权限是分层级的：全局、库、表、列，层级越往下越细。常用权限一览如下：

权限	作用	适用层级
`ALL PRIVILEGES`	全部权限	全局/库/表
`SELECT` / `INSERT` / `UPDATE` / `DELETE`	查询、插入、更新、删除数据	库/表
`CREATE` / `ALTER` / `DROP`	建库建表、改结构、删对象	全局/库/表
`INDEX`	建索引	表
`PROCESS`	看所有线程（`SHOW PROCESSLIST` 全部）	全局
`SUPER`	高级管理（改全局变量、停从库等）	全局
`REPLICATION SLAVE`	主从复制的从库拉取 binlog	全局
`REPLICATION CLIENT`	查看主从状态	全局

GRANT 和 REVOKE 是最核心的两个动宾对，语法永远是 “权限 + ON 对象 + TO 用户” 或 “权限 + ON 对象 + FROM 用户”：

-- 授权：给 bangdream 本机账号只读 + 改自己库的权限
GRANT SELECT, INSERT, UPDATE, DELETE ON bangdream.* TO 'bangdream'@'localhost';

-- 授权：给报表账号只读整个库
GRANT SELECT ON lovelive.* TO 'report'@'%';

-- 授权：授予跨库只读权限（用 *.* 表示所有库）
GRANT SELECT ON *.* TO 'report'@'%';

-- 撤销：收回权限
REVOKE INSERT, UPDATE, DELETE ON bangdream.* FROM 'bangdream'@'localhost';

-- 配置权限变更后刷新权限表（修改 mysql.user 后必须执行）
FLUSH PRIVILEGES;

最小权限原则¶

安全提示：永远不要给业务应用授 ALL PRIVILEGES ON *.* 甚至 SUPER。这是典型安全事故的源头——一旦应用被注入或拿到这个账号，等于把整个服务器拱手让人。正确的姿势是：应用只需要自己那个库的那几种 DML 权限。

一个规范的授权流程通常是：

只给应用建 '应用名'@'localhost' 这样的本机账号（如果应用没在本机，就限定到固定的部署机 IP，而不是 %）。
只授予应用真正用到的权限（多数 Web 应用只需 SELECT, INSERT, UPDATE, DELETE，连 DDL 都交给迁移工具用管理员账号做）。
把密码放到只有 root 可读的配置文件里，用上一章提到的 --defaults-extra-file 注入。

案例场景：为 bangdream 应用配置最小权限账号¶

这台服务器的 bangdream 库配套了一个应用，我们需要一个“只能读写 bangdream 库”的账号，绝不能碰其它库：

# 先用 root 登录（本机走 auth_socket）
sudo mysql

# 建库账号并只授予它 bangdream 的增删改查
CREATE USER 'bangdream_app'@'localhost' IDENTIFIED BY 'StrongAppPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON bangdream.* TO 'bangdream_app'@'localhost';

# 验证：带用户翻一遍它的权限
SHOW GRANTS FOR 'bangdream_app'@'localhost';
-- 结果示例: GRANT USAGE ON *.* TO `bangdream_app`@`localhost`
--           GRANT SELECT, INSERT, UPDATE, DELETE ON `bangdream`.* TO `bangdream_app`@`localhost`

易错点：GRANT USAGE ON *.* 并不是“给了全库权限”，它只是表示这个账号存在但没有任何实际权限，是创建用户时自动附带的空壳。真正决定它能不能干嘛的是后面那条对 bangdream.* 的授权。

案例场景：失败回退与权限排查¶

当应用报“Access denied”，排查顺序是：账号是否存在 → 主机范围是否匹配 → 插件是否兼容 → 权限是否到位。

-- 1) 这个用户到底存不存在？从哪来？
SELECT user, host, plugin FROM mysql.user WHERE user='bangdream_app';

-- 2) 名字 + 主机都配对的情况下，可能是密码认证方式不匹配
--    看它用的插件，若是 caching_sha2_password 而旧驱动只认 native，则改回：
-- ALTER USER 'bangdream_app'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pwd';

-- 3) 用该账号实际登录试一下权限
-- mysql -u bangdream_app -p bangdream -e "SELECT COUNT(*) FROM bands;"

权限变更的后手¶

GRANT / REVOKE 之后，涉及 mysql.user 表的直接修改（比如 UPDATE mysql.user SET ...）必须 FLUSH PRIVILEGES 才会生效；而通过 GRANT / REVOKE / CREATE USER 做的变更会自动生效，无需 flush。这一点容易记混。

-- 用 GRANT/REVOKE/CREATE USER 后无需 flush，立即生效
GRANT SELECT ON lovelive.* TO 'report'@'localhost';

-- 只有直接改 mysql.user / mysql.db 等权限表后才需要
UPDATE mysql.user SET plugin='caching_sha2_password' WHERE user='report' AND host='localhost';
FLUSH PRIVILEGES;

给一个账号“能看所有线程”排查问题，但绝不给它写的权限，这种只读管理员也很常用：

-- 只读排查账号：能看全部连接、能只读所有库，但没任何写权限
CREATE USER 'ops'@'localhost' IDENTIFIED BY 'OpsReadOnly#2024';
GRANT SELECT, PROCESS ON *.* TO 'ops'@'localhost';
SHOW GRANTS FOR 'ops'@'localhost';