MySQL用戶執行存儲過程的權限

 　　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&