返回文章列表 →

Note

DCL-数据控制

记录 MySQL 用户管理与权限控制的常见语法

发布于 更新于

DCL

DCL 的英文全称是 Data Control Language（数据控制语言），主要用来管理数据库用户以及控制用户对数据库的访问权限。在 MySQL 中，常见操作包括创建、修改和删除用户，以及使用 GRANT、REVOKE 管理权限。

用户和权限操作通常需要管理员账号或具备相应管理权限的账号执行。实际配置时应遵循最小权限原则，只授予账号完成工作所必需的权限。

用户管理

MySQL 账户

MySQL 账户由用户名和允许连接的主机共同确定，完整格式为：

'用户名'@'主机名'

例如，'app_user'@'localhost' 和 'app_user'@'10.0.0.25' 是两个不同的账户：

  • 'app_user'@'localhost'：只允许从数据库服务器本机连接。
  • 'app_user'@'10.0.0.25'：只允许从指定 IP 地址连接。

用户名和主机名建议始终使用单引号分别包裹，并且不要省略主机名。省略主机名等价于使用 '%'；'%' 可以匹配任意主机，但会扩大账号的访问来源，而且 MySQL 8.4 已弃用在主机部分使用 % 和 _ 通配符，因此新配置应优先使用 localhost、明确的主机名、IP 地址或受控网段。

查询用户

查询 MySQL 中的账户列表：

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

与直接执行 SELECT * FROM mysql.user 相比，显式列出所需字段更容易阅读，也能避免读取无关的账户信息。查询系统授权表需要相应权限。

查询当前连接使用的登录身份和服务器实际用于权限校验的账户：

SELECT USER(), CURRENT_USER();
  • USER() 返回客户端连接时提供的用户名和来源主机。
  • CURRENT_USER() 返回 MySQL 完成账户匹配后用于权限校验的账户。

查询指定账户的完整定义：

SHOW CREATE USER '用户名'@'主机名';

创建用户

CREATE USER [IF NOT EXISTS] '用户名'@'主机名'
IDENTIFIED BY '密码';

IF NOT EXISTS 可以避免账户已存在时语句报错。新创建的账户默认没有业务数据的访问权限，需要再通过 GRANT 授权。

创建一个只允许从数据库服务器本机连接的用户：

CREATE USER 'dcl_demo_user'@'localhost'
IDENTIFIED BY 'S7rong!Demo#2026';

创建一个只允许从指定 IP 地址连接的用户：

CREATE USER 'dcl_remote_user'@'10.0.0.25'
IDENTIFIED BY 'S7rong!Remote#2026';

示例密码仅用于演示语法。生产环境应使用随机生成且符合密码策略的独立密码，并通过安全的密钥管理方式保存；不要将真实密码提交到代码仓库。服务器启用了密码校验策略时，不符合策略的密码会被拒绝。

修改用户密码

管理员修改指定账户的密码：

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

当前登录用户修改自己的密码：

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

普通改密不需要显式指定认证插件，MySQL 会继续使用该账户的认证配置处理新密码。只有在确实需要迁移认证方式时，才应通过 IDENTIFIED WITH 指定插件。

删除用户

DROP USER [IF EXISTS] '用户名'@'主机名';

DROP USER 会删除账户及其权限。删除前应确认该账户不再被应用程序使用，并检查视图、触发器、事件或存储程序等对象是否将它作为 DEFINER，避免对象失去有效的定义者。

用户管理示例

下面的示例依次创建用户、查询用户、修改密码并删除用户：

-- 创建用户
CREATE USER 'dcl_demo_user'@'localhost'
IDENTIFIED BY 'S7rong!Demo#2026';

-- 查询用户
SELECT User, Host, plugin, account_locked
FROM mysql.user
WHERE User = 'dcl_demo_user' AND Host = 'localhost';

-- 查询用户定义
SHOW CREATE USER 'dcl_demo_user'@'localhost';

-- 修改密码
ALTER USER 'dcl_demo_user'@'localhost'
IDENTIFIED BY 'N3w!Demo#2026';

-- 删除用户
DROP USER 'dcl_demo_user'@'localhost';

权限控制

MySQL 权限决定账户能够访问哪些数据库对象，以及能够对这些对象执行哪些操作。权限既有 SELECT、INSERT 等内置的静态权限，也有由服务器组件在运行时提供的动态权限；可以使用 SHOW PRIVILEGES 查看当前服务器支持的权限列表。

常用权限

权限 说明
ALL、ALL PRIVILEGES 当前授权范围内可授予的全部权限，不包含 GRANT OPTION
SELECT 查询表中的数据
INSERT 向表中插入数据
UPDATE 修改表中的数据
DELETE 删除表中的数据
CREATE 在授权范围内创建数据库、表或索引
ALTER 修改表结构；某些具体操作还需要其他相关权限
DROP 删除数据库、表或视图；执行 TRUNCATE TABLE 也需要该权限

上表只列出了教程中常见的权限，并不是 MySQL 的完整权限集合。

权限作用范围

