MySQL“mysql没有为用户定义这样的授权”

This error happened when I granted all privileges to a new root account I just created.

当我将所有权限授予刚刚创建的新root帐户时,会发生此错误。

Steps to produce the problem:

产生问题的步骤:

CREATE USER 'root'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SHOW GRANTS for 'root'@'localhost';

After "show grants" I got the error "mysql there is no such grant defined for user 'root' on host 'localhost'". There were no errors after executing the first three commands. The new user was created successfully.

在“show grants”之后我收到错误“mysql没有为主机'localhost'上的用户'root'定义这样的授权”。执行前三个命令后没有错误。新用户已成功创建。

How do I solve this problem?

我该如何解决这个问题?

More info: I'm running MySQL 5.7 on my MacOS laptop(OSX 10.10.5).

更多信息:我在MacOS笔记本电脑上运行MySQL 5.7(OSX 10.10.5)。

3 个解决方案

#1

There is nothing wrong with your posted code but as guess try with wildcard symbol % like

您发布的代码没有任何问题,但猜测尝试使用通配符%

SHOW GRANTS for 'root'@'%';

(OR)

As an alternative, login with your created user 'root'@'localhost' and just use SHOW GRANTS. See Documentation

作为替代方案,使用您创建的用户“root”@“localhost”登录,并使用SHOW GRANTS。见文档

#2

I don't think mysql allows you to create another root account. So the create causes an error.

我不认为mysql允许您创建另一个root帐户。因此创建会导致错误。

CREATE USER 'root'@'localhost';

ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'

You should check for the existing root account in the user table and you'll find the wildcard to be '%' which should mean you do not need to create a localhost root user.

您应该检查用户表中的现有root帐户,并且您将发现通配符为'%',这意味着您不需要创建localhost root用户。

select * from user where user = 'root';

Asking to show grants on root localhost should work, and does work for me.

要求在root localhost上显示授权应该有效,并且对我有用。

show grants for 'root'@'localhost';

#3

Step-1: sudo mysql -u root -p

第1步:sudo mysql -u root -p

Step-2: REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user name'@'localhost';

步骤2:从“用户名”@“localhost”中撤消所有特权,授予选项权限;

Ex.- REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'admin'@'localhost';

例如 - 从'admin'@'localhost'中撤销所有特权,授权选项;

Step-3: FLUSH PRIVILEGES;

第3步:冲洗特权;

I think it will work.

我认为它会起作用。

#1