distal-attribute
distal-attribute
distal-attribute
distal-attribute

Error Need Show Databases Privilege

richyrich posted 5 years ago in Creating a connection
I've just downloaded the most recent build and when I try and connect to a remote server I get an error saying "Access Denied; You need the SHOW DATABASES privilege for this operation."

I read in another thread that you can enter the database you do have access to in the databases box. But, I don't see this box on the Session screen. Link How to I connect to the DB?
richyrich posted 5 years ago
What I'm also confused about is I downloaded the 5.1 Installer but if I go into the HeidiSQL folder in Program Files the version number of the exe is 5.0.0.3272. Is this the most recent build?
ansgar posted 5 years ago
See issue #1485

Version number is broken, yes. Please ignore that wink
richyrich posted 5 years ago
OK. I see.

The problem is that if "Deactivate SHOW DATABASE" setting is ticked on the server, it doesn't matter what privileges the specified user has, they won't be able to connect.

I've just had exactly the same issue with MySQL's own GUI tools, even with the ability to specify the schema name.

I run my own MySQL server and was able to untick this setting, however, my understanding was this was a security risk and just giving each user the relevant privileges should be sufficient.

So, if someone is on a hosted server and the host has this setting ticked, I guess the only way they could connect to their server would be using PHPMyAdmin provided by the host.
richyrich posted 5 years ago
When I untick this setting, I can connect fine using HeidiSQL... :)

Thanks for your reply..

ansgar posted 5 years ago
My understanding is that the "Show_db" privilege is not required to fire SHOW DATABASES. Even with zero privileges, SHOW DATABASES returns the "information_schema" db. Only 4.0 and older servers do return an error on SHOW DATABASES.

richyrich wrote: I've just had exactly the same issue with MySQL's own GUI tools, even with the ability to specify the schema name.



How do you mean - it was impossible to get into your databases or was it possible with the MySQL tools?
richyrich posted 5 years ago
It's a little strange. I'd been connecting fine previously and then today I got an Access Violation when using MySQL GUI.

I thought I'd download HeidiSQL and try that and then I got the SHOW DATABASES privilege error. Some further digging found the error in the MySQL GUI was exactly the same issue.

So, I couldn't connect to a Schema with a username that had sufficient privileges when "Deactivate SHOW DATABASE" was ticked. Now I've unticked it, I can connect fine using both MySQL GUI and HeidiSQL.

Unfortunately, I'm not entirely sure what caused this to just stop working in the first place though. I hadn't made any changes to the server. Just suddenly couldn't connect.
richyrich posted 5 years ago
I have just tried changing the setting back so it is ticked and when I try to connect using a username that has privileges for the schema I'm trying to connect to and information_schema I receive the error again.

So, it seems, "Deactivate SHOW DATABASE" must be unticked for a GUI to be able to connect, irrespective of the privileges the user has.

I'm using MySQL 5.0.77, btw. If you make a change to the settings, you must restart the MySQL service for that changes to take effect.

Please login to leave a reply, or register at first.