• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

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

MySQL SEian.G 11个月前 (01-15) 544次浏览 已收录 0个评论
文章目录[隐藏]

今天在将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

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


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL 8.0 Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
喜欢 (3)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址