第14章 MySQL用户与权限管理实战：构建安全的企业数据库环境

14.1 权限表：MySQL访问控制的基石

在MySQL数据库中，用户权限的管理依赖于一系列系统表，这些表存储在mysql数据库中。理解这些表的结构和作用，是进行精细权限控制的前提。

14.1.1 user表：全局权限的存储中心

user表是权限控制的核心，它决定了一个用户能否连接到MySQL服务器，以及是否拥有全局权限。所谓全局权限，是指对数据库中所有数据库、所有表的操作权限，比如超级权限、创建数据库、关闭服务器等。

user表的结构非常复杂，包含几十个字段，但我们可以将其分为几类：

• 用户连接字段：Host、User，共同组成一个用户标识（如 ‘root’@‘localhost’）。
• 密码字段：authentication_string（5.7中存储密码哈希）。
• 权限字段：以 ‘_priv’ 结尾的字段，如 Select_priv、Insert_priv、Update_priv 等，每个字段都是枚举类型（‘Y’或’N’），表示用户是否拥有该全局权限。
• 安全字段：ssl_type、ssl_cipher等，用于SSL连接。
• 资源限制字段：max_questions、max_updates、max_connections等，限制用户资源消耗。

在商业环境中，除了数据库管理员（DBA），几乎不应该有任何用户拥有全局权限。通常我们只给应用账号针对特定库的权限，而不是全局权限。

14.1.2 db表和host表：数据库级别的权限

db表存储用户对某个数据库的权限。它包含Host、Db、User以及一系列权限字段（如Select_priv、Insert_priv等）。db表中的权限适用于该数据库下的所有表。

host表在较新版本中已废弃（在MySQL 5.7中仍然存在，但不再使用），早期用于扩展db表中的Host字段，现在权限控制更推荐直接使用db表。

当一个用户尝试访问某个数据库时，MySQL首先检查user表中的全局权限，如果有则放行；如果没有，则检查db表中是否有匹配的数据库权限。

14.1.3 tables_priv表和columns_priv表：表级和列级的精细控制

tables_priv表存储用户对特定表的权限，包括Table_priv（表权限，如Select、Insert等）和Column_priv（列权限，可以细化到某些列）。columns_priv表则专门用于列级别的权限，指定用户对某张表的某些列的操作权限。

例如，我们可以允许客服人员只能查看用户表的用户名和电话，而不能查看密码和身份证号。这种精细控制通常用于多租户应用或敏感数据保护。

14.1.4 procs_priv表：存储过程和函数的权限

procs_priv表管理用户对存储过程和函数的执行权限。当应用需要通过存储过程操作数据，而不直接访问表时，可以只授予EXECUTE权限，提高安全性。

了解这些权限表的结构，有助于我们在出现权限问题时进行排查。例如，如果一个用户能连接但不能访问某个数据库，我们可以查询这些表来确认权限是否配置正确。

14.2 账户管理：从创建到销毁的全生命周期

账户管理是DBA的日常工作，包括创建用户、修改密码、删除用户等操作。在商业项目中，通常遵循最小权限原则，为每个应用或人员创建独立的账户。

14.2.1 登录和退出MySQL服务器

登录MySQL命令：

mysql -u 用户名 -p -h 主机名 -P 端口号

例如，用root登录本地服务器：

mysql -u root -p

输入密码后进入MySQL命令行。退出使用 exit 或 quit。

在商业环境中，通常禁止使用root直接操作应用数据库，而是使用具有适当权限的普通用户。

14.2.2 新建普通用户

创建用户使用 CREATE USER 语句，语法如下：

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

主机部分可以是具体IP、主机名、域名，或者使用通配符 % 表示任意主机。但在生产环境中，应尽量限制主机范围，减少安全风险。例如，只允许应用服务器IP连接：

CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'StrongPassword!';

对于开发人员，可以允许从公司内网连接：

CREATE USER 'dev_zhang'@'10.0.%.%' IDENTIFIED BY 'DevPass123';

在MySQL 5.7中，密码默认使用 PASSWORD() 函数加密，但该函数已废弃，推荐直接使用明文（由MySQL内部加密）。

14.2.3 删除普通用户

删除用户使用 DROP USER 语句：

DROP USER '用户名'@'主机';

例如：

DROP USER 'dev_zhang'@'10.0.%.%';

如果用户离职或应用下线，应及时删除用户，避免安全漏洞。

14.2.4 root用户修改自己的密码

root修改自己的密码有多种方式：

-- 方法1：使用SET PASSWORD
SET PASSWORD = PASSWORD('新密码');

-- 方法2：直接修改mysql.user表（不推荐）
UPDATE mysql.user SET authentication_string = PASSWORD('新密码') WHERE User = 'root';
FLUSH PRIVILEGES;

