MySQL : 사용자가 있는지 확인하고 삭제하십시오.

MySQL : 사용자가 있는지 확인하고 삭제하십시오.

---

MySQL 사용자가 존재하는지 확인하는 표준 방법은 없으며이를 기반으로 삭제합니다. 이에 대한 해결 방법이 있습니까?

편집 :
예를 들어 오류를 던지지 않고 이것을 실행하는 직접적인 방법이 필요합니다.

DROP USER test@localhost; :    

---

MySQL 5.7부터는 DROP USER IF EXISTS test

추가 정보 : http://dev.mysql.com/doc/refman/5.7/en/drop-user.html

---

이것은 나를 위해 일했습니다.

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.

---

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

---

phyzome의 답변 (가장 많이 득표 한 답변)에 대해, 승인 명세서 끝에 "식별자"를 입력하면 사용자가 자동으로 생성되는 것 같습니다. 그러나 그렇지 않으면 사용자가 생성되지 않습니다. 다음 코드는 저에게 효과적입니다.

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

도움이 되었기를 바랍니다.

---

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

Maria DB에서 오류를 일으키지 않고 나를 위해 작동합니다.

---

업데이트 : MySQL 5.7부터는 DROP USER IF EXISTS문 을 사용할 수 있습니다 . 참조 : https://dev.mysql.com/doc/refman/5.7/en/drop-user.html

통사론: DROP USER [IF EXISTS] user [, user] ...

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

참고로 (그리고 이전 버전의 MySQL의 경우) 더 나은 솔루션입니다 ... !!!

다음 SP는 다음 'tempuser'@'%'을 실행하여 사용자를 제거하는 데 도움이됩니다.CALL DropUserIfExistsAdvanced('tempuser', '%');

이름이 지정된 모든 사용자 'tempuser'(예 'tempuser'@'%': 'tempuser'@'localhost'및 'tempuser'@'192.168.1.101') 를 제거 하려면 SP를 실행합니다. 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')

---

음 ... 왜 모든 합병증과 속임수?

대신 DROP USER를 사용합니다 ... mysql.user 테이블에서 사용자를 삭제 한 다음 (사용자가 존재하지 않는 경우 오류가 발생하지 않음) 권한을 플러시하여 변경 사항을 적용 할 수 있습니다.

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

-업데이트-

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;

---

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

---

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

---

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

---

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;

---

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.

참고URL : https://stackoverflow.com/questions/598190/mysql-check-if-the-user-exists-and-drop-it