I want to execute a CREATE USER statement, but only when the user doesn't already exist.
What would be the best way of doing this?
2 Answers
If you're creating a user, you need to create a grant too. The grant implicitly creates a user if it doesn't exist (which is why you are encouraged to include the password when creating a grant, just in case they don't exist). see http://dev.mysql.com/doc/refman/5.1/en/grant.html
So an option is to just create the grant (with the password) and the user is implicitly created.
E.g:
GRANT ALL PRIVILEGES ON db_name.*
TO 'user'@'%' IDENTIFIED BY 'password'
WITH GRANT OPTION;
7 Comments
ihavprobs
Simple and straightforward approach.
Tim Diggins
@iMysak have you tested this in 5.5 / 5.6 and it's not working - that would be odd, as I haven't found a note that this has been dropped (in fact doc seem to imply implicit creation - check dev.mysql.com/doc/refman/5.5/en/grant.html)
iMysak
in my case I needed to enable mode_no_auto_create_user dev.mysql.com/doc/refman/5.5/en/… which is was enabled by default in old versions, but are disabled by default in new (5.5, 5.6).
Tim Diggins
Oh, ok - I think that setting only comes to force if you don't supply an authentication clause, ie.
"IDENTIFIED BY 'password'"
Leonmax
When I did this, here is the resonse I got:
0 row(s) affected, 1 warning(s): 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. !? |
You can select from the "user" table on the default mysql database. Like this:
select *
from user
where User = 'username'
If no results are returned, create a new user.
1 Comment
Confusion
The problem with that is that by the time your 'create' command is issued, the user may have been created after all. The risk of that happening is far from vanishingly small, if you are creating a web application that suddenly becomes popular.
GRANT?