[原创]批量给存储过程赋权限的存储过程(第二版)

在我原来的基础上增加了些内容。避免原来的主键冲突，还有支持单个存储过程的赋权。


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)