关于初始化root被删 导致权限授予异常

🪐 TiDB 技术问题 部署&运维管理
1

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

  • 【TiDB 版本】：v4.0.1
  • 【问题描述】：手工删除了用户root@’%’,导致执行grant all privileges on . to test@‘localhost’;异常

mysql> select user,host from mysql.user;
±-----------±------------+
| user | host |
±-----------±------------+
| root | x.x.x.x |
| test| x.x.x.x |
mysql> grant all privileges on . to test@‘x.x.x.x’;
ERROR 8121 (HY000): privilege check fail

2

看下下面链接是否能帮助到你

https://pingcap.com/docs-cn/stable/user-account-management/#忘记-root-密码

image
image834×153 9.69 KB

3

看过这个文档，我是手动删除root@’%’.然后在执行任何授权语句都报错 grant select on test.* to test@‘x’;ERROR 8121 (HY000): privilege check fail。执行账户是all privileges权限:
操作步骤:

create user root@'192.168.16.10' identified by "x";
grant all privileges on *.* to root@'192.168.16.10';
doop user root@'%';

然后新的root用户登录:
新建用户:
create user test@‘192.168.16.11’ identified by ‘x’;
执行授权grant 失败,报上面描述错误

4

可以直接操作 mysql.user 表看是否可以。

INSERT INTO mysql.user(Host, User, authentication_string, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Process_priv, Grant_priv, References_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Index_priv, Create_user_priv, Event_priv, Trigger_priv, Create_role_priv, Drop_role_priv, Account_locked, Shutdown_priv, Reload_priv, FILE_priv, Config_priv) VALUES (‘%’, ‘root’, ‘’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘N’, ‘Y’, ‘Y’, ‘Y’, ‘Y’);

flush privileges;

5

找到原因啦，继承的用户权限为新建的root@‘x.x.x.x’.直接
update mysql.user set Grant_priv=‘Y’ where User=‘root’ and Host=‘x.x.x.x’;
就正常使用grant。谢谢指导

6

:joy::ok_hand: