Comments - mariadb drop user "wildcard"
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.
You can drop multiple users in the same statement by comma-separating them:
You don't specify your version, but you don't want to be directly deleting from mysql.user, especially from MariaDB 10.4 where it is a view.
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.
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;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.
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);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!