MySQL: MySQL用户管理与权限控制
最后更新：2026-08-26
权限控制是数据库安全的基础——不同用户只能访问被授权的数据。
本课讲解用户和权限管理。
graph TB
A[MySQL 权限层级] --> B[全局权限<br/>*.*]
A --> C[数据库权限<br/>mydb.*]
A --> D[表权限<br/>mydb.users]
A --> E[列权限<br/>mydb.users col]
B --> B1[ALL/CREATE/RELOAD...]
C --> C1[SELECT/INSERT/UPDATE/DELETE]
D --> D1[SELECT/ALTER/INDEX...]
E --> E1[SELECT col1, col2]
1. 你将学到
- CREATE USER 创建用户
- GRANT 授权
- REVOKE 撤销权限
- 权限级别（全局/数据库/表/列）
- 角色管理（8.0+）
2. 一个真实的故事
(1) 痛点：root 账号误操作酿成大祸
开发团队一直用 root 账号连接生产数据库，方便是方便，直到那天——一个开发人员误执行了 DROP TABLE users，300 万用户数据瞬间消失。备份恢复花了 4 小时，期间服务完全中断，直接损失超百万。
(2) 权限系统的解法
用最小权限原则：每个角色只授予必要的权限。开发只读+读写开发库、运维有特定库的管理权限、root 仅限 DBA 在堡垒机使用。
| 维度 | 全部用 root | 最小权限原则 |
|---|---|---|
| 误操作风险 | 极高（可 DROP 任何表） | 极低（权限受限） |
| 事故恢复 | 难（影响范围大） | 易（影响范围小） |
| 审计追踪 | 无法区分操作者 | 按用户追溯 |
| 安全等级 | ❌ | ✅ |
3. 用户管理
▶ 示例：创建用户
SQL
-- 创建本地用户
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'DevPass123!';
-- 创建允许远程连接的用户
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'RemotePass123!';
-- 创建允许特定IP连接的用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppPass123!';
▶ 示例：修改和删除用户
SQL
-- 修改密码
ALTER USER 'dev_user'@'localhost' IDENTIFIED BY 'NewPass123!';
-- 删除用户
DROP USER 'dev_user'@'localhost';
-- 查看所有用户
SELECT user, host FROM mysql.user;
4. GRANT 授权
▶ 示例：权限管理
SQL
-- 授予所有权限（管理员）
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
-- 授予特定数据库权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'dev_user'@'localhost';
-- 授予特定表权限
GRANT SELECT ON mydb.users TO 'readonly'@'localhost';
-- 授予特定列权限
GRANT SELECT (username, email) ON mydb.users TO 'limited'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;
5. 权限级别
| 级别 | 语法 | 说明 |
|---|---|---|
| 全局 | *.* |
所有数据库所有表 |
| 数据库 | mydb.* |
特定数据库所有表 |
| 表 | mydb.users |
特定表 |
| 列 | mydb.users(email) |
特定列 |
6. REVOKE 撤销权限
SQL
-- 撤销所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'dev_user'@'localhost';
-- 撤销特定权限
REVOKE INSERT, DELETE ON mydb.* FROM 'dev_user'@'localhost';
FLUSH PRIVILEGES;
7. 查看权限
SQL
-- 查看当前用户权限
SHOW GRANTS;
-- 查看指定用户权限
SHOW GRANTS FOR 'dev_user'@'localhost';
8. 角色管理（MySQL 8.0+）
SQL
-- 创建角色
CREATE ROLE 'app_read', 'app_write';
-- 给角色授权
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
-- 给用户分配角色
GRANT 'app_read', 'app_write' TO 'dev_user'@'localhost';
-- 激活角色
SET DEFAULT ROLE ALL TO 'dev_user'@'localhost';
❓ 常见问题
Q
'user'@'localhost' 和 'user'@'%' 有什么区别？A localhost 只允许本地连接，% 允许任意主机连接。
Q GRANT 后需要 FLUSH PRIVILEGES？
A 直接 GRANT 不需要。直接修改 mysql.user 表才需要。
Q 忘记 root 密码怎么办？
A 跳过权限验证启动，修改密码后重启。
Q WITH GRANT OPTION 是什么？
A 允许用户把自己的权限授予别人。授予此权限需谨慎，避免权限扩散。
Q 角色和用户区别？
A 角色是权限集合，不能直接登录；用户登录后用 SET ROLE 激活角色。
📖 小节
- CREATE USER 创建用户，指定主机和密码
- GRANT 授权，REVOKE 撤销权限
- 权限级别：全局 → 数据库 → 表 → 列
- 角色（8.0+）批量管理权限
- FLUSH PRIVILEGES 刷新权限
📝 作业
-
基础题（难度⭐）：创建只读用户，只能查询 mydb 数据库。
-
进阶题（难度⭐⭐）：创建角色并分配给用户。
-
挑战题（难度⭐⭐⭐）：设计权限方案：开发人员读写、运营只读、管理员全权。
