1

I have a remote MySQL server running on x.x.x.x. And there is a database named abc.

I can do the following

mysql -h x.x.x.x -uroot
use abc

But when I try to

mysql -h x.x.x.x -uroot --database=abc

I get

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'abc'

Why is that? And how do I fix it?

edit:

I have now resolved the issue by doing the following:

  1. ssh in the box
  2. run mysql -uroot
  3. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

But I still don't understand why.

mysql> SELECT USER(),CURRENT_USER();

+-----------------------------+----------------+
| USER()                      | CURRENT_USER() |
+-----------------------------+----------------+
| root@Maxs-MacBook-Air.local | root@%         |
+-----------------------------+----------------+

mysql> SHOW GRANTS FOR root@'%';

+------------------------------------------------------------------+
| Grants for root@%                                                |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%'                                 |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'root'@'%' WITH GRANT OPTION |
+------------------------------------------------------------------+

mysql> SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) from mysql.user WHERE user='root';
ERROR 1142 (42000): SELECT command denied to user 'root'@'Maxs-MacBook-Air.local' for table 'user'

Max

  • Please run mysql -h x.x.x.x -uroot. When you get in, run SELECT USER(),CURRENT_USER(); and post that output. – RolandoMySQLDBA Sep 24 '14 at 18:43
  • @RolandoMySQLDBA Done! – Max Sep 24 '14 at 18:46
  • Please run SHOW GRANTS FOR root@'%'; and SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) from mysql.user WHERE user='root'; and post them as well. (Please hide the encrypted password). – RolandoMySQLDBA Sep 24 '14 at 18:52
  • Done. And I got a permission denied error for the select statement. Somehow it ran as root@my-laptop. – Max Sep 24 '14 at 18:56
  • @RolandoMySQLDBA Hi, seems I have resolved the issue. I manually ssh'ed into the box, and then run GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION and now I can do mysql -h x.x.x.x -uroot --database=abc. Still not sure why this is the case but thanks! – Max Sep 24 '14 at 19:06
1

You cannot run

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) from mysql.user WHERE user='root';

because you do not have remote rights to the mysql database.

You only have remote rights to mydb

GRANT ALL PRIVILEGES ON `mydb`.* TO 'root'@'%' WITH GRANT OPTION;

When you ran

mysql -h x.x.x.x -uroot --database=abc

you evidently do not have remote rights to database abc.

When you ran

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

you should now have rights to every database.

  • That makes sense, but how come I could access and modify database abc but doing a "mysql -h x.x.x.x -uroot" first then doing a "use abc"? – Max Sep 24 '14 at 19:13
  • Did you actually run any SELECTs, INSERTs, UPDATEs, or DELETEs after you ran use abc ? Note that you ran SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) from mysql.user WHERE user='root'; and it did not work. It should work now. When your grant was GRANT ALL PRIVILEGES ON mydb.* TO 'root'@'%' WITH GRANT OPTION;, you were not restricted from running use abc. Once inside, you were restricted from running SELECTs, INSERTs, UPDATEs, or DELETEs. – RolandoMySQLDBA Sep 24 '14 at 19:14
  • Gotcha. Now that I think about it, I probably did all CRUD on that box rather than through remote access. – Max Sep 24 '14 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.