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?

score 28 · Accepted Answer · answered Apr 25 '12 at 11:19

28

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;

answered Apr 25 '12 at 11:19

Graham

296
3
2

8

+1 for \G not seen that before – jx12345 Apr 08 '17 at 08:35
4

When using `\G` you don't need a `;` anymore. – halfpastfour.am Aug 12 '20 at 14:22

score 9 · Answer 2 · answered May 01 '09 at 17:29

9

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

answered May 01 '09 at 17:29

K. Brian Kelley

9,034
32
33

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 01 '09 at 18:06

sjas · Answer 3 · 2015-07-03T13:45:46.000

6

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;

edited Jul 03 '15 at 13:45

answered Jul 03 '15 at 13:35

sjas

324
1
4
13

Which MySQL users have access to a database?

3 Answers3