数据库基础体系 · 第 90/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

MySQL 用户、角色与安全：认证插件、权限、TLS、审计和密钥

MySQL 安全不是一个单独的“密码配置”问题，而是多层控制共同作用的结果：

  1. 认证（authentication）：连接者能否证明自己是某个账户。
  2. 授权（authorization）：该账户可以访问哪些对象、执行哪些操作。
  3. 传输保护：客户端与服务器之间的数据是否使用 TLS 保护。
  4. 审计（audit）：谁在什么时间执行了什么操作，是否能够事后追溯。
  5. 静态数据保护：磁盘、表空间、日志和备份中的数据是否加密，以及密钥如何管理。
  6. 应用边界：SQL 注入、连接池、存储过程 DEFINER、备份恢复和运维账户是否破坏了前面的控制。

本文以 MySQL 8.4 的公开语义为基础。除非特别说明，示例假设使用 InnoDB、事务型表和单个 MySQL 实例；复制、组复制、云托管服务和审计商业组件会增加额外边界。

一、先区分账户、角色、认证和权限

1. user@host 才是 MySQL 账户的完整身份

MySQL 账户不是单独的用户名，而是：

'user'@'host'

例如：

'app'@'localhost'
'app'@'10.20.%.%'
'app'@'%'

这三个账户可以拥有不同的密码、认证插件、权限和 TLS 要求。

其中：

  • user 是账户名；
  • host 是允许匹配客户端来源的主机模式；
  • '%' 表示较宽泛的来源匹配；
  • 'localhost' 通常对应本机连接，但 Unix socket、TCP 连接以及客户端参数会影响实际匹配结果。

因此，下面两个账户不是同一个账户：

'app'@'localhost'
'app'@'%'

执行：

SELECT USER(), CURRENT_USER();

可能得到：

USER()          = app@client.example
CURRENT_USER()  = app@%

USER() 表示客户端在连接时声明或呈现的用户和来源；CURRENT_USER() 表示服务器最终用于认证和授权的账户。权限判断依赖后者。

2. 账户匹配不是“先找到用户名再检查权限”

服务器收到连接后，需要从账户定义中选择匹配的 user@host。同一用户名存在多个来源规则时，具体账户匹配结果非常重要。宽泛的：

'app'@'%'

可能意外接管本应属于：

'app'@'10.20.30.%'

的连接，或者让管理员误以为自己修改的是实际生效账户。

排查账户身份时，不要只执行：

SELECT USER();

应同时检查：

SELECT USER(), CURRENT_USER();

并查询账户定义：

SELECT User, Host, plugin, account_locked, ssl_type
FROM mysql.user
WHERE User = 'app';

直接查询系统表需要相应权限。生产环境更推荐使用 SHOW CREATE USER 查看账户定义：

SHOW CREATE USER 'app'@'%';

3. 认证成功不等于可以执行 SQL

认证回答的是：

这个连接者是否能够证明自己是 'app'@'%'？

授权回答的是：

'app'@'%' 是否可以执行这条具体语句？

例如：

CREATE USER 'report'@'10.20.%'
  IDENTIFIED WITH caching_sha2_password BY 'A-long-test-password-Only-for-demo';

GRANT SELECT ON reporting.* TO 'report'@'10.20.%';

此后：

  • 密码正确，认证成功；
  • 查询 reporting 数据库中的对象，授权成功；
  • 执行 INSERT 或 DROP，认证仍然成功，但授权失败，通常报 ERROR 1142 或相关权限错误。

这两个阶段必须分开诊断。密码错误不是 GRANT 问题，权限不足也不是认证插件问题。

二、认证插件：服务器如何验证身份

1. 认证插件的职责

认证插件定义客户端和服务器如何完成认证交换，以及服务器如何验证凭据。它通常不决定账户能访问哪些表。

一次典型连接包含以下阶段：

  1. 客户端发起连接。
  2. 服务器根据用户名和来源匹配账户。
  3. 服务器确定该账户使用的认证插件。
  4. 服务器发送认证挑战或其他协议数据。
  5. 客户端使用密码、密钥或外部身份凭据计算响应。
  6. 服务器验证响应。
  7. 认证成功后，连接进入权限检查阶段。

密码并不是“每次以明文发送后在服务器上比较”。具体是否发送明文、是否需要 TLS、是否进行 RSA 加密，取决于认证插件和连接条件。

