在我原来的基础上增加了些内容。避免原来的主键冲突，还有支持单个存储过程的赋权。
DELIMITER $$
DROP PROCEDURE IF EXISTS `mysql`.`sp_grant_privileges_on_routine`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_grant_privileges_on_routine`(
IN f_db varchar(255), IN f_username varchar(255),IN f_host varchar(255),
IN f_routine_name varchar(255), IN f_privileges varchar(255))
BEGIN
-- Mod by david yeung 20080123.
-- Grant privileges on routine.
declare i int default 0;
-- To change the definer.
set @definer_field = concat(f_username,'@',f_host);
-- To determinate whether the procedure 's name supply or not.
if char_length(f_routine_name) = 0 then
-- Delete all the privileges on specific user.
set @stmt = concat('delete from mysql.procs_priv where db=''',f_db,'''');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = '';
-- Get the routine number from exact database.
set @stmt = concat('select count(1) from mysql.proc where db = ''',f_db,''' into @cnt');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
while i < @cnt do
-- Get the routine name from exact database.
set @stmt = concat('select `name` from mysql.proc where db = ''',f_db,''' and type = 2 limit ',i,',1 into @name');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = '';
-- Add new data to privilege table.
set @stmt = concat('insert into mysql.procs_priv values (''',f_host,''',''',f_db,''',''',f_username,''',''',@name,''',2,''',user(),''',''',f_privileges,''',''',now(),''')');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = '';
set i = i + 1;
end while;
-- Change all the definer to the same user.
set @stmt = concat('update mysql.proc set definer = ''',@definer_field,''' where db = ''',f_db,'''');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
else
-- Remove the old routine privilege.
set @stmt = concat('delete from mysql.procs_priv where db = ''',f_db,''' and routine_name = ''',f_routine_name,'''');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
-- To grant individual routine.
set @stmt = concat('grant ',f_privileges,' on procedure ',f_db,'.',f_routine_name,' to ''',f_username,'''@''',f_host,'''');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = '';
-- Change all the definer to the same user.
set @stmt = concat('update mysql.proc set definer = ''',@definer_field,''' where db = ''',f_db,''' and `name` = ''',f_routine_name,'''');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
end if;
-- Refresh privilege table.
flush privileges;
END$$
DELIMITER ;
调用方法
mysql> call sp_grant_privileges_on_routine('test','test_user','%','','execute');
Query OK, 0 rows affected (0.02 sec)
mysql> show grants for test_user@'%';
+---------------------------------------------------------------------------------------+
| Grants for test_user@% |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_insert_partition_data` TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_grant_privileges_on_routine` TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_insert_batch` TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_test_sleep` TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_check_security` TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_alter_engine` TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_insert_temp` TO 'test_user'@'%' |
+---------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> call sp_grant_privileges_on_routine('test','root','%','sp_test_sleep','execute');
Query OK, 0 rows affected (0.02 sec)
mysql> show grants for test_user@'%';
+---------------------------------------------------------------------------------------+
| Grants for test_user@% |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_insert_partition_data` TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_grant_privileges_on_routine` TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_insert_batch` TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_check_security` TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_alter_engine` TO 'test_user'@'%' |
| GRANT EXECUTE ON PROCEDURE `test`.`sp_insert_temp` TO 'test_user'@'%' |
+---------------------------------------------------------------------------------------+
7 rows in set (0.01 sec)
mysql> show grants for root@'%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------+
| GRANT EXECUTE ON PROCEDURE `test`.`sp_test_sleep` TO 'root'@'%' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)