MySQL权限与角色

MySQL

MySQL 权限介绍

MySQL 权限范围可分为三类：

管理整个数据库实例，比如 root 超级管理员，一般只能 localhost 本地访问，有些初学者安装好 MySQL 之后喜欢把 root 的 localhost 改成 %（允许任何 IP 进行访问）。但自己本地学习可以，但是在生产环境严禁这样子。

管理某个或多个指定的库，也可以是所有的数据库。

管理指定的数据库对象（表，视图）或者所有数据库对象上。

实际上还有另一种权限级别，只允许授予字段查询。

有两种方案：

一种是grant select(id) on employees.employees to 'xxx';

另一种人为设置，创建一个只包含允许访问字段的视图，并将权限授权用户访问该视图。

权限权力

介绍完了权限范围，我们再来说说权限的权力。这就好比《人民的名义》里面的赵德汉，他虽然只是一个处长，但是他手里的权力可不少，这让他捞到了不少油水。

让我们先回归正题，我们先登录 root 用户，然后输入 show grants;因为 root 用户的权限是最全的，我们就根据它的值来慢慢来介绍介绍。

SELECT ：权限代表允许从表中查看数据，某些不查询表数据的select执行则不需要此权限，如Select 1+1， Select PI()+2；而且select权限在执行update/delete语句中含有where条件的情况下也是需要的

INSERT：权限代表是否允许在表里插入数据，同时在执行analyze table,optimize table,repair table语句的时候也需要insert权限

UPDATE：权限代表允许修改表中的数据的权限

DELETE：权限代表允许删除行数据的权限

CREATE TABLESPACE：允许创建，修改，删除表空间和日志组的权限

CREATE ROLE：允许创建角色

CREATE：表示允许创建新的数据库和表的权限

CREATE TEMPORARY TABLES：创建临时表的权限

CREATE VIEW：允许创建，修改，删除视图

CREATE ROUTINE：权限代表允许创建存储过程、函数的权限

CREATE USER：权限代表允许创建、修改、删除、重命名user的权限

INDEX：创建，修改，删除索引的权限

TRIGGER：允许创建或删除触发器

DROP：代表允许删除数据库，表，视图的权限，该权限包括 truncate table 命令。

DROP ROLE：允许删除角色

RELOAD：表示允许执行 flush 命令，指明重新加载权限表到系统内存里，refresh 命令代表关闭和重新开启日志文件并刷新所有的表。

SHUTDOWN：表示允许关闭数据库实例，执行语句包括 mysqladmin shutdown

PROCESS：权限代表允许查看MySQL中的进程信息，比如执行show processlist, mysqladmin processlist, show engine等命令

FILE:权限代表允许在MySQL可以访问的目录进行读写磁盘文件操作，可使用的命令包括load data infile,select … into outfile,load file()函数

REFERENCES：表示是否允许创建外键

ALTER：代表允许修改表结构的权限，但必须要求有 create 和 insert 权限配合。如果是 rename 表名，则要求有 alter，drop，create 和 insert 的权限。

SUPER：代表语序执行一系列数据库管理命令，包括 kill 强制关闭某个连接命令，change master to 创建复制关系命令等等

LOCK TABLES：允许用户加锁或者解锁

EXECUTE：允许执行存储过程和函数的权限

REPLICATION SLAVE：权限代表允许slave主机通过此用户连接master以便建立主从复制关系

REPLICATION CLIENT：权限代表允许执行show master status,show slave status,show binary logs命令

SHOW DATABASES：查看所有数据库名的权限

SHOW VIEW：允许查看视图

ALTER ROUTINE：权限代表允许创建存储过程、函数的权限

EVENT：权限代表允许查询，创建，修改，删除MySQL事件

笔者这里的 MySQL 版本为 8.0.41，可能会与各位的不太一样，输出结果会有偏差属于正常现象。

我们介绍了第一行的权限，还有第二行的，这些是MySQL 8.0 引入的动态权限（Dynamic Privileges），用于更细粒度地控制用户权限。与传统的全局权限（如 ALL PRIVILEGES、CREATE、SELECT 等）不同，动态权限通常针对特定管理操作或高级功能，允许管理员更灵活地分配权限。我们也简单介绍下

