Why didn't MySQL GRANT create associated user accounts?

Question

I installed Percona Toolkit to use pt-show-grants but it's not showing up all the grants. When I run it I see the following output:

-- 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';
...

In this case, bob is just a user. However all the web sites use specific credentials, for instance developer Bob might have an account for his example.com web site, called bob_examplecom_1. When I show the grants for this account:

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)

However, when I try find an associated user:

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

Presumably the original GRANTs didn't create an associated user account? Also note that the above is an example of one web site, where there are many sites. I'm probably missing something here but I expected to see NO_AUTO_CREATE_USER in a mode:

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

So, my problem is I want to use pt-show-grants to create an SQL file of the many GRANTs that need running for a new user on this development server, but I can't figure out how. Do I need to retrospectively create user accounts that match up to the GRANTs? Should I change some settings and/or setup accounts differently in the future?

Update: I just ran FLUSH PRIVILEGES and all the GRANTs that were working that had no associated accounts in mysql.user vanished. Does this mean they're gone for good, and all need to be recreated manually? Why would such a thing happen? I've looked through the MySQL command history and see no commands that would have dropped these accounts in the past. The uptime on this server is over 400 days and the sites have all worked in that time with little messing about.

Update 2: I had to recreate all the accounts. This time, with the GRANT USAGE and then granting privileges did indeed create the user accounts. My question is now a simple one:

Why didn't MySQL GRANT create associated user accounts when performing GRANTs?

Answer 1

Based on your description of the observed behavior, it sounds as if rows were removed from the mysql.user table, using a DELETE statement, rather than a DROP USER statement.

Changes made to the privilege tables (mysql.user, mysql.db, et al.) via DML statements (DELETE, INSERT, UPDATE), do not take effect immediately. MySQL has already read those tables, and the information is held in memory. Checks of privileges go against the in memory store; MySQL doesn't check the contents of the tables.

So it's possible to make changes to the mysql.user table, and not have those changes reflected in the effective privileges.

The FLUSH PRIVILEGES statement is what causes MySQL to re-read all the privilege tables, and rebuild the "in memory" store of privilege information.

---

To answer your question(s)...

Q: Presumably the original GRANTs didn't create an associated user account?

Q: Why didn't MySQL GRANT create associated user accounts when performing GRANTs?

A: The GRANT for a "new" user did create the user account, if it completed successfully. The appropriate row was added to the mysql.user table, and the privileges became effective (the change was also applied to the "in memory" privilege structure.

Q: Does this mean they're gone for good, and all need to be recreated manually?

A: Yes. If the rows are not in the mysql.user table, then those will need to be recreated. The rows in the mysql.user, mysql.db tables could be restored from a backup.

Q: Why would such a thing happen?

A: As mentioned earlier, someone may have inadvertently run a DELETE statement against mysql.user table. (It's also possible a TRUNCATE, or a DROP and CREATE. (Executing the SQL from mysqldump script that includes DROP TABLE statement, to reload the table from an old backup?)

If operations like that weren't performed on the table, then another possibility is that MyISAM table became corrupted, and the repair of the corruption caused the loss of rows. (A known issue with MyISAM tables; and one of the reasons we take backups of the databases, and test restores.)

---

Here's a demonstration of the behavior... removing a row from mysql.user is not immediately reflected in the effective privileges:

Verify user does not exist:

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'

Create user with GRANT statement:

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

Check contents of mysql.user table and effective privileges:

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 [email protected]                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| 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)

Remove row from mysql.user table (using DML operation and not a DROP USER statement)

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

Row is gone from mysql.user table, but privileges are still effective:

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 [email protected]                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| 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)

Force MySQL to rebuild privileges, reading from mysql.user table...

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

Privileges are no longer effective:

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'