mysql 8.0.21 对用户授权报错 near ‘IDENTIFIED BY ‘123456‘ with grant option‘ at line 1

mysql 8.0.21 对用户授权报错 near ‘IDENTIFIED BY ‘123456’ with grant option’ at line 1

问题描述

mysql在创建用户，对用户授权时报错，详细如下：

mysql> GRANT ALL PRIVILEGES ON wordpress.* TO 'user'@'localhost' IDENTIFIED BY '                                                                                                                              123456' with grant option;

报错

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 'IDENT                                                                                                                              IFIED BY '123456' with grant option' at line 1

步骤

创建数据库

CREATE DATABASE wordpress;

创建用户

CREATE USER 'user'@'localhost' IDENTIFIED BY '123456';

设置访问权限

GRANT ALL PRIVILEGES ON wordpress.* TO 'user'@'localhost' IDENTIFIED BY '123456';

命令讲解：grant 权限列表 on 数据库 to ‘用户名’@’访问主机’ ;
结果报错，我上网搜了下，说需要在末尾加上，with grant option。还是不对，经过尝试去掉加上Identified by ‘密码’，就可以了。

解决方案：

mysql> grant all privileges on wordpress.* to user@'localhost' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privilege;

**%和localhost的区别：%允许所有ip访问，localhost允许本机访问。