MySQL 8.0.16新特性：SYSTEM_USER动态权限（英译中文）

文本使用翻译 工具 协助翻译，存在明显机翻味，同时加入了我的一些实际操作见解。

  翻译这篇文章，源于我在最新（8.0.16）版本的 MySQL 使用root用户创建用户并授予所有（all）权限后，出现无法通过 revoke all 撤销新建用户的所有权限，后在官网得知这是8.0.16新出的一个权限，又由于本人捉急的英语水平和直接用翻译页面的错误翻译，于是尝试手动调整翻译内容，便于理解，便有了本文，欢迎大佬指出、完善翻译。

本系列共三部分：

• 第一部分： SYSTEM_USER动态权限（本文）
• 第二部分： 从数据库对象中撤消部分权限（原文未译）
• 第三部分： 如何创建一个应用程序的多个帐户？（原文未译）

  若要修改用户，必须在MySQL架构上具有CREATE USER权限或UPDATE权限。在MySQL 8.0.16中我们添加了SYSTEM_USER动态权限，以保护用户不受第一种情况的影响：其他拥有CREATE USER权限的用户进行修改。具有SYSTEM_USER权限的帐户不能被没有SYSTEM_USER的帐户修改，即使该帐户具有CREATE USER权限

  在本文中，我们将讨论第一个用例。下一篇博客文章(原文)将介绍如何防止用户被MySQL模式上具有UPDATE权限的帐户所修改

SYSTEM_USER权限是如何工作的？

  SYSTEM_USER权限强制执行这样的约定：如果要修改授予SYSTEM_USER权限的用户，那么除了修改用户所需的权限外，还 需要具有SYSTEM_USER权限 。换句话说，拥有SYSTEM_USER和CREATE权限的用户可以修改拥有SYSTEM_USER权限的用户

下面我们通过例子更好的理解：

使用root用户，创建两个用户，一个具有CREATE USER权限，另一个具有SYSTEM_USER权限

在实际操作中，在不改动配置下，不能创建空密码的用户： ERROR 1819 (HY000): Your password does not satisfy the current policy requirements ，但这并不影响教程所要表达的意思

mysql> CREATE USER admin_user, elite_user; #创建admin_user用户和elite_user用户
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT CREATE USER ON *.* TO admin_user; #赋予admin_user用户CREATE USER权限
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT SYSTEM_USER ON *.* TO elite_user; #赋予elite_user用户SYSTEM_USER权限
Query OK, 0 rows affected (0.00 sec)
复制代码

尝试在admin_user用户会话下更改elite_user用户的密码

mysql> SELECT USER();
+----------------------+
| USER()               |
+----------------------+
| admin_user@localhost |
+----------------------+
1 row in set (0.00 sec)
 
mysql> ALTER USER elite_user IDENTIFIED BY 'blahblah';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
复制代码

显然是不可以的

返回到root用户会话下，并创建另一个具有这两种权限的用户

mysql> CREATE USER power_user; #创建power_user用户
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT CREATE USER, SYSTEM_USER ON *.* TO power_user; #赋予power_user用户CREATE USER和SYSTEM_USER权限
Query OK, 0 rows affected (0.01 sec)
复制代码

尝试通过新创建的用户修改power_user密码

mysql> SELECT USER();
+------------------------+
| USER()                 |
+------------------------+
| power_user@localhost   |
+------------------------+
1 row in set (0.00 sec)
 
mysql> ALTER USER power_user IDENTIFIED BY 'blahblah';
Query OK, 0 rows affected (0.01 sec)
复制代码

是可以成功修个power_user的密码的

如何通过角色（ROLE）授予权限？

如果将角色授予用户，则用户只有在激活该角色之后才能在给定会话中获得该角色的功能。如果用户没有被直接授予SYSTEM_USER，但只能通过一个或多个角色授予，则除非激活了具有SYSTEM_USER的角色，否则用户的会话不具有SYSTEM_USER。一旦激活了这样的角色，用户就能够修改具有SYSTEM_USER特权的其他用户。

