MySQL8版本grant报错：ERROR 1410 (42000)

问题简介：

创建完用户给用户赋权的时候报错：ERROR 1410 (42000): You are not allowed to create a user with GRANT

问题复现

创建备份用户命令：

use mysql;
CREATE USER backup IDENTIFIED BY 'backup';

紧接着赋权给用户（限制本地登录）：

GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES ON . TO 'backup'@'127.0.0.1';
出现报错：ERROR 1410 (42000): You are not allowed to create a user with GRANT

问题原因

在用命令 CREATE USER backup IDENTIFIED BY 'backup';创建完用户后，用户默认的权限为'%'

所以想要赋予@'127.0.0.1'给test用户的解决办法为：

GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES ON . TO 'test'@'%';
update user set host='127.0.0.1' where user='test';

或者直接在创建用户的时候指定连接权限，eg：

CREATE USER test@'127.0.0.1' IDENTIFIED BY 'test';
GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES on . to 'test'@'127.0.0.1' ;
flush privileges;

MySQL8.0完整创建用户命令

use mysql;
CREATE USER admin IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' with grant option;

use mysql;
CREATE USER test@'127.0.0.1' IDENTIFIED BY 'test';
grant select, insert, update, delete,CREATE,DROP,REFERENCES,ALTER,INDEX,LOCK TABLES   on  *.* to 'test'@'127.0.0.1' ;