MySQL8.0賬户system_user權限，你瞭解嗎？

語言: CN / TW / HK

時間 2022-07-04 00:03:57 楊建榮的學習筆記

主題: MySQL

公眾號：DBA隨筆

MySQL8.0賬户system_user權限，你瞭解嗎？

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,[email protected]';

瞭解了強制角色概念之後，説回到system_user權限，在 MySQL8.0.16版本以後，如果一個角色role有system_user權限，那麼它不能被放在強制角色列表中，而之前，任何角色role都可以放在這個列表中。

5、審計方面的影響。

這個特點很少使用，這裏我不做贅述了。有興趣可以查看官網。

具體案例分析

1、 system_user權限對賬號安全性的影響

MySQL5.7版本下：

一、
利用root用户登錄，創建root和test 2個賬號,
其中，test賬號只有create user權限
create user [email protected]'10.%' ;
grant all privileges on *.* to [email protected]'10.%';

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

二、
再次利用test賬號登錄,並刪除[email protected]''賬號：
drop user [email protected]'10.%' ;
Query OK, 0 rows affected (0.00 sec)

執行成功

MySQL 8.0 版本下：

一、
利用root用户登錄，創建root和test 2個賬號,
其中，test賬號只有create user權限
create user [email protected]'10.%' ;
grant all privileges on *.* to [email protected]'10.%';

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

二、
再次利用test賬號登錄,並刪除[email protected]''賬號：
drop user [email protected]'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的權限。

0 3

總結

從MySQL8.0版本開始，系統賬號和普通賬號之間有了鴻溝，具體表現在：

1、擁有system_user權限的賬號，是系統賬號

2、沒有system_user權限的賬號，是普通賬號

3、在擁有create user權限的情況下，系統賬號可以修改其他系統賬號和所有普通賬號；

4、在擁有create user權限的情況下，普通賬號只能修改其他普通賬號。

PS：這裏還有一個細節。

如果一個賬號本身沒有system_user的權限，但是被賦予了一個角色，這個角色擁有system_user權限，那麼相當於這個賬號也就擁有了system_user權限。也就變成了一個系統賬號。

更多更詳細的內容，可以參考官方文檔：