5

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?

Werner
  • 1,695
  • 3
  • 21
  • 42
  • I don't know of a single command to do what you want, but this will generate the individual statements: select concat('drop user \'', user, '\'@\'', host, '\';') as drop_statement from mysql.user where user = 'username'; – lusional Oct 13 '15 at 20:49

3 Answers3

4

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

ninjabber
  • 371
  • 2
  • 7
  • Here's something that worked for me without the need of a (temp) file `echo 'select concat("DROP USER ",user,"@\"",host,"\";") from mysql.user where User = "alex"' | mysql -uroot -N | mysql -uroot` – Alex Dec 11 '18 at 11:12
0

If you're using MySQL >= 5.0.2, you can remove an account and its privileges as follows:

DROP USER user;

The statement removes privilege rows for the account from all grant tables.

(from: https://dev.mysql.com/doc/refman/5.0/en/drop-user.html)

daveblake
  • 194
  • 8
  • 3
    While true, this is not an answer to the question. From the page you linked to: "If you specify only the user name part of the account name, a host name part of '%' is used." The question is how to drop all users with the same name but different host parts. – lusional Oct 13 '15 at 20:45
0
SELECT GROUP_CONCAT('\'',user, '\'@\'', host, '\'') INTO @users FROM mysql.user WHERE user = 'root';
SET @users = CONCAT('DROP USER ', @users);
PREPARE stmt FROM @users;
EXECUTE stmt;
h0tw1r3
  • 6,618
  • 1
  • 28
  • 34
  • While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Machavity Mar 28 '19 at 00:31
  • Thanks so much. I had a user with a malformed host that included newlines, %, and ;. I had no idea how I was going to escape all that the right way for mysql to recognize it. – cs_alumnus Oct 10 '19 at 20:49
  • This answer goes probably from https://stackoverflow.com/a/11925999/5113030 – Artfaith Oct 03 '20 at 16:18