MySQL 8.0 Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

今天在将5.7的逻辑备份文件导入到8.0的实例中的时候出现如下的报错信息：

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

根据报错信息提示的第814行的内容如下所示：

/*!50003 DROP PROCEDURE IF EXISTS `multirest_set_manual_test_failed_cvs_waiwang` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `multirest_set_xxxxxx`(
   in p_numerical_system_name varchar(64),

从SQL文件内容看，是由于存储过程在导入的时候出现报错，由于导入的用户使用的是管理员的账户dba_admin，该账户的权限仅次于root，并且也是包含super权限的，而且可以发现，该存储过程的definer用户是`root`@`localhost`，再结合报错信息看，提示说是由于没有SYSTEM_USER的权限导致，SYSTEM_USER权限是MySQL 8.0新增的权限【接着往下看，后面会关于该权限具体的使用示例】

那么解决方法就很简单了，只需要给该账户dba_admin授权SYSTEM_USER的权限，然后在重新导入即可；

解决方法：
1、通过root用户连接到mysql实例

2、执行授权操作，将SYSTEM_USER的权限授权给dba_admin用户

grant system_user on *.* to 'dba_admin'@'localhost';

3、授权成功后，重新执行导入操作，即可导入成功

问题既然解决了，那我们就来简单的了解一下SYSTEM_USER权限的用途

官方文档链接：
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_system-user
https://dev.mysql.com/doc/refman/8.0/en/account-categories.html

SYSTEM_USER权限是如何工作的？

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

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

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

mysql--root@localhost:(none) 14:04:39>>CREATE USER user1, user2;               
Query OK, 0 rows affected (0.01 sec)

mysql--root@localhost:(none) 14:05:03>>GRANT CREATE USER ON *.* TO user1;
Query OK, 0 rows affected (0.00 sec)

mysql--root@localhost:(none) 14:06:20>>GRANT SYSTEM_USER ON *.* TO user2;
Query OK, 0 rows affected (0.00 sec)

mysql--root@localhost:(none) 14:06:31>>show grants for user1;
+-----------------------------------------+
| Grants for user1@%                      |
+-----------------------------------------+
| GRANT CREATE USER ON *.* TO `user1`@`%` |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql--root@localhost:(none) 14:06:37>>show grants for user2;
+-----------------------------------------+
| Grants for user2@%                      |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `user2`@`%`       |
| GRANT SYSTEM_USER ON *.* TO `user2`@`%` |
+-----------------------------------------+
2 rows in set (0.00 sec)

尝试在user1用户会话下更改user2用户的密码

mysql user1@127.0.0.1:(none)14:08:59>select user();
+-----------------+
| user()          |
+-----------------+
| user1@127.0.0.1 |
+-----------------+
1 row in set (0.00 sec)


mysql user1@127.0.0.1:(none)14:09:04>ALTER USER user2 IDENTIFIED BY 'test';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

显然是不可以的

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

mysql--root@localhost:(none) 14:10:47>>CREATE USER user3;
Query OK, 0 rows affected (0.00 sec)

mysql--root@localhost:(none) 14:10:58>>GRANT CREATE USER, SYSTEM_USER ON *.* TO user3;
Query OK, 0 rows affected (0.00 sec)

+-----------------------------------------+
| Grants for user3@%                      |
+-----------------------------------------+
| GRANT CREATE USER ON *.* TO `user3`@`%` |
| GRANT SYSTEM_USER ON *.* TO `user3`@`%` |
+-----------------------------------------+
2 rows in set (0.00 sec)


mysql user3@127.0.0.1:(none)14:11:38>select user();
+-----------------+
| user()          |
+-----------------+
| user3@127.0.0.1 |
+-----------------+
1 row in set (0.00 sec)

mysql user3@127.0.0.1:(none)14:11:20>ALTER USER user1 IDENTIFIED BY 'test';   
Query OK, 0 rows affected (0.02 sec)

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

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

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

创建一个user4的用户

mysql--root@localhost:(none) 14:14:48>>create user user4;
Query OK, 0 rows affected (0.00 sec)

mysql--root@localhost:(none) 14:14:56>>GRANT SUPER, CREATE USER ON *.* TO user4 WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql--root@localhost:(none) 14:15:09>>show grants for user4;
+------------------------------------------------------------------+
| Grants for user4@%                                               |
+------------------------------------------------------------------+
| GRANT SUPER, CREATE USER ON *.* TO `user4`@`%` WITH GRANT OPTION |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

登录到user4用户下

mysql user4@127.0.0.1:(none)14:16:24>select user();
+-----------------+
| user()          |
+-----------------+
| user4@127.0.0.1 |
+-----------------+
1 row in set (0.00 sec)

mysql user4@127.0.0.1:(none)14:16:00>revoke SYSTEM_USER ON *.* FROM user2;
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

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

mysql--root@localhost:(none) 14:19:50>>create user user5;
Query OK, 0 rows affected (0.01 sec)

mysql--root@localhost:(none) 14:20:41>>GRANT SYSTEM_USER ON *.* TO `user5`@`%` WITH GRANT OPTION; 
Query OK, 0 rows affected (0.00 sec)

mysql--root@localhost:(none) 14:21:00>>
mysql--root@localhost:(none) 14:21:00>>show grants for user5;
+-----------------------------------------------------------+
| Grants for user5@%                                        |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `user5`@`%`                         |
| GRANT SYSTEM_USER ON *.* TO `user5`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql user5@127.0.0.1:(none)14:21:21>select user();
+-----------------+
| user()          |
+-----------------+
| user5@127.0.0.1 |
+-----------------+
1 row in set (0.00 sec)

mysql user5@127.0.0.1:(none)14:21:16>revoke SYSTEM_USER ON *.* FROM user2;
Query OK, 0 rows affected (0.00 sec)

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

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

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

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

通过例子了解：

test_user1：具有创建存储过程的权限

test_user2：可以执行存储过程，有SYSTEM_USER权限

test_user3：可以执行存储过程，没有SYSTEM_USER权限

mysql--root@localhost:(none) 14:29:23>>show grants for test_user1;                        
+----------------------------------------------------------------+
| Grants for test_user1@%                                        |
+----------------------------------------------------------------+
| GRANT CREATE USER ON *.* TO `test_user1`@`%` WITH GRANT OPTION |
| GRANT SET_USER_ID ON *.* TO `test_user1`@`%`                   |
| GRANT ALL PRIVILEGES ON `test`.* TO `test_user1`@`%`           |
+----------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql--root@localhost:(none) 14:29:34>>show grants for test_user2;
+----------------------------------------------------------------+
| Grants for test_user2@%                                        |
+----------------------------------------------------------------+
| GRANT EXECUTE ON *.* TO `test_user2`@`%`                       |
| GRANT SYSTEM_USER ON *.* TO `test_user2`@`%` WITH GRANT OPTION |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql--root@localhost:(none) 14:29:36>>show grants for test_user3;
+------------------------------------------+
| Grants for test_user3@%                  |
+------------------------------------------+
| GRANT EXECUTE ON *.* TO `test_user3`@`%` |
+------------------------------------------+
1 row in set (0.00 sec)

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

CREATE DEFINER=test_user  PROCEDURE test.testproc() GRANT SYSTEM_USER ON *.* TO CURRENT_USER();
mysql test_user1@127.0.0.1:(none)14:34:29>select user();
+----------------------+
| user()               |
+----------------------+
| test_user1@127.0.0.1 |
+----------------------+
1 row in set (0.00 sec)

mysql test_user1@127.0.0.1:(none)14:34:12>CREATE DEFINER=test_user2  PROCEDURE test.testproc() 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 test_user1@127.0.0.1:(none)14:35:56>CREATE DEFINER=test_user3  PROCEDURE test.testproc() GRANT SYSTEM_USER ON *.* TO CURRENT_USER(); 
Query OK, 0 rows affected (0.01 sec)

mysql test_user1@127.0.0.1:(none)14:36:02>call test.testproc();
ERROR 1227 (42000): Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation

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

参考链接：https://blog.csdn.net/weixin_33701251/article/details/91398644