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:Microsoft Windows (W764)
Assigned to: CPU Architecture:Any
Tags: show databases privilege, workbench

[22 Mar 2012 15:18] Bob Dankert
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.
[22 Mar 2012 17:38] Rafael Antonio Bedoy Torres
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!
[22 Mar 2012 17:59] Valeriy Kravchuk
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.
[22 Mar 2012 18:47] Bob Dankert
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.
[22 Mar 2012 18:48] Bob Dankert
Current output pane contents on this server

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

[10 May 2012 17:41] Armando Lopez Valencia
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.
[10 May 2012 19:07] Bob Dankert
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]'@'%'
[15 Aug 2012 13:09] MySQL Verification Team
I couldn't repeat with 5.2.42, please try this version. Thanks.
[16 Aug 2012 13:56] Bob Dankert
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.
[19 Oct 2012 13:31] MySQL Verification Team
Please try version 5.2.44. Thanks.
[19 Oct 2012 13:51] Bob Dankert
Workbench 5.2.44 also crashed when I tried to open the connection.
[11 Feb 2013 21:35] Ruben Dario Morquecho Valdez
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!
[15 May 2014 7:45] Olle Nilsson
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.
[19 Sep 2014 11:56] Dave Hellings
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
[19 Sep 2014 11:58] Dave Hellings
By the way, I'm using as connection method "Standard TCP/IP over SSH"  The MYSQL hostname is localhost and port is 3306.