在试图授予特权时，用户“根”@“localhost”被拒绝访问。如何授予特权?

I've looked at a number of similar questions and so I'm demonstrating that I've checked the basics. Though of course, that doesn't mean I haven't missed something totally obvious. :-)

我看过很多类似的问题，所以我证明我已经检查了基本的问题。当然，这并不意味着我没有错过一些非常明显的东西。:-)

My question is: why am I denied access on a user with the privileges to do what I'm trying to do and where I have already typed the password and been granted access? (For the sake of completeness, I tried typing the wrong password just to make sure that MySQL client would deny me access at program start.)

我的问题是:为什么我被拒绝访问一个有权限做我正在做的事情的用户，以及我已经输入密码并被授予访问权限的地方?(为了完整性起见，我尝试键入错误的密码，以确保MySQL客户端在程序启动时拒绝访问我。)

Background:

背景:

Logged in to the shell of the machine running the MySQL server via ssh, I log in as root:

通过ssh登录到运行MySQL服务器的机器外壳，我作为root用户登录:

[myname@host ~]$ mysql -u root -p -hlocalhost
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62396
Server version: 5.5.18-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Awesome. My reading of the answers to similar questions suggests that I should make sure the the privileges are current with what is in the grant tables

太棒了。我对类似问题的答案的阅读表明，我应该确保赠款表中所包含的内容具有当前的特权

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> 

Next make sure I am who I think I am:

接下来，确保我就是我自己:

mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

...and really really make sure:

…真的要确保:

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> 

So far so good. Now what privileges do I have?

目前为止一切都很顺利。我有什么特权呢?

mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '[OBSCURED]' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now that's a little hard to read, so lets try this way (you will also get to see that there is a non-localhost 'root' user):

现在读起来有点难，让我们试试这种方式(您还会看到有一个非本地主机“root”用户):

mysql> SELECT * FROM mysql.user WHERE User='root'\G
*************************** 1. row ***************************
                 Host: localhost
                 User: root
             Password: *[OBSCURED]
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
           Event_priv: Y
         Trigger_priv: Y
             ssl_type: 
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
*************************** 2. row ***************************
                 Host: [HOSTNAME].com
                 User: root
             Password: *[OBSCURED]
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
           Event_priv: Y
         Trigger_priv: Y
             ssl_type: 
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
 2 rows in set (0.00 sec)

Awesome! MySQL thinks that I am root@localhost and root@localhost has all those privileges. That means I ought to be able to do what I want, right?

太棒了!MySQL认为我是root@localhost，而root@localhost拥有所有这些特权。这意味着我应该能够做我想做的，对吧?

mysql> GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

How could I have screwed up something this basic?

我怎么会搞砸了这么基本的东西?

Side note: for anyone who wants to suggest that I not have a user named root with all privileges, that's great and something I'll consider doing once I can give another user some privileges.

附注:对于任何想建议我没有一个用户名为root的用户，这是很好的，我将考虑做一次，我可以给另一个用户一些特权。

Thank you!

谢谢你！

11 个解决方案

#1

Notice how the output of

注意输出

SHOW GRANTS FOR 'root'@'localhost';

did not say 'ALL PRIVILEGES' but had to spell out what root@localhost has.

没有说“ALL PRIVILEGES”，但是必须说明root@localhost是什么。

GRANT ALL PRIVILEGES will fail, because a user can not grant what he/she does not have, and the server seem to think something is not here ...

授予所有特权将失败，因为用户不能授予他/她没有的特权，服务器似乎认为这里没有什么东西……

Now, what's missing then ?

那么，现在缺少什么呢?

On my system, I get this:

在我的系统中，我得到:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.21-log |
+------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM mysql.user WHERE User='root' and Host='localhost'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: 
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y <----------------------------- new column in 5.5
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: <------------------------------- new column in 5.5
 authentication_string: <------------------------------- new column in 5.5
1 row in set (0.00 sec)

There are also new tables in 5.5, such as mysql.proxies_user: make sure you have them.

