Which MySQL users have access to a database?

Question

How can I tell which MySQL users have access to a database and what privileges they have?

I seem to be able to get this information from phpMyAdmin when I click "Privileges". . .

                 Users having access to "mydatabase"
User     Host       Type               Privileges              Grant
myuser1  %          database-specific  ALL PRIVILEGES          Yes
root     localhost  global             ALL PRIVILEGES          Yes
myuser2  %          database-specific  SELECT, INSERT, UPDATE  No

. . . but I'd like to know how to perform this query from the command line.

(phpMyAdmin often shows me the SQL syntax of the command it is executing, but I don't see it in this case.)

Please note that I'm not asking what grants a particular user has (i.e. "SHOW GRANTS for myuser1") but rather, given the name of a database, how do I determine which MySQL users have access to that database and what privileges they have? Basically, how can I get the chart above from the command line?

Answer 1

You can append \G to the command to get results displayed in 'grid' veiw

SELECT * FROM mysql.db WHERE Db = '<database name in LC>'\G;

Answer 2

SELECT * FROM mysql.db WHERE Db = '<database name in LC>';

Answer 3

You might give this a try, should provide the best readability:

select db 'DATABASE', host HOST, user USER from mysql.db where db = '<databasename>';

Something more memorizeable for the cli:

select db,host,user from mysql.db;