Listing databases, users, and permissions may not be as easy as it sounds. Yes there is SHOW DATABASES command which does exactly what it says on the tin – shows all databases. What about users? Which user can access what database? Well, cutting long story short, here are three queries I use to get all this info:

SELECT Host, Db, User,       Select_priv, Grant_priv FROM db   ORDER BY Db, User;
SELECT Host, User, Password, Select_priv, Grant_priv FROM user ORDER BY User;
SHOW DATABASES;

Don’t forget to switch into system database first using USE mysql.

The first query is probably the most useful. I usually create separate database for each new project, and new user, which can only access that database. This query will list all those user account and user databases, while system databases and account will not be displayed. So, if there are 5 project with 5 databases and 5 users, this query will return exactly these 5 rows:

mysql> SELECT Host, Db, User, Select_priv, Grant_priv FROM db ORDER BY Db, User;
+-----------+------------------+------------------+-------------+------------+
| Host      | Db               | User             | Select_priv | Grant_priv |
+-----------+------------------+------------------+-------------+------------+
| localhost | apple            | user_apple       | Y           | Y          |
| localhost | cider            | user_cider       | Y           | Y          |
| localhost | music            | user_music       | Y           | Y          |
| localhost | rocknroll        | user_rocknroll   | Y           | Y          |
| localhost | terminator       | user_terminator  | Y           | Y          |
+-----------+------------------+------------------+-------------+------------+
5 rows in set (0.01 sec)

The second query will list the same 5 users plus the system user, root:

mysql> SELECT Host, User, Select_priv, Grant_priv FROM user ORDER BY User;
+-----------+------------------+-------------+------------+
| Host      | User             | Select_priv | Grant_priv |
+-----------+------------------+-------------+------------+
| localhost | root             | Y           | Y          |
| localhost | user_apple       | N           | N          |
| localhost | user_cider       | N           | N          |
| localhost | user_music       | N           | N          |
| localhost | user_rocknroll   | N           | N          |
| localhost | user_terminator  | N           | N          |
+-----------+------------------+-------------+------------+
6 rows in set (0.00 sec)

Third, SHOW DATABASES will return all 5 user databases plus system database mysql plus up to two more system databases, information_schema and performance_schema:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| apple              |
| cider              |
| music              |
| rocknroll          |
| terminator         |
| mysql              |
| information_schema |
| performance_schema |
+--------------------+
8 rows in set (0.00 sec)

Finally, check the content of the folder where MySQL stores database files. Usually, this will be /var/lib/mysql/. For each database that uses MyISAM engine, there should be one folder. System databases like mysql and performance_schema may have folders as well.

All these tools together should give you pretty good picture of what’s going on in your system.

2,344 total views, 2 views today