MySQL Check if the user exists and drop it

Question

There   s not standard way to check if a MySQL user exists and based on that drop it  Are there any workarounds for this     Edit   I need a straight way to run this without throwing up an error e g   DROP USER test localhost

User · Answer

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 )

User · Answer

Update  As of MySQL 5 7 you can use DROP USER IF EXISTS statement  Ref  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    FYI  and for older version of MySQL   this is a better solution       The following SP will help you to remove user  tempuser      by executing 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    Now please have a look on mentioned 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  quot DROP USER  quot    quot   quot   MyUserName   quot     quot   MyHostName   quot   quot   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  quot DROP USER  quot    quot   quot   mUser   quot     quot   mHost   quot   quot   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    192 168 1 101    to remove user  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

User · Answer

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  gt  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

User · Answer

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   GRANT USAGE ON     TO  username   localhost  IDENTIFIED BY  password   DROP USER  username   localhost     Hope this helps

User · Answer

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

User · Answer

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  Updates   Hao recommends adding IDENTIFIED BY   andreb  in comments  suggests disabling NO AUTO CREATE USER

User · Answer

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

User · Answer

DROP USER IF EXISTS  user   localhost      that works for me without throwing any errors in Maria DB  it should work for u too

User · Answer

Found the answer to this from one of the MySQL forums  We   ll need to use a procedure to delete the user     User here is    test    and    databaseName    the database name    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

User · Answer

Since MySQL 5 7 you can do a DROP USER IF EXISTS test  More info  http   dev mysql com doc refman 5 7 en drop-user html

User · Answer

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

User · Answer

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     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   DECLARE userCount INT DEFAULT 0  SELECT COUNT    INTO userCount FROM mysql user WHERE User   userName AND Host  localhost   IF userCount  gt  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

User · Answer

in terminal do   sudo mysql -u root -p   enter the password   select user from mysql user    now delete the user  the username   DROP USER the unername    replace  the username  with the user that you want to delete

User · Answer

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

[mysql] MySQL: Check if the user exists and drop it

Examples related to mysql

Examples related to database