我们通过例子来理解

创建具有SYSTEM_USER权限的角色，将该角色授予我们在前面的示例中创建的admin_user

mysql> CREATE ROLE elite_role; #创建elite_role角色
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT SYSTEM_USER ON *.* TO elite_role; #赋予elite_role角色SYSTEM_USER权限
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT elite_role to admin_user; #赋予admin_user用户elite_role角色
Query OK, 0 rows affected (0.01 sec)
复制代码

【MySQL8.0-新特性-role】role可以看做一个权限的集合，这个集合有一个统一的名字role名。可以给多个账户统一的某个role的权限权限的修改直接通过修改role来实现，不需要每个账户一个一个的grant权限，方便运维和管理。role可以创建、删除、修改并作用到他管理的账户上。

即使通过将角色授予的admin_user，该用户也只能在激活该角色之后才能修改elite_user

mysql> SHOW GRANTS;
+----------------------------------------------+
| Grants for admin_user@%                      |
+----------------------------------------------+
| GRANT CREATE USER ON *.* TO `admin_user`@`%` |
| GRANT `elite_role`@`%` TO `admin_user`@`%`   |
+----------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> ALTER USER elite_user IDENTIFIED BY 'blahblah'; #更改elite_user用户的密码
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation  #更改失败
 
mysql> SET ROLE elite_role; #激活elite_role角色
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW GRANTS;
+----------------------------------------------+
| Grants for admin_user@%                      |
+----------------------------------------------+
| GRANT CREATE USER ON *.* TO `admin_user`@`%` |
| GRANT SYSTEM_USER ON *.* TO `admin_user`@`%` |
| GRANT `elite_role`@`%` TO `admin_user`@`%`   |
+----------------------------------------------+
3 rows in set (0.00 sec)
 
mysql> ALTER USER elite_user IDENTIFIED BY 'blahblah'; #更改elite_user用户的密码
Query OK, 0 rows affected (0.01 sec) #更改成功
复制代码

何时可以将SYSTEM_USER权限授予或撤销其他用户？

只有在具有授予GRANT选项的SYSTEM_USER权限的情况下。正如我们所看到的，即使用户拥有SUPER权限与GRANT OPTION，它也不能从其他用户中撤销SYSTEM_USER

mysql> SHOW GRANTS;
+-----------------------------------------------------------------------+
| Grants for admin_user@%                                               |
+-----------------------------------------------------------------------+
| GRANT SUPER, CREATE USER ON *.* TO `admin_user`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> REVOKE SYSTEM_USER ON *.* FROM elite_user;
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
复制代码

如果用户拥有SYSTEM_USER权限，则可以撤消其他用户的SYSTEM_USER权限

mysql> SHOW GRANTS;
+-----------------------------------------------------------------------+
| Grants for admin_user@%                                               |
+-----------------------------------------------------------------------+
| GRANT SUPER, CREATE USER ON *.* TO `admin_user`@`%` WITH GRANT OPTION |
| GRANT SYSTEM_USER ON *.* TO `admin_user`@`%` WITH GRANT OPTION        |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> REVOKE SYSTEM_USER ON *.* FROM power_user;
Query OK, 0 rows affected (0.01 sec)
复制代码

可以通过函数或存储过程来提升权限吗？

如果你拥有SET_USER_ID权限，那你可以创建一个带有DEFINER属性的存储过程，并将DEFINER属性设置为任何用户

也可以通过在DEFINER属性中具有SYSTEM_USER、clever、no权限的用户来授予自己SYSTEM_USER权限

嗯，不太好。若要将DEFINER属性设置为具有SYSTEM_USER权限的用户，还必须在SET_USER_ID权限之外拥有SYSTEM_USER权限。

通过例子了解：