-- 方法3：使用mysqladmin命令行工具
mysqladmin -u root -p旧密码 password '新密码'

推荐使用第一种方法。

14.2.5 root用户修改普通用户的密码

root可以修改任意用户的密码：

SET PASSWORD FOR '用户名'@'主机' = PASSWORD('新密码');

或者：

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

ALTER USER 是更现代的方式，推荐使用。

14.2.6 普通用户修改自己的密码

普通用户可以修改自己的密码（需要有权限）：

SET PASSWORD = PASSWORD('新密码');

或者：

ALTER USER USER() IDENTIFIED BY '新密码';

其中 USER() 返回当前用户。

14.2.7 root用户密码丢失的解决办法

这是DBA经常遇到的紧急情况。步骤如下：

1. 停止MySQL服务。
2. 使用 --skip-grant-tables 选项启动MySQL，跳过权限验证：

mysqld_safe --skip-grant-tables &

或者编辑配置文件my.cnf，在[mysqld]下添加 skip-grant-tables。

3. 此时无需密码即可登录：

mysql -u root

4. 刷新权限表（使 SET PASSWORD 生效）：

FLUSH PRIVILEGES;

5. 修改root密码：

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

6. 退出，去掉 skip-grant-tables 选项，重启MySQL服务。

注意：此操作期间数据库没有任何访问控制，极其危险，必须确保服务器离线或在安全环境操作。

14.3 权限管理：授予、回收与查看

权限管理是用户管理的核心，通过 GRANT 和 REVOKE 语句实现。

14.3.1 MySQL的各种权限

MySQL权限分为多个级别：

• 全局权限：作用于所有数据库和所有表，如 SELECT、INSERT、CREATE、DROP、SUPER、PROCESS 等。
• 数据库权限：作用于指定数据库的所有表，如 SELECT ON db_name.*。
• 表权限：作用于指定表，如 SELECT ON db_name.table_name。
• 列权限：作用于指定列，如 SELECT (col1, col2) ON db_name.table_name。
• 存储过程权限：EXECUTE、ALTER ROUTINE 等。

常用权限列表：

权限	说明
ALL PRIVILEGES	所有权限（除GRANT OPTION外）
SELECT	查询数据
INSERT	插入数据
UPDATE	更新数据
DELETE	删除数据
CREATE	创建数据库或表
DROP	删除数据库或表
ALTER	修改表结构
INDEX	创建或删除索引
REFERENCES	外键约束（通常不需要）
CREATE VIEW	创建视图
SHOW VIEW	查看视图定义
CREATE ROUTINE	创建存储过程/函数
ALTER ROUTINE	修改/删除存储过程/函数
EXECUTE	执行存储过程/函数
TRIGGER	创建触发器
EVENT	创建事件
GRANT OPTION	授予或回收权限的能力
SUPER	超级权限（修改全局变量、停止复制等）
PROCESS	查看所有进程
SHOW DATABASES	列出所有数据库
LOCK TABLES	锁表
REPLICATION SLAVE	从库复制所需
REPLICATION CLIENT	查看主从状态

14.3.2 授权

GRANT 语句的基本语法：

GRANT 权限列表 ON 权限级别 TO '用户'@'主机' [IDENTIFIED BY '密码'] [WITH GRANT OPTION];

权限列表可以是逗号分隔的多个权限，如 SELECT, INSERT, UPDATE。权限级别可以是 *.*（全局）、db_name.*（数据库）、db_name.table_name（表）等。

14.3.2.1 商业实战场景1：给应用账号授权

假设我们有一个电商应用，需要连接数据库 ecommerce，并执行增删改查操作。我们创建应用账号，并授予必要的权限：

-- 创建用户
CREATE USER 'app_ecom'@'192.168.1.%' IDENTIFIED BY 'AppP@ssw0rd';

-- 授予数据库所有表的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.* TO 'app_ecom'@'192.168.1.%';

-- 刷新权限
FLUSH PRIVILEGES;

如果应用还需要创建临时表、执行存储过程等，可以增加相应权限。

14.3.2.2 商业实战场景2：给开发人员授权

开发人员可能需要查询生产数据（只读）以排查问题，但不应有修改权限。可以授予只读权限：

GRANT SELECT ON ecommerce.* TO 'dev_lisi'@'10.0.0.%' IDENTIFIED BY 'DevReadOnly';

如果开发人员需要修改表结构（DDL），应在测试环境操作，生产环境严格控制。

14.3.2.3 商业实战场景3：给报表系统授权

报表系统需要读取大量数据，但不需要写入。可以授予只读权限，甚至可以限制只能查询某些表：

GRANT SELECT ON ecommerce.orders TO 'reporter'@'%' IDENTIFIED BY 'Report123';
GRANT SELECT ON ecommerce.order_items TO 'reporter'@'%';

