MySQL 用户无法访问数据库

User can't access a database

提问by dev.e.loper

In my PHP script, I'm accessing two databases db1and db2. I have a user myuser@localhostthat can access db1but can't access db2.

在我的 PHP 脚本中，我正在访问两个数据库db1和db2. 我有一个myuser@localhost可以访问db1但无法访问的用户db2。

When selecting from mysql.usertable, there is one record and the host for that user is a wildcard %, there isn't a localhosthost.

从mysql.user表中选择时，有一条记录，该用户的主机是通配符%，没有localhost主机。

SELECT user, host FROM mysql.user WHERE user = 'myuser';give me:

SELECT user, host FROM mysql.user WHERE user = 'myuser';给我吗：

+------------+------+
| user       | host |
+------------+------+
| myuser     | %    |
+------------+------+
1 row in set (0.00 sec)

Looking at GRANTS for that user, I see same permissions for db1as for db2

看着赠款用户，我看到相同的权限db1为db2

SHOW GRANTS FOR 'myuser'@'%';

SHOW GRANTS FOR 'myuser'@'%';

+-----------------------------------------------------------------------------------------------------------+
| Grants for myuser@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*7733323232...' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'myuser'@'%'                                                |
| GRANT ALL PRIVILEGES ON `db2`.* TO 'myuser'@'%'                                              |
+-----------------------------------------------------------------------------------------------------------+

In my PHP script I can access db1however I get an error: INSERT command denied to user 'myuser'@'localhost' for table 'HISTORY'.

在我的 PHP 脚本中，我可以访问，db1但是出现错误：INSERT command denied to user 'myuser'@'localhost' for table 'HISTORY'.

It says user is myuser@localhostand people suggested adding permission for myuser@localhosthowever, why does this user have access to db1and not to db2?

它说用户是myuser@localhost，人们建议添加权限，myuser@localhost但是，为什么这个用户有权访问db1而不是db2？

回答by Bill Karwin

localhostdoes not match %in MySQL. It seems like it should, but in fact it doesn't. You'd have to separately grant privileges to user@localhost, both for the USAGEprivilege, and for the privileges on each database.

localhost%在 MySQL中不匹配。看起来应该如此，但实际上并非如此。您必须分别向 授予特权user@localhost，包括USAGE特权和对每个数据库的特权。

Or you can connect as [email protected]which doesmatch %. Using the IP address for localhost seems like it should work identically to localhost, but it doesn't. You need to have two lines in the mysql.usertable (and also in the mysql.dbtable in your case) to enable both.

或者您可以连接 as [email protected]which doesmatch %。使用 localhost 的 IP 地址似乎应该与 localhost 相同，但事实并非如此。您需要在mysql.user表中（以及mysql.db在您的情况下在表中）有两行才能启用两者。

To demonstrate the difference between localhost and 127.0.0.1:

为了演示 localhost 和 127.0.0.1 之间的区别：

Connecting as mysql -h localhostuses the UNIX socket interface, and bypasses TCP/IP. This can be slightly better for performance, but it has the effect on grant matching described above.

连接 asmysql -h localhost使用 UNIX 套接字接口，并绕过 TCP/IP。这对性能来说可能稍微好一点，但它对上述授权匹配有影响。

You can force a local TCP/IP connection by connecting as mysql -h 127.0.0.1. Then it will pick up the grants you have made to myuser@%.

您可以通过作为 连接来强制本地 TCP/IP 连接mysql -h 127.0.0.1。然后它会提取您已提供的赠款myuser@%。

So to get the same user, password, and privileges for both the socket interface and the TCP/IP interface, you'd need to run allof the following statements:

因此，要为套接字接口和 TCP/IP 接口获得相同的用户、密码和权限，您需要运行以下所有语句：

GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*7733323232...'
GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*7733323232...'
GRANT ALL PRIVILEGES ON `db1`.* TO 'myuser'@'%'
GRANT ALL PRIVILEGES ON `db1`.* TO 'myuser'@'localhost'
GRANT ALL PRIVILEGES ON `db2`.* TO 'myuser'@'%'
GRANT ALL PRIVILEGES ON `db2`.* TO 'myuser'@'localhost'

回答by Ji?í Pospí?il

If you haven't done that already, you need to run flush privilegesso that mysqlknows there was a change and reloads the privileges table for users:

如果您还没有这样做，您需要运行flush privileges以便mysql知道发生了更改并为用户重新加载权限表：

FLUSH PRIVILEGES;

回答by Sithsu

This very likely has nothing to do with GRANTs.

这很可能与 GRANT 无关。

A very common reason for having incorrect access rights is because of default usersthat exist in MySQL. Specially ones with ''for User(anonymous users) and/or Hostin mysql.usertable. Because of the way MySQL handles authentication and proxy users, and the sorting rules used on mysql.usertable entries, one could end up using an unexpected user than the one they used for authentication.

访问权限不正确的一个非常常见的原因是因为MySQL 中存在默认用户。特别那些用''了User（匿名用户）和/或Host在mysql.user表中。由于 MySQL 处理身份验证和代理用户的方式，以及mysql.user表条目上使用的排序规则，最终可能会使用一个意外的用户而不是他们用于身份验证的用户。

Use SELECT USER();to find out the connecting user that was used during authentication and SELECT CURRENT_USER();to find out the effective user whose privileges apply during the current session.

SELECT USER();用于找出在身份验证期间使用的连接用户，并SELECT CURRENT_USER();找出在当前会话期间应用其权限的有效用户。

And from http://dev.mysql.com/doc/refman/5.6/en/connection-access.html

来自http://dev.mysql.com/doc/refman/5.6/en/connection-access.html

It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. If you are able to connect to the server, but your privileges are not what you expect, you probably are being authenticated as some other account.

