MySQL Bugs: #64738: Workbench doesn't work if you don't have SHOW DATABASES privilege

Bug #64738	Workbench doesn't work if you don't have SHOW DATABASES privilege - hangs/crash
Submitted:	22 Mar 2012 15:18	Modified:	11 Feb 2013 21:35
Reporter:	Bob Dankert	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.44	OS:	Windows (W764)
Assigned to:		CPU Architecture:	Any
Tags:	show databases privilege, workbench

Description:
Workbench doesn't work if you don't have the show databases privilege on the user account you are using.  The object browser doesn't display anything, and workbench hangs/crashes when you try to execute queries, instead of just returning the results.  It does return the results for "Show Tables", but I am unable to get any queries of the tables to work without it hanging/crashing.  

How to repeat:
Establish a connection with a user that is lacking the show databases privilege.  Unfortunately, some shared hosting providers don't always give the show databases privilege to accounts.

Suggested fix:
When a default database is entered into the connection, it should not require that show databases work.  If Show databases fails due to missing privileges, add the default database (the database the current connection is using) to the list and populate the tables with show tables/describe tables.

There is no reason that queries should not be able to execute as well.  I verified that the legacy Query Browser is at least able to execute queries, although I think that WB should also populate the object browser with at least the default database (current connection) and the tables in this connection.

Hello Bob,

Thanks for your report, I can't reproduce your problem, can you please provide more info regarding your environment.

On Workbench click on Help -> System info and paste here what you get.

Thanks in advance!

What exact server version, x.y.z, are you working with? I have no problems connecting and sending queries to default test database with a user having only ALL on test.* privilege.

This is not a server I administer, and I have no way to find out the granted permissions, but I will give you what I can.

When I first open workbench, I get this message in the output pane:

0	13:41:45	Get schemata	Error Code: 1227 Access denied; you need the SHOW DATABASES privilege for this operation

Executing a select version() returns 5.0.90

I don't think the system info requested by Rafael will help at all, as this is a remote server that is not on my computer.  I have ~100 other connections in workbench that work fine, so I do not believe this is an issue that the system info will help.  If you still want this, let me know and I can provide it.

When I execute a simple query, such as "select * from <tablename>", this is added to the output, and it will never complete.  Sometimes it crashes workbench, sometimes I can close the connection, sometimes I can cancel the query.  It's very erratic, but it consistenly never works:

5	13:47:44	select * from tblusers LIMIT 0, 5000	Fetching...

I will attach a screen shot showing the output pane contents.

Current output pane contents on this server

Attachment: bug_64738_1.JPG (image/jpeg, text), 44.60 KiB.

Hello Bob.
I wasn't able to reproduce this with an user with no "Show Databases" Privileges.
Can you please run the following command 
SHOW GRANTS FOR '[YOUR-USER]'@'localhost'; and paste the output here?
Thanks.

It's a remote database, so I issued this command:
show grants for '[USERNAME]'@'%';

Results (2 rows returned):

GRANT USAGE ON *.* TO '[USERNAME]'@'%' IDENTIFIED BY PASSWORD '[PASSWORD]'

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES ON `[DATABASE_NAME]`.* TO '[USERNAME]'@'%'

I couldn't repeat with 5.2.42, please try this version. Thanks.

When I tried to connect to the same database that was previously giving me problem is 5.2.42, it crashed WorkBench.  I Double clicked the connection to open it and it crashed about 1 second later.  It crashed before it was able to open the tab for the connection.

Please try version 5.2.44. Thanks.

Workbench 5.2.44 also crashed when I tried to open the connection.

please try new version 5.2.46.

- could you please povide details in remote conection parameter set ?
- Could you please provide the remote server version?

Thank you for your interest in MySQL workbench!

Trying to use a user lacking 'show databases' privilege to connect to a specified schema crashes workbench also for me.

Ubuntu: 13.10
Workbench: 6.1.4.11733 build 1454
Remote MySQL: 5.6.16-log MySQL Community Server (GPL)

Configuring the connection, and running "test connection" works fine. When trying to actually use it, workbench crashes immediately after submitting the password.

Same here, using '5.5.38-0ubuntu0.14.04.1' & MySQL Workbench Community (GPL) for Windows version 6.1.4  revision 11773 build 1454 on Windows 8.1

With an SQL user with only SELECT,INSERT,UPDATE,DELETE GRANTS and the folowing statement in the mysql config file Workbench crashes when opening the DB connection.

[mysqld]
skip-show-database

By the way, I'm using as connection method "Standard TCP/IP over SSH"  The MYSQL hostname is localhost and port is 3306.