[mysql] MySQL: Check if the user exists and drop it

There’s not standard way to check if a MySQL user exists and based on that drop it. Are there any workarounds for this?

Edit: I need a straight way to run this without throwing up an error
e.g.

DROP USER test@localhost; :    

To phyzome's answer (most highly voted one), it seems to me that if you put "identified by" at the end of the grant statement, the user will be created automatically. But if you don't, the user is not created. The following code works for me,

GRANT USAGE ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
DROP USER 'username'@'localhost';

Hope this helps.

If you mean you want to delete a drop from a table if it exists, you can use the DELETE command, for example:

 DELETE FROM users WHERE user_login = 'foobar'

If no rows match, it's not an error.

Update: As of MySQL 5.7 you can use DROP USER IF EXISTS statement. Ref: https://dev.mysql.com/doc/refman/5.7/en/drop-user.html

Syntax: DROP USER [IF EXISTS] user [, user] ...

Example: DROP USER IF EXISTS 'jeffrey'@'localhost';

FYI (and for older version of MySQL), this is a better solution...!!!

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

If you want to remove all users named 'tempuser' (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 ;

Usage:

CALL DropUserIfExistsAdvanced('tempuser', '%'); to remove user 'tempuser'@'%'

CALL DropUserIfExistsAdvanced('tempuser', '192.168.1.101'); to remove user 'tempuser'@'192.168.1.101'

CALL DropUserIfExistsAdvanced('tempuser', NULL); to remove all users named 'tempuser' (eg., say 'tempuser'@'%', 'tempuser'@'localhost' and 'tempuser'@'192.168.1.101')

Um... Why all the complications and tricks?

Rather then using DROP USER... You can simply delete the user from the mysql.user table (which doesn't throw an error if the user does not exist), and then flush privileges to apply the change.

DELETE FROM mysql.user WHERE User = 'SomeUser' AND Host = 'localhost';
FLUSH PRIVILEGES;

-- UPDATE --

I was wrong. It's not safe to delete the user like that. You do need to use DROP USER. Since it is possible to have mysql options set to not create users automatically via grants (an option I use), I still wouldn't recommend that trick. Here's a snipet from a stored procedure that works for me:

DECLARE userCount INT DEFAULT 0;
SELECT COUNT(*) INTO userCount FROM mysql.user WHERE User = userName AND Host='localhost';
IF userCount > 0 THEN
    SET @S=CONCAT("DROP USER ", userName, "@localhost" );
    PREPARE stmt FROM @S;
    EXECUTE stmt;
    SELECT CONCAT("DROPPED PRE-EXISTING USER: ", userName, "@localhost" ) as info;
END IF;
FLUSH PRIVILEGES;

In case you have a school server where the pupils worked a lot. You can just clean up the mess by:

delete from user where User != 'root' and User != 'admin';
delete from db where User != 'root' and User != 'admin';

delete from tables_priv;
delete from columns_priv;

flush privileges;

in terminal do:

sudo mysql -u root -p

enter the password.

select user from mysql.user;

now delete the user 'the_username'

DROP USER the_unername;

replace 'the_username' with the user that you want to delete.

I wrote this procedure inspired by Cherian's answer. The difference is that in my version the user name is an argument of the procedure ( and not hard coded ) . I'm also doing a much necessary FLUSH PRIVILEGES after dropping the user.

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 ;

I also posted this code on the CodeReview website ( https://codereview.stackexchange.com/questions/15716/mysql-drop-user-if-exists )

DROP USER 'user'@'localhost';

The above command will drop the user from the database, however, it is Important to know if the same user is already using the database, that session will not end until the user closes that session. It is important to note that dropped user will STILL access the database and perform any operations. DROPPING THE USER DOES NOT DROP THE CURRENT USER SESSION

Regarding @Cherian's answer, the following lines can be removed:

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI';
...
SET SQL_MODE=@OLD_SQL_MODE;
...

This was a bug pre 5.1.23. After that version these are no longer required. So, for copy/paste convenience, here is the same with the above lines removed. Again, for example purposes "test" is the user and "databaseName" is the database; and this was from this bug.

DROP PROCEDURE IF EXISTS databaseName.drop_user_if_exists ;
DELIMITER $$
CREATE PROCEDURE databaseName.drop_user_if_exists()
BEGIN
  DECLARE foo BIGINT DEFAULT 0 ;
  SELECT COUNT(*)
  INTO foo
    FROM mysql.user
      WHERE User = 'test' and  Host = 'localhost';
   IF foo > 0 THEN
         DROP USER 'test'@'localhost' ;
  END IF;
END ;$$
DELIMITER ;
CALL databaseName.drop_user_if_exists() ;
DROP PROCEDURE IF EXISTS databaseName.drop_users_if_exists ;

CREATE USER 'test'@'localhost' IDENTIFIED BY 'a';
GRANT ALL PRIVILEGES  ON databaseName.* TO 'test'@'localhost'
 WITH GRANT OPTION

This worked for me:

GRANT USAGE ON *.* TO 'username'@'localhost';
DROP USER 'username'@'localhost';

This creates the user if it doesn't already exist (and grants it a harmless privilege), then deletes it either way. Found solution here: http://bugs.mysql.com/bug.php?id=19166

Updates: @Hao recommends adding IDENTIFIED BY; @andreb (in comments) suggests disabling NO_AUTO_CREATE_USER.

Found the answer to this from one of the MySQL forums. We’ll need to use a procedure to delete the user.

User here is “test” and “databaseName” the database name.

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI';
USE databaseName ;
DROP PROCEDURE IF EXISTS databaseName.drop_user_if_exists ;
DELIMITER $$
CREATE PROCEDURE databaseName.drop_user_if_exists()
BEGIN
  DECLARE foo BIGINT DEFAULT 0 ;
  SELECT COUNT(*)
  INTO foo
    FROM mysql.user
      WHERE User = 'test' and  Host = 'localhost';
   IF foo > 0 THEN
         DROP USER 'test'@'localhost' ;
  END IF;
END ;$$
DELIMITER ;
CALL databaseName.drop_user_if_exists() ;
DROP PROCEDURE IF EXISTS databaseName.drop_users_if_exists ;
SET SQL_MODE=@OLD_SQL_MODE ;

CREATE USER 'test'@'localhost' IDENTIFIED BY 'a';
GRANT ALL PRIVILEGES ON databaseName.* TO 'test'@'localhost'
WITH GRANT OPTION

Combining phyzome's answer (which didn't work right away for me) with andreb's comment (which explains why it didn't) I ended up with this seemingly working code that temporarily disables NO_AUTO_CREATE_USER mode if it is active:

set @mode = @@SESSION.sql_mode;
set session sql_mode = replace(replace(@mode, 'NO_AUTO_CREATE_USER', ''), ',,', ',');
grant usage on *.* to 'myuser'@'%';
set session sql_mode = @mode;
drop user 'myuser'@'%';

DROP USER IF EXISTS 'user'@'localhost' ;

that works for me without throwing any errors in Maria DB, it should work for u too