Confused by GRANT ALL PRIVILEGES causing Access Denied for database 'mysql'

Question

I thought I understood grants in mysql, but this particular problem has me stumped.

I am logged in as the 'dbadmin' user on a mysql 5.6.27 database.

+------------------------------------------------------------------------------------------------------------------------------------+
| Grants for dbadmin@10.%                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS, SHOW DATABASES, SHOW VIEW, CREATE USER ON *.* TO 'dbadmin'@'10.%' IDENTIFIED BY PASSWORD <secret> WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `%_pt`.* TO 'dbadmin'@'10.%'                                                                               |
| GRANT SELECT ON `mysql`.`db` TO 'dbadmin'@'10.%'                                                                                   |
| GRANT SELECT ON `mysql`.`user` TO 'dbadmin'@'10.%'                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------+

I also have a 'DEF@localhost' user

mysql> show grants for 'DEF'@'localhost';
+------------------------------------------------------------------------------------------------------------+
| Grants for DEF@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'DEF'@'localhost' IDENTIFIED BY PASSWORD '<secret>'                                  |
+------------------------------------------------------------------------------------------------------------+

This 'dbadmin' user has the Grant_priv, but not the Super_priv. The 'DEF' user has neither.

mysql> SELECT user,host,Grant_priv,Super_priv FROM mysql.user;
+------------------+-----------+------------+------------+
| user             | host      | Grant_priv | Super_priv |
+------------------+-----------+------------+------------+
| dbadmin          | 10.%      | Y          | N          |
| DEF              | localhost | N          | N          |
| DEF              | %.        | N          | N          |
| ...              | ...       | ...        | ...        |
+------------------+-----------+------------+------------+

When logged in from a remote 10.% host as the 'dbadmin' user, I would expect the following command to work, but instead I get an Access Denied for database 'mysql'. I am not sure why this is happening.

mysql> GRANT ALL PRIVILEGES ON `mydb_pt`.* TO 'DEF'@'localhost' IDENTIFIED BY PASSWORD <secret>;
ERROR 1044 (42000): Access denied for user 'dbadmin'@'10.%' to database 'mysql'```

Answer 1

You only have SELECT privilege on mysql.user and mysql.db.

You need to have more than that. If you could be granted

GRANT UPDATE,INSERT,DELETE on mysql.user TO 'dbadmin'@'10.%';
GRANT UPDATE,INSERT,DELETE on mysql.db TO 'dbadmin'@'10.%';

This will allow you to CREATE USER as it must be able to write to those two tables.

With you current grants on mysql.user and mysql.db, you cannot create any user.