How to know all the users that can access a database?
4 Answers
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';
-
I just ran this against a live MariaDB (MySQL fork) and it returned 2 blank rows.– Chris SCommented Apr 27, 2011 at 12:36
-
1What is your database name? you need to replace "DB_NAME" by the name of your db. Commented Apr 27, 2011 at 12:50
-
Running
select user from mysql.db
without qualification returns 2 blank rows as well.– Chris SCommented 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.– rvfCommented 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– user4514Commented Jan 19, 2012 at 13:33
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.
-
1This 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– user4514Commented Jan 19, 2012 at 13:13
-
1Which database? I'm getting
ERROR 1046 (3D000): No database selected
Commented 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 commandUSE mysql;
of add the database name to the table names, such asFROM mysql.db
orFROM mysql.tables_priv
– user396327Commented Apr 10, 2019 at 5:20
# 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;
-
3show grants shows only the grants of the current (logged in) user– nl-xCommented Apr 21, 2017 at 13:00
-
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';