How to grant MySQL user privilege to "SHOW TABLES"

Question

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?

Answer 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.