Emulating "DROP USER IF EXISTS"

Question

I wrote this MySQL stored procedure to emulate DROP USER IF EXISTS (not available in MySQL). Any comments or identified bugs?

DROP PROCEDURE IF EXISTS DropUserIfExists;
DELIMITER $$
 CREATE PROCEDURE DropUserIfExists(MyUserName VARCHAR(100))
 BEGIN
 DECLARE foo BIGINT DEFAULT 0 ;
 SELECT COUNT(*)
 INTO foo
 FROM mysql.user
 WHERE User = MyUserName ;
 IF foo > 0 THEN
 SET @A = (SELECT Result FROM (SELECT GROUP_CONCAT("DROP USER"," ",MyUserName,"@'%'") AS Result) AS Q LIMIT 1);
 PREPARE STMT FROM @A;
 EXECUTE STMT;
 FLUSH PRIVILEGES;
 END IF;
 END ;$$
DELIMITER ;

Answer 1

I would say you are wrong!!!

Let me explain...

If there is a user named 'tempuser'@'localhost' and you are trying to call DropUserIfExists('tempuser'), the above procedure may produce error something like:

Error Code: 1396 Operation DROP USER failed for 'tempuser'@'%', since there is no user 'tempuser'@'%'

A workaround for this problem is to pass both username and host name to the SP (pass a null host name if you want to delete all users named tempuser)

The following SP will help you to remove user 'tempuser'@'%' by executing CALL DropUserIfExistsAdvanced('tempuser', '%');

If you want to remove all users (say 'tempuser'@'%', 'tempuser'@'localhost' and 'tempuser'@'192.168.1.101') execute SP like CALL DropUserIfExistsAdvanced('tempuser', NULL); This will delete all users named tempuser!!! seriously...

Now please have a look on mentioned SP DropUserIfExistsAdvanced:

DELIMITER $$
DROP PROCEDURE IF EXISTS `DropUserIfExistsAdvanced`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `DropUserIfExistsAdvanced`(
 MyUserName VARCHAR(100)
 , MyHostName VARCHAR(100)
)
BEGIN
DECLARE pDone INT DEFAULT 0;
DECLARE mUser VARCHAR(100);
DECLARE mHost VARCHAR(100);
DECLARE recUserCursor CURSOR FOR
 SELECT `User`, `Host` FROM `mysql`.`user` WHERE `User` = MyUserName;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET pDone = 1;
IF (MyHostName IS NOT NULL) THEN
 -- 'username'@'hostname' exists
 IF (EXISTS(SELECT NULL FROM `mysql`.`user` WHERE `User` = MyUserName AND `Host` = MyHostName)) THEN
 SET @SQL = (SELECT mResult FROM (SELECT GROUP_CONCAT("DROP USER ", "'", MyUserName, "'@'", MyHostName, "'") AS mResult) AS Q LIMIT 1);
 PREPARE STMT FROM @SQL;
 EXECUTE STMT;
 DEALLOCATE PREPARE STMT;
 END IF;
ELSE
 -- check whether MyUserName exists (MyUserName@'%' , MyUserName@'localhost' etc)
 OPEN recUserCursor;
 REPEAT
 FETCH recUserCursor INTO mUser, mHost;
 IF NOT pDone THEN
 SET @SQL = (SELECT mResult FROM (SELECT GROUP_CONCAT("DROP USER ", "'", mUser, "'@'", mHost, "'") AS mResult) AS Q LIMIT 1);
 PREPARE STMT FROM @SQL;
 EXECUTE STMT;
 DEALLOCATE PREPARE STMT;
 END IF;
 UNTIL pDone END REPEAT;
END IF;
FLUSH PRIVILEGES;
END$$
DELIMITER ;