mysql grant授权报错

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘identified by “Root_123456”’ at line 1
ERROR 1410 (42000): You are not allowed to create a user with GRANT**

问题描述：

想用navicat连接本机虚拟机上的mysql数据库，在进行grant授权时报错。如下：

mysql> grant all on *.* to 'root'@"%" identified by "Root_123456";

报错信息：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by "Root_123456"' at line 1

后来将命令改为

grant all on *.* to 'root'@'%' with grant option;

还是报错，报错信息为：

ERROR 1410 (42000): You are not allowed to create a user with GRANT

原因：

经过排查后发现：
mysql在8.0以后，这个特性已被移除，官方文档如下：

Using GRANT to modify account properties other than privilege assignments. This includes
authentication, SSL, and resource-limit properties. Instead, establish such properties at account-creation
time with CREATE USER or modify them afterward with ALTER USER.
翻译：
使用 GRANT 修改权限分配以外的帐户属性。这包括
身份验证、SSL 和资源限制属性。相反，请在创建帐户时建立此类属性
时间与创建用户或之后使用更改用户修改它们。

解决办法：

-- 1. 使用update user
update user set host='%' where user ='root';
-- 2. 使用create user
create user 'userName'@'%' identified by 'your_password';