37

How to know all the users that can access a database?

ZygD
  • 377
  • 1
  • 3
  • 11
user79483
  • 431

4 Answers4

34

Connect to the mysql instance as an admin user (generally as root) and give the following command...

select user from mysql.db where db='DB_NAME';
user79644
  • 656
16

user79644's answer gets the users with database-level privileges but will miss users with only table-level, column-level, or procedure-level privileges. To find all of them, use the following statements:

SELECT user,host FROM db WHERE db='name';
SELECT user,host FROM tables_priv WHERE db='name';
SELECT user,host FROM columns_priv WHERE db='name';
SELECT user,host FROM procs_priv WHERE db='name';

In MySQL 5.5 at least, it seems as though having column-level privileges implies that you have table-level privileges. Having table-level privileges does not imply that you have database-level privileges. Not sure about procedure-level privileges.

Scott Duckworth
  • 866
10
# current users that access the db
mysql> show processlist;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id  | User | Host      | db   | Command | Time | State | Info             |
+-----+------+-----------+------+---------+------+-------+------------------+
| 214 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+-----+------+-----------+------+---------+------+-------+------------------+

# who can access what at anytime and his privilege level
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

# what privileges are available
mysql> show privileges;
silviud
  • 2,735
4

You have to bear in mind, that the MySQL GRANT for databases can contain wildcard characters. This has to be accounted for by using LIKE in the query:

SELECT user,host FROM db WHERE 'name' LIKE db;
SELECT user,host FROM tables_priv WHERE db='name';
SELECT user,host FROM columns_priv WHERE db='name';
SELECT user,host FROM procs_priv WHERE db='name';
user4514
  • 593