另外还有5.5个新表，比如mysql。proxies_user:确保您拥有它们。

When installing a brand new mysql server instance, the install script will create all the mysql.* tables with the proper structure.

当安装一个全新的mysql服务器实例时，安装脚本将创建所有的mysql。*有适当结构的桌子。

When upgrading from an old version, make sure the proper upgrade procedure (mysql_upgrade) is used, which will add the missing tables / columns.

当从旧版本升级时，请确保使用了适当的升级过程(mysql_upgrade)，这将添加缺失的表/列。

It is only a guess, but it seems mysql_upgrade was not done for this instance, causing the behavior seen.

这只是猜测，但似乎mysql_upgrade并没有为这个实例完成，从而导致所看到的行为。

#2

I also had the same problem with this but on Windows after upgrading to MySQL 5.5 from MySQL 5.1. I already tried changing, creating, and resetting password mentioned in here, here, here, and here, no clue. I still get the same error:

我也遇到过同样的问题，但是在Windows升级到MySQL 5.5之后，我又遇到了同样的问题。我已经尝试过改变，创建，重新设置密码在这里，这里，这里，这里，没有线索。我仍然得到相同的错误:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I'm able to connect normally, show all databases, do selects and inserts, create and add users, and but when it comes to GRANT, I'm screwed up. Those access denied error shows up again.

我可以正常地连接，显示所有数据库，执行选择和插入，创建和添加用户，但是当涉及到授予时，我就搞砸了。那些拒绝访问的错误再次出现。

I managed to solve this problem by fixing the privileges by the following command on the MySQL server bin/ directory as mentioned in here:

我通过以下命令修复MySQL服务器bin/ directory上的特权，解决了这个问题:

C:\MySQL Server 5.5\bin> mysql_upgrade

Then, the problem gone away. I hope this solution works on Linux too since usually MySQL provide the same command both on Linux and Windows.

然后，问题就消失了。我希望这个解决方案也适用于Linux，因为通常MySQL在Linux和Windows上都提供相同的命令。

#3

This might happen when you attempt to grant all privileges on all tables to another user, because the mysql.users table is considered off-limits for a user other than root.

当您试图将所有表上的所有特权授予另一个用户时，可能会发生这种情况，因为是mysql。users表被认为是root之外的用户的禁区。

The following however, should work:

但是，应采取下列措施:

GRANT ALL PRIVILEGES ON `%`.* TO '[user]'@'[hostname]' IDENTIFIED BY '[password]' WITH GRANT OPTION;

Note that we use `%`.* instead of *.*

注意，我们使用' % '。*不是*。*

#4

This happened to me when I tried to install a higher MySQL version than the one coming with the distribution.

当我试图安装一个比发行版更高的MySQL版本时，我就遇到了这种情况。

I erased the old version then installed the new one (rpm -e ... then rpm -i MySQL-server* ) But did not realize that the files in /var/lib/mysql were still from the older version (with differences as explained by Marc Alff - thanks!)

我删除了旧版本，然后安装了新版本(rpm -e…)然后rpm -i - sql -server*)，但是没有意识到/var/lib/mysql中的文件仍然来自旧版本(如Marc Alff所解释的差异-谢谢!)

I could have done an mysql_upgrade, but as I wanted to start from scratch I did:

我本可以进行mysql_upgrade，但由于我想从头开始，我做了:

