如何通用/动态创建/删除用户
(相关,但独立于模拟“创建用户”的语法错误如果不存在”。)
是否可以在MySQL中实现通用/动态添加用户的功能(即模拟其他DBMS中包含的sp_adduser
系统过程)?
MySQL 不支持以下 if [not] contains
语法,请参阅 http://bugs.mysql.com/bug.php?id=15287:
create user if not exists 'foo'@'%' identified by password 'bar';
它也不支持这一点:
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 ;
因为如果您尝试调用所述过程:
call create_user_if_not_exists ( 'foo', '%', 'bar' );
您会收到可爱的消息:
准备好的语句协议尚不支持此命令
下面的方法可以工作,但显然不是特别可重用:
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 ;
(Related to, but separate from 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 ;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
哦,抱歉,我刚刚注意到你正在谈论数据库用户。不是应用程序用户。
您可能喜欢
INSERT INTO ...... ON DUPLICATE KEY UPDATE ......=VALUE(.....)
语句。我在我的一般保存对象方法中使用它,使用它我不必关心用户是否存在,在我提交后它会在那里(并且是最新的)。
Oh sorry i've just twigged you are talking about db users. not application users.
You might like the
INSERT INTO ...... ON DUPLICATE KEY UPDATE ......=VALUE(.....)
statement.I use that in my general saving object method, using this i don't have to care if the user exists or not it'll be in there (and up-to-date) after i commit.