2. caching_sha2_password

MySQL 8 默认认证插件是：

caching_sha2_password

它使用 SHA-256 系列的密码验证机制，并提供服务器端缓存路径。缓存命中可以减少重复的密码验证工作，但缓存不是授权缓存，也不是把密码保存为明文。

在非 TLS 连接上，caching_sha2_password 在某些认证路径中需要使用 RSA 公钥保护密码交换。实际能否完成，取决于服务器是否配置了相应 RSA 密钥，或者客户端是否能够获取服务器公钥。

最简单且推荐的条件是：使用 TLS。创建账户：

CREATE USER 'app'@'10.20.%'
  IDENTIFIED WITH caching_sha2_password BY 'A-long-test-password-Only-for-demo'
  REQUIRE SSL;

这条语句表达两个独立要求：

  • 使用 caching_sha2_password 验证密码；
  • 该账户的连接必须使用 TLS。

REQUIRE SSL 不等价于“客户端验证了服务器证书”。它主要要求连接使用加密层；客户端是否验证服务器身份，还取决于客户端 TLS 配置。

3. mysql_native_password

mysql_native_password 是历史上广泛使用的认证插件。MySQL 8.4 中它已被弃用，并且默认禁用；如果确实需要兼容旧客户端，可以通过服务器配置显式启用后再使用。它不应作为新部署的默认选择。

迁移时需要同时确认：

  • 客户端驱动是否支持 caching_sha2_password；
  • 连接是否通过 TLS；
  • 连接池是否会复用旧连接；
  • 复制、监控、备份工具是否使用独立账户；
  • 认证失败究竟是插件不支持，还是密码、来源、TLS 条件不满足。

不能仅通过把所有账户改为旧插件来“解决兼容性”，否则会把客户端升级问题变成长期安全债务。

4. 其他认证方式

MySQL 还支持基于操作系统、LDAP、Kerberos、PAM 或其他外部身份系统的认证插件，但其中一些属于特定发行版、商业版或额外组件能力。使用时需要分别确认：

  • 插件是否实际安装并启用；
  • 客户端是否支持对应认证协议；
  • 外部身份系统不可用时，连接是否会全部失败；
  • 服务器启动时能否加载插件；
  • 故障切换和恢复环境是否具有相同插件与配置。

认证插件本身不是“加密算法列表”。它是服务器与客户端完成身份验证的协议实现；密码散列、挑战响应、外部身份映射和 TLS 是不同层次。

三、密码账户的创建、修改和锁定

1. 使用账户管理语句，不直接改系统表

创建账户：

CREATE USER 'writer'@'10.20.%'
  IDENTIFIED BY 'A-long-test-password-Only-for-demo';

修改密码：

ALTER USER 'writer'@'10.20.%'
  IDENTIFIED BY 'Another-long-test-password-Only-for-demo';

锁定和解锁：

ALTER USER 'writer'@'10.20.%' ACCOUNT LOCK;
ALTER USER 'writer'@'10.20.%' ACCOUNT UNLOCK;

删除账户：

DROP USER 'writer'@'10.20.%';

直接修改 mysql.user 等系统表会绕过正常的账户管理语义，可能导致缓存、格式、复制或版本兼容问题。正确方式是使用 CREATE USER、ALTER USER、DROP USER、GRANT 和 REVOKE。

通常不需要在这些语句后执行：

FLUSH PRIVILEGES;

FLUSH PRIVILEGES 主要用于服务器重新读取被直接修改的授权表；它不是常规 CREATE USER 或 GRANT 的必需步骤。

2. 密码生命周期不等于密钥生命周期

密码账户通常保存的是验证所需的凭据表示，而不是可逆的明文密码。应用配置中的密码、连接字符串、环境变量、CI 日志和备份仍可能泄露明文凭据。

生产环境应特别避免：

mysql -u app -pSecret ...

因为命令行参数可能出现在进程列表或审计记录中。可以使用交互式 -p、受保护的客户端配置文件或外部密钥管理系统，但客户端配置文件本身必须限制文件权限，并纳入凭据轮换和吊销流程。

四、权限模型：从全局到对象和列

1. 权限是对操作和对象的允许集合

可以把某个账户最终获得的权限抽象为：

Peffective=Pdirect∪Proles∪Pmandatory−PrevokedP_{\text{effective}} = P_{\text{direct}} \cup P_{\text{roles}} \cup P_{\text{mandatory}} - P_{\text{revoked}}

