MySQL 預存程序執行權限涉及調用者權限與定義者權限雙重檢查，且受 SQL SECURITY 特性 (DEFINER/INVOKER) 控制。本文通過完整實戰案例，深度剖析 EXECUTE、CREATE USER、GRANT OPTION 等權限交互邏輯，解決「權限明明給了卻報錯」的經典困惑。
| 概念 | 說明 |
|---|---|
| DEFINER | 建立預存程序時指定的 'user'@'host'，預設為建立者 |
| SQL SECURITY | DEFINER (預設) 以定義者權限執行；INVOKER 以調用者權限執行 |
| 權限檢查點 | 1. 調用者需有程序 EXECUTE 權限2. 執行時 (DEFINER 模式) 需有定義者在程序內每條 SQL 所需權限 |
configdbsp_dev_test_user_add，DEFINER=`user_admin`@`%`，SQL SECURITY DEFINER (預設)mysql.user、動態建用戶、GRANT 權限、寫業務表user_test1@%GRANT SELECT,INSERT,UPDATE,DELETE ON configdb.* TO 'user_test1'@'%';
-- 執行 CALL 報錯
ERROR 1370 (42000): execute command denied to user 'user_test1'@'%' for routine 'configdb.sp_dev_test_user_add'
修正： GRANT EXECUTE ON configdb.* TO 'user_test1'@'%';
ERROR 1449 (HY000): The user specified as a definer ('user_admin'@'%') does not exist
修正： CREATE USER 'user_admin'@'%' IDENTIFIED BY 'pass';
GRANT SELECT,INSERT,UPDATE,DELETE ON configdb.* TO 'user_admin'@'%';
-- 執行報錯
ERROR 1370 (42000): execute command denied to user 'user_admin'@'%' for routine 'configdb.sp_dev_test_user_add'
關鍵認知： DEFINER 同樣需要程序的 EXECUTE 權限 (DEFINER 模式下以定義者身份執行，故需定義者有 EXECUTE)。
修正： GRANT EXECUTE ON configdb.* TO 'user_admin'@'%';
ERROR 1044 (42000): Access denied for user 'user_admin'@'%' to database 'configdb'
分析： 程序內含 CREATE USER、GRANT、CREATE TEMPORARY TABLES、寫 mysql.user 等操作，DEFINER 必須擁有對應權限。
修正 (逐步補齊)：
-- 全域建用戶與授權權限 (需 WITH GRANT OPTION)
GRANT CREATE USER ON *.* TO 'user_admin'@'%' WITH GRANT OPTION;
-- 庫級臨時表、增刪改查、執行
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE TEMPORARY TABLES,EXECUTE ON configdb.* TO 'user_admin'@'%';
-- mysql 系統庫權限 (程序查 mysql.user、mysql.proc、mysql.func)
GRANT SELECT,INSERT,UPDATE,DELETE ON mysql.* TO 'user_admin'@'%';
ERROR 1142 (42000): SELECT command denied to user 'user_admin'@'%' for table 'user'
修正： 已包含在上一步 GRANT SELECT ... ON mysql.* 中。
mysql> CALL sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','MySQL DBA');
Query OK, 0 rows affected (0.05 sec)
| 角色 | 需求權限 | 授權範例 |
|---|---|---|
| 調用者 (INVOKER) | 程序 EXECUTE | GRANT EXECUTE ON db.* TO 'caller'@'%'; |
| 定義者 (DEFINER, SQL SECURITY DEFINER) | 程序 EXECUTE + 程序內每條 SQL 所需權限 | 見上文分步授權 |
| 建立者 (CREATE ROUTINE) | 庫級 CREATE ROUTINE | GRANT CREATE ROUTINE ON db.* TO 'dev'@'%'; |
| 修改者 (ALTER ROUTINE) | 庫級 ALTER ROUTINE | GRANT ALTER ROUTINE ON db.* TO 'dev'@'%'; |
CREATE PROCEDURE ... SQL SECURITY INVOKER，權限檢查僅針對調用者，避免 DEFINER 權限維護地獄。routine_executor@% 帳號，僅授予程序所需最小權限集，嚴禁使用 root/高權限帳號。SHOW GRANTS FOR 'definer'@'%'; 核對與程序邏輯一致性。SET DEFINER 需 SET_USER_ID 權限，修改 DEFINER 更嚴格。| 錯誤碼 | 訊息 | 原因 | 解決 |
|---|---|---|---|
| 1370 | execute command denied ... for routine | 調用者或 DEFINER 缺程序 EXECUTE | GRANT EXECUTE ON db.* TO user |
| 1449 | The user specified as a definer ... does not exist | DEFINER 帳號不存在 | CREATE USER definer |
| 1044 | Access denied for user 'definer' to database | DEFINER 缺庫級業務權限 | 補授予對應庫權限 |
| 1142 | SELECT/INSERT/UPDATE command denied for table | DEFINER 缺具體表權限 | 補授予表級權限 |
徹底理解 MySQL 預存程序的雙重權限模型，是資料庫開發進階的必修課。將權限設計視為架構一環，而非事後補丁，方能構建安全、可維護的資料庫應用。
