由于MYSQL中给PROCEDURE 赋比如EXECUTE权限的时候不能像其他表级或则库级别的可以用通配符来代替。
只能一个一个单独赋权限。所以我写了一个批量的。
数据库:test
用户名:test_user
主机: localhost
权限: execute
调用:call sp_grant_privileges_on_routine('test','test_user','localhost','execute');
如果有什么意见可以讨论。
只能一个一个单独赋权限。所以我写了一个批量的。
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_privileges varchar(255))
BEGIN
declare i int default 0;
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_privileges varchar(255))
BEGIN
declare i int default 0;
-- Get the total of the procedure.
set @stmt = concat('select count(1) from mysql.proc where db = ''',f_db,''' into @cnt');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = concat('select count(1) from mysql.proc where db = ''',f_db,''' into @cnt');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = '';
while i < @cnt do
while i < @cnt do
-- Grant privilege one by one.
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 = '';
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;
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = '';
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;
flush privileges;
END$$
DELIMITER ;
测试end while;
flush privileges;
END$$
DELIMITER ;
数据库:test
用户名:test_user
主机: localhost
权限: execute
调用:call sp_grant_privileges_on_routine('test','test_user','localhost','execute');
如果有什么意见可以讨论。