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!
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
Solution for your question will consist of two parts:
We need to list all MySQL users:
select user from mysql.user;
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!).
USAGEpermission?