MySQL:检查用户是否存在并删除它

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

没有标准的方法来检查MySQL用户是否存在并基于此删除它。有什么解决办法吗?

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

编辑:我需要一个直接的方法来运行这个，而不需要抛出一个错误。

DROP USER test@localhost; :

13 个解决方案

#1

Since MySQL 5.7 you can do a DROP USER IF EXISTS test

因为MySQL 5.7，如果存在测试，您可以做一个DROP用户。

More info: http://dev.mysql.com/doc/refman/5.7/en/drop-user.html

更多信息:http://dev.mysql.com/doc/refman/5.7/en/drop-user.html。

#2

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

如果用户还不存在(并授予它一个无害的特权)，这将创建用户，然后以任何一种方式删除它。找到解决办法:http://bugs.mysql.com/bug.php?id=19166

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

更新:@Hao建议添加由;@andreb(在评论中)建议禁用NO_AUTO_CREATE_USER。

#3

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

从MySQL论坛中找到这个问题的答案。我们需要使用一个过程来删除用户。

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

这里的用户是“测试”和“databaseName”数据库名称。


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 
创建由“a”标识的用户“测试”@“localhost”;授予databaseName上的所有特权。*使用GRANT选项“测试”@“localhost”

#4

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,

对于phyzome的回答(投票最多的)，在我看来，如果您在grant语句的末尾加上“identify by”，用户将自动创建。但是如果不这样做，用户就不会被创建。下面的代码适用于我，

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

Hope this helps.

希望这个有帮助。

#5

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

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

这对我来说是可行的，不会在Maria DB中产生任何错误，对你来说也是可行的

#6

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

关于@Cherian的回答，以下行可以删除:

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.

这是5.1.23之前的一个错误。在那个版本之后，这些不再需要。因此，为了方便复制/粘贴，这里删除了上面的行。同样，例如，“test”是用户，“databaseName”是数据库;这是来自这个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

#7

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.

而是使用DROP USER…只需从mysql中删除用户即可。user表(如果用户不存在，它不会抛出错误)，然后刷新特权来应用更改。

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:

我错了。像这样删除用户是不安全的。您确实需要使用DROP用户。由于可以将mysql选项设置为不通过grant自动创建用户(我使用的一个选项)，所以我仍然不建议使用这种技巧。这里有一个对我有用的存储过程中的狙击器:

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;

#8

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 )

我还在CodeReview网站上发布了这段代码(https://codereview.stackexchange.com/questions/15716/mysql-drop-user-if存在)

#9

FYI, this is a better solution...!!!

这是一个更好的解决方案…!!

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

下面的SP将通过执行调用DropUserIfExistsAdvanced('tempuser'， '%')来帮助您删除用户'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...

如果您想删除所有命名为“tempuser”(输入“tempuser”@“%”、“tempuser”@“localhost”和“tempuser”@“192.168.1.101”)的用户，请执行SP，如调用DropUserIfExistsAdvanced(“tempuser”，NULL);这将删除所有命名为tempuser的用户!!!认真……

Now please have a look on mentioned SP DropUserIfExistsAdvanced:

现在请大家看看前面提到的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'@'%'

调用DropUserIfExistsAdvanced(颞部,' % ');删除用户的颞部@‘%’

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

叫DropUserIfExistsAdvanced(“颞部”、“192.168.1.101”);删除用户的颞部“@”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')

调用DropUserIfExistsAdvanced(颞部,NULL);删除所有名为“tempuser”的用户。，输入'tempuser'@'%'， 'tempuser'@'localhost'， 'tempuser'@'192.168.1.101')

#10

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

上面的命令将用户从数据库中删除，但是，重要的是要知道，如果相同的用户已经在使用数据库，那么直到用户关闭会话时，会话才会结束。值得注意的是，被删除的用户仍将访问数据库并执行任何操作。删除用户不会删除当前用户会话。

#11

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:

结合phyzome的答案(这对我来说并不是立刻有效)和andreb的评论(这解释了为什么它没有)，我最终得到了这个看似有效的代码，如果它是活动的，它会暂时禁用NO_AUTO_CREATE_USER模式:

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'@'%';

#12

-1

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;

#13

-5

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

如果您的意思是要删除表中存在的drop，您可以使用delete命令，例如:

 DELETE FROM users WHERE user_login = 'foobar'

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

如果没有行匹配，则不是错误。

#1