mysql create user if not exists

Question

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

Tomasatomasina

Answer 1

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.

Carol

Does this change the password if the user did exist and had a different password? – Alves

Nevermind - answered my own question - yes it does - but it doesn't replace the user if the Host is different – Alves

@Alves 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. – Carol

Use 'user'@'%' to create this user for all hosts. – Squalor

Yes @roundar, however that leaves security holes. Be aware. – Carol

It solves the problem, since it does create a user if it doesn't exists. It doesn't just grant permissions. – Excide

I see one problem with this answer. @Alves 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? – Cathedral

@Cathedral 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. – Alves

@Cathedral 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 ; – Alves

@Cathedral really you should create your own question referencing this one and I can give you a more complete answer. – Alves

@Alves I posted it on the DB admin StackExchange. I got a couple answers, one of which is a stored procedure. – Cathedral

@Ascherer, soon this solution might be outdated, it is deprecated as of MySQL 5.7.6. – Fallingout

@Fallingout Thanks, I've updated the post to add the >=5.7.6 method. – Carol

According to mysql 5.6 docs, this'll only work if NO_AUTO_CREATE_USER is not enabled, which is very insecure. – Despite

Its only insecure if you don't specify a password @B166ER, which.... duh. – Carol

Answer 2

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

Spermatozoid

the question is not about how to check, but how to create – Women