# su - mysql
$ rm -rf /var/lib/mysql/*
$ mysql_install_db
# /etc/init.d/mysql start

Then set root password (/usr/bin/mysqladmin -u root password), and all worked as expected with the GRANT commands...

然后设置根密码(/usr/bin/mysqladmin -u根密码)，并按照预期使用GRANT命令……

#5

I had the same problem, i.e. all privileges granted for root:

我有同样的问题，即所有授予root的特权:

SHOW GRANTS FOR 'root'@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*[blabla]' WITH GRANT OPTION

...but still not allowed to create a table:

…但仍不允许创建表格:

 create table t3(id int, txt varchar(50), primary key(id));
ERROR 1142 (42000): CREATE command denied to user 'root'@'localhost' for table 't3'

Well, it was cause by an annoying user error, i.e. I didn't select a database. After issuing USE dbname it worked fine.

它是由一个烦人的用户错误引起的，比如我没有选择数据库。在使用dbname之后，它工作得很好。

#6

Typing SHOW GRANTS FOR 'root'@'localhost'; showed me some obscured password, so I logged into mysql of that system using HeidiSQL on another system (using root as the username and the corresponding password) and typed
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'thepassword' WITH GRANT OPTION;

输入显示授予“root”@“localhost”;向我展示了一些模糊的密码，所以我在另一个系统上使用HeidiSQL登录了该系统的mysql(使用root作为用户名和相应的密码)，并输入了*上的所有特权。*到“thepassword”标识的“root”@“localhost”，并带有GRANT选项;

and it worked when I went back to the system and logged on using
mysql -uroot -pthepassword;

当我回到系统，使用mysql -uroot -pthepassword登录时，它起作用了;

#7

Basically this error comes when you have not specified a password, it means that you have an incorrect password listed in some option file.

基本上，当您没有指定密码时就会出现这个错误，这意味着您在某些选项文件中列出了不正确的密码。

Read this DOC on understanding how to assign and manage Passwords to accounts.

阅读本文档，了解如何为帐户分配和管理密码。

Also , Check if the permission on the folder /var/lib/mysql/mysql is 711 or not.

另外，检查文件夹/var/lib/mysql/mysql上的权限是否为711。

#8

On Debian (Wheezy, 7.8) with MySQL 5.5.40, I found SELECT * FROM mysql.user WHERE User='root'\G showed the Event_priv and 'Trigger_priv` fields were present but not set to Y.

在使用MySQL 5.5.5.40的Debian (Wheezy, 7.8)上，我从MySQL中找到了SELECT *。user ='root'\G显示了Event_priv和'Trigger_priv '字段，但没有设置为Y。

Running mysql_upgrade (with or without --force) made no difference; I needed to do a manual:

运行mysql_upgrade(有或没有——force)没有区别;我需要做一个手册:

update user set Event_priv = 'Y',Trigger_priv = 'Y' where user = 'root'

更新用户设置Event_priv = 'Y'，Trigger_priv = 'Y'，其中用户= 'root'

Then finally I could use:

最后我可以使用:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION

授予*的所有特权。*到“root”@“localhost”，由“password”标识，并带有GRANT选项

…and then use it more precisely on an individual database/user account.

然后在个人数据库/用户帐户上更精确地使用它。

#9

I run at this when I tried to add privileges to performance_schema, which is mysql bug http://bugs.mysql.com/bug.php?id=44898 (workaround to add --single-transaction).

当我尝试给performance ce_schema添加特权时，我就运行它，它是mysql bug http://bugs.mysql.com/bug.php?id=44898(处理添加——单事务)。

#10

For those who still stumble upon this like I did, it's worth checking to make sure the attempted GRANT does not already exist:

对于那些仍然像我一样偶然发现这个问题的人来说，有必要检查一下，以确保尝试过的资助不存在:

SHOW GRANTS FOR username;

In my case, the error was not actually because there was a permission error, but because the GRANT already existed.

在我的例子中，错误并不是因为有权限错误，而是因为GRANT已经存在。

#11

I had the same problem and it took a lot of reading SO posts and Google's documentation. I finally found this from the Cloud SQL FAQ:

我也遇到了同样的问题，需要大量的阅读，所以贴子和谷歌的文档。我终于在云SQL FAQ中找到了这个问题:

Google Cloud SQL does not support SUPER privileges, which means that GRANT ALL PRIVILEGES statements will not work. As an alternative, you can use GRANT ALL ON `%`.*

谷歌云SQL不支持超特权，这意味着授予所有特权语句将不起作用。作为另一种选择，您可以使用GRANT ALL ON ' % ' .*。