以下是这些动态权限的简要分类和用途：

1. 管理类权限

AUTHENTICATION_POLICY_ADMIN：配置认证策略。

APPLICATION_PASSWORD_ADMIN：管理双密码功能（用于密码轮换）

PASSORDLESS_USER_ADMIN：管理无密码认证用户

ROLE_ADMIN：允许授予或撤销角色（即使用户没有该角色的权限）

SYSTEM_USER：标记用户为“系统账户”（不受其他用户权限回收影响）。

2. 复制与集群

BINLOG_ADMIN：管理二进制日志（如PURGEBINARYLOGS）。

BINLOG_ENCRYPTION_ADMIN：启用/禁用二进制日志加密。

GROUP_REPLICATION_ADMIN：管理组复制（GroupReplication）。

REPLICATION_SLAVE_ADMIN：配置主从复制。

REPLICATION_APPLIER：作为复制通道的应用线程用户。

3. 数据安全与加密

ENCRYPTION_KEY_ADMIN：管理加密密钥（如keyring操作）。

TABLE_ENCRYPTION_ADMIN：覆盖表的加密设置。

SENSITIVE_VARIABLES_OBSERVER：查看敏感系统变量（如ssl_cert）。

4. 备份与恢复

BACKUP_ADMIN：执行备份操作（如LOCKINSTANCEFORBACKUP）。

CLONE_ADMIN：使用克隆插件（ClonePlugin）克隆数据。

XA_RECOVER_ADMIN：管理XA事务恢复。

5. 审计与日志

AUDIT_ADMIN：配置审计日志。

AUDIT_ABORT_EXEMPT：允许被审计规则阻止的操作。

TELEMETRY_LOG_ADMIN：管理遥测日志。

6. 资源管理

RESOURCE_GROUP_ADMIN：创建/管理资源组。

RESOURCE_GROUP_USER：使用资源组。

7. 系统操作

FLUSH_* 权限：执行特定FLUSH命令（如优化器成本、状态、表等）。

PERSIST_RO_VARIABLES_ADMIN：持久化只读系统变量。

SYSTEM_VARIABLES_ADMIN：修改动态系统变量。

SESSION_VARIABLES_ADMIN：修改会话级系统变量。

8. 网络与连接

CONNECTION_ADMIN：终止其他用户连接。

FIREWALL_EXEMPT：绕过防火墙规则。

SERVICE_CONNECTION_ADMIN：管理共享服务连接。

9. 其他

SET_USER_ID：在视图或存储程序中定义者上下文。

SHOW_ROUTINE：查看存储过程/函数定义（即使无SELECT权限）。

INNODB_REDO_LOG_*：控制InnoDB重做日志归档。

授权用户权限语法

GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user[IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH with_option [with_option]...]


# 创建finley 这只是创建用户并没有权限
mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';

# 把finley 变成管理员用户，WITH GRANT OPTION表示允许该用户授权给别的用户
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH GRANT OPTION;

#创建用户并赋予RELOAD,PROCESS权限 ，在所有的库和表上
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost' identified by '123456';

# 创建keme用户，在test库，temp表， 上的id列只有select 权限
mysql> grant select(id) on test.temp to keme@'localhost' identified by '123456';

回收权限

mysql> show grants for 'dev_role'@'%';
+---------------------------------------+
| Grants for dev_role@%                 |
+---------------------------------------+
| GRANT SELECT ON *.* TO `dev_role`@`%` |
+---------------------------------------+
1 row in set (0.00 sec)

#回收角色权限
mysql> revoke SELECT ON *.* from 'dev_role'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'dev_role'@'%';
+--------------------------------------+
| Grants for dev_role@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO `dev_role`@`%` |
+--------------------------------------+
1 row in set (0.00 sec)

#解除用户与角色的绑定关系
mysql> revoke 'dev_role'@'%' from 'zxc'@'localhost';

Usage权限

当我们创建完用户之后没有授权，登录新用户执行 show grants，会发现有个 usage 的权限

mysql> show grants;

+-----------------------------------------+

| Grants for zxc@localhost |

+-----------------------------------------+

| GRANT USAGE ON . TO zxclocalhost |

+-----------------------------------------+

1 row in set (0.00 sec)

