DCL语句

---

DCL（Data Control Language，数据控制语言） 是 SQL 语言中用于管理数据库用户权限和访问控制的一类命令。它的核心作用是 保障数据库的安全性，通过授权（Grant）与回收（Revoke）机制，控制哪些用户可以访问哪些数据、执行哪些操作。

其核心功能分为两部分：

• 用户管理：创建、查看、修改、删除用户账号；
• 权限管理：授予、查看、撤销用户对数据库对象的操作权限。

1. 用户管理

1.1 查看用户

MySQL 的用户信息存储在系统库 mysql 的 user 表中。

-- 查看所有用户（需有权限）
SELECT user, host FROM mysql.user;

⚠️ 注意：user + host 共同构成一个唯一用户标识（如 'app_user'@'%' 与 'app_user'@'localhost' 是两个不同用户）。

1.2. 创建用户

• 基本语法

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

• 示例

-- 创建一个可从任意主机连接的用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'SecurePass123!';

-- 创建仅限本地连接的用户
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'DevPass456!';

MySQL 8.0 对密码复杂度有一定要求。

1.3. 修改用户

主要用于修改用户密码或认证方式。

• 基本语法

ALTER USER '用户名'@'主机' IDENTIFIED WITH 认证插件 BY '新密码';

ALTER USER '用户名'@'主机' IDENTIFIED WITH 认证插件 BY '新密码';

• MySQL 8.0+ 默认认证插件为 caching_sha2_password
• MySQL 5.7 及更早版本默认为 mysql_native_password
• 刷新权限：FLUSH PRIVILEGES;（部分版本需要）

• 示例

-- 修改密码（推荐方式）
ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'NewPass789!';
ALTER USER 'app_user'@'%' IDENTIFIED WITH caching_sha2_password BY 'NewPass789!';

-- 锁定/解锁用户（MySQL 8.0+）
ALTER USER '用户名'@'主机' ACCOUNT LOCK;   -- 锁定
ALTER USER '用户名'@'主机' ACCOUNT UNLOCK; -- 解锁

1.4. 删除用户

• 基础语法

-- 删除单个用户
DROP USER '用户名'@'主机';

-- 删除多个用户
DROP USER '用户名-1'@'主机-1', '用户名-1'@'主机-1';

2. 权限管理

2.1. 查看授予的权限

• 基础语法

-- 查看当前用户权限
SHOW GRANTS;

-- 查看指定用户权限
SHOW GRANTS FOR '用户名'@'主机';

USAGE 表示“无权限”，是用户存在的标志。

2.2. 授予权限

• 基础语法

GRANT 权限列表 ON 数据库对象 TO '用户'@'主机';

• 示例

-- 授予 test_db 库所有表的 SELECT 和 INSERT 权限
GRANT SELECT, INSERT ON test_db.* TO 'app_user'@'%';

-- 授予对特定表的全部权限
GRANT ALL PRIVILEGES ON test_db.orders TO 'app_user'@'%';

-- 授予全局权限（谨慎！）
GRANT CREATE, DROP ON *.* TO 'dev_user'@'localhost';

-- 允许该用户再授权给他人（慎用！）
GRANT SELECT ON test_db.* TO 'admin'@'localhost' WITH GRANT OPTION;

2.3. 撤销权限

• 基础语法

REVOKE 权限列表 ON 数据库对象 FROM '用户'@'主机';

• 示例

-- 撤销 INSERT 权限
REVOKE INSERT ON test_db.* FROM 'app_user'@'%';

-- 撤销所有权限（保留用户账号）
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'%';

3. 注意事项

1. 权限存储位置
   MySQL 的用户权限信息存储在系统库 mysql 的多张表中（如 user、db、tables_priv 等），修改后需 FLUSH PRIVILEGES 生效（但 GRANT/REVOKE 会自动刷新）。

2. 权限层级
   MySQL 权限分为多个层级：

   • 全局权限（*.*）
   • 数据库级（db.*）
   • 表级（db.table）
   • 列级（db.table (col1, col2)）

3. 最小权限原则
   永远不要给应用账号 GRANT ALL！应遵循：

   • 应用账号：只给 SELECT/INSERT/UPDATE/DELETE
   • 开发账号：可加 CREATE/DROP
   • 管理员账号：才给 GRANT OPTION

4. 安全建议

   • 避免使用 'user'@'%'（允许任意主机连接），尽量指定 IP
   • 定期审计权限：SHOW GRANTS FOR 'user'@'host';
   • 删除不用的账号：DROP USER 'old_user'@'host';