mysql system_user连接_MySQL 8.0 Access denied; you need (at least one of) the SYSTEM_USER privilege(s)...

今天在将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权限的用途

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