MySQL 預存程序執行權限完整解析：DEFINER、SQL SECURITY 與權限鏈實戰

核心概念：誰在執行預存程序？

MySQL 預存程序建立時可指定 DEFINER (預設建立者) 與 SQL SECURITY (DEFINER / INVOKER) 兩個屬性，決定執行時的權限檢查依據：

• DEFINER 權限模式 (預設)：執行時檢查 DEFINER 帳號是否擁有程序內所有 SQL 所需權限，調用者僅需 EXECUTE 權限。
• INVOKER 權限模式：執行時檢查 調用者 本身權限，需擁有程序內所有操作權限。

實戰案例：層層權限遞補

場景

• 預存程序 sp_dev_test_user_add 由 user_admin@% 建立 (DEFINER)。
• 程序內執行：建立使用者、授權、建立暫存表、寫入業務表。
• 一般帳號 user_test1@% 僅有 configdb 的 CRUD + EXECUTE。

錯誤演進與修復軌跡

權限授予最小化原則腳本

-- 1. 建立 DEFINER 高權限帳號 (僅供預存程序使用，不給開發者)
CREATE USER 'sp_definer'@'%' IDENTIFIED BY 'StrongPass!23';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE
  ON `configdb`.* TO 'sp_definer'@'%';
GRANT CREATE USER ON *.* TO 'sp_definer'@'%' WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO 'sp_definer'@'%';

-- 2. 建立預存程序指定 DEFINER
CREATE DEFINER=`sp_definer`@`%` PROCEDURE `sp_dev_test_user_add`(...)
SQL SECURITY DEFINER  -- 明確指定，預設即此模式
BEGIN ... END;

-- 3. 給應用帳號最小權限
CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppPass!23';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
  ON `configdb`.* TO 'app_user'@'%';
-- 無需給 CREATE USER、mysql.* 等高危權限

關鍵權限對照表

最佳實踐總結

1. 專用 DEFINER：建立專屬高權限帳號 sp_definer，密碼複雜、不對外開放登入。
2. SQL SECURITY DEFINER：預存程序預設模式，封裝高危操作，應用帳號僅需 EXECUTE。
3. 最小權限原則：應用帳號絕不給 CREATE USER、GRANT OPTION、mysql.* 權限。
4. 審計與輪換：定期檢視 mysql.proc、information_schema.ROUTINES，輪換 DEFINER 密碼。
5. 避免動態 SQL 注入：PREPARE 參數化，嚴禁字串拼接 SQL。