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'```
