错误1044(42000):具有所有特权的"root"访问被拒绝 [英] ERROR 1044 (42000): Access denied for 'root' With All Privileges

问题描述

我有一个奇怪的错误.我通过命令行以root用户身份登录到本地Mysql.创建数据库后:

I have strange error. I am logged in local Mysql as root via command line. After creating database:

create database some_db;

然后将特权授予某些用户:

Then giving privileges to some user:

grant all privileges on some_db.* to some_user@'localhost' identified by 'password';

这给出了错误:

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'some_db'

root的权限(显示授予；)显示:

The permissions for the root(show grants;) shows:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*8919C53DC7A4DFBF3F8584382E96463583EB7FDA' 

我还要确保我以root用户身份登录:

I am also making sure i am logged in as root:

select current_user();

这确认我以'root'@'localhost'

And this confirms that i am logged in as 'root'@'localhost'

我已经多次创建数据库并分配了用户，但从未遇到过问题.为什么在以root用户身份登录时无法将用户分配给root用户创建的数据库?

I have created database and assigned user multiple times and never had an issue. Why i am unable assign user to the database created by root while logged in as root?

p.s.从其他帖子中，我认为该问题可能是由于某些奇怪的用户引起的

p.s. from other posts, i am thinking the issue may be caused due to some strange users

select host, user from mysql.user;

一些我没有添加但确实出现的用户:

Some users that i have not added but does show up:

 MY_COMPuTER_name.local |       ''
 MY_COMPuTER_name.local |     root

我试图删除这些用户

drop user 'root'@'MY_COMPuTER_name.local';
drop user ''@'MY_COMPuTER_name.local';

但是，尽管它指出查询运行成功，但是即使具有刷新特权，用户也不会被丢弃.为什么我无法删除用户?任何帮助都非常有用

However, while it states query run successful, the users are not dropped even after flush privileges. Why i am unable delete users? Any help is much appricated

推荐答案

首先，确定您以以下身份登录的用户:

First, Identify the user you are logged in as:

 select user();
 select current_user();

第一个命令的结果是您尝试登录时所使用的身份，第二个命令是您实际连接时所使用的身份.确认您已在mysql中以root@localhost身份登录.

The result for the first command is what you attempted to login as, the second is what you actually connected as. Confirm that you are logged in as root@localhost in mysql.

Grant_priv至root@localhost.这是您可以检查的方式.

Grant_priv to root@localhost. Here is how you can check.

mysql> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user;
+-----------+------------------+-------------------------------------------+------------+------------+
| host      | user             | password                                  | Grant_priv | Super_priv |
+-----------+------------------+-------------------------------------------+------------+------------+
| localhost | root             | ***************************************** | N          | Y          |
| localhost | debian-sys-maint | ***************************************** | Y          | Y          |
| localhost | staging          | ***************************************** | N          | N          |
+-----------+------------------+-------------------------------------------+------------+------------+

您可以看到，root @ localhost的Grant_priv设置为N.必须为Y.下面是解决方法:

You can see that the Grant_priv is set to N for root@localhost. This needs to be Y. Below is how to fixed this:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';

我重新登录，一切正常.

I logged back in, it was fine.

这篇关于错误1044(42000):具有所有特权的"root"访问被拒绝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！