无法授予对mysql数据库的特权 [英] cannot grant privileges to mysql database

问题描述

我有mysql服务器版本:5.5.32-0ubuntu0.12.04.1(Ubuntu)已安装Linux Ubuntu 12.04 LTS.

I have mysql Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu) installed linux Ubuntu 12.04 LTS.

我似乎拥有所有权限(以root用户身份).我可以创建一个用户和一个数据库.但是，我似乎无法向用户授予对数据库的所有权限.

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.

我的.my.cnf:

[client]
user=root
password=test

我通过mysql -u root -h localhost -p登录，但是虽然我有.my.cnf(不是问题，但很奇怪)，但没有-p选项也无法登录.

I login through mysql -u root -h localhost -p, but I cannot login without the -p option though I have the .my.cnf (not an issue, but odd).

有一堆root用户，因此我摆脱了这些用户，并且拥有以下用户:

There were a bunch of root users, so I got rid of them and I have these users:

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'

推荐答案

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

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.

问题是我提出的安装未向root@localhost提供Grant_priv.这是您可以检查的方式.

The issue was that the installation I came up with did not provide 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. Here is how I fixed this:

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.

这篇关于无法授予对mysql数据库的特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！