其中：

  • PdirectP_{\text{direct}}：直接授予账户的权限；
  • ProlesP_{\text{roles}}：账户当前激活角色带来的权限；
  • PmandatoryP_{\text{mandatory}}：服务器配置要求的强制角色权限；
  • PrevokedP_{\text{revoked}}：更细粒度撤销或限制的权限。

这不是一个可以替代 MySQL 实现细节的完整公式，但能说明核心事实：账户拥有的权限不一定只来自直接 GRANT。

权限通常按作用域划分：

作用域 示例 影响
全局 *.* 整个服务器
数据库 sales.* 一个数据库中的对象
表 sales.orders 一张表
列 SELECT(order_id, total) 指定列
存储程序 PROCEDURE sales.p 特定存储过程或函数
动态权限 BACKUP_ADMIN 等 服务器级管理能力

权限的作用域不是简单的“越近越优先”。一条语句是否允许，取决于该语句所需的权限，以及账户在相关作用域和角色路径上最终拥有的权限。

2. 一个最小权限示例

先创建应用账户：

CREATE USER 'orders_app'@'10.20.%'
  IDENTIFIED WITH caching_sha2_password BY 'A-long-test-password-Only-for-demo';

只允许它读写订单表：

GRANT SELECT, INSERT, UPDATE
ON shop.orders
TO 'orders_app'@'10.20.%';

验证授权：

SHOW GRANTS FOR 'orders_app'@'10.20.%';

预期会看到类似：

GRANT USAGE ON *.* TO `orders_app`@`10.20.%`
GRANT SELECT, INSERT, UPDATE ON `shop`.`orders`
  TO `orders_app`@`10.20.%`

USAGE 通常表示账户存在，但没有额外的对象权限；它不是“允许访问所有对象”。

此时：

SELECT * FROM shop.orders;
INSERT INTO shop.orders (...);
UPDATE shop.orders SET ...;

可能成功，而：

DROP TABLE shop.orders;
SELECT * FROM shop.customers;

应当失败。

3. 不要把 ALL PRIVILEGES 当成“应用权限”

下面的授权包含的范围远大于普通应用所需：

GRANT ALL PRIVILEGES ON *.* TO 'orders_app'@'10.20.%';

它可能允许删除数据、修改结构、访问其他业务库，甚至在授予了相应动态权限时执行服务器管理操作。

尤其危险的是：

GRANT SUPER ON *.* ...

在较新的 MySQL 中，许多高风险管理能力已经拆分为动态权限，例如备份、复制、线程管理、日志管理等。动态权限不是表级权限，不能通过“只授予某个数据库”来限制其影响范围。

4. 列权限不是行权限

可以限制列：

GRANT SELECT (order_id, created_at, total)
ON shop.orders
TO 'report'@'10.20.%';

但这不表示限制了行。该账户仍可能读取这些列对应的所有行。

如果要求“只能读取自己部门的数据”或“只能读取当前租户的数据”，列权限不够。常见做法是：

  • 使用视图隐藏行；
  • 使用存储过程作为访问接口；
  • 在应用层强制加入租户条件；
  • 使用专门的行级安全方案或数据库架构。

例如：

CREATE VIEW reporting.my_orders AS
SELECT order_id, created_at, total
FROM shop.orders
WHERE customer_id = CURRENT_USER();

这个示例只有在 customer_id 与 CURRENT_USER() 的值设计一致时才成立。现实系统通常需要更明确的身份映射，而不是直接把 SQL 账户字符串当作业务租户标识。

五、角色：把权限集合与账户分离

1. 角色本质上也是一种账户对象

MySQL 角色使用账户命名空间表示，例如：

`orders_reader`@`%`

创建角色：

CREATE ROLE 'orders_reader'@'%';
GRANT SELECT ON shop.orders TO 'orders_reader'@'%';

把角色授予账户：

GRANT 'orders_reader'@'%'
TO 'report'@'10.20.%';

这一步只建立“账户可以使用该角色”的关系，不一定意味着角色权限已经在当前会话中生效。

2. 角色必须激活

查看角色：

SHOW GRANTS FOR 'report'@'10.20.%';

设置默认角色：

SET DEFAULT ROLE 'orders_reader'@'%'
TO 'report'@'10.20.%';

