授予其他MySQL数据库导致问题的权限 [英] Granting rights to additional MySQL database causing issues

问题描述

我有一个用户'myuser'和两个数据库. 'db1'和'db2'.

I have a user 'myuser' and two databases. 'db1' and 'db2'.

'myuser'已经具有使用db1的权限，我想通过以下查询为他授予使用'db2'的其他权限:

'myuser' already has rights to use db1 and I wanted to grant him additional permission to use 'db2' by using the following query:

GRANT ALL ON db2.* TO 'myuser'@'localhost';

我运行了Grant语句后，"myuser"失去了与第一个数据库"db1"的连接

After I ran the grant statement, 'myuser' lost the connection to the first database 'db1'

恐怕我使用了错误的主机名.我应该使用％"代替"localhost". 当我这样做时:

I'm afraid that I used a wrong host name. Instead of 'localhost' I should have used '%'. When I do:

 select host from mysql.user where user = 'myuser';

现在我看到该用户的两个主机记录'%'和'localhost'

Now I see two host records '%' and 'localhost' for that user

当我运行SHOW GRANT语句时，我获得了不同主机的权限. 本地主机"和％".

When I run SHOW GRANT statements I get permissions for different hosts. 'localhost' and '%'.

mysql> SHOW GRANTS FOR 'myuser'@'localhost';
+----------------------------------------------------------------------+
| Grants for myuser@localhost                                      |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'localhost'                       |
| GRANT ALL PRIVILEGES ON `beta_version`.* TO 'myuser'@'localhost' |
+----------------------------------------------------------------------+

和

mysql> SHOW GRANTS FOR 'myuser'@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for myuser@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*6ASDFASDFASDF...' 
| GRANT ALL PRIVILEGES ON `myuser`.* TO 'myuser'@'%'                                                
+-----------------------------------------------------------------------------------------------------------+

发生了什么事，导致"myuser"在我的PHP脚本和phpMyAdmin中不起作用?

What happened that caused 'myuser' to not work in my PHP scripts and in phpMyAdmin?

推荐答案

MySQL通过用户名和主机来标识用户.当MySQL在登录时进行身份验证时，MySQL首先会查找完全匹配的主机名.如果找不到完全匹配的内容，则它将查找包含％"通配符的主机.

MySQL identifies a user by BOTH the username and the host. When MySQL does the authentication at login, MySQL first looks for a hostname that is an exact match. If it doesn't find an exact match, then it looks for a host containing a '%' wildcard.

当您执行GRANT ... TO myuser@localhost时，MySQL创建了一个新用户(没有密码，因为该语句中没有提供IDENTIFIED BY.

When you did the GRANT ... TO myuser@localhost, MySQL created a new user (with no password, because there was no IDENTIFIED BY given in the statement.

然后发生了什么，当您尝试从本地主机以myuser身份登录时，mysqld试图在mysql.user表中找到与'myuser'@'localhost'匹配的条目，并找到了它.会话获得了分配给该用户的特权.

Then what happened, when you attempted to login as myuser from the localhost, mysqld tried to find an entry that matched 'myuser'@'localhost' in the mysql.user table and it found it. And the session got the privileges assigned to that user.

(更确切地说，mysqld并没有真正查看mysql.user表的内容，而是真正查看了内存中的结构，该结构是在构建表时从表中填充的.内存结构的重建是由GRANT，REVOKE或FLUSH PRIVILEGES语句触发的.)

(To be a little more precise, mysqld doesn't really look at the contents of the mysql.user table, what it really looks at the in-memory structure, which was populated from the table when it was built. A rebuild of the memory structure is triggered by a GRANT, a REVOKE or a FLUSH PRIVILEGES statement.)

在添加新用户之前发生了什么，mysqld一直在寻找用户名和主机名的完全匹配项，但没有找到一个匹配项.但是它确实找到了带有'％'通配符的条目，因此它与之匹配，因此会话获得了授予'myuser'@'％'用户的所有特权.

What was happening BEFORE you added that new user, mysqld was looking for an exact match on user and hostname, and didn't find one. But it did find an entry with the '%' wildcard, so it matched to that, so the session got all the privileges granted to the 'myuser'@'%' user.

两个用户'u'@'%'和'u'@'localhost'是分开的，并且彼此不同.特权必须分别授予每个用户.授予'u'@'%'的任何特权仅适用于该用户，不适用于'u'@'localhost'.

The two users 'u'@'%' and 'u'@'localhost' are separate and distinct from each other. Privileges must be granted to each user individually. Any privileges granted to 'u'@'%' apply ONLY to that user and NOT to 'u'@'localhost'.

这篇关于授予其他MySQL数据库导致问题的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！