mysql - 为什么 MySQL GRANT 没有创建关联的用户帐户？

Question

我安装了 Percona Toolkit 以使用 pt-show-grants 但它没有显示所有的授权。当我运行它时，我看到以下输出:

-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.5.43-log at 2015-06-11 09:19:19
-- Grants for 'bob'@'12.34.56.78'
GRANT SUPER ON *.* TO 'bob'@'12.34.56.78' IDENTIFIED BY PASSWORD '*4F72B97CAAAAAAAAAAA9C38064C4CCB18CA0DD8';
GRANT SELECT ON `mydb`.* TO 'bob'@'12.34.56.78';
...

在这种情况下，bob 只是一个用户。但是，所有网站都使用特定的凭据，例如开发人员 Bob 可能有一个名为 bob_examplecom_1 的 example.com 网站帐户。当我显示此帐户的赠款时:

mysql> SHOW GRANTS FOR 'bob_examplecom_1'@'localhost';
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for bob_examplecom_1@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bob_examplecom_1'@'localhost' IDENTIFIED BY PASSWORD '*74AE8018AAAAAAAAAAAAAAAABB87B5C83E650CB' |
| GRANT ALL PRIVILEGES ON `bob_core`.* TO 'bob_examplecom_1'@'localhost' WITH GRANT OPTION                            |
| GRANT ALL PRIVILEGES ON `bob_examplecom_main`.* TO 'bob_examplecom_1'@'localhost' WITH GRANT OPTION                      |
| GRANT ALL PRIVILEGES ON `bob_blog`.* TO 'bob_examplecom_1'@'localhost'                                    |
+-------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

但是，当我尝试查找关联用户时:

mysql> SELECT User, Host FROM mysql.user WHERE User LIKE 'bob\_%';
Empty set (0.00 sec)

大概原始的 GRANT 没有创建关联的用户帐户？另请注意，以上是一个网站的示例，其中有许多 个网站。我可能在这里遗漏了一些东西，但我希望在一种模式下看到 NO_AUTO_CREATE_USER:

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

所以，我的问题是我想使用 pt-show-grants 创建一个包含 many GRANT 的 SQL 文件在此开发服务器上为新用户运行，但我不知道如何运行。我是否需要追溯创建与 GRANT 匹配的用户帐户？以后我是否应该更改某些设置和/或以不同方式设置帐户？

更新:我刚刚运行了 FLUSH PRIVILEGES 和所有正在工作的 GRANT 在 mysql.user 中没有关联的帐户> 消失了。 这是否意味着它们永远消失了，并且都需要手动重新创建？为什么会发生这样的事情？我查看了 MySQL 命令历史记录，没有看到过去会删除这些帐户的命令。该服务器的正常运行时间超过 400 天，所有站点在这段时间内都正常运行，几乎没有出现任何问题。

更新 2:我不得不重新创建所有帐户。这一次，使用 GRANT USAGE 然后授予权限确实创建了用户帐户。我的问题现在很简单:

为什么在执行 GRANT 时 MySQL GRANT 没有创建关联的用户帐户？

Answer 1

根据您对观察到的行为的描述，听起来好像使用 DELETE 语句而不是 从 mysql.user 表中删除了行>DROP USER 语句。

通过 DML 语句(DELETE、INSERT, UPDATE), not 立即生效。 MySQL 已经读取了那些表，并且信息保存在内存中。特权检查违背了内存存储； MySQL 不检查表的内容。

因此可以对 mysql.user 表进行更改，并且不会将这些更改反射(reflect)在有效权限中。

FLUSH PRIVILEGES 语句导致 MySQL 重新读取所有权限表，并重建权限信息的“内存”存储。

---

回答您的问题...

问:大概原始的 GRANT 没有创建关联的用户帐户？

问:为什么 MySQL GRANT 在执行 GRANT 时没有创建关联的用户帐户？

答:如果成功完成，"new"用户的 GRANT 确实 创建了用户帐户。适当的行被添加到 mysql.user 表中，权限生效(更改也应用于“内存中”权限结构。

问:这是否意味着它们永远消失了，需要手动重新创建？

答:是的。如果行不在 mysql.user 表中，则需要重新创建这些行。 mysql.user、mysql.db 表中的行可以从备份中恢复。

问:为什么会发生这样的事情？

答: 如前所述，有人可能无意中对 mysql.user 表运行了 DELETE 语句。 (也可能是 TRUNCATE，或 DROP 和 CREATE。(从包含 DROP TABLE 语句的 mysqldump 脚本执行 SQL，以从旧备份重新加载表？)

如果没有对表执行这样的操作，那么另一种可能是 MyISAM 表已损坏，修复损坏导致行丢失。 (MyISAM 表的一个已知问题；也是我们备份数据库并测试恢复的原因之一。)

---

这是行为的演示...从 mysql.user 中删除一行不会立即反射(reflect)在有效权限中:

验证用户不存在:

mysql> SELECT USER, HOST FROM mysql.user WHERE USER LIKE 'bob' ;
Empty set (0.00 sec)
mysql> SHOW GRANTS FOR 'bob'@'192.168.11.121' ;
ERROR 1141 (42000): There is no such grant defined for user 'bob' on host '192.168.11.121'

使用 GRANT 语句创建用户:

mysql> GRANT SELECT ON ergo.* TO 'bob'@'192.168.11.121' IDENTIFIED BY 'mysecret';
Query OK, 0 rows affected (0.00 sec)

检查mysql.user表的内容和有效权限:

mysql> SELECT USER, HOST FROM mysql.user WHERE USER LIKE 'bob' ;
+------+----------------+
| USER | HOST           |
+------+----------------+
| bob  | 192.168.11.121 |
+------+----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'bob'@'192.168.11.121' ;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for bob@192.168.11.121                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bob'@'192.168.11.121' IDENTIFIED BY PASSWORD '*440A4F469FD488A1C73204842936CC18A62A7D7F' |
| GRANT SELECT ON `ergo`.* TO 'bob'@'192.168.11.121'                                                              |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

从 mysql.user 表中删除行(使用 DML 操作而不是 DROP USER 语句)

mysql> DELETE FROM mysql.user WHERE USER = 'bob' AND HOST = '192.168.11.121';
Query OK, 1 row affected (0.00 sec)

行已从 mysql.user 表中消失，但权限仍然有效:

mysql> SELECT USER, HOST FROM mysql.user WHERE USER LIKE 'bob' ;
Empty set (0.00 sec)

mysql> SHOW GRANTS FOR 'bob'@'192.168.11.121' ;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for bob@192.168.11.121                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bob'@'192.168.11.121' IDENTIFIED BY PASSWORD '*440A4F469FD488A1C73204842936CC18A62A7D7F' |
| GRANT SELECT ON `ergo`.* TO 'bob'@'192.168.11.121'                                                              |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

强制 MySQL 重建权限，从 mysql.user 表读取...

mysql> FLUSH PRIVILEGES ;
Query OK, 0 rows affected (0.00 sec)

权限不再有效:

mysql> SHOW GRANTS FOR 'bob'@'192.168.11.121' ;
ERROR 1141 (42000): There is no such grant defined for user 'bob' on host '192.168.11.121'