MySQL DCL 高频权限操作速查表

DCL（Data Control Language，数据控制语言）用于管理数据库账号和权限，日常核心命令包括 CREATE USER、GRANT、REVOKE 和 SHOW GRANTS。

本文以 MySQL 8.0 为准。账号管理通常需要管理员权限，生产环境应遵循最小权限原则。

MySQL 账号由“用户名 + 来源主机”共同确定。'app_user'@'localhost' 与 'app_user'@'10.%' 是两个不同账号。

1. 查看当前账号和权限

1.1 当前认证账号

SELECT USER(), CURRENT_USER();

USER()：客户端连接时提供的用户名和来源。
CURRENT_USER()：MySQL 实际用于权限检查的账号。

1.2 查看当前账号权限

SHOW GRANTS;

1.3 查看指定账号权限

SHOW GRANTS FOR 'app_user'@'localhost';

1.4 查看已有账号

SELECT User, Host, plugin, account_locked
FROM mysql.user
ORDER BY User, Host;

查询 mysql.user 需要相应管理权限。不要把系统表的查询权限授予普通业务账号。

2. 创建账号

2.1 创建本机业务账号

CREATE USER 'shop_app'@'localhost'
IDENTIFIED BY '请替换为高强度随机密码';

2.2 创建指定网段账号

CREATE USER 'shop_app'@'10.10.%'
IDENTIFIED BY '请替换为高强度随机密码';

不要为图方便默认使用任意来源主机：

-- 风险较高，除非网络边界和业务确实需要
CREATE USER 'shop_app'@'%'
IDENTIFIED BY '请替换为高强度随机密码';

'%' 表示允许从任意主机来源匹配。即使数据库端口有防火墙保护，也应尽量限制 Host 范围。

2.3 要求安全连接

CREATE USER 'remote_app'@'10.10.%'
IDENTIFIED BY '请替换为高强度随机密码'
REQUIRE SSL;

启用 REQUIRE SSL 后，客户端必须正确配置 TLS。它只保证连接要求，不替代证书验证、网络访问控制和密码管理。

2.4 中文用户名是否推荐

MySQL 可以处理 Unicode 数据，但数据库账号名、角色名、数据库名和表名建议使用简洁的 ASCII 英文命名：

shop_app
shop_readonly
shop_migration

原因：

Shell、配置文件、监控和跨平台工具兼容性更稳定。
避免大小写、终端编码和转义问题。
中文业务含义可以写在运维文档中，不必放进账号标识符。

业务表中的中文内容仍应使用 utf8mb4，账号命名与业务数据字符集是两个不同问题。

3. 高频授权

3.1 业务应用的 CRUD 权限

GRANT SELECT, INSERT, UPDATE, DELETE
ON shop.*
TO 'shop_app'@'localhost';

业务应用通常不需要 CREATE、ALTER、DROP、GRANT OPTION 等高风险权限。

3.2 只读账号

CREATE USER 'shop_readonly'@'10.10.%'
IDENTIFIED BY '请替换为高强度随机密码'
REQUIRE SSL;

GRANT SELECT
ON shop.*
TO 'shop_readonly'@'10.10.%';

适用于报表、排障查询和只读后台。只读账号也可能执行高成本查询，因此仍需要连接数、超时和资源层面的控制。

3.3 只授权某张表

GRANT SELECT, UPDATE
ON shop.products
TO 'inventory_service'@'10.10.%';

3.4 只授权某些字段

GRANT SELECT (id, username, status)
ON shop.users
TO 'support_reader'@'10.10.%';

列级权限可以减少敏感字段暴露，但权限管理会更复杂。真实系统还可以通过视图提供经过筛选的数据。

3.5 迁移账号

CREATE USER 'shop_migration'@'localhost'
IDENTIFIED BY '请替换为高强度随机密码';

GRANT SELECT, INSERT, UPDATE, DELETE,
      CREATE, ALTER, DROP, INDEX, REFERENCES
ON shop.*
TO 'shop_migration'@'localhost';

迁移账号权限高于普通应用账号，应限制来源、妥善保管密码，并只在发布或迁移期间使用。

