数据控制语言 DCL

作者：IvanCodes
日期：2025年6月7日

核心目标： 学习如何管理 MySQL 用户账户及其对数据库对象的访问权限，确保数据库安全。

主要命令： CREATE USER, ALTER USER, DROP USER, GRANT, REVOKE.

前提： DCL 操作通常需要具有相应管理权限的用户（如 root 用户或拥有 CREATE USER、GRANT 等权限的用户）来执行。

1. 用户管理

创建用户 (create user)
作用：创建新的 MySQL 用户账户。
语法：

create user 'username'@'host' identified by 'password';

说明：

• 'username': 要创建的用户名。
• 'host': 指定用户可以从哪个主机连接 ('localhost', '%', 特定 IP)。
• identified by 'password': 设置用户的登录密码。务必使用强密码！

示例:

-- 创建一个只能本地登录，密码为 'pass123' 的用户 'appuser'
create user 'appuser'@'localhost' identified by 'pass123';

-- 创建一个可以从任何地方登录，密码为 'complex_Pwd!@#' 的用户 'remote_admin'
create user 'remote_admin'@'%' identified by 'complex_Pwd!@#';

-- 创建一个只能从特定 IP 登录的用户
create user 'report_user'@'192.168.1.50' identified by 'report_secret';

查看用户
作用：列出 MySQL 中的用户账户。
语法 (查询系统表):

select user, host from mysql.user;

修改用户 (alter user)
作用：修改现有用户的属性，最常用的是修改密码。
语法 (修改密码 - 推荐方式):

alter user 'username'@'host' identified by 'new_password';

语法 (其他修改 - 不常用):

-- rename user 'olduser'@'oldhost' to 'newuser'@'newhost'; -- 重命名
-- alter user 'username'@'host' account lock; -- 锁定
-- alter user 'username'@'host' account unlock; -- 解锁

示例:

-- 修改 'appuser'@'localhost' 的密码为 'newStrongPass456'
alter user 'appuser'@'localhost' identified by 'newStrongPass456';

删除用户 (drop user)
作用：永久删除一个用户账户。
语法：

drop user 'username'@'host';

!!! 警告：删除用户是不可逆的 !!!

示例:

-- 删除本地用户 'testuser'
drop user 'testuser'@'localhost';

-- 删除远程用户 'old_admin'
drop user 'old_admin'@'%';

2. 权限管理

权限 (Privileges) 概念:
定义用户能在数据库上执行的操作。常见权限：select, insert, update, delete, create, drop, alter, all privileges 等。权限作用域：全局 (*.*), 数据库 (database_name.*), 表 (database_name.table_name)。

授予权限 (grant)
作用：给用户分配操作数据库对象的权限。
语法：

grant privilege_list on object_type to 'username'@'host' [with grant option];

说明：

• privilege_list: 权限列表 (如 select, insert) 或 all privileges。
• object_type: 权限作用的对象 (如 db_name.*, db_name.table_name, *.*)。
• with grant option: (可选) 允许该用户将权限授予他人（危险）。

示例:

-- 授予 'appuser'@'localhost' 对 'company_db' 所有表的 select, insert, update 权限
grant select, insert, update on company_db.* to 'appuser'@'localhost';

-- 授予 'report_user'@'192.168.1.50' 对 'sales_db.orders' 表的只读权限
grant select on sales_db.orders to 'report_user'@'192.168.1.50';

-- 授予 'db_admin'@'localhost' 对 'inventory_db' 的所有权限并允许授权
grant all privileges on inventory_db.* to 'db_admin'@'localhost' with grant option;

-- 授予创建数据库的全局权限 (谨慎)
grant create on *.* to 'developer'@'localhost';

查看权限 (show grants)
作用：显示指定用户拥有的权限。
语法：

show grants for 'username'@'host';

示例:

-- 查看 'appuser'@'localhost' 的权限
show grants for 'appuser'@'localhost';

-- 查看当前登录用户的权限
show grants;
-- 或者
-- show grants for current_user();

撤销权限 (revoke)
作用：收回已授予用户的权限。
语法：

revoke privilege_list on object_type from 'username'@'host';

注意：privilege_list 和 object_type 需与 grant 时匹配。撤销 grant option 使用 revoke grant option on ... from ...。

示例:

-- 从 'appuser'@'localhost' 收回对 'company_db' 的 update 权限
revoke update on company_db.* from 'appuser'@'localhost';

-- 从 'report_user'@'192.168.1.50' 收回对 'sales_db.orders' 的 select 权限
revoke select on sales_db.orders from 'report_user'@'192.168.1.50';

-- 撤销 'db_admin'@'localhost' 对 'inventory_db' 的所有权限
revoke all privileges on inventory_db.* from 'db_admin'@'localhost';

-- 撤销 'db_admin'@'localhost' 的授权能力
revoke grant option on inventory_db.* from 'db_admin'@'localhost';

3. 重要说明与最佳实践

• flush privileges;: 通常不需要手动执行。仅在直接修改系统权限表（不推荐）后才需要。标准 DCL 命令会自动刷新权限。
• 最小权限原则: 只授予必需的最小权限。
• 应用程序用户: 为应用创建独立用户，精确授权。
• 强密码策略: 使用复杂密码。
• 定期审查: 定期检查用户和权限。

练习题 (Practice Exercises - DCL with Answers)

假设你以 root 用户或其他有足够权限的用户登录。

1. 创建一个新用户 readonly_user，只能从本地 (localhost) 登录，密码设置为 ReadOnly123。
   答案:

   create user 'readonly_user'@'localhost' identified by 'ReadOnly123';
   

2. 授予 readonly_user 对数据库 mydatabase 中所有表的只读权限 (select)。
   答案:

   grant select on mydatabase.* to 'readonly_user'@'localhost';
   

3. 查看 readonly_user 现在拥有的权限。
   答案:

   show grants for 'readonly_user'@'localhost';
   

4. 创建一个新用户 webapp，可以从任何主机 (%) 登录，密码为 WebAppSecurePwd!.
   答案:

   create user 'webapp'@'%' identified by 'WebAppSecurePwd!';
   

5. 授予 webapp 用户对 mydatabase 数据库中的 users 表和 orders 表执行 select, insert, update 操作的权限。
   答案:

   grant select, insert, update on mydatabase.users to 'webapp'@'%';
   grant select, insert, update on mydatabase.orders to 'webapp'@'%';
   

6. 修改 readonly_user 的密码为 NewPass456。
   答案:

   alter user 'readonly_user'@'localhost' identified by 'NewPass456';
   

7. 撤销 webapp 用户对 mydatabase.users 表的 update 权限。
   答案:

   revoke update on mydatabase.users from 'webapp'@'%';
   

8. 查看 webapp 用户现在的权限，确认 update on users 已被撤销。
   答案:

   show grants for 'webapp'@'%';
   

9. 删除用户 readonly_user。
   答案:

   drop user 'readonly_user'@'localhost';