默认角色表示新会话建立时应激活的角色。也可以在会话中显式激活：

SET ROLE 'orders_reader'@'%';

查看当前会话激活的角色：

SELECT CURRENT_ROLE();

因此，以下三种状态不同：

  1. 角色存在；
  2. 角色已授予账户；
  3. 角色已在当前会话激活。

连接池尤其容易暴露这个区别：连接被复用时，应用应明确设置角色状态，不能假定连接天然处于正确的权限上下文。

3. 角色可以形成继承图

角色可以授予另一个角色：

CREATE ROLE 'orders_writer'@'%';

GRANT SELECT, INSERT, UPDATE
ON shop.orders
TO 'orders_writer'@'%';

GRANT 'orders_reader'@'%'
TO 'orders_writer'@'%';

GRANT 'orders_writer'@'%'
TO 'orders_app'@'10.20.%';

这形成：

orders_app
   └── orders_writer
          └── orders_reader

账户激活 orders_writer 后，可以通过继承关系获得 orders_reader 的权限。

角色图必须经过验证。复杂继承会使撤销、审计和故障排查变得困难；角色循环也不能作为正常权限继承结构使用。

4. 角色误解：REVOKE 的对象必须正确

如果权限来自角色：

GRANT SELECT ON shop.orders TO 'orders_reader'@'%';
GRANT 'orders_reader'@'%' TO 'report'@'10.20.%';

直接执行：

REVOKE SELECT ON shop.orders FROM 'report'@'10.20.%';

并不会删除角色本身的 SELECT 权限。真正的选择通常是：

REVOKE 'orders_reader'@'%'
FROM 'report'@'10.20.%';

或者修改角色：

REVOKE SELECT ON shop.orders
FROM 'orders_reader'@'%';

前者影响一个账户，后者影响所有使用该角色的账户。权限变更前必须先查清权限来源：

SHOW GRANTS FOR 'report'@'10.20.%';
SHOW GRANTS FOR 'orders_reader'@'%';

六、DEFINER、存储程序与权限边界

存储过程、函数、视图和触发器可能带有定义者上下文。典型属性包括：

  • SQL SECURITY DEFINER：执行时使用定义者权限；
  • SQL SECURITY INVOKER：执行时使用调用者权限。

如果给应用账户：

GRANT EXECUTE ON PROCEDURE shop.create_order
TO 'orders_app'@'10.20.%';

应用可能无需直接拥有订单表的全部写权限，而是通过受控过程写入。

但 DEFINER 也可能扩大权限边界。若高权限账户创建了定义者为自己的过程，再把执行权限授予低权限账户，低权限账户可能通过过程间接执行高权限操作。

应检查：

SHOW CREATE PROCEDURE shop.create_order;
SHOW CREATE VIEW shop.order_summary;

删除或重命名定义者账户也可能使对象执行失败。账户生命周期必须和依赖它的存储对象一起管理。

七、TLS：保护连接，不等于保护所有数据

1. TLS 保护什么

TLS 位于客户端与服务器之间，主要提供：

  • 机密性：窃听者不能直接读取传输内容；
  • 完整性：传输内容被修改时能够检测；
  • 可选的服务器身份验证；
  • 可选的客户端证书身份验证。

TLS 保护的是网络路径。它不自动保护：

  • 磁盘上的 InnoDB 表空间；
  • 未加密的备份；
  • 服务器内存中的明文数据；
  • 已经被授权账户读取后的数据；
  • 应用日志中的 SQL 或密码；
  • 数据库外部的导出文件。

2. 账户级 TLS 要求

创建一个必须使用 TLS 的账户：

CREATE USER 'report'@'10.20.%'
  IDENTIFIED WITH caching_sha2_password BY 'A-long-test-password-Only-for-demo'
  REQUIRE SSL;

更严格的证书身份要求可以使用：

CREATE USER 'admin'@'10.20.%'
  IDENTIFIED WITH caching_sha2_password BY 'A-long-test-password-Only-for-demo'
  REQUIRE X509;

REQUIRE X509 表示客户端必须提供有效客户端证书，但证书具体由哪个 CA 签发、主题或发行者是什么，还可以进一步约束。例如：

ALTER USER 'admin'@'10.20.%'
  REQUIRE SUBJECT '/C=CN/ST=Beijing/O=Example/OU=DBA/CN=db-admin'
  AND ISSUER '/C=CN/O=Example-CA/CN=Example-DB-CA';

