116

I have a query to check mysql users list for create new user.

IF (SELECT EXISTS(SELECT 1 FROM `mysql`.`user` WHERE `user` = '{{ title }}')) = 0 THEN
    CREATE USER '{{ title }}'@'localhost' IDENTIFIED BY '{{ password }}'
END IF;

But i get this error:

ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (SELECT EXISTS(SELECT 1 FROM `mysql`.`user` WHERE `user` = 'cms_localhost')) = 0 ' at line 1
Konrad Borowski
  • 11,584
  • 3
  • 57
  • 71
Mohammad Hossein Fattahizadeh
  • 2,651
  • 5
  • 35
  • 50

2 Answers2

318

In 5.7.6 and above, you should be able to use CREATE USER

CREATE USER IF NOT EXISTS 'user'@'localhost' IDENTIFIED BY 'password';

Note that the 5.7.6 method doesn't actually grant any permissions.

If you aren't using a version which has this capability (something below 5.7.6), you can do the following:

GRANT ALL ON `database`.* TO 'user'@'localhost' IDENTIFIED BY 'password';

This will create the user if it doesn't exist

Note, if you are on MySQL 8, the GRANT ALL method will not create a user.

Ascherer
  • 8,223
  • 3
  • 42
  • 60
  • 4
    Does this change the password if the user did exist and had a different password? – m3z Oct 13 '14 at 14:41
  • 5
    Nevermind - answered my own question - yes it does - but it doesn't replace the user if the Host is different – m3z Oct 13 '14 at 14:46
  • 1
    @m3z technically, if you have user who can log in on two different hosts, they are not the same user. They can have different permissions, different passwords, and everything. – Ascherer Oct 13 '14 at 21:32
  • 1
    Use `'user'@'%'` to create this user for all hosts. – roundar Feb 07 '15 at 20:50
  • 1
    Yes @roundar, however that leaves security holes. Be aware. – Ascherer Feb 07 '15 at 21:55
  • It solves the problem, since it does create a user if it doesn't exists. It doesn't just grant permissions. – Chen Jun 25 '15 at 06:33
  • I see one problem with this answer. @m3z addressed the issue. In my case this is problematic. The password is randomly generated through a bash script for me, and thus I would have to update a file with the new password everytime the script is ran. (Debian package) Does anyone have a way to improve this so it only changes the password upon the the user not existing? – Timberwolf Jun 28 '15 at 21:24
  • @Timberwolf if you can do this without changing the password then the only way would probably be to select out the existing password (or new password using an if null) into a variable before this query, then run this query using the variable as the pre hashed password. Can't remember if that's possible as I haven't used mysql for a while. – m3z Jun 28 '15 at 22:18
  • @Timberwolf I would however recommend creating a stored procedure instead and calling that to check for and then create your users. a very basic such procedure could look something like: DELIMITER // CREATE PROCEDURE addUserIfNotExisting (IN username CHAR(16), IN hostname CHAR(60)) BEGIN IF ( EXISTS(SELECT `User`, `Host` FROM `mysql`.`user` WHERE `User` = username AND `Host` = hostname) ) THEN SELECT "does exist!"; ELSE SELECT "does not exist!"; END IF; END// DELIMITER ; – m3z Jun 28 '15 at 22:19
  • @Timberwolf really you should create your own question referencing this one and I can give you a more complete answer. – m3z Jun 28 '15 at 22:20
  • @m3z I [posted it](http://dba.stackexchange.com/questions/105372/mysql-add-grant-user-if-it-does-not-exist/) on the DB admin StackExchange. I got a couple answers, one of which is a stored procedure. – Timberwolf Jun 29 '15 at 05:19
  • @Ascherer, soon this solution might be outdated, [it is deprecated as of MySQL 5.7.6](http://dev.mysql.com/doc/refman/5.7/en/grant.html). – TMMC Apr 15 '16 at 11:45
  • @TMMC Thanks, I've updated the post to add the >=5.7.6 method. – Ascherer Apr 16 '16 at 04:48
  • According to [mysql 5.6 docs](https://dev.mysql.com/doc/refman/5.6/en/grant.html#grant-account-creation), this'll only work if `NO_AUTO_CREATE_USER` is not enabled, **which is very insecure**. – Axel Nov 28 '17 at 13:45
  • 1
    Its only insecure if you don't specify a password @B166ER, which.... duh. – Ascherer Dec 04 '17 at 19:44
-4

I use

SELECT EXISTS (SELECT DISTINCT user FROM mysql.user WHERE user = "username") as is_user

should return 1 if exists or 0 if it does not

ingocnito
  • 9