用户无法访问数据库 [英] User can't access a database

问题描述

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

In my PHP script, I'm accessing two databases db1 and db2. I have a user myuser@localhost that can access db1 but can't access db2.

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

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

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

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

在查看该用户的GRANTS时，我看到的db1权限与db2

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

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'@'%'                                              |
+-----------------------------------------------------------------------------------------------------------+

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

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

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

It says user is myuser@localhost and people suggested adding permission for myuser@localhost however, why does this user have access to db1 and not to db2?

推荐答案

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

localhost does 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 USAGE privilege, and for the privileges on each database.

或者您可以连接匹配%的user@127.0.0.1.为本地主机使用IP地址似乎应该与本地主机相同，但事实并非如此.您必须在mysql.user表中(并且在您的案例中的mysql.db表中)有两行才能同时启用两者.

Or you can connect as user@127.0.0.1 which does match %. 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.user table (and also in the mysql.db table in your case) to enable both.

演示本地主机和127.0.0.1之间的区别:

To demonstrate the difference between localhost and 127.0.0.1:

以mysql -h localhost身份连接使用UNIX套接字接口，并绕过TCP/IP.这可能会稍微提高性能，但会影响上述授予匹配.

Connecting as mysql -h localhost uses 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.

您可以通过按mysql -h 127.0.0.1连接来强制进行本地TCP/IP连接.然后它将提取您对myuser@%所做的资助.

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接口获得相同的用户，密码和特权，您需要运行以下语句的 all :

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

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'

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