MySQL中以用户执行存储过程的权限为EXECUTE
比如我们在名为configdb的
数据库下创建了如下存储过程,存储过程的定义者为user_admin
use configdb;
drop procedure if exists sp_dev_test_user_add;
delimiter $$
CREATE DEFINER=`user_admin`@`%` PROCEDURE `sp_dev_test_user_add`(
in var_user varchar(30),
in var_ip varchar(15),
in var_username varchar(30),
in var_email varchar(30),
in var_orginfo varchar(30)
)
BEGIN
create temporary table errors (error varchar(500));
if exists ( select user from
mysql.user where user=var_user) then
insert into errors values (concat('用户名 "',var_user,'" 已存在!'));
end if;
if exists (select * from errors) then
select error from errors;
else
set @user=concat(var_user,'@\'',var_ip,'\'');
set @s=concat('create user ',@user,' identified by ''12345'';');
prepare cmd from @s;
execute cmd;
set @s=concat('GRANT SELECT ON `mysql`.`func` TO ',@user,';');
prepare cmd from @s;
execute cmd;
set @s=concat('GRANT SELECT ON `mysql`.`proc` TO ',@user,';');
prepare cmd from @s;
execute cmd;
replace into dev_test_userinfo values (var_user,var_username,var_email,var_orginfo);
end if;
drop temporary table errors;
END
$$
delimiter ;
试着创建一个普通用户user_test1
mysql>create user user_test1 identified by '12345';
查看其权限
mysql>show grants for user_test1;
+-----------------------------------------------------------------------------------------------------------+
| Grants for user_test1@% |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |
+-----------------------------------------------------------------------------------------------------------+
赋予其configdb上的select\insert\delete\update权限
mysql>grant select,insert,delete,update on configdb.* to 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9'
mysql> show grants for user_test1;
+-----------------------------------------------------------------------------------------------------------+
| Grants for user_test1@% |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `configdb`.* TO 'user_test1'@'%' |
+-----------------------------------------------------------------------------------------------------------+
使用此用户登录MySQL执行刚才定义的存储过程
mysql>use configdb;
mysql>call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','MySQL DBA');
ERROR 1370 (42000): execute command denied to user 'user_test1'@'%' for routine 'configdb.sp_dev_test_user_add'
看来是权限不足，继续赋予其configdb上的execute权限
mysql> grant execute on configdb.* to 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9';
mysql> show grants for user_test1;
+-----------------------------------------------------------------------------------------------------------+
| Grants for user_test1@% |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_test1'@'%' IDENTIFIED BY PASSWOR |