一种常见的误解是认为对于给定的用户名，当服务器尝试为连接查找匹配项时，首先使用明确命名该用户的所有行。这不是真的。如果您能够连接到服务器，但您的权限不是您所期望的，则您可能正在以其他帐户身份进行身份验证。

A mysql.usertable similar to following

mysql.user类似于下面的表格

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ... (root from any host)
| %         | jeffrey  | ... (jeffrey from any host)
| localhost | root     | ... (root from localhost)
| localhost |          | ... (any user from localhost)
+-----------+----------+-

becomes,

变成，

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| localhost | root     | ...
| localhost |          | ...
| %         | jeffrey  | ...
| %         | root     | ...
+-----------+----------+-

whenever the server reads the user table into memory, in order to handle multiple matches.
When a client attempts to connect, the server looks through the rows in sorted order and uses the first row that matches the client host name and user name.
Precedence is given as: values (IP address, host name, user name, etc.) > '%'> ''

每当服务器将用户表读入内存时，以处理多个匹配项。
当客户端尝试连接时，服务器按排序顺序查看行并使用与客户端主机名和用户名匹配的第一行。
优先级为：值（IP 地址、主机名、用户名等）> '%'>''

Most of the time application server/client is running in the same host as the database, causing the host name to be picked up as localhostduring authentication.
mysql -u jeffreyuses jeffrey@localhostwhich gets matched against ''@localhostinstead of jeffrey@%.

大多数情况下，应用程序服务器/客户端与数据库在同一台主机上运行，​​导致localhost在身份验证期间选择主机名。
mysql -u jeffrey使用jeffrey@localhost匹配''@localhost而不是jeffrey@%.

Executing $MYSQL_HOME/bin/mysql_secure_installationwill remove anonymous users, while securing the installation, alleviating this unexpected behaviour.

执行$MYSQL_HOME/bin/mysql_secure_installation将删除匿名用户，同时保护安装，减轻这种意外行为。

Also check:
[1] http://bugs.mysql.com/bug.php?id=36576(check comment before last)
[2] http://bugs.mysql.com/bug.php?id=69570

还要检查：
[1] http://bugs.mysql.com/bug.php?id=36576（检查最后一次之前的评论）
[2] http://bugs.mysql.com/bug.php?id=69570

回答by Greg

Just thought I'd add an answer. I was trying this on ubuntu. Tried the grants, flushes, nothing worked (this is immediately after by apt-get install mysql-server). Just for grins I bounced the server, that worked and my new user can now login. I did:

只是想我会添加一个答案。我正在 ubuntu 上尝试这个。尝试了授权，刷新，没有任何效果（这是在 apt-get install mysql-server 之后立即执行的）。只是为了咧嘴一笑，我打开了服务器，这奏效了，我的新用户现在可以登录了。我做了：

sudo service mysql restart

I don't know what that worked, but it did.

我不知道那有什么作用，但确实有效。

回答by Roberto Paolillo

You must GRANT privileges also to 'myuser'@'localhost':

您还必须授予 'myuser'@'localhost' 权限：

GRANT ALL PRIVILEGES ON `db1`.* TO 'myuser'@'localhost';
GRANT ALL PRIVILEGES ON `db2_beta`.* TO 'myuser'@'localhost';

Otherwise the anonymous user @localhost created during db install takes precedence among your user with the wildcard hostname (%), as described here:

否则，在 db install 期间创建的匿名用户 @localhost 在具有通配符主机名 (%) 的用户中优先，如下所述：

http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

回答by SnapShot

According to the mysql manual here:

根据这里的mysql手册：

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, or SET PASSWORD, the server notices these changes and loads the grant tables into memory again immediately.

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. If you change the grant tables directly but forget to reload them, your changes have no effect until you restart the server. This may leave you wondering why your changes do not seem to make any difference!

如果您使用帐户管理语句（如 GRANT、REVOKE 或 SET PASSWORD）间接修改授权表，服务器会注意到这些更改并立即再次将授权表加载到内存中。

如果您直接使用 INSERT、UPDATE 或 DELETE 等语句修改授权表，则您的更改不会影响权限检查，直到您重新启动服务器或告诉它重新加载表。如果您直接更改授权表但忘记重新加载它们，则您的更改在重新启动服务器之前无效。这可能会让您想知道为什么您的更改似乎没有任何区别！

This does seem to be true in most cases. However, in my situation I was working with an Amazon Web Services (AWS) RDS mysql instance. After many unsuccessful attempts to grant the user permissions I tried a FLUSH PRIVILEGESand the database was immediately visible to the user. If you come across this while looking for a solution on the Amazon Web Services RDS platform you might want to give this a try and see if it helps.

在大多数情况下，这似乎是正确的。但是，在我的情况下，我正在使用 Amazon Web Services (AWS) RDS mysql 实例。在多次尝试授予用户权限失败后，我尝试了 aFLUSH PRIVILEGES并且该数据库立即对用户可见。如果您在 Amazon Web Services RDS 平台上寻找解决方案时遇到此问题，您可能想尝试一下，看看它是否有帮助。

This SO question contains the most complete solutions to this problem and is the first in most search results so I wanted to add this response for anyone using RDS. Hopefully it will save RDS admins some time.

这个 SO question 包含这个问题的最完整的解决方案，并且是大多数搜索结果中的第一个，所以我想为使用 RDS 的任何人添加这个响应。希望它可以为 RDS 管理员节省一些时间。

回答by Delicia Brummitt

I have run into the same problem in the past. Have you tried the following?

我过去遇到过同样的问题。您是否尝试过以下方法？

GRANT ALL ON `db1`.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*7733323232...';
GRANT ALL ON `db2`.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*7733323232...';
FLUSH PRIVILEGES;