GRANT 和 REVOKE 通过 ON 后面的对象确定权限作用范围：

写法 作用范围
*.* 当前 MySQL 服务器中的所有数据库，属于全局范围
`数据库名`.* 指定数据库中的所有对象
`数据库名`.`表名` 指定数据库中的一张表

数据库名和表名属于标识符，包含特殊字符或与关键字冲突时使用反引号包裹；'用户名'@'主机名' 属于账户名，使用单引号包裹。全局权限影响范围很大，除专门的管理账号外，应优先选择数据库级或表级授权。

查询权限

查询当前登录账户的权限：

SHOW GRANTS;

查询指定账户的权限：

SHOW GRANTS FOR '用户名'@'主机名';

查询当前服务器支持的权限：

SHOW PRIVILEGES;

授予权限

GRANT 权限列表
ON 权限作用范围
TO '用户名'@'主机名';

一次授予多个权限时使用逗号分隔。GRANT 只负责授权，目标账户应先通过 CREATE USER 创建。

如果在语句末尾添加 WITH GRANT OPTION，目标账户还可以把自己拥有的这些权限授予其他账户。该能力应谨慎授予，并且不包含在 ALL PRIVILEGES 中。

撤销权限

撤销指定范围内的一个或多个权限：

REVOKE 权限列表
ON 权限作用范围
FROM '用户名'@'主机名';

撤销账户直接获得的全部权限及转授权能力：

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM '用户名'@'主机名';

GRANT 和 REVOKE 会更新 MySQL 授权表，不需要再执行 FLUSH PRIVILEGES。如果正在使用目标账户验证权限变化，重新连接可以避免已有会话状态干扰验证结果。

权限控制示例

数据准备

使用管理员账户创建示例数据库、数据表和四个不同职责的账户：

CREATE DATABASE IF NOT EXISTS dcl_demo;

CREATE TABLE IF NOT EXISTS dcl_demo.employee (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL
);

CREATE USER IF NOT EXISTS 'report_reader'@'localhost'
IDENTIFIED BY 'R3port!Demo#2026';

CREATE USER IF NOT EXISTS 'data_editor'@'localhost'
IDENTIFIED BY 'Ed1tor!Demo#2026';

CREATE USER IF NOT EXISTS 'schema_maintainer'@'localhost'
IDENTIFIED BY 'Sch3ma!Demo#2026';

CREATE USER IF NOT EXISTS 'database_manager'@'localhost'
IDENTIFIED BY 'DbM4nage!Demo#2026';

重复执行 CREATE USER IF NOT EXISTS 时，已存在账户的密码不会被更新；需要修改已有账户密码时应使用 ALTER USER。

授予查询权限

报表账号只需要读取员工表，因此仅授予 SELECT：

GRANT SELECT
ON dcl_demo.employee
TO 'report_reader'@'localhost';
授予数据操作权限

数据维护账号需要查询、添加、修改和删除员工数据，因此授予 SELECT、INSERT、UPDATE、DELETE：

GRANT SELECT, INSERT, UPDATE, DELETE
ON dcl_demo.employee
TO 'data_editor'@'localhost';
授予结构管理权限

表结构维护账号需要在示例数据库中创建、修改和删除表，因此授予 CREATE、ALTER、DROP：

GRANT CREATE, ALTER, DROP
ON dcl_demo.*
TO 'schema_maintainer'@'localhost';
授予数据库内的全部权限

数据库管理账号需要管理示例数据库中的全部对象，因此在数据库范围内授予 ALL PRIVILEGES：

GRANT ALL PRIVILEGES
ON dcl_demo.*
TO 'database_manager'@'localhost';

这里的 ALL PRIVILEGES 只作用于 dcl_demo.*，不会自动获得其他数据库的权限、服务器级管理权限或 GRANT OPTION。

查询授权结果
SHOW GRANTS FOR 'report_reader'@'localhost';
SHOW GRANTS FOR 'data_editor'@'localhost';
SHOW GRANTS FOR 'schema_maintainer'@'localhost';
SHOW GRANTS FOR 'database_manager'@'localhost';
撤销权限

撤销报表账号的查询权限、数据维护账号的删除权限，以及结构维护账号的修改和删除表权限：

REVOKE SELECT
ON dcl_demo.employee
FROM 'report_reader'@'localhost';

REVOKE DELETE
ON dcl_demo.employee
FROM 'data_editor'@'localhost';

REVOKE ALTER, DROP
ON dcl_demo.*
FROM 'schema_maintainer'@'localhost';

撤销数据库管理账号在示例数据库中的全部权限：

REVOKE ALL PRIVILEGES
ON dcl_demo.*
FROM 'database_manager'@'localhost';
清理示例

确认不再需要示例数据后，使用管理员账户删除示例用户和数据库：

DROP USER IF EXISTS
    'report_reader'@'localhost',
    'data_editor'@'localhost',
    'schema_maintainer'@'localhost',
    'database_manager'@'localhost';

DROP DATABASE IF EXISTS dcl_demo;