關于MySQL删除或重命名使用者對自定義對象的影響

目錄

• ​​1. 背景介紹​​
• ​​2. 資料庫自定義對象​​
• ​​3. 自定義對象的SECURITY_TYPE屬性​​
• ​​4. 修改使用者定義後如何確定對象有效性​​
• ​​5. 資料庫自定義對象的最佳實踐​​
• ​​6. 8.0.22對rename和drop user的增加校驗​​
• ​​7. 參考連結​​

1. 背景介紹

最近在做資料遷移過程中遇到一個MySQL使用者相關的遷移問題,涉及到2個機房之間,由于2個機房的網段不一樣,但資料庫中使用者定義對可通路的IP網段做了明确限定,​

​如,[email protected].%,當遷移到目标網段為200.1開頭時,會導緻使用者無法連接配接資料庫​

​,通常我們的做法可以有以下2種

1. 建立一個相同權限及密碼的使用者,但IP網段限制為目标端的IP段或直接用%不做限制
2. 對該使用者用rename user的方式做重命名,将使用者改為目标端的IP段或改為%不做限制

以上2種方式的都可以,個人會更傾向于使用第2種方式,減少備援使用者,但其中還有一些情況需要做考慮,那就是資料庫中的自定義對象,這裡我們把由使用者建立的存儲過程,觸發器,視圖,事件統稱為資料庫自定義對象,我們知道,​

​在通過drop user删除一個使用者時,該使用者的自定義對象并不會一起删除,依舊保留在資料庫中,或者使用rename user對使用者重命名時,自定義對象也不會一并修改​

​,這對于我們需要調用自定義對象時,很容易觸發權限不足或自定義對象建立者不存在的錯誤.

下面我們具體讨論一下,MySQL中自定義對象涉及的​

​建立者,調用者,以及權限之間的關系​

​,以便我們在做資料遷移或對使用者做變更時,能一并對相關的資料庫對象做調整.

2. 資料庫自定義對象

首先我們來看看資料庫自定義對象(​

​存儲過程,函數,事件,觸發器,視圖​

​)的定義可以通過哪些表檢視到,以下是對應的5種資料庫自定義對象的中繼資料對應表,其中部分定義在mysql庫和information_schema有重疊

• information_schema.EVENTS

  - mysql.event

• information_schema.ROUTINES

  - mysql.proc

• information_schema.TRIGGERS
• information_schema.VIEWS

3. 自定義對象的SECURITY_TYPE屬性

在我們建立自定義對象時都有一個屬性SECURITY_TYPE可定義,從字面意思我們可以猜到其目的是安全相關,也就是做鑒權的處理,該屬性有2個可選值,分别為​

​DEFINER和INVOKER​

​,下面我們分别介紹其各自值的作用

​

​設定為DEFINER​

​​表示的是,當某使用者對該存儲過程有execute權限,在調用時,我們判斷的是該存儲過程​

​建立者​

​是否有execute及相關的庫表的通路權限,如果沒有則報錯,缺一不可

• 也就是在SECURITY_TYPE為DEFINER時,如果該存儲過程的所屬使用者被删除了,那這個存儲過程即被辨別為失效/孤兒的存儲對象(資料庫中也叫做Orphan Stored Objects)

​

​設定為INVOKER​

​​表示的是,當某使用者對該存儲過程有execute權限,在調用時,我們判斷的是​

​調用者​

​是否對該存儲過程中涉及的相關庫表的通路權限,如果沒有則報錯,缺一不可

• 也就是在SECURITY_TYPE為INVOKER的情況下,即使該存儲過程的所屬使用者被删除了,隻要調用者有該存儲過程的執行權限及對應庫表的權限,依舊可以正常使用

4. 修改使用者定義後如何確定對象有效性

對于​

​存儲過程,函數,事件​

​這3種,由于這類定義在mysql庫中存在

• 對definer屬性我們可以直接更新definer字段的值來修改
• 對SECURITY_TYPE屬性,我們則可以直接用​

  ​alter procedure​

  ​文法來修改

update mysql.proc set definer='user_a@%' where definer='[email protected].%';
update mysql.event set definer='user_a@%' where definer='[email protected].%';

alter procedure employees.p_select SQL SECURITY INVOKER;      

對于​

​觸發器,視圖​

​​,由于mysql庫中沒存儲,而information_schema庫為特殊的記憶體臨時資料庫,無法做DDL操作,雖然MySQL提供了​

​ALTER VIEW​

​文法,實際就是對其做重建處理,我們可以通過cancat将需要修改的視圖的定義拼接出來,修改其DEFINER重新執行即可,觸發器由于涉及的定義較多,可考慮拼接,也可以直接查詢觸發器定義後手工執行

select concat("alter DEFINER=`user_a`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where definer='[email protected].%';      

5. 資料庫自定義對象的最佳實踐

• 能不用就不用
• 在建立時​

  ​明确對DEFINER屬性做定義​

  ​,盡量保證DEFINER定義的使用者不會被修改或删除
• 在建立時​

  ​優先選擇SQL SECURITY為INVOKER​

  ​​,以便DEFINER定義的使用者被删除也不會影響對象的調用(可能會報權限不足,但不會報使用者不存在),且能​

  ​更明确的控制​

  ​調用者是否有權限對資料庫中的表操作

​

​ps:假設root使用者建立一個存儲過程包含drop database/drop table的操作,且SQL SECURITY設定為DEFINER,隻要普通使用者有該存儲過程的執行權限,即使沒有drop database和drop table的權限,則也可觸發相關操作,帶來潛在的風險​

​

6. 8.0.22對rename和drop user的增加校驗

在對使用者做rename或drop user時,如果該使用者存在存儲過程等自定義對象,會在執行完成後提示warning

mysql> rename user user_a to user_b;
Query OK, 0 rows affected, 1 warning (0.0060 sec)
Warning (code 4005): User 'user_a'@'%' is referenced as a definer account in a stored routine      

7. 參考連結

• ​​https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html#stored-objects-security-sql-security​​
• ​​http://blog.itpub.net/27067062/viewspace-2130598/​​
• ​​https://www.bbsmax.com/A/QV5ZEGqwJy/​​