官方对usage的解释： USAGE “无权限”的同义词当您想要创建一个没有权限的用户时，可以指定USAGE。

Usage是连接（登陆）权限，
当建立一个用户时，就会自动授予其usage权限（默认授予）。
该权限只能用于数据库登陆，不能执行任何操作；
且usage权限不能被回收，也即REVOKE权限并不能删除usage权限。

MySQL 角色管理

mysql 8.0 新增了 role 功能，使得管理权限更加方便。我们可以把角色看作是一组权限的集合，可以给多个用户同时授权，要修改权限也只需修改一次就行。

我们先来动手试验一下：

mysql> create role 'dev_role';
Query OK, 0 rows affected (0.03 sec)

mysql> grant select on *.* to 'dev_role'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'dev_role'@'%';
+---------------------------------------+
| Grants for dev_role@%                 |
+---------------------------------------+
| GRANT SELECT ON *.* TO `dev_role`@`%` |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> create user 'zxc'@'localhost' identified by '1234.Zxc';
Query OK, 0 rows affected (0.02 sec)

mysql> grant 'dev_role' to 'zxc'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
root@LAPTOP-GND3H906:~# /usr/local/mysql/bin/mysql -uzxc -p1234.Zxc
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.41 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> show grants;
+-------------------------------------------+
| Grants for zxc@localhost                  |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `zxc`@`localhost`   |
| GRANT `dev_role`@`%` TO `zxc`@`localhost` |
+-------------------------------------------+
2 rows in set (0.00 sec)

#查看当前会话中哪些角色处于活动状态
mysql> select CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

我们发现登录新授权角色的用户后，show databases;结果跟我们想象中不太一样，理论上应该还要有 mysql，sys 和employees （笔者自己的库）才对。怎么回事呢，赋予用户某个角色权限后，该用户并没有获得相应权限。

原来新创建好的用户处于未启动状态，我们还需要把它激活才行。

（不理解为什么要有这种操作，猜想可能是防止某一个权限授权错，激活之前让用户确定好）

临时激活角色临时激活角色可以使用 SET DEFAULT ROLE 语句。例如：SET DEFAULT ROLE role_name TO user_name;

mysql> set default role dev_role to 'zxc'@'localhost';

#退出root会花切换zxc
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `dev_role`@`%` |
+----------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

除了使用 set default role 命令激活角色外，还可以修改系统变量 activate_all_roles_on_login ，该变量决定是否自动激活 role ，默认为 OFF 即不自动激活，建议将该变量改为 ON ，这样以后赋予角色给新用户后就不需要再手动激活了。

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (0.01 sec)

mysql> set global activate_all_roles_on_login=on;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | ON    |
+-----------------------------+-------+
1 row in set (0.01 sec)

我们还可以通过 mandatory_roles 变量来配置强制性角色。使用强制性角色，服务器会为全部的用户户默认赋予该角色，而不需要显示执行授予角色。可以使用 my.cnf 文件或者使用 SET PERSIST 进行配置，例如：

# my.cnf 配置
[mysqld]
mandatory_roles='dev_role'

# set 更改变量
SET PERSIST mandatory_roles = 'dev_role';

需要注意的是，配置在 mandatory_roles 中的角色不能撤销其权限，也不能删除。

总结：

关于 role 角色相关知识，简单总结几点如下：

role 是一个权限的集合，可以被赋予不同权限。
开启 activate_all_roles_on_login 变量，才可以自动激活角色。
一个用户可以拥有多个角色，一个角色也可以授予多个用户。
角色权限变化会应用到对应用户。
删除角色，则拥有此角色的用户也会丧失此角色的权限。
可设置强制性角色，使得所有用户都拥有此角色的权限。
角色管理和用户管理相似，只是角色不能用于登录数据库。

如果觉得文章对你有用，请随意赞赏

MySQL

MySQL权限与角色

https://www.zhangyan1997.xyz/archives/mysql-quan-xian-jie-shao

作者

张颜

发布于

2025-06-23

更新于

2025-06-23

许可协议

CC BY 4.0

MySQL权限与角色

MySQL 权限介绍

权限权力

授权用户权限语法

回收权限

Usage权限

MySQL 角色管理

作者

发布于

更新于

许可协议

评论