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

灰太狼 2022-01-20 10:01 266阅读 0赞

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

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

--------------------

本系列共三部分：

*  第一部分： SYSTEM\_USER动态权限（本文）
 *  第二部分： [从数据库对象中撤消部分权限（原文未译）][Link 1]
 *  第三部分： [如何创建一个应用程序的多个帐户？（原文未译）][Link 2]

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

在本文中，我们将讨论第一个用例。[下一篇博客文章(原文)][Link 1]将介绍如何防止用户被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][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.2 Privileges Provided by MySQL]
 *  [MySQL :: MySQL 8.0 Reference Manual :: 6.2.11 Account Categories ][MySQL _ MySQL 8.0 Reference Manual _ 6.2.11 Account Categories]

[Link 1]: https://link.juejin.im?target=https%3A%2F%2Fwp.me%2Fp4oaqh-3kn
[Link 2]: https://link.juejin.im?target=https%3A%2F%2Fwp.me%2Fp4oaqh-3kU
[The SYSTEM_USER Dynamic Privilege _ MySQL Server Blog]: https://link.juejin.im?target=https%3A%2F%2Fmysqlserverteam.com%2Fthe-SYSTEM_USER-dynamic-privilege%2F
[MySQL _ MySQL 8.0 Reference Manual _ 6.2.2 Privileges Provided by MySQL]: https://link.juejin.im?target=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fprivileges-provided.html
[MySQL _ MySQL 8.0 Reference Manual _ 6.2.11 Account Categories]: https://link.juejin.im?target=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Faccount-categories.html%23system-user-operations