44. 用户与权限管理

44.1 权限系统概述

MySQL 的权限系统基于”什么用户、从什么主机、能做什么”的三维控制。每条权限记录包含：

• 用户（User）：username@host 组合，'app'@'localhost' 和 'app'@'%' 是两个不同账户。
• 主机（Host）：用户从哪台机器连接。localhost 表示本机，% 表示任意主机。
• 权限（Privilege）：能执行什么操作。

权限信息存储在 mysql 系统库的 user、db、tables_priv、columns_priv 等表中。

44.2 创建用户

CREATE USER 语法

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

示例：

-- 本机访问账户
CREATE USER 'app'@'localhost' IDENTIFIED BY 'StrongPass!2026';

-- 任意主机可访问
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass!2026';

-- 指定 IP 段
CREATE USER 'app'@'192.168.1.%' IDENTIFIED BY 'StrongPass!2026';

认证插件

8.0+ 默认使用 caching_sha2_password：

-- 创建时指定认证插件
CREATE USER 'app'@'%'
  IDENTIFIED WITH caching_sha2_password BY 'StrongPass!2026';

-- 兼容老客户端，使用旧插件
CREATE USER 'legacy'@'%'
  IDENTIFIED WITH mysql_native_password BY 'password';

44.3 授权（GRANT）

权限层级

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

层级	语法	适用范围
全局	ON *.*	所有数据库的所有对象
数据库	ON db_name.*	指定数据库的所有表
表	ON db_name.table_name	指定表
列	SELECT(col1,col2)	指定列（较少用）

常用权限

权限	说明
ALL PRIVILEGES	所有权限（不含 GRANT OPTION）
SELECT	查询
INSERT	插入
UPDATE	更新
DELETE	删除
CREATE	创建库/表
DROP	删除库/表
ALTER	修改表结构
INDEX	创建/删除索引
CREATE USER	创建用户
FILE	读写服务器文件（LOAD DATA / INTO OUTFILE）
GRANT OPTION	把自己的权限转授他人

授权示例

-- 授予 myapp 数据库的读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app'@'%';

-- 授予所有权限（生产环境慎用）
GRANT ALL PRIVILEGES ON myapp.* TO 'app'@'%';

-- 授予只读权限
GRANT SELECT ON myapp.* TO 'readonly'@'%';

-- 授予创建用户的权限 + 可转授
GRANT CREATE USER ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

44.4 回收权限（REVOKE）

-- 回收指定权限
REVOKE INSERT, UPDATE ON myapp.* FROM 'app'@'%';

-- 回收所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';

44.5 查看权限

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

-- 查看指定用户权限
SHOW GRANTS FOR 'app'@'%';

-- 查看用户基本信息
SELECT user, host, plugin FROM mysql.user WHERE user = 'app';

44.6 角色（ROLE）

角色是 8.0+ 引入的功能，把一组权限打包成一个”角色”，再把角色授给用户。这样权限管理更清晰。

创建角色并授权

-- 创建角色
CREATE ROLE 'app_read', 'app_write';

-- 给角色授权
GRANT SELECT ON myapp.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON myapp.* TO 'app_write';

-- 把角色授给用户
GRANT 'app_read' TO 'reader'@'%';
GRANT 'app_read', 'app_write' TO 'editor'@'%';

设置默认角色

角色授给用户后，还需要设为默认，用户登录后才自动拥有角色权限：

-- 设置默认角色（用户登录后自动激活）
SET DEFAULT ROLE 'app_read' TO 'reader'@'%';

-- 设置所有角色为默认
SET DEFAULT ROLE ALL TO 'editor'@'%';

-- 手动激活角色（当前会话）
SET ROLE 'app_write';

44.7 修改密码

-- 修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'NewPass!2026';

-- 修改指定用户密码
ALTER USER 'app'@'%' IDENTIFIED BY 'NewPass!2026';

-- SET PASSWORD（另一种写法）
SET PASSWORD FOR 'app'@'%' = 'NewPass!2026';

44.8 删除用户

DROP USER 'app'@'%';

-- 一次删除多个
DROP USER 'app'@'%', 'app'@'localhost';

44.9 常见踩坑

1. 用户存在但连不上：检查 host 是否匹配。'app'@'localhost' 和 'app'@'%' 是两个账户，从远程连接时前者不生效。
2. 权限不生效：授权后让用户重新登录。已建立的连接权限不会刷新。
3. root 无法远程连接：默认 root 是 'root'@'localhost'，远程连不上。需要创建 'root'@'%' 或单独授权。
4. FILE 权限风险：FILE 权限允许读写服务器文件，给出去要慎重，配合 secure_file_priv 限制可读写目录。
5. 角色未激活：授予角色后忘记 SET DEFAULT ROLE，用户登录后没有角色权限。

44.10 实战建议

一个典型的应用账户体系：

-- 管理员（仅本机）
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'AdminPass!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 应用账户（读写指定库）
CREATE USER 'myapp'@'10.0.0.%' IDENTIFIED BY 'AppPass!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'myapp'@'10.0.0.%';

-- 只读账户（报表/分析）
CREATE USER 'reporter'@'10.0.0.%' IDENTIFIED BY 'ReportPass!';
GRANT SELECT ON myapp.* TO 'reporter'@'10.0.0.%';

按业务需求拆分账户，每个账户只给最小权限，出问题时影响范围可控。