3.6 存储过程权限

GRANT EXECUTE
ON PROCEDURE shop.recalculate_order_amount
TO 'shop_app'@'localhost';

4. 使用角色管理权限

MySQL 8.0 支持角色。角色适合多个账号共享同一组权限，减少重复授权。

4.1 创建角色

CREATE ROLE 'shop_app_role', 'shop_readonly_role';

4.2 给角色授权

GRANT SELECT, INSERT, UPDATE, DELETE
ON shop.*
TO 'shop_app_role';

GRANT SELECT
ON shop.*
TO 'shop_readonly_role';

4.3 把角色授予账号

GRANT 'shop_app_role'
TO 'shop_app'@'localhost';

4.4 设置默认角色

SET DEFAULT ROLE 'shop_app_role'
TO 'shop_app'@'localhost';

如果没有设置默认角色，用户登录后角色可能不会自动生效。可以查看：

SELECT CURRENT_ROLE();

当前会话启用所有已授予角色：

SET ROLE ALL;

5. 撤销权限

5.1 撤销指定权限

REVOKE DELETE
ON shop.*
FROM 'shop_app'@'localhost';

5.2 撤销某个对象的全部权限

REVOKE ALL PRIVILEGES
ON shop.*
FROM 'shop_app'@'localhost';

5.3 撤销角色

REVOKE 'shop_app_role'
FROM 'shop_app'@'localhost';

撤销后使用 SHOW GRANTS 再次确认：

SHOW GRANTS FOR 'shop_app'@'localhost';

6. 修改账号

6.1 修改密码

ALTER USER 'shop_app'@'localhost'
IDENTIFIED BY '新的高强度随机密码';

修改密码后，应用连接池中的旧连接可能仍暂时存在，新建连接会使用新密码。生产环境应制定密码轮换和应用配置更新顺序。

6.2 锁定和解锁账号

ALTER USER 'shop_app'@'localhost' ACCOUNT LOCK;

ALTER USER 'shop_app'@'localhost' ACCOUNT UNLOCK;

账号异常、离职交接或暂时停用时，锁定通常比立即删除更容易恢复。

6.3 修改账号来源主机

RENAME USER
  'shop_app'@'localhost'
TO
  'shop_app'@'10.10.%';

修改 Host 会改变允许匹配的连接来源，执行前应确认应用服务器地址和网络策略。

7. 删除账号和角色

DROP USER IF EXISTS 'shop_app'@'localhost';

DROP ROLE IF EXISTS 'shop_app_role';

删除前检查：

SHOW GRANTS FOR 'shop_app'@'localhost';

还应确认配置中心、CI/CD、定时任务和其他服务是否仍在使用该账号。

8. 权限范围和常见权限

8.1 权限范围

-- 全局范围
ON *.*

-- 数据库范围
ON shop.*

-- 表范围
ON shop.orders

授权范围越大，账号被泄露后的影响越大。优先限制到真实需要的数据库或表。

8.2 高频权限

权限	作用	常见账号
`SELECT`	查询数据	应用、只读账号
`INSERT`	新增数据	应用账号
`UPDATE`	更新数据	应用账号
`DELETE`	删除数据	应用账号，按需授予
`CREATE`	创建对象	迁移账号
`ALTER`	修改表结构	迁移账号
`DROP`	删除对象	迁移账号，谨慎授予
`INDEX`	创建或删除索引	迁移账号
`REFERENCES`	创建外键	迁移账号
`EXECUTE`	执行存储过程或函数	按需授予
`GRANT OPTION`	把自己的权限继续授予他人	仅权限管理员

不要给普通业务账号授予：

GRANT ALL PRIVILEGES ON *.* ... WITH GRANT OPTION;

这会显著扩大账号泄露或 SQL 注入后的影响范围。

9. `FLUSH PRIVILEGES` 是否需要

通过标准账号和权限语句操作时：

CREATE USER ...;
ALTER USER ...;
GRANT ...;
REVOKE ...;
DROP USER ...;

通常不需要执行 FLUSH PRIVILEGES，变更会立即生效。

