25

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

19

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';
8
  • I just ran this against a live MariaDB (MySQL fork) and it returned 2 blank rows. – Chris S Apr 27 '11 at 12:36
  • 1
    What is your database name? you need to replace "DB_NAME" by the name of your db. – user79644 Apr 27 '11 at 12:50
  • Running select user from mysql.db without qualification returns 2 blank rows as well. – Chris S Apr 27 '11 at 12:51
  • Not sure what the differences are between MariaDB and MySQL, but that query runs as expected on a standard MySQL 5 installation. – rvf Apr 27 '11 at 13:38
  • This is only part of the answer to the OP. The mysql.user.db field can contain wildcard characters that match several databases. using your proposed query misses them. dev.mysql.com/doc/refman/5.0/en/grant.html – user4514 Jan 19 '12 at 13:33
14

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.

3
  • 1
    This is only part of the answer. The mysql.user.db field can contain wildcard characters that match several databases. using your proposed query misses them. dev.mysql.com/doc/refman/5.0/en/grant.html – user4514 Jan 19 '12 at 13:13
  • Which database? I'm getting ERROR 1046 (3D000): No database selected – user124384 Jul 25 '18 at 22:01
  • @user124384 What's not said in the answer is that you should be using the mysql database. That's where all the data used by MySQL is stored. Either give the command USE mysql; of add the database name to the table names, such as FROM mysql.db or FROM mysql.tables_priv – user396327 Apr 10 '19 at 5:20
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;
2
  • 3
    show grants shows only the grants of the current (logged in) user – nl-x Apr 21 '17 at 13:00
  • as root you can also do show grants for – Rowan Hawkins Jul 10 '17 at 15:33
3

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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