DBA | MySQL 数据库基础用户和数据权限管理实践

大家好，我是?WeiyiGeek，一名深耕安全运维开发（SecOpsDev）领域的技术从业者，致力于探索DevOps与安全的融合（DevSecOps），自动化运维工具开发与实践，企业网络安全防护，欢迎各位道友一起学习交流、一起进步 ，若此文对你有帮助，一定记得倒点个关注?与小红星??，收藏学习不迷路??。

0x00 前言简述

描述：上一章，我们学习了基础的 DQL 查询语句，了解了如何从数据库中检索数据。在本章中，我们将深入学习 DCL（Data Control Language）—— 数据控制语言，它主要负责管理用户权限、锁定和解锁表，以及用户访问操作数据库、表权限等操作。通过掌握DCL，你将能够更好地管理和保护你的数据库资源。

DCL 数据控制语言（Data Control Language）是用来定义数据库对象（用户、角色）、操纵数据、控制数据访问权限的语言。简单的说，DCL 决定了有那些用户可以访问数据库服务，以及用户可访问操作数据库中的那些数据，最后还能控制针对那些表可写、那些表可读。

温馨提示：若文章代码块中存在乱码，请通过文末的阅读原文链接，在知识星球中阅读，或者直接访问?https://articles.zsxq.com/id_hxrnaptolc5z.html

0x01 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?更安全，但你可能因为兼容性问题遇到连接错误（例如提示“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

上述的权限太多看着脑袋疼，以我们先来学习开发场景中常用的几种数据库权限，例如：

查询权限

-- # 语法
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)

总结

1. 用户管理：通过

CREATE USER, DROP USER, RENAME USER, ALTER USER

1. ?管理 MySQL 中的用户。权限管理：通过

GRANT, REVOKE

1. ?管理用户对数据库的访问和操作权限。用户角色：通过

CREATE ROLE, DROP ROLE

1. ?管理用户角色，并通过

GRANT, REVOKE

?将权限授予或撤销给角色。