Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

how to know all users that can access certain database ?

share|improve this question
add comment

4 Answers

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.

share|improve this answer
    
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
add comment

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';
share|improve this answer
    
I just ran this against a live MariaDB (MySQL fork) and it returned 2 blank rows. –  Chris S Apr 27 '11 at 12:36
    
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
add comment
# 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;
share|improve this answer
add comment

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';
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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