3

so I'm trying to connect to a MySQL 5.1.x server remotely using a recent version of MySQLWorkbench (6.3.6).

I have run these commands on the MySQL prompt:

use mysql;
grant all privileges on mydb.*  to 'myuser'@'%' identified by 'mypassword';
flush privileges;

and I can connect to the schema/DB using workbench but I cannot get a listing of the tables/other schema objects.

This is the exact response I'm getting:

Error Code: 1227 Access denied; you need the SHOW DATABASES privilege for this operation

This is the response I'm getting from show grants. I ran this as root user on the localhost. I've obfuscated any info that might identify my DB.

mysql> show grants for 'mydb'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for mydb@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mydb'@'%' IDENTIFIED BY PASSWORD 'mypassword' |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'mydb'@'%'                                                      |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Any ideas on how to fix it? Thank you.

mrjayviper
  • 2,258
  • 11
  • 46
  • 82

1 Answers1

6

With this line

grant all privileges on mydb.* ...

you are giving privileges to your user to every table in the mydb database. SHOW DATABASES however is a global privilege, not affected by above line.

You can run below to also allow the SHOW DATABASES command:

GRANT SHOW DATABASES ON *.* TO 'myuser'@'%'
baao
  • 71,625
  • 17
  • 143
  • 203
  • is there ways an option so in the app so I don't show other databases objects? I don't really care for them. Only the objects within the database where I have access. :) – mrjayviper Apr 11 '16 at 08:23
  • So you only want to see the tables in `mydb`? @mrjayviper -- If so, use `show tables in mydb;` – baao Apr 11 '16 at 08:25
  • but not gonna help me with my mysqlworkbench issue? mysqlworkbench won't even list the objects in the database where I have access. – mrjayviper Apr 11 '16 at 08:35
  • Sure it will. @mrjayviper -- Why not? – baao Apr 11 '16 at 08:35
  • please see link. objects normally appear here. I can't see them. http://i.imgur.com/ObunO1F.jpg – mrjayviper Apr 11 '16 at 08:39
  • Ahh, now I get it. No, you'll need to run the command I posted in my answer. Thereafter, you can hide all the databases you don't want to see. See this answer on howto http://stackoverflow.com/questions/19392133/user-cannot-see-databases-in-mysql-workbench – baao Apr 11 '16 at 08:42