Cry MySQL How to...

Create user if not exist

---

The simplest way to create a user is with the GUI tools, but if you want to script it then that isn't an option so the next simplest is to use:

create user brian@localhost identified by 'his_password';

and should you wish to later remove the user it is simply:

drop user brian@localhost;

However, creating a user in this way (using create user ...) will fail if the user already exists:

Error Code: 1396. Operation CREATE USER failed for 'brian'@'localhost'

What would be nice would be a "create user if not exists" much like you can do a "create table if not exists". Unfortunately MySQL doesn't let you do this.

There are a few ways round this.

One is to create a stored procedure which can query to determine whether the user exists and create the account if not.

Another is to drop the user and recreate. This might be viable in some scenarios, but I do not like this approach.

The approach I prefer is to use grant - because grant will create the user if the account does not already exist. So:

grant select on myDB.* to brian@localhost identified by 'his_password';

will create the account "brian@localhost" if that account does not already exist and grant select to that account for all tables in the "myDB" database.

If you are writing scripts then using the grant approach is the most elegant.

One gotcha to be aware of: If "NO_AUTO_CREATE_USER" is set then grant will not create new users.

---

These notes have been tested against MySQL 5, and may apply to other versions as well.