芯耀
1914
大家好，我是 WeiyiGeek，一名深耕安全运维开发（SecOpsDev）领域的技术从业者，致力于探索DevOps与安全的融合（DevSecOps），自动化运维工具开发与实践，企业网络安全防护，欢迎各位道友一起学习交流、一起进步 ，若此文对你有帮助，一定记得倒点个关注⭐与小红星❤️，收藏学习不迷路 。
前言简述
描述：上一章，我们学习了基础的 DQL 查询语句，了解了如何从数据库中检索数据。在本章中，我们将深入学习 DCL（Data Control Language）—— 数据控制语言，它主要负责管理用户权限、锁定和解锁表，以及用户访问操作数据库、表权限等操作。通过掌握DCL，你将能够更好地管理和保护你的数据库资源。
DCL 数据控制语言（Data Control Language）是用来定义数据库对象（用户、角色）、操纵数据、控制数据访问权限的语言。简单的说，DCL 决定了有那些用户可以访问数据库服务，以及用户可访问操作数据库中的那些数据，最后还能控制针对那些表可写、那些表可读。
温馨提示：若文章代码块中存在乱码，请通过文末的阅读原文链接，在知识星球中阅读，或者直接访问 https://articles.zsxq.com/id_hxrnaptolc5z.html
01 DCL 数据控制语句
1.MySQL 用户管理
描述：数据库用户管理，包括创建用户、删除用户、修改密码、用户更改、用户锁定解锁等这类SQL操作，开发人员一般不会直接操作，主要是由企业的DBA（数据库管理员）或者运维管理员进行操作。
查询用户
-- 切换到mysql数据库 USE mysql; -- 查询用户列表及主机地址其是否允许远程连接 SELECT user, host FROM user; -- 查询用户密码套件，以及过期、锁定相关配置 SELECT user, host,plugin,authentication_string,password_expired,password_lifetime,account_locked FROM user;weiyigeek.top-查询MySQL用户信息图
创建用户
-- # 语法
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '密码';
-- # 示例
-- 创建用户local，仅允许从localhost连接
CREATE USER 'local'@'localhost' IDENTIFIED BY'password';
-- 创建用户test，允许从任意主机连接，并使用以前的 mysql_native_password 加密方式
CREATE USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
-- 查询创建用户信息
mysql> SELECT user, host,plugin,authentication_string,password_expired,password_lifetime,account_locked FROM user WHERE user in ('test','local');
| user | host | plugin | authentication_string | password_expired | password_lifetime | account_locked |
+-------+-----------+-----------------------+-----------------------------------------------------------------+------------------+-------------------+----------------+
| test | % | mysql_native_password | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | N | NULL | N |
| local | localhost | caching_sha2_password | $A$005$cL%0fZ^xf/}s;8uyr/VXKX/2CVPT7bFQtQjtZo8ecterO6yh.PSxLZx8 | N | NULL | N |
weiyigeek.top-navicate使用创建的用户链接到数据库图
温馨提示：MySQL 8.x 出于提升安全性和性能的考虑将 caching_sha2_password 作为默认的身份验证插件，取代了之前版本中的 mysql_native_password，如果要使用 mysql_native_password 加密方式，则需要创建时指定，或者使用 ALTER USER 手动修改用户的认证插件。
知识扩展：MySQL 8.x 两种身份验证插件 caching_sha2_password 和 mysql_native_password 的区别：
| 特性对比 | caching_sha2_password | mysql_native_password |
|---|---|---|
| 加密算法 | SHA-256（更安全） | SHA-1（相对较弱） |
| 性能表现 | 有缓存机制，性能较好 | 无缓存 |
| 安全性 | 更高，支持双重认证，依赖安全连接或RSA密钥 | 相对较低 |
| 兼容性 | 需要MySQL 8.0+客户端或特定版本驱动（部分老客户端可能报错） | 兼容绝大多数旧版客户端和连接器 |
| 连接要求 | 默认需要安全连接（SSL/TLS）或RSA密钥交换 | 无特殊要求 |
特别注意：虽然 caching_sha2_password 更安全，但你可能因为兼容性问题遇到连接错误（例如提示“Authentication plugin 'caching_sha2_password' cannot be loaded”），尤其是在使用旧版客户端或某些编程语言的数据库驱动时（如某些旧版本的PHP mysqli 扩展或 Python 连接器），其解决方案如下：
1.升级客户端或驱动到支持caching_sha2_password 的版本（首选的长期方案，但是存在改造成本）。
2.如果暂时无法升级客户端，可手动将用户身份验证插件设置为mysql_native_password ，例如：ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';，若存在大量旧版客户端连接需求，可以在MySQL服务器配置文件中[mysqld] 段落下设置default_authentication_plugin=mysql_native_password 参数，使其对新创建的用户默认使用该插件。
用户更名
-- # 语法
RENAME USER '旧用户名'@'主机名' TO '新用户名'@'主机名';
-- 示例
-- 将local用户更名为local_new
RENAME USER 'local'@'localhost' TO 'local_new'@'localhost';
Query OK, 0 rows affected (0.07 sec)修改用户
-- # 语法
-- 修改用户密码及其用户身份验证插件
ALTER USER '用户名'@'主机名' IDENTIFIED BY'密码';
ALTER USER '用户名'@'主机名' IDENTIFIED WITH [ caching_sha2_password | mysql_native_password] BY '密码';
-- 锁定、解锁用户
ALTER USER '用户名'@'主机名' ACCOUNT LOCK;
ALTER USER '用户名'@'主机名' ACCOUNT UNLOCK;
-- # 示例
-- 1.修改local_new用户密码以及更新其身份验证插件为默认的 caching_sha2_password
ALTER USER 'local_new'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'newpassword';
-- 查看修改结果
mysql> SELECT user, host,plugin,authentication_string,password_expired,password_lifetime,account_locked FROM user WHERE user='local_new';
| user | host | plugin | authentication_string | password_expired | password_lifetime | account_locked |
+-------+-----------+-----------------------+------------------------------------------------------------------------+------------------+-------------------+----------------+
| local | localhost | caching_sha2_password | $A$005***************** | N | NULL | N |
-- 2.锁定与解锁 local_new 用户
ALTER USER 'local_new'@'localhost' ACCOUNT LOCK;
-- 使用 local_new 用户登录，会发现无法连接，并提示账户被锁定
[root@weiyigeek_top ~]# mysql -u local_new -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'local_new'@'localhost'. Account is locked.
-- 解锁 local_new 用户
ALTER USER 'local_new'@'localhost' ACCOUNT UNLOCK;删除用户
-- # 语法
DROP USER'用户名'@'主机名';
-- # 示例
-- 删除用户 local_new
DROP USER 'local_new'@'localhost';
Query OK, 0 rows affected (0.10 sec)
-- 注意：删除用户时，其主机名需要精确匹配，否则会报错，例如
mysql> DROP USER 'test'@'localhost';
ERROR 1396 (HY000): Operation DRO PUSER failed for 'test'@'localhost'
-- 正确写法如下：
mysql> DROP USER 'test'@'%';
2.MySQL 权限管理
描述：前面我们创建了用户，我们发现用户是可以连接到MySQL服务，但是还不能访问业务数据库中的数据，例如，上面创建的 test 用户，只能够访问自带的 information_schema 库，原因是因为没有为其分配任何权限，那么这个用户是无法访问到其它库中的数据，所以接下来就需要给这些用户分配权限，让其可以访问数据库中的数据。
这里在学习权限管理之前，我们需要先了解 MySQL 中到底有那些权限，作者在 MySQL 官网复制了一份 GRANT 和 REVOKE 的允许静态权限表格，详细的权限描述及函数，可直接参考官方文档地址：https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html
| 权限 | 授权表对应字段名称 | 上下文 |
|---|---|---|
ALL [PRIVILEGES] |
Synonym for “all privileges” | Server administration |
ALTER |
Alter_priv |
Tables |
ALTER ROUTINE |
Alter_routine_priv |
Stored routines |
CREATE |
Create_priv |
Databases, tables, or indexes |
CREATE ROLE |
Create_role_priv |
Server administration |
CREATE ROUTINE |
Create_routine_priv |
Stored routines |
CREATE TABLESPACE |
Create_tablespace_priv |
Server administration |
CREATE TEMPORARY TABLES |
Create_tmp_table_priv |
Tables |
CREATE USER |
Create_user_priv |
Server administration |
CREATE VIEW |
Create_view_priv |
Views |
DELETE |
Delete_priv |
Tables |
DROP |
Drop_priv |
Databases, tables, or views |
DROP ROLE |
Drop_role_priv |
Server administration |
EVENT |
Event_priv |
Databases |
EXECUTE |
Execute_priv |
Stored routines |
FILE |
File_priv |
File access on server host |
GRANT OPTION |
Grant_priv |
Databases, tables, or stored routines |
INDEX |
Index_priv |
Tables |
INSERT |
Insert_priv |
Tables or columns |
LOCK TABLES |
Lock_tables_priv |
Databases |
PROCESS |
Process_priv |
Server administration |
PROXY |
See proxies_priv table |
Server administration |
REFERENCES |
References_priv |
Databases or tables |
RELOAD |
Reload_priv |
Server administration |
REPLICATION CLIENT |
Repl_client_priv |
Server administration |
REPLICATION SLAVE |
Repl_slave_priv |
Server administration |
SELECT |
Select_priv |
Tables or columns |
SHOW DATABASES |
Show_db_priv |
Server administration |
SHOW VIEW |
Show_view_priv |
Views |
SHUTDOWN |
Shutdown_priv |
Server administration |
SUPER |
Super_priv |
Server administration |
TRIGGER |
Trigger_priv |
Tables |
UPDATE |
Update_priv |
Tables or columns |
USAGE |
Synonym for “no privileges” | Server administration |
上述的权限太多看着脑袋疼，以我们先来学习开发场景中常用的几种数据库权限，例如：
| 权限名称 | 权限说明 |
|---|---|
| ALL , ALL PRIVILEGES | 所有权限，等同于其它所有权限的总和。 |
| SELECT | 查询数据权限 |
| INSERT | 插入数据权限 |
| UPDATE | 更新数据权限 |
| DELETE | 删除数据权限 |
| CREATE | 创建数据库或表权限 |
| ALTER | 修改表结构权限 |
| DROP | 删除数据库或表或视图权限 |
查询权限
-- # 语法
SHOW GRANTS FOR '用户名'@'登录主机';
SELECT * FROM mysql.user WHERE user='用户名' AND host='登录主机';
-- # 示例：
-- 方式1.查询 test、root 用户权限：
mysql > SHOW GRANTS FOR 'test'@'%';
| Grants for test@% |
+----------------------------------+
| GRANTUSAGEON *.* TO `test`@`%` | -- USAGE权限，表示没有任何权限
mysql > SHOW GRANTS FOR 'test'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOWDATABASES, SUPER, CREATETEMPORARYTABLES, LOCKTABLES, EXECUTE, REPLICATIONSLAVE, REPLICATIONCLIENT, CREATEVIEW, SHOWVIEW, CREATE ROUTINE, ALTER ROUTINE, CREATEUSER, EVENT, TRIGGER, CREATETABLESPACE, CREATEROLE, DROPROLEON *.* TO`root`@`%`WITHGRANTOPTION -- root 用户拥有所有权限，包括 GRANT OPTION 授权权限。
-- 方式2.通过 mysql.user 表中以 _priv 结尾的字段中查询 root 用户对应权限的字段值。
mysql > DESC mysql.user;
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
-- 查看 mysql.user 查询当前 root 用户权限
mysql> SELECT * FROM mysql.user WHEREUser = 'root';
weiyigeek.top-user表中对应权限的_priv字段图
授予权限
温馨提示：在赋予多个权限时，可以使用逗号分隔每个权限。其次，授权时数据库名和表名可以使用 * 作为通配符，代表所有数据库或所有表，也可指定单个数据库或表，例如：GRANT SELECT ON test.employee TO 'test'@'%'; 表示只授予 test 用户对 test 数据库的 employee 表查询权限。
-- # 语法
GRANT 权限列表 ON 数据库名.表名 TO'用户名'@'登录主机';
-- # 举例
-- 1.授予 test 用户对 test 数据库的查询权限、插入权限、更新权限
GRANT SELECT,INSERT,UPDATE ON test.* TO'test'@'%';
-- Query OK, 0 rows affected (0.08 sec)
mysql> SHOW GRANTS FOR 'test'@'%';
| Grants for test@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
| GRANT SELECT, INSERT, UPDATEON `test`.* TO `test`@`%` |
-- 2.授予 test 用户对 test 数据库的所有权限。
GRANT ALL ON test.* TO'test'@'%';
mysql> SHOW GRANTS FOR'test'@'%';
| Grants for test@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
| GRANT ALL PRIVILEGESON `test`.* TO `test`@`%` |
验证示例1，的结果，新创建一个命令行终端，使用 test 用户登录 mysql 数据库 执行如下图命令，可查看到查询、插入，更新都没问题，由于没有赋予其删除权限，所以报 ERROR 1142 (42000): DELETE command denied to user 'test'@'localhost' for table 'employee' 错误。
mysql -u test -p -- 登录
SHOW DATABASES; -- 查看数据库
USE test; -- 切换 test 数据库
SELECT * FROM employee LIMIT 0,2; -- 查询 employee 表中前两条数据
INSERT INTO employee (uid, name, gender, age, phone_number, skills, id_card, entry_date) VALUES ('009', '唯一极客', '男', 30, '13800138666', '{"编程": "高级", "运维": "高级"}', '50010219900101000x', '2019-01-01'); -- 插入数据
UPDATE employee SET name = 'WeiyiGeek' WHERE uid = '009'; -- 更新数据
weiyigeek.top-验证授予的权限图
撤销权限
-- # 语法
REVOKE 权限列表 ON 数据库名.表名 FROM'用户名'@'登录主机';
-- # 示例
-- 1.撤销 test 用户对 test 数据库的插入权限、更新权限、删除权限。
REVOKE INSERT,UPDATE,DELETEON test.* FROM'test'@'%';
-- 查看撤销权限后的结果
mysql> SHOW GRANTS FOR'test'@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@% |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGEON *.* TO`test`@`%` |
| GRANT SELECT, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATETEMPORARYTABLES, LOCKTABLES, EXECUTE, CREATEVIEW, SHOWVIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGERON`test`.* TO`test`@`%` |
-- 1.撤销 test 用户对 test 数据库的所有权限。
REVOKEALLON test.* FROM'test'@'%';刷新权限
描述： FLUSH PRIVILEGES; 命令的作用是重新加载权限表，使权限的更改立即生效，但并不是所有权限修改后都需要执行它。
知识扩展：MySQL 什么时候需要使用到 FLUSH PRIVILEGES; ? 简单来说，核心原则是：
使用 GRANT, REVOKE, CREATE USER, DROP USER, ALTER USER 等标准权限管理语句时，MySQL 会自动将权限更改应用到内存中，FLUSH PRIVILEGES; 是多余的，不需要执行。
当通过 INSERT, UPDATE, DELETE 等SQL语句直接修改底层权限表（如 mysql.user, mysql.db 等）时，必须手动执行 FLUSH PRIVILEGES; 才能使更改生效。
-- 1. 在 mysql.user 表中插入一条新用户记录
INSERT INTO mysql.user (Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject)
VALUES ('%', 'test', PASSWORD('mypassword'), '', '', '');
-- 2. 为该用户授予特定数据库的所有权限
INSERT INTO mysql.db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, ...)
VALUES ('%', 'test', 'test', 'Y', 'Y', 'Y', 'Y', ...);
-- 3. ！！！关键步骤：重新加载权限，使上面的直接修改生效
FLUSH PRIVILEGES;
特别注意：始终优先使用 GRANT, REVOKE, CREATE USER, ALTER USER 等标准SQL语句来管理权限，这样可以避免忘记执行 FLUSH PRIVILEGES; 导致的问题，也更安全、更符合规范。 只有在不得不直接操作底层权限表时，才需要记住最后一步是 FLUSH PRIVILEGES;。
3.MySQL 用户角色
在 MySQL（8.0及以上版本）中，角色是管理权限的利器，它可以非常便利地管理权限，而无需直接授予或撤销单个用户的具体权限，例如：对多个用户授予相同的权限，只需创建一个角色并赋予这些权限即可。
创建角色
-- # 语法
CREATEROLE [ IFNOTEXISTS ] 角色名称
-- # 示例
CREATE ROLE IF NOT EXISTS test_role;
CREATE ROLE IF NOT EXISTS 'local_role'@'locahost';查看角色
-- # 语法
SHOW GRANTS FOR'角色名称'@'登录主机';
-- 示例
-- 查看角色授予的权限
mysql> SHOW GRANTS FOR'test_role'@'%';
| Grants for test_role@% |
+---------------------------------------+
| GRANTUSAGEON *.* TO`test_role`@`%` |
mysql> SHOW GRANTS FOR'local_role'@'locahost';
| Grants for local_role@locahost |
+-----------------------------------------------+
| GRANTUSAGEON *.* TO`local_role`@`locahost` |授予权限给角色
-- # 语法
-- 授予权限给角色
GRANT 权限列表 ON 数据库名.表名 TO '角色名称';
-- 示例
GRANT SELECT, INSERT, UPDATE ON test.* TO 'test_role';分配角色给用户
-- # 语法
-- 将角色授予用户
GRANT '角色名称' TO '用户名'@'登录主机';
-- 示例
mysql> GRANT 'test_role' TO 'test'@'%';
Query OK, 0 rows affected (0.10 sec)
激活角色（关键步骤）在MySQL中，用户被授予角色后，默认不会自动激活。你需要显式设置默认角色或手动激活，用户才能真正使用角色的权限。
-- 方式1.为该用户设置默认角色
SET DEFAULT ROLE 'test_role' TO 'test'@'%';
-- 方式2.用户在当前会话中手动激活角色，验证时记得切换到指定用户，否则报 ERROR 3530 (HY000): `test_role`@`%` is not granted to `root`@`%`
SET ROLE 'test_role';撤销权限给角色
-- # 语法
REVOKE 权限列表 ON 数据库名.表名 FROM '角色名称';
-- # 示例
REVOKE SELECT, INSERT, UPDATE ON test.* FROM 'test_role';
Query OK, 0 rows affected (0.08 sec)删除角色
-- # 语法
DROP ROLE 角色名称;
-- # 示例
DROP ROLE 'test_role';
DROP ROLE 'local_role'@'locahost';
除了 SHOW GRANTS，MySQL 还提供了一些系统表来查看更详细的信息：
-
- 查看角色关联：
SELECT * FROM mysql.role_edges;
- 可以查看用户与角色、角色与角色之间的授予关系。
mysql> SELECT * FROM mysql.role_edges;
+-----------+-----------+---------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+---------+-------------------+
| % | test_role | % | test | N |
+-----------+-----------+---------+---------+-------------------+
1 row in set (0.00 sec)
-
- 查看默认角色：
SELECT * FROM mysql.default_roles;
- 可以查看哪些用户设置了默认角色。
mysql> SELECT * FROM mysql.default_roles;
+------+------+-------------------+-------------------+
| HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+------+-------------------+-------------------+
| % | test | % | test_role |
+------+------+-------------------+-------------------+
1 row in set (0.00 sec)总结
用户管理：通过CREATE USER, DROP USER, RENAME USER, ALTER USER 管理 MySQL 中的用户。
权限管理：通过GRANT, REVOKE 管理用户对数据库的访问和操作权限。
用户角色：通过CREATE ROLE, DROP ROLE 管理用户角色，并通过GRANT, REVOKE 将权限授予或撤销给角色。
阅读全文
