Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 5 down vote accepted

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;
share|improve this answer
This is much more readable! Thanks! – Philip Durbin Apr 25 '12 at 19:44
SELECT * FROM mysql.db WHERE Db = '<database name in LC>';
share|improve this answer
Thanks. This answer does seem to output the same raw data as the chart in the question. . . but in a less readable format. I guess presenting the data in the same format is an exercise for the reader. . . ;) – Philip Durbin May 1 '09 at 18:06

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;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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