实际证书主题字符串必须与客户端证书和 MySQL 的匹配规则一致，不能照抄示例。

还可以约束密码认证使用的 TLS 加密套件：

ALTER USER 'admin'@'10.20.%'
  REQUIRE CIPHER 'TLS_AES_256_GCM_SHA384';

这不是普遍适用的默认配置，因为可用套件受 OpenSSL、服务器版本和客户端能力影响。配置后必须使用实际客户端验证。

3. REQUIRE SSL 与证书校验的差别

以下命题不能混为一谈：

连接使用 TLS

和：

客户端确认自己连接的是正确的服务器

服务器可以加密连接，但如果客户端不验证证书链、主机名或信任根，主动中间人仍可能伪装服务器。生产客户端应配置等价于“验证 CA 和服务器身份”的 TLS 模式；具体参数名称依赖客户端驱动。

服务器侧可以查看 TLS 配置和连接状态：

SHOW VARIABLES LIKE 'require_secure_transport';

SHOW STATUS LIKE 'Ssl_cipher';
SHOW STATUS LIKE 'Ssl_version';

也可以查询当前连接的会话状态：

SHOW SESSION STATUS LIKE 'Ssl_cipher';

如果 Ssl_cipher 为空，通常表示当前连接没有使用 TLS。必须在实际应用连接、连接池和管理工具上验证，而不能只看服务器配置文件。

4. 全局要求与账户要求

服务器可以设置：

require_secure_transport=ON

它要求支持的客户端连接使用安全传输。账户级：

REQUIRE SSL

则是该账户自己的连接约束。

两者的关系可以理解为：

全局要求是服务器入口策略；
账户要求是特定身份策略。

如果全局要求关闭，某个账户仍可通过 REQUIRE SSL 强制 TLS；如果全局要求开启，未具备安全传输能力的连接会在更早阶段失败。

TLS 证书、私钥和 CA 文件也属于敏感密钥材料。服务器进程必须能够读取它们，但普通应用账户、备份账户和日志收集账户不应因此获得私钥读取权限。

八、审计：记录“发生了什么”，不是替代授权

1. 审计与日志的区别

常见日志用途不同：

  • 错误日志：启动失败、崩溃、复制错误、警告等；
  • 慢查询日志：满足时间或条件的慢语句；
  • 通用查询日志：记录较广泛的连接和语句活动；
  • 二进制日志：记录用于复制和恢复的变更事件；
  • 审计日志：按审计规则记录登录、连接、查询、权限或管理行为等事件。

二进制日志不是完整审计日志：

  • 它主要面向复制和时间点恢复；
  • 读操作通常不会像审计系统那样完整记录；
  • 记录内容和格式受日志模式、语句类型及复制语义影响；
  • 不能仅凭 binlog 可靠回答“某个管理员查阅过哪些个人数据”。

通用查询日志也不是理想审计系统，因为它通常缺少业务所需的结构化分类、过滤、保留和防篡改治理能力。

2. MySQL Enterprise Audit

MySQL Enterprise Audit 是 MySQL Enterprise 提供的审计能力，通常通过审计插件和审计日志实现。它可以按账户、事件类型、状态等条件过滤和记录审计事件，具体格式、过滤接口和配置方式应以所安装版本的官方文档及商业授权能力为准。

审计事件至少要能回答：

时间：何时发生？
身份：哪个 MySQL 账户？
来源：从哪里连接？
动作：执行了什么类型的操作？
对象：涉及哪个库、表或管理对象？
结果：成功还是失败？

审计日志应写入普通业务账户不能修改的位置，并考虑：

  • 日志轮换和容量；
  • 远程集中收集；
  • 时间同步；
  • 访问控制；
  • 保留期限；
  • 脱敏和隐私要求；
  • 备份与恢复；
  • 审计系统不可用时是阻断数据库操作，还是降级记录。

3. 审计的边界

审计只能记录已发生或正在发生的行为，不能阻止未授权操作。阻止依赖：

  • 认证；
  • 权限；
  • 角色激活；
  • TLS；
  • 防火墙和网络边界；
  • 应用参数化查询。

审计也不能恢复已经泄露的数据。它的价值是检测、追责、合规证明和故障分析。

审计日志还可能包含敏感 SQL、参数或个人数据。为了“审计一切”而把所有语句永久保存，可能造成新的泄露面。应根据调查需求和法规要求设计过滤与保留策略，并在测试环境验证日志量和失败行为，而不是预设不会影响生产系统。

