(Related to, but separate from http://stackoverflow.com/questions/1502885/syntax-error-with-emulating-create-user-if-not-exists.)
Is it possible to achieve the functionality of generically/dynamically adding a user (i.e. emulating the sp_adduser system procedure included with other DBMSs) in MySQL?
MySQL doesn't support the following if [not] exists syntax, see http://bugs.mysql.com/bug.php?id=15287:
create user if not exists 'foo'@'%' identified by password 'bar';
It also doesn't support this:
drop procedure if exists create_user_if_not_exists;
delimiter ||
create procedure create_user_if_not_exists
( sUser varchar(60),
sHost varchar(16),
sPassword varchar(255) )
begin
-- ensure user does not yet exist
if (select ifnull((select 1
from mysql.user
where User = sUser
and Host = sHost), 0) = 0) then
set @createUserText = concat('create user ''', sUser, '''@''', sHost, ''' identified by ''', sPassword, ''';');
prepare createUserStatement FROM @createUserText;
execute createUserStatement;
deallocate prepare createUserStatement;
end if;
end ||
delimiter ;
because if you try to call said procedure:
call create_user_if_not_exists ( 'foo', '%', 'bar' );
you get the lovely message:
This command is not supported in the prepared statement protocol yet
The following works, but obviously is not particularly reusable:
drop procedure if exists create_user_if_not_exists;
delimiter ||
create procedure create_user_if_not_exists
( )
begin
if (select ifnull((select 1
from mysql.user
where User = 'foo'
and Host = '%'), 0) = 0) then
create user 'foo'@'%' identified by password 'bar';
end if;
end ||
delimiter ;