SHOW DATABASE PRIVILEGE error

[expired user #1367]'s profile image [expired user #1367] posted 17 years ago in General Permalink
Hello there!

We would like to move a PHPBB forum to a different hoster. And we want do this with HeidiSQL.
The Problem is, that we get a connection Problem to our database.
I still can Login but got an Error.

The Errorcode:

"SQL Error: Access denied. You need the SHOW DATABASES privilege for this operation"

When I klick OK the next message is:

ZQuery3: Cannot perform this operation on a closed dataset.

The Webhoster says arrogantly there configuration is perfect...
they mean the problem belongs to us... they tried also with HeidiSQL (localy) and didnt get the error.

Strange... with the old database on a different server we did not have any accessing problems. ;)


For a better understanding:

Old server:
Access via Phpmyadmin: OK
Access via HeidiSQL: OK

New Server:
Access via Phpmyadmin: OK
Access via HeidiSQL: Access Denied (see error code above)

Any one have suggestions for solving the Problem?
I Think the new Hoster have a problem with there configuration.

Thanks for help (and sorry for my bad english articulating)

Greets
EyeKeyFun

PS: Ive also tried this with old MYSQL-Front... Same error
ansgar's profile image ansgar posted 17 years ago Permalink
Type the database(s) you are sure you have access to into the "Database(s)" box in the connection screen of HeidiSQL.

HeidiSQL then doesn't use a SHOW DATABASES statement to retrieve all db-names.
ansgar's profile image ansgar posted 17 years ago Permalink
Alternatively ask your hoster to give you the "Show_db_priv" privilege. I guess they disabled that for security reasons which is not the baddest thing.
[expired user #2621]'s profile image [expired user #2621] posted 17 years ago Permalink

Alternatively ask your hoster to give you the "Show_db_priv" privilege. I guess they disabled that for security reasons which is not the baddest thing.



It may well be that the databases are on a shared MySQL server. That's what happens on my websites; it's a shared server so each user only has permission to view their specific databases, thus the "SHOW DATABASES" command is not allowed.

I list the databases for the site in the appropriate field, and that allows the connections to be made, but as the databases are loading I get that error message that eyekeyfun mentioned:

"SQL Error: Access denied. You need the SHOW DATABASES privilege for this operation"



That's annoying, but not a show stopper, and I love what you've done with the program, anse. Any chance you can add a checkbox option to the connection dialog in the future that will allow the user to disable the "SHOW DATABASES" command on those specific connections where its inappropriate?
ansgar's profile image ansgar posted 17 years ago Permalink
The solution will be to move the
SHOW DATABASE LIKE 'mysql'

to a place where it is only fired when it's needed. Apart from that we have no other place with a SHOW DATABASE when you specify some DBs in your session.

Will be fixed ASAP.
[expired user #2621]'s profile image [expired user #2621] posted 17 years ago Permalink
That sounds like an excellent solution, anse. Thank you very much for considering that so quickly.
ansgar's profile image ansgar posted 17 years ago Permalink
Done in revision 623: http://svn.sourceforge.net/heidisql/?rev=623&view=rev
[expired user #2621]'s profile image [expired user #2621] posted 17 years ago Permalink

Done in revision 623: http://svn.sourceforge.net/heidisql/?rev=623&view=rev[/quote:9fc770a958]
Cool - thanks for adding that in so fast! 8)

[expired user #2621]'s profile image [expired user #2621] posted 17 years ago Permalink

The solution will be to move the

SHOW DATABASE LIKE 'mysql'

to a place where it is only fired when it's needed. Apart from that we have no other place with a SHOW DATABASE when you specify some DBs in your session.



I came across another spot in HeidiSQL where the SHOW DATABASES command can cause an error (although your earlier update may have already taken care of this one.)

I am sporadically working on a complete rebuild of my ThreeStooges.net web site, and this weekend I wanted to update the database on my development site with the last data on the current site. When I tried to export the tables and data directly from the one host to the other I ran into that SHOW DATABASES error again. (Fortunately, I still had a copy of MySQL-Front 2.5 installed on my home PC, so I was able to use that to export the database tables.)

If you haven't already, could you set the export feature to use the databases listed for a host in its connection dialog, rather than automatically run the SHOW DATABASES?
ansgar's profile image ansgar posted 17 years ago Permalink

I came across another spot in HeidiSQL where the SHOW DATABASES command can cause an error (although your earlier update may have already taken care of this one.)



Unfortunately my last change here did not take care of that. Maybe I should search the whole project for SHOW DATABASE commands.

Anyway, I think I can fix that ASAP.
[expired user #2621]'s profile image [expired user #2621] posted 17 years ago Permalink

I came across another spot in HeidiSQL where the SHOW DATABASES command can cause an error (although your earlier update may have already taken care of this one.)



Unfortunately my last change here did not take care of that. Maybe I should search the whole project for SHOW DATABASE commands.

Anyway, I think I can fix that ASAP.



I had used the export feature last week, but that was at work copying a couple of tables from my intranet server to my local machine, so I had full privileges on both hosts and everythinig worked fine. (I really like the redesigned export dialog, by the way.) happy

Those are the only to places where I've run into the problem. If I do stumble across any others I'll post them here to let you know, and many thanks for the quick responses. :D

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