CODEWITHSUNDEEP
mysqldatabase
skatz
skatz

Reputation: 125

List users with no database in MySQL

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!

Upvotes: 2

Views: 1727

Answers (2)

Paweł Tomkiel
Paweł Tomkiel

Reputation: 1984

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!).

Upvotes: 1

Jhonny Montoya
Jhonny Montoya

Reputation: 109

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

Upvotes: 1

Related Questions