5.3 用户与权限管理：别把总钥匙给每个人

📖 本节导读

• 理解为什么不能所有人都用 root 账号操作数据库
• 掌握用户管理：CREATE USER、ALTER USER 改密码、DROP USER
• 掌握权限管理：GRANT 授权、SHOW GRANTS 查看、REVOKE 收回
• 完整演练：创建一个"只读"用户，亲眼看到它越权操作时的报错

---

一、为什么不能人人都用 root

到目前为止，我们一直用 root 账号登录 MySQL。root 是超级管理员，拥有至高无上的权力：建库删库、建表删表、增删改查、管理其他用户……无所不能。

打个比方：root 就是你家的总钥匙，能打开每一扇门、每一个保险柜。你会把总钥匙复制一把交给每个来访的客人吗？当然不会——

• 客人不小心碰坏东西怎么办？（误操作 DROP DATABASE，整个库瞬间蒸发）
• 客人心怀不轨怎么办？（把数据偷走或恶意篡改）
• 出了事查不清是谁干的怎么办？（所有人都叫 root，无法追责）

正确的做法是：按需发放"专用钥匙"。比如：

• 只需要看数据的报表人员 → 给一把"只能开阅览室"的钥匙（只有 SELECT 权限）
• 负责录入成绩的老师 → 给"能看能录"的钥匙（SELECT + INSERT + UPDATE）
• root 总钥匙 → 锁进抽屉，只在必要的管理操作时拿出来

这就是本节的主题：用户（账号）与权限（这个账号能干什么）。以下操作请先用 root 登录执行。

二、创建用户：CREATE USER

语法：

CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';

例：创建一个叫 reader 的用户，密码为 Reader@123：

CREATE USER 'reader'@'localhost' IDENTIFIED BY 'Reader@123';

Query OK, 0 rows affected (0.02 sec)

'主机'部分是什么意思？

MySQL 里的用户不只是一个名字，而是 "用户名 + 从哪里连过来" 的组合：

写法	含义
'reader'@'localhost'	只允许从本机登录
'reader'@'%'	允许从任何机器远程登录（% 是通配符，代表任意主机）
'reader'@'192.168.1.100'	只允许从指定 IP 的机器登录

注意：'reader'@'localhost' 和 'reader'@'%' 在 MySQL 眼里是两个不同的用户，可以有不同的密码和权限。新手自己练习时用 localhost 就够了，也更安全。

三、授予权限：GRANT

刚创建的用户几乎什么都干不了——它连我们的 school 库都看不见。要让它能干活，需要 root 授权：

GRANT 权限列表 ON 库名.表名 TO '用户名'@'主机';

给 reader 授予 school 库中所有表的查询权限：

GRANT SELECT ON school.* TO 'reader'@'localhost';

Query OK, 0 rows affected (0.01 sec)

拆解一下各部分：

• 权限列表：常用的有
  • SELECT：查
  • INSERT：增
  • UPDATE：改
  • DELETE：删
  • ALL PRIVILEGES：以上全部（以及建表等更多权限），可简写为 ALL
  • 多个权限用逗号隔开，如 GRANT SELECT, INSERT ON ...
• 作用范围 ON 后面：
  • school.*：school 库里的所有表
  • school.student：只针对 student 这一张表
  • *.*：所有库的所有表（权力极大，慎用）

再举两个例子（看懂即可，不必执行）：

-- 给录入成绩的老师账号：score 表可查、可插、可改
GRANT SELECT, INSERT, UPDATE ON school.score TO 'teacher_wang'@'localhost';

-- 给开发人员：school 库全部权限
GRANT ALL PRIVILEGES ON school.* TO 'dev'@'localhost';

四、查看权限：SHOW GRANTS

SHOW GRANTS FOR 'reader'@'localhost';

+---------------------------------------------------+
| Grants for reader@localhost                       |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `reader`@`localhost`        |
| GRANT SELECT ON `school`.* TO `reader`@`localhost`|
+---------------------------------------------------+
2 rows in set (0.00 sec)

• 第一行的 USAGE 意思是"仅能登录，没有任何实际权限"，是每个用户都有的"底线"，不用管它。
• 第二行就是我们刚授予的：school 库的 SELECT 权限。

查看自己当前账号的权限：SHOW GRANTS;（不加 FOR）。

五、完整演练：用 reader 登录，验证权限生效

另开一个系统命令行窗口（不要关掉 root 的窗口），用新用户登录：

mysql -u reader -p

输入密码 Reader@123 后进入。先试试"权限内"的操作：

USE school;
SELECT id, name FROM student WHERE class_id = 1;

+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  5 | 陈刚   |
+----+--------+
3 rows in set (0.00 sec)

查询没问题！再试试"权限外"的操作——插入一条数据：

INSERT INTO student (name, gender, birthday, class_id)
VALUES ('测试', '男', '2006-01-01', 1);

ERROR 1142 (42000): INSERT command denied to user 'reader'@'localhost' for table 'student'

被拒绝了！ERROR 1142 就是 MySQL 的"权限不足"报错，意思是：INSERT 命令对用户 'reader'@'localhost' 在 student 表上被拒绝。删除也一样不行：

DELETE FROM student WHERE id = 5;

ERROR 1142 (42000): DELETE command denied to user 'reader'@'localhost' for table 'student'

这正是我们想要的效果：reader 是一把只能"看"的钥匙，想搞破坏？门都没有。

顺便一提：reader 登录后执行 SHOW DATABASES;，只能看到 school 和少数系统库——没有权限的库对它是"隐形"的，这就是权限隔离。

