MySQL system_user权限介绍

     MySQL从8.0.16版本开始，MySQL利用system_user权限来区分普通用户和系统用户。具体表现是：

1、拥有system_user权限的账号，是系统账号

2、没有system_user权限的账号，是普通账号

系统账号可以修改系统账号自身和普通账号；

普通账号只能修改普通账号自身。

system_user权限的影响范围：

1、账号管理方面

具有system_user权限的系统账号，可以对普通账号和其他系统账号进行管理，包含账号的创建、删除、修改，权限的授予和回收，密码修改，或者密码属性修改等（例如修改密码过期时间）；

2、kill会话方面

a、kill一个具有system_user权限的账号A持有的会话，你自身执行kill命令的账号B也必须有system_user权限；

b、从MySQL8.0.30开始，

    如果账号A将MySQL server置为下线模式(参数offline-mode控制)，除非账号B拥有connection_admin权限或者super权限，否则账号B无法创建新的连接，但是账号B老的连接不会被打断。

c、MySQL8.0.16 之前，如果一个用户拥有connection_admin权限或者super权限，则它可以kill任何正在执行的会话和语句。

3、修改存储过程、事件或者视图的定义

   想要修改某个存储过程的定义用户，如果原来的账号A拥有system_user权限，那么修改的账号也需要拥有system_user权限。

4、定义强制角色（mandatory role）

首先，我们简单解释下强制角色的概念，mandatory role，它是MySQL8.0里面的一个配置，可以通过下面的语句查看：

show variables like '%manda%';

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

| Variable_name   | Value |

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

| mandatory_roles |       |

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

1 row in set (0.00 sec)

这个参数里面可以写入一些定义好的角色，对于任意一个定义的用户，这些角色都会被强加在这个用户上。一个常见的用法是：

SET  mandatory_roles = '`role1`@`%`,`role2`,role3,role4@localhost';

    了解了强制角色概念之后，说回到system_user权限，在MySQL8.0.16版本以后，如果一个角色role有system_user权限，那么它不能被放在强制角色列表中，而之前，任何角色role都可以放在这个列表中。

5、审计方面的影响。

     这个特点很少使用，这里我不做赘述了。有兴趣可以查看官网。

 02 

具体案例分析

1、system_user权限对账号安全性的影响

MySQL5.7版本下：

一、

利用root用户登录，创建root和test 2个账号,

其中，test账号只有create user权限

create user root@'10.%' ;

grant all privileges on *.* to root@'10.%';

 

create user test@'%';

grant create user on *.* to test@'%';

 

二、

再次利用test账号登录,并删除root@''账号：

drop user root@'10.%' ;

Query OK, 0 rows affected (0.00 sec)

 

执行成功

MySQL 8.0 版本下：

一、

利用root用户登录，创建root和test 2个账号,

其中，test账号只有create user权限

create user root@'10.%' ;

grant all privileges on *.* to root@'10.%';

 

create user test@'%';

grant create user on *.* to test@'%';

 

二、

再次利用test账号登录,并删除root@''账号：

drop user root@'10.%' ;

ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

 

执行失败！！！

上述例子中不难看出：

MySQL5.7版本下，只要一个账号有create user权限，就可以对其他账号进行删除操作，甚至root账号它都可以删除，也就是说普通账号可以删除root这种高权限的系统账号；

MySQL8.0版本下，一个账号A有create user权限，无法对拥有system_user权限的root账号进行删除。

A账号还需要拥有system_user权限，才可以对root账号进行删除，因为root用户本身拥有system_user权限，也是一个系统账号，只有系统账号才能操作系统账号

2、system_user权限，对账号密码的修改

看下面MySQL8.0.22版本下的例子，让我们对system_user权限更加理解：

create user a,b,c,d;

grant create user             on *.* to a;

grant system_user             on *.* to b;

grant create user,system_user on *.* to c;

grant all                     on *.* to d;

 

1、用 a 用户登录，去改 c 用户的密码，提示需要SYSTEM_USER 权限

alter user c identified by 'c';

ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

 

理由：a用户是一个普通用户，无法修改具有system_user权限的系统用户

 

2、用 b 用户登录，去改 c 用户的密码，提示需要CREATE USER 权限

alter user c identified by 'c';

ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

 

理由：b用户是一个系统用户，但是由于缺少create user权限，所以不能修改系统用户c的密码

 

3、用 c 用户登录，去改 c 用户的密码，修改成功

alter user c identified by 'c'; 

Query OK, 0 rows affected (0.01 sec)

 

理由：c用户是一个系统用户，本身有create user和system_user的权限，所以修改自己也能成功

 

4、用 c 用户登录，去改 d 用户的密码，修改成功（d用户有all的权限）

alter user d identified by 'd'; 

Query OK, 0 rows affected (0.01 sec)

 

理由：c用户是一个系统用户，本身有create user和system_user的权限，所以可以修改其他任意的系统账号

可以看到：

普通账号想要修改系统账号的密码，必须将普通账号变成系统账号，也就是添加system_user的权限。

03

总结

从MySQL8.0版本开始，系统账号和普通账号之间有了鸿沟，具体表现在：

1、拥有system_user权限的账号，是系统账号

2、没有system_user权限的账号，是普通账号

3、在拥有create user权限的情况下，系统账号可以修改其他系统账号和所有普通账号；

4、在拥有create user权限的情况下，普通账号只能修改其他普通账号。

PS：这里还有一个细节。

如果一个账号本身没有system_user的权限，但是被赋予了一个角色，这个角色拥有system_user权限，那么相当于这个账号也就拥有了system_user权限。也就变成了一个系统账号。

更多更详细的内容，可以参考官方文档：

https://dev.mysql.com/doc/refman/8.0/en/account-categories.html