无法向 mysql 数据库授予权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19237475/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
cannot grant privileges to mysql database
提问by Trewq
I have mysql Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu) installed linux Ubuntu 12.04 LTS.
我有 mysql 服务器版本：5.5.32-0ubuntu0.12.04.1 (Ubuntu) 安装了 linux Ubuntu 12.04 LTS。
I seem to have all the permissions as root. I can create a user and a db. However, I cannot seem to give the user all the permissions to the db.
我似乎以 root 身份拥有所有权限。我可以创建一个用户和一个数据库。但是，我似乎无法向用户授予数据库的所有权限。
My .my.cnf:
我的.my.cnf：
[client]
user=root
password=test
I login through mysql -u root -h localhost -p, but I cannot login without the -poption though I have the .my.cnf (not an issue, but odd).
我通过 登录mysql -u root -h localhost -p，但没有-p选项我无法登录，尽管我有 .my.cnf（不是问题，但很奇怪）。
There were a bunch of root users, so I got rid of them and I have these users:
有一堆 root 用户，所以我摆脱了他们，我有这些用户：
mysql> SELECT host,user,password FROM mysql.user;
+-----------+------------------+-------------------------------------------+
| host | user | password |
+-----------+------------------+-------------------------------------------+
| localhost | root | ***************************************** |
| localhost | debian-sys-maint | ***************************************** |
+-----------+------------------+-------------------------------------------+
mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------+
Now, I create a db, a user. The last line shows an error when I grant permissions. Can you please let me know why I am getting this error and what I can do to make this work?
现在，我创建了一个数据库，一个用户。当我授予权限时，最后一行显示错误。你能告诉我为什么我会收到这个错误以及我可以做些什么来完成这项工作吗？
mysql> create database staging;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE USER 'staging'@'localhost' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON staging.* TO 'staging'@'localhost';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'staging'
回答by Trewq
First, Identify the user you are logged in as:
首先，确定您登录的用户：
select user(); select current_user();
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@localhostin mysql.
第一个命令的结果是您尝试登录的身份，第二个是您实际连接的身份。确认您root@localhost以 mysql 的身份登录。
The issue was that the installation I came up with did not provide Grant_privto root@localhost. Here is how you can check.
问题是我想出的安装没有提供Grant_priv给root@localhost. 这是您可以检查的方法。
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 |
+-----------+------------------+-------------------------------------------+------------+------------+
You can see that the Grant_priv is set to N for root@localhost. This needs to be Y. Here is how I fixed this:
您可以看到 root@localhost 的 Grant_priv 设置为 N。这需要是 Y。这是我解决这个问题的方法：
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';
I did get some permission error, but when I logged back in, it was fine.
我确实遇到了一些权限错误，但是当我重新登录时，一切正常。
回答by xiriusly
try flushing privileges after granting permissions
授予权限后尝试刷新权限
GRANT ALL PRIVILEGES ON staging.* TO 'staging'@'localhost' IDENTIFIED BY 'test';
FLUSH PRIVILEGES;
回答by mopo922
For those who still stumble upon this like I did, it's worth checking to make sure the attempted GRANTdoes not already exist:
对于那些仍然像我一样偶然发现的人，值得检查以确保尝试GRANT不存在：
SHOW GRANTS FOR username;
In my case, the error was not actually because there was a permission error, but because the GRANTalready existed.
就我而言，错误实际上并不是因为存在权限错误，而是因为GRANT已经存在。