• admin_user：具有创建存储过程的权限
• elite_user：可以执行存储过程，有SYSTEM_USER权限
• regular_user：可以执行存储过程，没有SYSTEM_USER权限

mysql> SHOW GRANTS FOR admin_user;  #显示授予admin_user的权限
+----------------------------------------------------------------+
| Grants for admin_user@%                                        |
+----------------------------------------------------------------+
| GRANT CREATE USER ON *.* TO `admin_user`@`%` WITH GRANT OPTION |
| GRANT SET_USER_ID ON *.* TO `admin_user`@`%`                   |
| GRANT ALL PRIVILEGES ON `test`.* TO `admin_user`@`%`           |
+----------------------------------------------------------------+
3 rows in set (0.00 sec)
 
mysql> SHOW GRANTS FOR elite_user; #显示授予elite_user的权限
+----------------------------------------------------------------+
| Grants for power_user@%                                        |
+----------------------------------------------------------------+
| GRANT EXECUTE ON *.* TO `elite_user`@`%` WITH GRANT OPTION     |
| GRANT SYSTEM_USER ON *.* TO `elite_user`@`%` WITH GRANT OPTION |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> SHOW GRANTS FOR regular_user; #显示授予regular_user的权限
+--------------------------------------------+
| Grants for regular_user@%                  |
+--------------------------------------------+
| GRANT EXECUTE ON *.* TO `regular_user`@`%` |
+--------------------------------------------+
1 row in set (0.00 sec)
复制代码

admin_user尝试使用具有SYSTEM_USER权限的DEFINER创建一个存储过程，但是它会得到一个错误。

mysql> CREATE DEFINER= elite_user PROCEDURE test.gp() GRANT SYSTEM_USER ON *.* TO CURRENT_USER();
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
复制代码

试图在存储过程中授予SYSTEM_USER权限的尝试也被阻止

mysql> CREATE DEFINER= regular_user PROCEDURE test.gp() GRANT SYSTEM_USER ON *.* TO CURRENT_USER();
Query OK, 0 rows affected (0.01 sec)
 
mysql> call test.gp();
ERROR 1227 (42000): Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation
复制代码

接受DEFINER属性的其他数据库对象的行为与上面演示的类似。这些对象是：函数、视图、触发器、事件。

在角色的情况下，将会有一些有趣的场景。我不应该一个人玩，所以我把这些场景留给你去尝试

哪些用户在新安装或升级的MySQL安装中被授予SYSTEM_USER权限？

作为起点，必须至少有一个具有SYSTEM_USER权限的用户。在升级服务器时，具有SET_USER_ID权限的用户将被授予SYSTEM_USER权限。如果没有用户拥有SET_USER_ID权限，那么拥有SUPER权限的用户将被授予SYSTEM_USER权限。我们选择SET_USER_ID来作出这个决定，因为它是管理权限之一。它使用户能够在视图或存储过程的DEFINER属性中指定任何帐户，这是SYSTEM_USER应用到的操作。

SYSTEM_USER权限与SYSTEM_USER()函数或操作系统用户之间的关系？

函数返回连接到服务器的当前MySQL用户。它是user()函数的同义词。操作系统用户通常也被称为系统用户

正如您可能已经注意到的，SYSTEM_USER权限与SYSTEM_USER()或操作系统用户都没有关联。我们将权限命名为‘SYSTEM_USER’，因为它恰当地描述了目的。

结尾

在这篇博文中，我们了解了SYSTEM_USER权限的目的以及它是如何工作的。在下一篇博客文章中，我们将更深入地研究如何在数据库对象上创建部分撤销，以防止直接修改MySQL模式中的用户。

在本系列的最后一篇博客文章中，我们将把各个部分放在一起，为一个应用程序创建多个用户。

谢谢你使用MySQL！

翻译原文： The SYSTEM_USER Dynamic Privilege | MySQL Server Blog

推荐资料扩展：

• MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL
• MySQL :: MySQL 8.0 Reference Manual :: 6.2.11 Account Categories