MySQL: check if the user exists and discards it.

Question

There is no standard way to check if a MySQL user exists, and based on this, delete it. Are there any workarounds for this?

Edit: I need a direct way to run this without causing an error. eg.

DROP USER test@localhost; : 

Answer 1

Starting with MySQL 5.7 you can do the DROP USER IF EXISTS test

Additional information: http://dev.mysql.com/doc/refman/5.7/en/drop-user.html

Answer 2

This worked for me:

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

This creates the user if he does not already exist (and grants him a harmless privilege), and then deletes him anyway. The solution found here: http://bugs.mysql.com/bug.php?id=19166

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

Answer 3

Found the answer to this question from one of the MySQL forums. You must use the procedure to delete the user.

The user here is "test" and "databaseName" is the name of the database.

 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 ; 

'test' @'localhost' IDENTIFIED BY 'a'; . * TO 'test' @'localhost'

code>

Answer 4

To answer phyzome (the most voted), it seems to me that if you put “identified” at the end of the grant statement, the user will be created automatically. But if you do not, the user will not be created. The following code works for me,

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

Hope this helps.

Answer 5

FYI, this is the best solution ... !!!

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

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

Now, please look at the mentioned DropUserIfExistsAdvanced SP:

 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 ; 

Application:

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

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

CALL DropUserIfExistsAdvanced('tempuser', NULL); delete all users with the name 'tempuser' (for example, 'tempuser'@'%' , 'tempuser'@'localhost' and 'tempuser'@'192.168.1.101' )

Answer 6

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

which works for me without any errors in Maria DB, it should also work for u

Answer 7

Um ... Why all the difficulties and tricks?

Instead, using DROP USER ... you can simply remove the user from the mysql.user table (which does not throw an error if the user does not exist), and then reset the privileges to apply the change.

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

- UPDATE -

I was wrong. Unable to delete such user. You need to use DROP USER. Since it is possible that the mysql options do not allow users to be automatically created through grants (the option I'm using), I still do not recommend this trick. Here's a sniper 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; 

Answer 8

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

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

This was a bug prior to 5.1.23. After this version, they are no longer required. Thus, for the convenience of copying / pasting, the same with deleting the specified lines. Again, for example, the goal of the "test" is the user, and "databaseName" is the database; and it was from this mistake .

 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 

Answer 9

I wrote this procedure inspired by Cherian. The difference is that in my version the username is the argument of the procedure (and not the hard one). I also do the much needed FLUSH PRIVILEGES after deleting 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 )

Answer 10

 DROP USER 'user'@'localhost'; 

The above command will remove the user from the database, however, it is important to know that the same user is already using the database, this session will not end until the user closes this session. It is important to note that a lost user will STILL access the database and perform any operations. USER SEPARATION DOES NOT REMOVE THE CURRENT USER SESSION

Answer 11

By combining the phyzome answer (which did not work right away for me) with the andreb comment (which explains why this is not so), I ended up with this seemingly working code that temporarily disables the 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'@'%'; 

Answer 12

If you have a school server where students worked a lot. You can just clear the clutter:

 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; 

Answer 13

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

  DELETE FROM users WHERE user_login = 'foobar' 

If the lines do not match, this is not an error.