Comments - mariadb drop user "wildcard"

2 years, 9 months ago Mark Edwards

thank you, but i was hoping for a wildcard, rather than having to first determine all the IP numbers.

in other words, i would like to be able to just remove ALL of them no matter what IP number they have, or for that matter, no matter what value is in the "Host" column.

this is for an automated script to approve users given a new IP number.

 
2 years, 9 months ago Ian Gilfillan

It's not a one-liner, but perhaps something like this?

SELECT GROUP_CONCAT('\'',user, '\'@\'', host, '\'') INTO @accounts FROM mysql.user WHERE user = 'ILOVEMARIADB';
SET @accounts = CONCAT('DROP USER ', @accounts);
PREPARE s FROM @accounts;
EXECUTE s;
 
2 years, 9 months ago Mark Edwards

excellent! thank you. i have never used "PREPARE" before so this makes a great introduction.

i should think that there would be a one-liner, but this is certainly the next best option.

 
2 years, 9 months ago Daniel Black

In slightly shorter form:

SELECT GROUP_CONCAT(QUOTE(user),'@',QUOTE(host)) INTO @accounts FROM mysql.user WHERE user = 'ILOVEMARIADB';
EXECUTE IMMEDIATE CONCAT('DROP USER ', @accounts);
 
2 years, 9 months ago Mark Edwards
SELECT GROUP_CONCAT(QUOTE(user),'@',QUOTE(host)) INTO @accounts FROM mysql.user WHERE user = 'ILOVEMARIADB';
EXECUTE IMMEDIATE CONCAT('DROP USER ', @accounts);

thank you Daniel! life keeps getting better and better (while the solution shrinks). it still surprises me this requires a procedure, but this is EXCELLENT!

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.