九、密钥与静态加密：数据密钥不应等于数据库密码

1. 密钥层次

静态加密通常使用分层密钥模型：

外部密钥管理系统或 keyring
          │
      主密钥（master key）
          │
   数据加密密钥（DEK）
          │
  表空间、重做日志或其他数据

直觉上：

  • 主密钥用于保护或包装数据密钥；
  • 数据加密密钥用于实际加密数据；
  • 数据库可以轮换主密钥，而不必把所有数据明文导出后重新导入；
  • 但具体哪些文件、哪些元数据和哪些日志被覆盖，取决于功能范围和配置。

主密钥不应和数据库数据放在同一备份介质中。否则“备份文件被盗但密钥不在其中”的保护目标会被破坏。

2. InnoDB 表空间加密

InnoDB 支持表空间加密，典型对象包括：

  • 加密表空间；
  • 加密的 redo log；
  • 加密的 undo log；
  • 临时表空间等相关范围，具体取决于版本和参数。

创建加密表：

CREATE TABLE shop.payments (
    payment_id BIGINT PRIMARY KEY,
    order_id   BIGINT NOT NULL,
    amount     DECIMAL(18,2) NOT NULL
) ENGINE = InnoDB
  ENCRYPTION = 'Y';

这条语句的前提是：

  1. 实例已正确配置 keyring；
  2. keyring 在服务器启动和恢复时可用；
  3. 当前账户拥有创建对象所需权限；
  4. 部署的 MySQL 版本支持该加密范围。

如果表已存在，也可以通过 ALTER TABLE 改变加密属性，但这通常会重建或重写大量数据，产生磁盘、IO、锁和备份压力。不能把它当作零成本的元数据变更。

检查表的加密属性：

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop'
  AND TABLE_NAME = 'payments';

输出中的 ENCRYPTION='Y' 等信息可以帮助确认表级配置，但不能单凭这一列证明所有相关日志、备份和临时文件都已加密。

3. keyring 是密钥存储接口，不等于完整 KMS

MySQL 的 keyring 组件或插件负责让服务器访问加密密钥。常见部署方式包括：

  • 本地文件型 keyring；
  • 由外部密钥管理系统提供的 keyring；
  • 商业版或特定平台提供的密钥组件。

本地文件型 keyring 便于测试，但生产环境需要仔细评估：

  • keyring 文件权限；
  • 主机级管理员是否能同时取得数据文件和 keyring；
  • 备份是否包含 keyring；
  • 密钥轮换；
  • 密钥撤销；
  • 故障切换和恢复；
  • 服务器重启时能否及时访问外部 KMS。

一个关键故障路径是：

数据库数据文件存在
但服务器启动时无法访问 keyring
        ↓
加密表空间无法打开
        ↓
查询、恢复或实例启动可能失败

所以密钥系统必须和备份、灾备演练一起设计。只备份 .ibd、系统表或数据目录，而没有可恢复的密钥材料，得到的不是可用备份。

4. 密钥轮换不是“重新设置数据库密码”

数据库账户密码控制的是连接认证；表空间加密主密钥控制的是静态数据解密。两者完全不同：

ALTER USER ... IDENTIFIED BY ...

不会轮换 InnoDB 加密密钥。

相反，密钥轮换也不会自动修改任何应用连接密码。密钥治理至少应记录：

  • 密钥标识和版本；
  • 生成时间；
  • 使用范围；
  • 轮换时间；
  • 旧密钥保留周期；
  • 备份与恢复所需的密钥版本；
  • 吊销后的业务影响。

十、事务、DDL 与安全变更的运维边界

InnoDB 的业务 DML 具有事务语义：

START TRANSACTION;

UPDATE shop.orders
SET status = 'paid'
WHERE order_id = 1001;

ROLLBACK;

但不能因此认为所有安全变更都可以像业务数据一样回滚。账户管理、权限、角色和部分 DDL 由服务器以专门的管理语义处理，常常伴随隐式提交或不适用于普通事务回滚。

例如，以下操作不要设计成“先执行，发现不对再 ROLLBACK”：

CREATE USER ...;
GRANT ...;
REVOKE ...;
ALTER USER ...;
CREATE ROLE ...;

