NipGeihou's blog
NipGeihou
2019-05-25
MySQL
目录

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

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

本系列共三部分：

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

在本文中，我们将讨论第一个用例。下一篇博客文章 (原文) (opens new window) 将介绍如何防止用户被 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 (opens new window)

推荐资料扩展：

#MySQL#SYSTEM_USER
上次更新: 2022/01/05, 22:36:44
最近更新
01
跟着官方示例学习Next.js
08-28
02
TypeScript
08-27
03
vite
08-27
更多文章>
Theme by Vdoing | Copyright © 2018-2024 NipGeihou | 友情链接