List users with no database in MySQL

Question

I'm trying to list all users in a MySQL 5.0.67 installation with no databases associated, because I have to delete them. The Mysql installation previously had ~500 databases in it and someone did a sort of cleaning deleting all empty databases, but not the related users.

I think I should play with information_schema and mysql database to find what I'm looking for. Right?

Does anyone could help me please?

Thanks in advance!

• eggyal: MySQL users are not necessarily "associated" with a database. A user's permissions could be at the database level, but could also be at the global level or even at the level of more specific schema objects such as tables, columns or routines. Exactly what set of users are you after? Those who now have nothing but the USAGE permission?

Answer 1

On MySQL the users not necessarily must be related to a database, but if you need to know which users are not related to a database(s) you should query the tables "db" and "user" from database "mysql"

Try this to know all the users "even the root user" that are not related to a database

select
 u.host,
 u.user
from
 mysql.user as u left join mysql.db db on u.User = db.User and u.Host = db.Host
where
 db.Host is null and
 db.User is null

Answer 2

Solution for your question will consist of two parts:

1. We need to list all MySQL users:

   select user from mysql.user;

2. We need to check user's privileges to databases:

   select * from mysql.db;

And finally, we can merge those two queries into one showing user names that don't exist in database privilege table:

select users.user from mysql.user as users where not exists (
 select privileges.user from mysql.db as privileges 
 where privileges.user=users.user
) group by user; -- GROUP BY added only for distinct

Beware, that this solution will list all users that don't have specific privileges to one of your database. For example, it will also show your root account(as it probably, indeed, doesn't have any specific privileges) and accounts of users, that have access only to specific table (not database!).