这样报表用户只能看到订单相关的表，无法访问用户密码等敏感数据。

14.3.2.4 授予列级权限

假设客服只需要查看用户的用户名和邮箱，不需要看密码和身份证，可以创建视图，或者直接授予列权限：

GRANT SELECT (user_id, username, email) ON ecommerce.users TO 'cs'@'%' IDENTIFIED BY 'CsPass';

这样客服执行 SELECT * FROM users 会失败，必须显式指定列。

14.3.2.5 授予存储过程执行权限

如果应用通过存储过程操作数据，可以只授予EXECUTE权限：

GRANT EXECUTE ON PROCEDURE ecommerce.sp_create_order TO 'app_ecom'@'192.168.1.%';

14.3.2.6 WITH GRANT OPTION

WITH GRANT OPTION 允许用户将自己拥有的权限授予其他用户。这通常只给DBA使用。例如：

GRANT SELECT ON ecommerce.* TO 'user1'@'%' WITH GRANT OPTION;

这样user1可以将SELECT权限授予其他用户。在生产环境中要谨慎使用。

14.3.3 收回权限

使用 REVOKE 语句收回权限：

REVOKE 权限列表 ON 权限级别 FROM '用户'@'主机';

例如，收回开发人员的INSERT权限：

REVOKE INSERT ON ecommerce.* FROM 'dev_lisi'@'10.0.0.%';

收回所有权限（包括GRANT OPTION）：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dev_lisi'@'10.0.0.%';

14.3.4 查看权限

查看当前用户的权限：

SHOW GRANTS;

查看指定用户的权限：

SHOW GRANTS FOR 'app_ecom'@'192.168.1.%';

也可以查询 mysql.user、mysql.db 等表，但 SHOW GRANTS 更直观。

14.4 访问控制：连接核实与请求核实

MySQL的访问控制分为两个阶段：连接核实和请求核实。

14.4.1 连接核实阶段

当客户端尝试连接MySQL服务器时，服务器根据 mysql.user 表检查：

• 客户端的主机名或IP是否匹配 Host 字段。
• 用户名是否匹配。
• 密码是否匹配（根据 authentication_string 验证）。

只有以上全部匹配，连接才被接受。然后服务器初始化用户会话，并分配权限。

14.4.2 请求核实阶段

连接建立后，用户发出SQL语句，服务器检查用户是否有执行该操作的权限。检查顺序为：

1. 如果用户拥有全局权限（如 SUPER 或 SELECT ON *.*），直接放行。
2. 否则，检查 mysql.db 表，看是否有针对当前数据库的权限。
3. 如果还不行，检查 mysql.tables_priv 和 mysql.columns_priv，看是否有表级或列级权限。
4. 如果是存储过程，检查 mysql.procs_priv。

这种分级检查机制既保证了效率（全局权限优先），又实现了精细控制。

14.5 综合案例：构建企业级MySQL权限体系

我们模拟一家中型互联网公司，需要为不同角色创建用户并分配权限，确保数据安全和操作规范。

14.5.1 角色定义

• DBA（数据库管理员）：拥有所有数据库的完全控制权，包括创建用户、授权、备份等。
• 应用账号：每个应用（如电商、CRM）有自己的数据库和账号，权限仅限该应用的库。
• 开发人员：可以查询线上数据（只读），但不能修改，用于排查问题。
• 运维人员：需要查看数据库状态、进程，但无数据访问权限。
• 数据分析师：需要读取某些业务表（如订单、用户），生成报表。
• 审计人员：需要查看所有操作日志（需要访问审计表）。

14.5.2 创建数据库和应用账号

假设有两个应用数据库：ecommerce 和 crm。

-- 创建电商应用账号
CREATE USER 'ecom_app'@'192.168.10.%' IDENTIFIED BY 'Ecom@2024!';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.* TO 'ecom_app'@'192.168.10.%';
GRANT EXECUTE ON PROCEDURE ecommerce.sp_create_order TO 'ecom_app'@'192.168.10.%';

-- 创建CRM应用账号
CREATE USER 'crm_app'@'192.168.20.%' IDENTIFIED BY 'Crm@2024!';
GRANT SELECT, INSERT, UPDATE, DELETE ON crm.* TO 'crm_app'@'192.168.20.%';

14.5.3 开发人员只读账号

CREATE USER 'dev_team'@'10.0.%' IDENTIFIED BY 'DevReadOnly!';
GRANT SELECT ON ecommerce.* TO 'dev_team'@'10.0.%';
GRANT SELECT ON crm.* TO 'dev_team'@'10.0.%';
-- 限制只能查询，不能修改

14.5.4 运维账号

运维需要查看进程、主从状态等，但不应访问数据：

