Home / server / Questions / 2616
Accepted
Philip Durbin
Asked: 2009-05-02 09:23:03 +0800 CST

Which MySQL users have access to a database?

  • 772

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?

mysql

3 Answers

  1. Best Answer

    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;
    • 24 
  2. SELECT * FROM mysql.db WHERE Db = '<database name in LC>';
    • 9

  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;
    • 5