33

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

4 Answers 4

28

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, 2011 at 12:36
  • 1
    What is your database name? you need to replace "DB_NAME" by the name of your db.
    – user79644
    Apr 27, 2011 at 12:50
  • Running select user from mysql.db without qualification returns 2 blank rows as well.
    – Chris S
    Apr 27, 2011 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, 2011 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, 2012 at 13:33
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.

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, 2012 at 13:13
  • 1
    Which database? I'm getting ERROR 1046 (3D000): No database selected
    – user124384
    Jul 25, 2018 at 22:01
  • 1
    @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, 2019 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, 2017 at 13:00
  • as root you can also do show grants for Jul 10, 2017 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.