六、收回权限：REVOKE

授权的反向操作，语法几乎和 GRANT 对称，只是 TO 换成 FROM：

REVOKE 权限列表 ON 库名.表名 FROM '用户名'@'主机';

回到 root 的窗口，收回 reader 的查询权限：

REVOKE SELECT ON school.* FROM 'reader'@'localhost';

Query OK, 0 rows affected (0.01 sec)

此时在 reader 的窗口里再执行查询：

SELECT * FROM student;

ERROR 1142 (42000): SELECT command denied to user 'reader'@'localhost' for table 'student'

连查都不让查了。演练完，可以把权限再授回去：

GRANT SELECT ON school.* TO 'reader'@'localhost';

七、修改密码：ALTER USER

用 root 给某个用户改密码：

ALTER USER 'reader'@'localhost' IDENTIFIED BY 'NewPass@456';

Query OK, 0 rows affected (0.01 sec)

改完后，reader 需要用新密码 NewPass@456 重新登录。用户也可以给自己改密码（登录后执行同样的语句，把用户名写自己）。

八、删除用户：DROP USER

用户不再需要时（比如员工离职），直接删除：

DROP USER 'reader'@'localhost';

Query OK, 0 rows affected (0.01 sec)

删除用户会连同它的所有权限一起清除，但不会删除任何数据——数据是库和表的，不属于某个用户。

练习完如果想保留 reader 继续做下一节实验，可以先不删；删了也没关系，随时可以重建。

⚠️ 新手常见坑

1. 把 'user'@'localhost' 和 'user'@'%' 当成同一个用户。它们是两个独立的账号，密码、权限都可以不同。你给 'reader'@'%' 授了权，用本机登录的 'reader'@'localhost' 可能依然没权限。
2. 远程连不上，以为是密码错。如果用户是按 'xxx'@'localhost' 创建的，从其他机器连接会直接被拒（报错里会显示 Host 'xxx' is not allowed）。需要远程访问时应创建 'xxx'@'%' 用户（还要确认服务器防火墙和 MySQL 的 bind-address 配置）。
3. 权限或密码改了却"不生效"。用 GRANT/REVOKE/ALTER USER 这些标准命令，MySQL 会自动刷新权限，已登录的会话有些权限变化要等对方重新连接才体现。只有手动改系统表这种野路子才需要 FLUSH PRIVILEGES;（新手根本不该用野路子）。
4. 图省事给普通账号 ALL PRIVILEGES ON *.*。这等于又造了一个 root，权限管理形同虚设。请遵循最小权限原则：用到什么给什么，不多给一分。
5. 日常开发也一直用 root。哪怕是自己练习，也建议建一个普通用户日常使用，既养成好习惯，也降低手滑 DROP DATABASE 的杀伤力。

📝 小结

• root 是总钥匙，只留给管理场景；日常按最小权限原则创建专用账号
• MySQL 用户 = '用户名'@'主机'：localhost 仅限本机，% 允许任意主机
• 常用命令一览：
  • 创建用户：CREATE USER 'u'@'localhost' IDENTIFIED BY '密码';
  • 授权：GRANT SELECT, INSERT ON school.* TO 'u'@'localhost';
  • 查看权限：SHOW GRANTS FOR 'u'@'localhost';
  • 收回权限：REVOKE INSERT ON school.* FROM 'u'@'localhost';
  • 改密码：ALTER USER 'u'@'localhost' IDENTIFIED BY '新密码';
  • 删除用户：DROP USER 'u'@'localhost';
• 越权操作会得到 ERROR 1142——这是权限系统在正常工作，不是故障

✍️ 练习题

1. 'tom'@'localhost' 和 'tom'@'%' 是同一个用户吗？各自的含义是什么？

参考答案

不是同一个用户，它们是两个独立账号，可以有不同的密码和权限。'tom'@'localhost' 只能从 MySQL 服务器本机登录；'tom'@'%' 可以从任意主机远程登录（% 是匹配任意主机的通配符）。

2. 请写出 SQL：创建用户 teacher1（仅限本机登录，密码 Teach@123），并授予它 school 库中 score 表的查询和修改（UPDATE）权限。

参考答案

CREATE USER 'teacher1'@'localhost' IDENTIFIED BY 'Teach@123';
GRANT SELECT, UPDATE ON school.score TO 'teacher1'@'localhost';

3. 用户 teacher1 执行 DELETE FROM score WHERE id = 1; 时会发生什么？请写出大致的报错信息。

参考答案

会因为没有 DELETE 权限而被拒绝，报错类似：

ERROR 1142 (42000): DELETE command denied to user 'teacher1'@'localhost' for table 'score'

4. 请写出 SQL：收回 teacher1 对 score 表的 UPDATE 权限，然后查看它当前剩余的权限。

参考答案

REVOKE UPDATE ON school.score FROM 'teacher1'@'localhost';
SHOW GRANTS FOR 'teacher1'@'localhost';

此时应只剩 GRANT SELECT ON school.score TO 'teacher1'@'localhost'（外加一条基础的 USAGE）。

5. 简答：什么是"最小权限原则"？为什么日常操作不建议一直用 root？

参考答案

最小权限原则：每个账号只授予完成其工作所必需的最小权限，用到什么给什么，绝不多给。 不建议一直用 root，因为 root 权力过大：误操作（如手滑 DROP DATABASE）没有任何拦截、账号泄露后损失不可控、多人共用 root 出了问题无法追查责任人。