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
Related posts:
How to Combine Columns in MySQL
MySQL Clustered Index
How to Export MySQL Schema Without Data
How to Find And Replace Text in Entire Table in MySQL
How to Find Tables with Column Name in MySQL
How to Extract Database from MySQL dump file
How to Find Non-ASCII Characters in MySQL
How to Take Backup of Single Table in MySQL

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