How to Show Users With Access to MySQL Database

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

Sreeram Sreenivasan

Sreeram has more than 10 years of experience in web development, Python, Linux, SQL and database programming.