CREATE USER 'ops'@'10.0.%' IDENTIFIED BY 'Ops@2024!';
GRANT PROCESS, REPLICATION CLIENT, SHOW DATABASES ON *.* TO 'ops'@'10.0.%';

14.5.5 数据分析师账号

数据分析师需要读取订单和用户数据，但不需要修改：

CREATE USER 'analyst'@'%' IDENTIFIED BY 'Analyst@2024!';
GRANT SELECT ON ecommerce.orders TO 'analyst'@'%';
GRANT SELECT ON ecommerce.order_items TO 'analyst'@'%';
GRANT SELECT (user_id, username, email) ON ecommerce.users TO 'analyst'@'%';

14.5.6 审计人员账号

假设有专门的审计表 audit_log，审计人员需要只读访问：

CREATE USER 'auditor'@'%' IDENTIFIED BY 'Audit@2024!';
GRANT SELECT ON ecommerce.audit_log TO 'auditor'@'%';

14.5.7 DBA账号

DBA账号通常由root承担，但也可以创建专门的DBA账号：

CREATE USER 'dba'@'localhost' IDENTIFIED BY 'DbaStrongP@ss';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

注意：DBA账号只允许从本地登录，提高安全性。

14.5.8 定期审查和回收

公司人员变动时，及时删除或修改用户权限。定期使用 SHOW GRANTS 查看各用户权限，确保没有过度授权。

14.6 专家解惑：常见权限问题与解决方案

14.6.1 为什么创建用户后无法登录？

可能原因：

• 主机限制：用户只能在指定主机登录，检查是否从正确主机尝试。
• 密码错误：重新确认密码。
• 权限未刷新：执行 FLUSH PRIVILEGES。
• 用户不存在：检查 mysql.user 表。

14.6.2 为什么用户有SELECT权限，但查询某些表提示无权限？

可能原因：

• 全局权限不足，但数据库权限有？检查权限级别是否正确。
• 表名大小写问题：MySQL在Linux上表名区分大小写，Windows不区分。保持统一。
• 用户权限来自不同级别，但实际被全局更严格的限制覆盖？通常不会，权限是累加的。

14.6.3 如何复制用户权限？

可以先用 SHOW GRANTS 查看用户权限，然后复制语句创建新用户并授权。

14.6.4 如何限制用户连接数？

可以在 CREATE USER 或 ALTER USER 中设置资源限制：

ALTER USER 'app_user'@'%' WITH MAX_USER_CONNECTIONS 100;

或者通过全局变量 max_user_connections。

14.6.5 如何回收所有权限并删除用户？

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';
DROP USER 'user'@'host';

14.6.6 忘记root密码怎么办？

前面已详细说明步骤。

14.6.7 如何备份和恢复权限？

可以通过 mysqldump 备份mysql数据库：

mysqldump -u root -p mysql user db tables_priv columns_priv procs_priv > grants_backup.sql

恢复时导入即可。注意：这种方法可能导致权限冲突，最好在干净系统恢复。

14.6.8 什么是代理用户？

MySQL支持代理用户，允许一个用户以另一个用户的身份执行操作，通过 GRANT PROXY 实现。这在一些特殊场景中有用。

14.6.9 如何查看用户有哪些权限？

SHOW GRANTS FOR 'user'@'host';

14.6.10 权限更改后何时生效？

权限更改后，已连接的会话不会自动生效，需要重新连接。执行 FLUSH PRIVILEGES 只是重载权限表，对已存在的连接无效。

14.7 经典习题与实战思考

1. 创建一个用户 ‘report’@‘localhost’，密码为 ‘Report123’，授予其对数据库 sales 的所有表的 SELECT 权限。
2. 如何查看当前登录用户是谁？有哪些权限？
3. 假设你是一个DBA，开发人员需要修改线上表结构，你应该怎么做？
4. 解释 mysql.user 表中的 Host 字段的作用，为什么不能随意设为 ‘%’？
5. 创建一个用户 ‘backup’@‘localhost’，授予它 SELECT、LOCK TABLES、RELOAD 权限，用于备份操作。
6. 如何收回用户对某个表的 UPDATE 权限？
7. 误删了root用户怎么办？请写出恢复步骤。
8. 在电商案例中，如果我们需要给客服人员授予只能查看用户表的前三列（id, name, phone）的权限，如何实现？
9. 什么是 WITH GRANT OPTION？为什么需要谨慎使用？
10. 如何限制某个用户每小时最多执行1000次查询？

本章全面介绍了MySQL用户与权限管理的各个方面，从底层的权限表到日常的账户管理，再到权限的授予与回收，最后通过企业级案例展示了如何构建安全的权限体系。掌握这些知识，你就能在生产环境中游刃有余地管理用户，确保数据安全。记住，权限管理是数据库安全的第一道防线，务必遵循最小权限原则，定期审计。