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

Question

目前没有标准方法可以检查MySQL用户是否存在并删除它。有没有任何解决方法？

编辑：我需要一种直接的方式来运行这个操作，而不会出现错误
例如：

DROP USER test@localhost; :    

- Cherian

---

你是指MySQL用户吗？还是指你在MySQL表中存储的用户记录？ - Jason Cohen

看起来MariaDB有这个功能：https://mariadb.com/kb/en/mariadb/drop-user/ - lmat - Reinstate Monica

对于未来的读者：MySQL 5.7具有“DROP USER IF EXISTS”功能，请参见https://dev59.com/L3RB5IYBdhLWcg3wiHll - Andrei Epure is hiring

@AndreiEpure 如果您可以将此作为答案放置，我会将其标记为正确答案。 - Cherian

Answer 1

这对我起作用：

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

如果该用户不存在，这将创建该用户（并授予其一项无害特权），然后无论如何都会删除该用户。在此处找到解决方案：http://bugs.mysql.com/bug.php?id=19166

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

- treat your mods well

---

在MySQL授权手册页面中，实际上写着：USAGE：表示“无权限”的同义词。 - stivlo

这已不再是一种选择。:( MySQL当前版本不再为GRANT语句创建新用户，这是他们添加的“特性”。;) 看起来，Cherian的选项是目前唯一可用的选项。 - GazB

@Zasurus。http://dev.mysql.com/doc/refman/5.1/en/grant.html（还有5.5和5.6）似乎表明您错了。存在“no auto user create”选项。除非我误解了手册，否则它表明授权将创建一个用户（如果不存在）。 - andreb

@andreb 或许在我尝试时，“NO_AUTO_CREATE_USER”选项是开启的，或者默认情况下是开启的（即我没有更改它）。如果我有时间，我会尝试关闭此选项并再次尝试。这个选项有一些未解决的bug报告，也许其中一个就是问题所在。:) - GazB

请参考@Hao的答案。我需要使用IDENTIFIED BY才能使其正常工作。 - Matthew

Answer 2

自MySQL 5.7版本起，您可以执行DROP USER IF EXISTS test操作。

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

- JavierCane

Answer 3

对于phyzome的答案（最高赞的那个），在授予语句的末尾加上"identified by"，用户将会被自动创建。但如果没有加上，用户将不会被创建。以下代码适用于我：

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

希望这有所帮助。

- Hao

---

我有一些旧脚本，在5.6上可以运行，但在5.7上却不行，因为它们没有包含“IDENTIFIED BY 'password'”这一部分。加上这个部分后，问题得到了解决。 - joensson

Answer 4

在MySQL论坛上找到了解决方案。我们需要使用一个过程来删除用户。

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

- Cherian

---

感谢@Cherian，我不确定这是否仍然是2017年最好的做事方式，但至少这是一个前进的方向！我无法相信这仍然如此困难... - JMac

只是提醒一下，您应该始终包含源URL（例如：从其中一个mysql表单中找到答案。或者从其中一个mysql表单中获取源代码）。 - user8803833

Answer 5

更新

从MySQL 5.7开始，您可以直接使用DROP USER IF EXISTS语句。 https://dev.mysql.com/doc/refman/5.7/en/drop-user.html

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

提供信息（对于旧版本的MySQL），这是更好的解决方案...!!!

以下存储过程将帮助您通过执行CALL DropUserIfExistsAdvanced('tempuser', '%');来删除用户'tempuser'@'%'

如果您想要删除所有名为'tempuser'的用户（例如'tempuser'@'%'，'tempuser'@'localhost'和'tempuser'@'192.168.1.101'），请执行类似于CALL DropUserIfExistsAdvanced('tempuser', NULL);的存储过程。这将删除所有名为tempuser的用户！真的...

现在请查看上述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 ;

用法：

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

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

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

- rajukoyilandy

Answer 6

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

如果这个在Maria DB中可以正常工作而不会出现任何错误，那么它也应该适用于你。

- Sathish Neel

---

在MySQL 5.7及以上版本上可以实现。 ...我很幸运，我们这里使用的最新版本是5.5。 - tetsujin

Answer 7

我受Cherian答案的启发编写了这个过程。不同之处在于，我的版本中用户名是过程的参数（而不是硬编码）。在删除用户后，我还进行了非常必要的FLUSH PRIVILEGES操作。

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 ;

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

- MadSeb

Answer 8

嗯...为什么要这么复杂和技巧性呢？

与其使用DROP USER...，你可以直接从mysql.user表中删除用户（如果用户不存在，则不会抛出错误），然后刷新权限以应用更改。

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

-- 更新 --

我错了。这样删除用户是不安全的。你确实需要使用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;

- BuvinJ

---

请分享一下为什么它“不安全”？对谁来说以何种方式是不安全的？ - dagelf

既然我发布了这个问题已经一年多了，我不会声称100％记得当时我在指什么。 我相当肯定我想说的是它不能可靠地完全从系统中删除用户。 我认为DROP USER完成的任务不仅仅是从mysql.user表中删除一行。 抱歉，我现在无法想起那些细节是什么！ - BuvinJ

如果我猜的话，我会说权限记录没有被删除，而是变成了“孤儿”？（取决于您的设置？） - BuvinJ

Answer 9

DROP USER 'user'@'localhost';

上述命令将从数据库中删除该用户，但是，重要的是要知道如果相同的用户已经在使用数据库，则该会话将一直存在，直到用户关闭该会话。需要注意的是，删除的用户仍然可以访问数据库并执行任何操作。 删除用户不会终止当前用户会话

- Prabhakar

Answer 10

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

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

这是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

- j4w7