Many times database administrators need to find out which users have access to their MySQL database, since it is important to prevent unauthorized access to your database. In this article, we will find out how to show users with access to MySQL database.
How to Show Users With Access to MySQL Database
You can easily find out which users have access to your database (e.g. mydb) with the following query.
mysql> SELECT * FROM mysql.db WHERE Db = '<database name in Lowercase>'\G;
Here is a sample query to view users who have access to your database mydb.
mysql> SELECT * FROM mysql.db WHERE Db = 'mydb'\G;
If you mention \G at the end of above query, you will see the result in grid format.
Users having access to "mydb" User Host Type Privileges Grant myuser1 % database-specific ALL PRIVILEGES Yes root localhost global ALL PRIVILEGES Yes myuser2 % database-specific SELECT, INSERT, UPDATE No
This is a very useful query to find out which users have what type of privileges to your database. You can add it to a shell script to automate these queries and regularly check for unauthorized access to your database.
Also read:
How to Show User Permissions in MySQL
How to Force Git Pull to Overwrite Local Changes
How to Check Remote SSL Certificate in Linux
How to Enable SSL for MySQL in Windows
How to Append One File to Another in Linux