更安全的变更流程是：

  1. 在测试实例验证语句和客户端兼容性；
  2. 保存变更前后的 SHOW GRANTS 或账户定义；
  3. 先创建新账户或新角色；
  4. 授予最小权限；
  5. 用真实客户端建立连接验证；
  6. 切换应用连接；
  7. 确认旧账户无活动后锁定；
  8. 观察一段时间再删除旧账户。

账户轮换示例

不要直接修改正在被连接池使用的账户密码并期待所有连接立即恢复。可使用双账户或双凭据窗口：

CREATE USER 'orders_app_v2'@'10.20.%'
  IDENTIFIED WITH caching_sha2_password BY 'New-long-password-Only-for-demo';

GRANT 'orders_writer'@'%'
TO 'orders_app_v2'@'10.20.%';

SET DEFAULT ROLE 'orders_writer'@'%'
TO 'orders_app_v2'@'10.20.%';

然后：

  1. 更新应用配置；
  2. 重建或刷新连接池；
  3. 验证 CURRENT_USER() 和 CURRENT_ROLE()；
  4. 验证读写行为；
  5. 锁定旧账户：
ALTER USER 'orders_app'@'10.20.%' ACCOUNT LOCK;
  1. 确认无回滚需求后再删除旧账户。

这样可以把“密码切换”和“旧连接自然消失”从一个不可控瞬间变成可验证的状态迁移。

十一、连接失败和权限失败的诊断路径

1. 先判断失败发生在哪一层

可以按以下顺序排查：

网络可达性
  ↓
TLS 协商
  ↓
账户匹配
  ↓
认证插件和凭据
  ↓
角色激活
  ↓
对象权限
  ↓
业务 SQL 和数据约束

典型现象：

现象 可能层次
Can't connect to MySQL server 网络、端口、监听、路由、防火墙
SSL/TLS 握手错误 证书、协议、套件、客户端 TLS 配置
Access denied ... using password: YES 用户、Host、密码、认证插件或账户锁定
Authentication plugin ... cannot be loaded 客户端不支持或插件缺失
SELECT command denied SELECT 权限或角色未激活
Table ... doesn't exist 对象名、默认数据库、视图或权限可见性
能连接但角色权限不生效 角色未激活或默认角色配置错误

2. 用当前身份验证实际状态

连接成功后执行：

SELECT
    USER(),
    CURRENT_USER(),
    CURRENT_ROLE();

SHOW GRANTS;

这几条语句可以分别确认：

  • 客户端呈现的身份；
  • 实际匹配的授权账户；
  • 当前激活角色；
  • 当前会话可见的授权来源。

如果 CURRENT_ROLE() 没有返回预期角色，先不要继续修改表权限，应先修复角色激活问题。

3. 用最小化测试隔离问题

假设应用报：

SELECT command denied to user 'report'@'10.20.5.8'
for table 'orders'

可以分四步：

SELECT USER(), CURRENT_USER(), CURRENT_ROLE();
SHOW GRANTS;
SELECT 1;
SELECT order_id FROM shop.orders LIMIT 1;

解释：

  1. SELECT 1 成功，只能说明会话可执行简单表达式；
  2. 它不说明账户拥有表权限；
  3. SHOW GRANTS 检查直接权限和角色；
  4. 最小列查询可以区分表权限、列权限和对象名问题。

不要为了让测试通过而临时执行：

GRANT ALL PRIVILEGES ON *.* ...

这种做法会破坏诊断的真实性，并扩大后续风险。

十二、SQL 注入与权限模型必须同时处理

参数化查询解决的是“数据被解析为 SQL 代码”的问题，权限模型解决的是“这段 SQL 即使被执行，最多能做什么”的问题。二者不能相互替代。

危险示例：

sql = "SELECT * FROM users WHERE name = '" + name + "'"

当 name 来自外部输入时，输入可能改变 SQL 语法。

参数化示例：

cursor.execute(
    "SELECT user_id, name FROM users WHERE name = %s",
    (name,)
)

参数作为值传输，而不是拼接成 SQL 语句。

但即使使用参数化查询，如果应用账户拥有：

GRANT ALL PRIVILEGES ON *.* ...

攻击者仍可能利用应用已拥有的合法能力执行破坏性操作。因此安全边界应组合为：

参数化查询
+ 最小权限
+ TLS
+ 审计
+ 安全的凭据与密钥管理

