Skip to main content
Database Administrators

Return to Question

Bumped by Community user
edited tags
Link
RolandoMySQLDBA
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531
Source Link
Reck
Reck
  • 111
  • 2

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

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