33

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

ZygD
  • 317
  • 1
  • 3
  • 11
user79483
  • 391
  • 1
  • 6
  • 9

4 Answers4

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';
user79644
  • 546
  • 3
  • 3
  • 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
  • @ChrisS This is like 6 years too late, but the blank rows are user/host pairs with no username, like ''@'localhost', where user = '', host = 'localhost' – kingledion Feb 10 '17 at 18:42
  • I will verify that this works properly on mysql 5.0.45 though the effective version may be newer because of RHEL's process of back porting. My test system has mysql-5.0.45-7.el5 – Rowan Hawkins Jul 10 '17 at 15:43
  • Somehow missed, or changed in the years since written, is that the field names are now proper-cased. At least they are for my version: 'Ver 15.1 Distrib 10.2.23-MariaDB' and I seem to recall they have been for a while now. So it would be `SELECT User FROM mysql.db WHERE Db='DB_NAME';` –  Apr 10 '19 at 05:32
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
  • 806
  • 1
  • 8
  • 12
  • 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. http://dev.mysql.com/doc/refman/5.0/en/grant.html – user4514 Jan 19 '12 at 13:13
  • 1
    Which database? I'm getting `ERROR 1046 (3D000): No database selected` – user124384 Jul 25 '18 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` –  Apr 10 '19 at 05: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;
silviud
  • 2,677
  • 2
  • 16
  • 19
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';
user4514
  • 433
  • 4
  • 7