不应直接修改 mysql.user 等系统权限表。只有在特殊维护场景中直接修改了授权表，才可能需要重新加载权限；正常项目不应采用这种方式。

10. Node.js 应用账号建议

数据库连接配置示意：

import mysql, { type PoolOptions } from "mysql2/promise";

const password: string | undefined = process.env.DB_PASSWORD;

if (password === undefined) {
  throw new Error("缺少 DB_PASSWORD 环境变量");
}

const poolOptions: PoolOptions = {
  host: process.env.DB_HOST ?? "127.0.0.1",
  port: 3306,
  user: "shop_app",
  password,
  database: "shop",
  connectionLimit: 10,
  charset: "utf8mb4",
};

export const pool = mysql.createPool(poolOptions);

实践建议：

密码通过环境变量或密钥管理系统读取，不写入源码。
应用账号只授予所需数据库的 CRUD 权限。
数据库迁移使用独立的高权限账号。
报表和人工排障使用只读账号。
远程连接使用 TLS，并限制来源网络。
账号标识符使用 ASCII；中文业务数据使用 utf8mb4。

mysql2 的具体字符集参数要以当前驱动版本文档为准。连接建立后可以查询 @@character_set_client 等变量，验证配置是否真正生效。

11. 常见问题

11.1 授权后仍然无权限

检查实际匹配账号：

SELECT USER(), CURRENT_USER();
SHOW GRANTS;

常见原因是 Host 不匹配，例如授权给 'shop_app'@'localhost'，但应用从另一台服务器连接。

11.2 可以登录但中文乱码

这通常不是 DCL 权限问题，应检查：

SELECT
  @@character_set_client,
  @@character_set_connection,
  @@character_set_results;

同时检查数据库、表、字段字符集和 Node.js 连接配置是否统一为 utf8mb4。

11.3 只读账号为什么还能造成压力

SELECT 不会直接修改业务数据，但没有索引的大查询、全表扫描和复杂聚合仍可能占用 CPU、内存和 I/O。权限控制不能替代查询治理和资源限制。

11.4 是否需要给应用账号 DDL 权限

通常不需要。把运行应用和执行数据库迁移分开：

shop_app：日常 SELECT、INSERT、UPDATE、DELETE。
shop_migration：发布期间执行 CREATE、ALTER、DROP、INDEX。

这样即使应用发生 SQL 注入，攻击范围也不会直接扩大到删除表结构。

12. DCL 安全检查清单

账号 Host 是否限制到真实来源？
是否遵循最小权限原则？
应用、迁移、只读账号是否分离？
是否错误授予了 ALL PRIVILEGES ON *.*？
是否错误授予了 GRANT OPTION？
远程连接是否要求 TLS 并受防火墙保护？
密码是否使用高强度随机值并安全保存？
删除或改密前是否检查全部调用方？
授权后是否用 SHOW GRANTS 和测试账号验证？
中文业务数据的连接字符集是否实际为 utf8mb4？

13. 一页式 DCL 模板

-- 创建应用账号
CREATE USER 'shop_app'@'localhost'
IDENTIFIED BY '高强度随机密码';

-- 授予 CRUD 权限
GRANT SELECT, INSERT, UPDATE, DELETE
ON shop.*
TO 'shop_app'@'localhost';

-- 创建只读账号
CREATE USER 'shop_readonly'@'10.10.%'
IDENTIFIED BY '高强度随机密码'
REQUIRE SSL;

GRANT SELECT
ON shop.*
TO 'shop_readonly'@'10.10.%';

-- 查看权限
SHOW GRANTS FOR 'shop_app'@'localhost';

-- 撤销权限
REVOKE DELETE
ON shop.*
FROM 'shop_app'@'localhost';

-- 修改密码
ALTER USER 'shop_app'@'localhost'
IDENTIFIED BY '新的高强度随机密码';

-- 锁定和解锁
ALTER USER 'shop_app'@'localhost' ACCOUNT LOCK;
ALTER USER 'shop_app'@'localhost' ACCOUNT UNLOCK;

-- 删除账号
DROP USER IF EXISTS 'shop_app'@'localhost';