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 ;