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上的selectinsertdeleteupdate权限
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 password '*00a51f3f48415c7d4e8908980d443c29c69b60c9' |
| grant select, insert, update, delete, execute 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 1449 (hy000): the user specified as a definer ('user_admin'@'%') does not exist
这次可以调用该存储过程了，但是提示存储过程定义中的definer不存在，原来仅仅是连接到mysql服务器的用户具有执行存储过程的权限是远远不够的，最终要通过存储过程定义中指定的definer来执行存储过程。
创建user_admin'@'%'这个用户，并赋予configdb上相应的权限
mysql>create user user_admin identified by '12345';
mysql> grant select,insert,delete,update on configdb.* to 'user_admin'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9';
mysql> show grants for user_admin;
+-----------------------------------------------------------------------------------------------------------+
| grants for user_admin@% |
+-----------------------------------------------------------------------------------------------------------+
| grant usage on *.* to 'user_admin'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9' |
| grant select, insert, update, delete on `configdb`.* to 'user_admin'@'%' |
+-----------------------------------------------------------------------------------------------------------+
重新使用'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_admin'@'%' for routine 'configdb.sp_dev_test_user_add'
看来不仅仅是连接到mysql服务器的用户需要具有存储过程上的执行权限，存储过程定义者同样需要该权限。
mysql> grant execute on configdb.* to 'user_admin'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9';
mysql> show grants for user_admin;
+-----------------------------------------------------------------------------------------------------------+
| grants for user_admin@% |
+-----------------------------------------------------------------------------------------------------------+
| grant usage on *.* to 'user_admin'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9' |
| grant select, insert, update, delete, execute on `configdb`.* to 'user_admin'@'%' |
+-----------------------------------------------------------------------------------------------------------+
重新使用'user_test1'@'%'用户登录mysql执行刚才定义的存储过程
mysql>use configdb;
mysql> call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','mysql dba');
error 1044 (42000): access denied for user 'user_admin'@'%' to database 'configdb'
可以执行存储过程了，但是提示权限不足，仔细查看存储过程的定义可以看到，存储过程中包含创建用户和赋予权限的语句，而我们赋给'user_test1'@'%'用户和'user_admin'@'%'都不具有这样的权限。
赋予'user_test1'@'%'创建用户的权限和赋权的权限，以及创建临时表的权限
mysql> grant create user on *.* to 'user_test1'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9' with grant option;
mysql> grant create temporary tables on configdb.* to 'user_test1'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9';
mysql> show grants for 'user_test1'@'%';
+-----------------------------------------------------------------------------------------------------------------------------------+
| grants for user_test1@% |
+-----------------------------------------------------------------------------------------------------------------------------------+
| grant create user on *.* to 'user_test1'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9' with grant option |
| grant select, insert, update, delete, create temporary tables, execute on `configdb`.* to 'user_test1'@'%' |
+-----------------------------------------------------------------------------------------------------------------------------------+
重新使用'user_test1'@'%'用户登录mysql执行刚才定义的存储过程
mysql>use configdb;
mysql> call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','mysql dba');
error 1044 (42000): access denied for user 'user_admin'@'%' to database 'configdb'
对了，不管你是以什么账户登录的mysql，最后是使用存储过程的definer执行存储过程的，所以应当把创建用户和赋权的权限付给definer，这里为user_admin'@'%'这个账户。
赋予'user_admin'@'%'创建用户的权限和赋权的权限
mysql> grant create user on *.* to 'user_admin'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9' with grant option;
mysql> grant create temporary tables on configdb.* to 'user_admin'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9';
mysql> show grants for 'user_admin'@'%';
+-----------------------------------------------------------------------------------------------------------------------------------+
| grants for user_admin@% |
+-----------------------------------------------------------------------------------------------------------------------------------+
| grant create user on *.* to 'user_admin'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9' with grant option |
| grant select, insert, update, delete, create temporary tables, execute on `configdb`.* to 'user_admin'@'%' |
+-----------------------------------------------------------------------------------------------------------------------------------+
重新使用'user_test1'@'%'用户登录mysql执行刚才定义的存储过程
mysql>use configdb;
mysql> call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','mysql dba');
error 1142 (42000): select command denied to user 'user_admin'@'%' for table 'user'
哦，除了configdb库外还得有mysql库上user表的权限，给加上，看来权限问题还真是棘手，呵呵~
mysql> grant select,insert,delete,update on mysql.* to 'user_admin'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9';
mysql> show grants for 'user_admin'@'%';
+-----------------------------------------------------------------------------------------------------------------------------------+
| grants for user_admin@% |
+-----------------------------------------------------------------------------------------------------------------------------------+
| grant create user on *.* to 'user_admin'@'%' identified by password '*00a51f3f48415c7d4e8908980d443c29c69b60c9' with grant option |
| grant select, insert, update, delete, create, create temporary tables, execute on `configdb`.* to 'user_admin'@'%' |
| grant select, insert, update, delete on `mysql`.* to 'user_admin'@'%' |
+-----------------------------------------------------------------------------------------------------------------------------------+
重新使用'user_test1'@'%'用户登录mysql执行刚才定义的存储过程
mysql>use configdb;
mysql> call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','mysql dba');
query ok, 0 rows affected (0.05 sec)