MySQL：检查用户是否存在并将其删除

Question

提问by Cherian

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; :

Answer 1

采纳答案by JavierCane

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

从 MySQL 5.7 开始，你可以做一个 DROP USER IF EXISTS test

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

Answer 2

回答by treat your mods well

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 recommendsadding IDENTIFIED BY; @andreb (in comments) suggests disabling NO_AUTO_CREATE_USER.

更新：@Hao 建议添加IDENTIFIED BY；@andreb（在评论中）建议禁用NO_AUTO_CREATE_USER.

Answer 3

回答by Hao

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 的回答（投票率最高的回答），在我看来，如果您在授权声明的末尾加上“identified by”，用户将被自动创建。但如果不这样做，则不会创建用户。以下代码对我有用，

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

Hope this helps.

希望这可以帮助。

Answer 4

回答by Cherian

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.

这里的用户是“test”，“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

Answer 5

回答by Sathish Neel

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 中引发任何错误，它也应该对你有用

Answer 6

回答by rajukoyilandy

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

更新：从 MySQL 5.7 开始，您可以使用DROP USER IF EXISTS语句。参考：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';

句法： DROP USER [IF EXISTS] user [, user] ...
例子： DROP USER IF EXISTS 'jeffrey'@'localhost';

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

仅供参考（对于旧版本的 MySQL），这是一个更好的解决方案......！！！

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

以下 SP 将帮助您'tempuser'@'%'通过执行删除用户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...

如果要删除所有名为'tempuser'（例如'tempuser'@'%'，'tempuser'@'localhost'和'tempuser'@'192.168.1.101'）的用户，请执行 SP likeCALL 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'@'%'

CALL DropUserIfExistsAdvanced('tempuser', '%');删除用户 'tempuser'@'%'

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

CALL DropUserIfExistsAdvanced('tempuser', '192.168.1.101');删除用户 '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')

CALL DropUserIfExistsAdvanced('tempuser', NULL);删除所有命名的用户'tempuser'（例如，说'tempuser'@'%'，'tempuser'@'localhost'和'tempuser'@'192.168.1.101'）

Answer 7

回答by BuvinJ

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 USER。由于可以将 mysql 选项设置为不通过授权自动创建用户（我使用的一个选项），我仍然不推荐这种技巧。这是来自对我有用的存储过程的片段：

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

回答by Prabhakar Undurthi

DROP USER 'user'@'localhost';

The above command will drop the user from the database, however, it is Importantto 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

上面的命令将从数据库中删除用户，但是，重要的是要知道同一个用户是否已经在使用该数据库，该会话在用户关闭该会话之前不会结束。需要注意的是，被删除的用户仍将访问数据库并执行任何操作。删除用户不会删除当前的用户会话

Answer 9

回答by j4w7

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”是数据库；这是来自这个错误。

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 10

回答by MadSeb

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.

我在 Cherian 的回答的启发下写了这个程序。不同之处在于，在我的版本中，用户名是过程的参数（而不是硬编码）。在删除用户后，我还做了一个非常必要的同花顺特权。

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-exists）

MySQL：检查用户是否存在并将其删除

提问by Cherian

采纳答案by JavierCane

回答by treat your mods well

回答by Hao

回答by Cherian

回答by Sathish Neel

回答by rajukoyilandy

回答by BuvinJ

回答by Prabhakar Undurthi

回答by j4w7

回答by MadSeb

相关推荐

最近更新

标签

MySQL：检查用户是否存在并将其删除

提问by Cherian

采纳答案by JavierCane

回答by treat your mods well

回答by Hao

回答by Cherian

回答by Sathish Neel

回答by rajukoyilandy

回答by BuvinJ

回答by Prabhakar Undurthi

回答by j4w7

回答by MadSeb

相关推荐

MySQL：存储过程中的事务

如何在 MySQL 查询中放置 IF 语句

MySQL 增加mysql导入大小

在 MySQL 中，“开销”是什么意思，它有什么不好，以及如何修复它？

相关推荐

最近更新

标签