批量给存储过程赋权限的存储过程
来源:互联网 发布:乐视电视如何设置网络 编辑:程序博客网 时间:2024/04/30 15:03
批量给存储过程赋权限的存储过程(第二版)
转载:http://blog.chinaunix.net/u/29134/showart_469805.html
转载:http://blog.chinaunix.net/u/29134/showart_469805.html
在我原来的基础上增加了些内容。避免原来的主键冲突,还有支持单个存储过程的赋权。
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)
- 批量给存储过程赋权限的存储过程
- GRANT 给存储过程赋权限
- Oracle 使用GRANT 给存储过程赋权限方法
- 在存储过程中查询dba_tables表的时候,需要赋权限给该用户
- sql server 创建用户角色,给角色赋存储过程执行权限, 将角色赋预某用户
- 批量删除存储过程的存储过程
- 存储过程,把查询结果赋给变量
- 批量删除的存储过程
- oracle 存储过程给另一个用户的权限问题
- mysql给函数,存储过程权限的问题
- oracle 存储过程给另一个用户的权限问题
- 给所有存储过程加所有人权限的一个存储过程
- 存储过程的执行权限
- 存储过程的权限问题
- 批量导出存储过程
- 批量导出存储过程
- 存储过程批量执行
- 存储过程批量更新
- 一生要做的五十件事(五)
- RedHat linux inittab详解
- 算法与数据结构上机经典算法(1)
- 别拿一分钱不当回事
- 成功将主站和dnt3.0论坛整合
- 批量给存储过程赋权限的存储过程
- 搭建一个简单的Python的Web环境
- Understanding the PureMVC Open Source Framework1
- JavaScript核心参考教程--RegExp对象
- Makefile.win
- Servlet的生命周期1
- 构造函数、析构函数是否要声明为虚函数的问题(网络转载)
- DLL+ ActiveX控件+WEB页面调用例子
- Java Servlet技术