mysql 預存程序許可權問題

標籤：mysql 預存程序許可權問題

協助開發匯入庫時，他提醒我，庫裡有預存程序的定義。我使用了--routines 的選項，可是匯入時，出了問題：

    1.提示無法建立該預存程序，原來是忘了開啟 log_bin_trust_function_creators 變數，

    set global log_bin_trust_function_creators=on; 後，成功建立，從伺服器也需要此操作

    才行，如果二進位日誌是 ROW 的話，即使從上有此預存程序，也是不會調用的。

    2.等到匯入成功後，卻又出現了無法調用的問題，搜尋了 http://www.2cto.com/database/201403/288436.html 這篇文章，記錄下此問題。

1. 建立一個 definer 為 user_admin 的預存程序

mysql> DELIMITER //mysql> CREATE DEFINER=‘user_admin‘ PROCEDURE num1()    -> BEGIN    -> SELECT COUNT(id) FROM itopserver;    -> END //Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> DELIMITER ;

2. 建立一個使用者，並賦於其 相關許可權

mysql> create user user_itop identified by ‘123456‘;Query OK, 0 rows affected (0.00 sec)mysql> grant select,insert,delete,update on itop.* to ‘user_test‘@‘%‘;Query OK, 0 rows affected (0.00 sec)mysql> show grants for [email protected]‘%‘;+---------------------------------------------------------------------+| Grants for [email protected]%                                              |+---------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘user_test‘@‘%‘                               || GRANT SELECT, INSERT, UPDATE, DELETE ON `itop`.* TO ‘user_test‘@‘%‘ |+---------------------------------------------------------------------+2 rows in set (0.00 sec)

3. 使用 user_test 串連 mysql 並執行剛才建立的 procedure

mysql> call num1;ERROR 1370 (42000): execute command denied to user ‘user_test‘@‘%‘ for routine ‘itop.num1‘

4. 這樣看是許可權的問題，因為只給了他動作表的方法。 增加其 itop 的 execute 許可權

mysql> grant execute on itop.* to [email protected]‘%‘;Query OK, 0 rows affected (0.00 sec)mysql> show grants for [email protected]‘%‘;+----------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘user_test‘@‘%‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ || GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `itop`.* TO ‘user_test‘@‘%‘                             |+----------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

5. 重新使用此使用者串連 Mysql 執行剛才定義的預存程序

mysql> use itop;mysql> call num1();ERROR 1449 (HY000): The user specified as a definer (‘user_admin‘@‘%‘) does not exist

現在可以調用該預存程序了，但是提示預存程序定義中的definer不存在，

原來僅僅是串連到MySQL伺服器的使用者具有執行預存程序的許可權是遠遠不夠的，

最終要通過預存程序定義中指定的definer來執行預存程序。

6. 說 user_admin 不存在，那就建立 user_admin 這個使用者

mysql> grant select,insert,delete,update on itop.* to ‘user_admin‘@‘%‘ identified by ‘123456‘;Query OK, 0 rows affected (0.00 sec)mysql> show grants for [email protected]‘%‘;+-----------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+-----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘user_admin‘@‘%‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ || GRANT SELECT, INSERT, UPDATE, DELETE ON `itop`.* TO ‘user_admin‘@‘%‘                                      |+-----------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

7. 重新使用 "user_test" 使用者串連 Mysql 執行剛才定義的預存程序

mysql> use itop;mysql> call num1();ERROR 1370 (42000): execute command denied to user ‘user_admin‘@‘%‘ for routine ‘itop.num1‘

8. 看來不僅僅是串連到MySQL伺服器的使用者需要具有預存程序上的執行許可權，預存程序定義者同樣需要該許可權。

mysql> grant execute on itop.* to ‘user_admin‘@‘%‘;Query OK, 0 rows affected (0.00 sec)mysql> show grants for user_admin;+-----------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+-----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘user_admin‘@‘%‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ || GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `itop`.* TO ‘user_admin‘@‘%‘                             |+-----------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

9. 重新使用 "user_test" 使用者串連 Mysql 執行剛才定義的預存程序

mysql> use itop;mysql> call num1();+-----------+| COUNT(id) |+-----------+|       372 |+-----------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

可以執行了

本文出自 “海風久遠” 部落格，請務必保留此出處http://beyondhf.blog.51cto.com/8953234/1838301

mysql 預存程序許可權問題