mysql - show all users/hosts with privileges to access a certain database

Question

Is there a way to list all users/hosts that have been granted access to a database? I know I can do this:

> show grants for someuser;
+------------------------------------------------------------------------------------------------------+
| Grants for someuser@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `someuser`@`%` IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXX' |
| GRANT ALL PRIVILEGES ON `a_database`.* TO `someuser`@`%` WITH GRANT OPTION                           |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Or this:

> show grants for someuser@afqdn;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for someuser@afqdn                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `someuser`@`qfqdn` IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXX' |
| GRANT ALL PRIVILEGES ON `a_database`.* TO `someuser`@`afqdn`                                             |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Is there a way in which I can provide the database in the request? And then get all different users that have been granted access to that db in a single shot? In the examples I used it starts from a given username (and for my specific use-case I do have the username) but how about we want to list all users in a single shot instead of guessing user by user / fqdn by fqdn?

Answer 1

Is there a way in which I can provide the database in the request? And then get all different users that have been granted access to that db in a single shot?

You can use the mysql.db table which stores the following data

mysql> select * from db limit 1 \G
*************************** 1. row ***************************
                 Host: xxx.xxx.xxx.xxx
                   Db: test
                 User: userTst1
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.00 sec)

In your particular case you can use the following query

mysql> select User,Host from mysql.db where db='test' group by User,Host;
+---------------+----------------+
| User          | Host           |
+---------------+----------------+
| userTst1      | xxx.xxx.xxx.xxx|
| userTst2      | xxx.xxx.xxx.xxx|
| userTst3      | xxx.xxx.xxx.xxx|
+---------------+----------------+