动态表名、列名和排序方向通常不能直接作为普通参数绑定。这类 SQL 必须使用白名单映射：

allowed = {
    "created": "created_at",
    "amount": "total"
}

column = allowed[user_choice]
sql = f"SELECT order_id, {column} FROM shop.orders"
cursor.execute(sql)

这里的安全性来自 user_choice 只能映射到预先定义的 SQL 标识符，而不是来自字符串转义。

十三、常见误解与反例

误解一：有密码就安全

反例：

CREATE USER 'app'@'%'
  IDENTIFIED BY 'strong-password';

如果应用账户还拥有全局写权限，密码强度不能阻止权限滥用。身份验证只保护入口，不限制入口后的能力。

误解二：用了 TLS 就完成了加密

反例：

客户端 --TLS--> MySQL
MySQL 数据目录、备份、导出文件仍为明文

TLS 只覆盖网络传输。静态加密、备份加密和密钥隔离需要单独设计。

误解三：授予角色后权限立即生效

角色可能已授予但未激活。必须检查：

SELECT CURRENT_ROLE();

并配置默认角色或在会话生命周期中显式设置角色。

误解四：REVOKE 一定能撤销权限

如果权限来自角色，撤销账户的直接权限不会撤销角色继承的权限。必须检查权限来源，并对正确的账户或角色执行 REVOKE。

误解五：binlog 就是审计日志

binlog 主要记录变更和复制所需事件，不能完整记录所有查询、登录和数据读取行为。需要审计时，应使用适合版本和部署的审计能力，并验证其过滤、保留和防篡改方案。

误解六：加密表空间后密钥丢失也没关系

没有 keyring 或外部 KMS 中的密钥，数据文件可能无法恢复。加密方案的可用性条件是：

数据备份 + 数据库配置 + 密钥材料 + 恢复流程

四者缺一不可。

十四、一个可验证的最小安全配置

以下示例用于测试实例，密码是演示值，不应直接用于生产。

-- 1. 创建角色
CREATE ROLE 'orders_reader'@'%';
CREATE ROLE 'orders_writer'@'%';

-- 2. 给角色授予对象级权限
GRANT SELECT
ON shop.orders
TO 'orders_reader'@'%';

GRANT SELECT, INSERT, UPDATE
ON shop.orders
TO 'orders_writer'@'%';

-- 3. 创建必须使用 TLS 的应用账户
CREATE USER 'orders_app'@'10.20.%'
  IDENTIFIED WITH caching_sha2_password BY 'A-long-test-password-Only-for-demo'
  REQUIRE SSL;

-- 4. 只授予写角色
GRANT 'orders_writer'@'%'
TO 'orders_app'@'10.20.%';

-- 5. 设置默认角色
SET DEFAULT ROLE 'orders_writer'@'%'
TO 'orders_app'@'10.20.%';

-- 6. 查看授权
SHOW GRANTS FOR 'orders_app'@'10.20.%';
SHOW GRANTS FOR 'orders_writer'@'%';

应用建立连接后执行：

SELECT
    USER(),
    CURRENT_USER(),
    CURRENT_ROLE();

SHOW SESSION STATUS LIKE 'Ssl_cipher';

SELECT order_id, created_at, total
FROM shop.orders
LIMIT 1;

验证结果应满足：

  1. CURRENT_USER() 是预期的 'orders_app'@'10.20.%'；
  2. CURRENT_ROLE() 包含 'orders_writer'@'%'；
  3. Ssl_cipher 非空；
  4. 订单查询成功；
  5. 对未授权对象的查询失败；
  6. DROP TABLE 等结构操作失败；
  7. 审计系统能够记录该连接和操作；
  8. 若使用加密表空间，实例重启和备份恢复测试能够访问 keyring。

这个验证顺序很重要：先验证身份和传输，再验证角色和权限，最后验证业务操作与审计、恢复链路。否则一旦失败，很难区分是 TLS、账户匹配、角色状态还是对象权限问题。

MySQL 安全的核心不是某个单独参数，而是让每一条边界都可解释、可验证、可撤销：

账户确定身份，
认证插件验证凭据，
角色组织权限，
授权限制操作范围，
TLS保护传输，
审计记录行为，
keyring保护静态数据密钥，
参数化查询阻断注入。

这些机制只有在账户来源、会话状态、日志、密钥和恢复流程都纳入同一个生命周期后，才构成可运行的安全体系。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。