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上的select\insert\delete\update許可權
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> show grants for 'user_test1'@'%'; 
+-----------------------------------------------------------------------------------------------------------------------------------+
| Grants for user_test1@%                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE USER ON *.* TO 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' WITH GRANT OPTION |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE ON `configdb`.* TO 'user_test1'@'%'                                         |
+-----------------------------------------------------------------------------------------------------------------------------------+

重新使用'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'
對了，不管你是以什麼賬戶登入的MySQL，最後是使用預存程序的definer執行預存程序的，所以應當把建立使用者和賦權的許可權付給definer，這裡為user_admin'@'%'這個賬戶。

賦予'user_admin'@'%'建立使用者的許可權和賦權的許可權
mysql> grant create user on *.* to 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' with grant option;
mysql> grant  create temporary tables on configdb.* to 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9';
mysql> show grants for 'user_admin'@'%'; 
+-----------------------------------------------------------------------------------------------------------------------------------+
| Grants for user_admin@%                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE USER ON *.* TO 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' WITH GRANT OPTION |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, 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 1142 (42000): SELECT command denied to user 'user_admin'@'%' for table 'user'

哦，除了configdb庫外還得有mysql庫上user表的許可權，給加上，看來許可權問題還真是棘手，呵呵~
mysql> grant select,insert,delete,update  on mysql.* to 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9';
mysql> show grants for 'user_admin'@'%'; 
+-----------------------------------------------------------------------------------------------------------------------------------+
| Grants for user_admin@%                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE USER ON *.* TO 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' WITH GRANT OPTION |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY TABLES, EXECUTE ON `configdb`.* TO 'user_admin'@'%'                |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* 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');
Query OK, 0 rows affected (0.05 sec)

終於OK了，相信通過這一系列過程，大家應該能夠很清楚的瞭解MySQL預存程序相關的執行許可權了。另外，定義該預存程序還需要有CREATE ROUTINE的許可權、更該預存程序需要有ALTER ROUTINE的許可權（這裡是用超級使用者在configdb建立的預存程序，上述許可權都是具備的），調用預存程序的使用者需要有EXECUTE許可權，最終執行預存程序的使用者也即預存程序定義者要具備預存程序定義語句中相關的各種許可權。

MySQL的許可權分的比較細，大致可分為表許可權、列許可權、過程許可權具體可參考MySQL官方手冊。