Mysql drop user with grants for multiple hosts

Question

I added a user to my mysql-db with grants to access from several hosts, like:

GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host1';
GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host2';
GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host3';
GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host4';
....

What is the shortest way to remove the user completly? I tried:

drop user 'dbuser'@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'dbuser'@'%'

drop user 'dbuser';
ERROR 1396 (HY000): Operation DROP USER failed for 'dbuser'@'%'

show grants for 'dbuser';
ERROR 1141 (42000): There is no such grant defined for user 'dbuser' on host '%'

I thought, % wouzld be treated as a wildcard. Butr the only way seems to drop the user for every host, like:

drop user 'dbuser'@'host1';
drop user 'dbuser'@'host2';
drop user 'dbuser'@'host3';
...

Isn't there a more convenient way to remove the user?

Answer 1

MySQL <= 5.6.x

select user,host,password from mysql.user;

MySQL >=5.7.x

select user,host,authentication_string from mysql.user;

The above you will need to create a rollback plan and believe me you will always need one if deleting a lot of users and you want to save time.

to your question now:

select concat("DROP USER ","'",user,"'@'",host,"';") from mysql.user where host like "127.0.%" or host like "192.168%";

Depending on your platform , explore how to stick the output into a file and execute it