0

I have two MySQL databases hosted on my website, one for a MyBB forum and the other for data displayed on the website that comes from Raspberry Pis. The MyBB database has a user associated with it, and I've created another MySQL user to associate with the other database and granted it all privileges in cPanel.

When I connect to the database on the Raspberry Pi, I am able to use most SQL commands (INSERT, SELECT, DELETE, etc.) and they work fine. However, the command "SHOW TABLES" gives an "access denied" error for the MySQL user.

I tried deleting the database and repopulating it didn't help. Any ideas?

  • What GRANTs does that user have? – Rick James Aug 2 '16 at 4:29
1

From the official MySQL Documentation, You see only those databases for which you have some kind of privilege, unless you have the global SHOW DATABASES privilege.

Check the grants for the connected user. To check grants:

SHOW GRANTS FOR root(user);

For example, if you wish grant SELECT privileges to a user, login as root (user with grant privilege) and issue the following command statement.

GRANT SELECT ON *.* to 'user'@'host';

Also, from the same document, you can use the mysqlshow command to display database and table information.

I hope this answer will help you. Thanks.

  • Thanks for your help. The problem was actually that I had the database name formatted into the SHOW TABLES query surrounded by single quotes. When I removed the single quotes, the query started working fine. Apparently, SHOW TABLES does not accept single quotes like the other SQL commands do. – tjohnson Aug 2 '16 at 10:16
  • 1
    I was confusing backticks with single quotes. I forgot that mogrify inserts single quotes, and the database name needs to be surrounded by backticks instead. – tjohnson Aug 2 '16 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.