mySQL show database for user

Question

Anyone knows the MySQL syntax to show the database names that a specific user has the privilege to see in a MySQL database? Say the user is 'dimitris' accessing a database at 'localhost', what would be the syntax to see all databases he has the privilege to select, update, insert etc?

Answer 1

For the current logged in user, you can use SHOW DATABASES;. But, if the user has the SHOW DATABASES; privilege, he'll be able to see all databases, even if he doesn't have access to it. (reference)

Assuming you've read access to the mysql.db table, you can use:

SELECT * FROM mysql.db WHERE User="dimitris";

This will return a result set, with Host (e.g. localhost), Db (e.g. somedatabase), User (e.g. dimitris) and the privileges for that database (Select_priv, Update_priv, etc)

Answer 2

You can get list of the databases you have access to with:

SHOW DATABASES;

If you want to get the list for some other user than the user you're logged in as, you have to query the mysql.db table.