更改用戶host留下的坑

前言：?

我們?cè)趧?chuàng)建數(shù)據(jù)庫(kù)用戶的時(shí)候都會(huì)指定host，即一個(gè)完整的用戶可描述為 'username'@'host' 。創(chuàng)建用戶時(shí)不顯式指定host則默認(rèn)為%，%代表所有ip段都可以使用這個(gè)用戶，我們也可以指定host為某個(gè)ip或ip段，這樣會(huì)僅允許在指定的ip主機(jī)使用該數(shù)據(jù)庫(kù)用戶。不過(guò)你也應(yīng)該明白 'username'@'%' 和 'username'@'192.168.6.%' 是兩個(gè)毫無(wú)關(guān)聯(lián)的用戶，這兩個(gè)用戶可以有不同的密碼和權(quán)限，這里不建議創(chuàng)建多個(gè)同名不同host的用戶，還有不要輕易更改用戶的host，筆者曾經(jīng)遇到過(guò)因?yàn)楦挠脩鬶ost引發(fā)的故障，下面將其分享出來(lái)，為你講述前因后果。

1.故障模擬

當(dāng)時(shí)為了規(guī)范安全，將某個(gè)程序用戶的host由%改為了應(yīng)用服務(wù)器ip段，過(guò)段時(shí)間業(yè)務(wù)反饋某些功能報(bào)錯(cuò)，經(jīng)排查發(fā)現(xiàn)是因?yàn)闊o(wú)法調(diào)用存儲(chǔ)過(guò)程（大家可以先思考下原因），下面模擬下故障操作。

# 原有用戶、表、存儲(chǔ)過(guò)程模擬創(chuàng)建
mysql> create user 'testuser'@'%' identified by '123456';
Query OK, 0 rows affected (0.04 sec)

mysql> grant select,insert,update,delete,execute on `testdb`.* to 'testuser'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'testuser'@'%';
+-------------------------------------------------------------------------------+
| Grants for testuser@%                                                         |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'                                          |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `testdb`.* TO 'testuser'@'%' |
+-------------------------------------------------------------------------------+

CREATE TABLE `students` (
 `id` int(11) NOT NULL ,
 `name` varchar(20),
 `age` int(11),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB ;
INSERT INTO `students` VALUES ('1001', 'lodd', '23');
INSERT INTO `students` VALUES ('1002', 'sdfs', '21');
INSERT INTO `students` VALUES ('1003', 'sdfsa', '24');

DROP PROCEDURE IF EXISTS select_students_count;
DELIMITER $$
CREATE DEFINER=`testuser`@`%` PROCEDURE `select_students_count`()
BEGIN
   SELECT count(id) from students;
END
$$
DELIMITER ;

# 使用testuser用戶調(diào)用存儲(chǔ)過(guò)程 調(diào)用正常
mysql> call select_students_count();
+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+

# 更改用戶host 重命名用戶
mysql> RENAME USER 'testuser'@'%' to 'testuser'@'192.168.6.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for  'testuser'@'192.168.6.%';
+---------------------------------------------------------------------------------------+
| Grants for testuser@localhost                                                         |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost'                                          |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `testdb`.* TO 'testuser'@'localhost' |
+---------------------------------------------------------------------------------------+

# 再次用testuser用戶調(diào)用存儲(chǔ)過(guò)程 無(wú)法調(diào)用 出現(xiàn)故障
mysql> call select_students_count();
ERROR 1449 (HY000): The user specified as a definer ('testuser'@'%') does not exist

2.故障排查與解決

其實(shí)我們手動(dòng)調(diào)用下存儲(chǔ)過(guò)程后，從報(bào)錯(cuò)內(nèi)容明顯可以看出是因?yàn)?testuser@'%'用戶不存在的問(wèn)題。因?yàn)樵摯鎯?chǔ)過(guò)程的定義者是'testuser@'%'，而我們將此用戶的host改成了192.168.6.%，那么當(dāng)我們之后調(diào)用該存儲(chǔ)過(guò)程時(shí)，系統(tǒng)判別到此存儲(chǔ)過(guò)程的屬主用戶不存在，因此系統(tǒng)拒絕請(qǐng)求并拋出異常。

當(dāng)知道上述原因后，解決方法就會(huì)明朗許多，我們只需要將該存儲(chǔ)過(guò)程的屬主改為新的用戶即可。其實(shí)更改過(guò)用戶后，該用戶下的視圖、存儲(chǔ)過(guò)程、函數(shù)、觸發(fā)器、事件都會(huì)受到影響，當(dāng)我們定義視圖、存儲(chǔ)過(guò)程、函數(shù)時(shí)使用 DEFINER?屬性時(shí)，若調(diào)用這些對(duì)象，系統(tǒng)會(huì)首先判別此對(duì)象的屬主用戶是否存在，不存在會(huì)直接拋出錯(cuò)誤。

此問(wèn)題的解決方案有兩種，一是將此存儲(chǔ)過(guò)程的安全屬性由 DEFINER?改為 INVOKER?，個(gè)人不推薦這個(gè)方案，至于?DEFINER?和?INVOKER?的區(qū)別，下個(gè)章節(jié)會(huì)額外講解。二是更改此存儲(chǔ)過(guò)程的屬主，下面給出更改方法并加以驗(yàn)證：

# 通過(guò)系統(tǒng)表更改存儲(chǔ)過(guò)程的屬主
mysql> update mysql.proc set definer='testuser@192.168.6.%' where db='testdb' and name='select_students_count' and type='PROCEDURE';           
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 使用testuser用戶調(diào)用驗(yàn)證 調(diào)用成功
mysql> call select_students_count();
+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

?

3.DEFINER與INVOKER拓展知識(shí)

MySQL中，創(chuàng)建視圖(view)、函數(shù)(function)、存儲(chǔ)過(guò)程(procedure)、觸發(fā)器(trigger)、事件(event)時(shí)，可以指定安全驗(yàn)證方式(也就是SQL SECURITY)屬性，其值可以為DEFINER或INVOKER，表示在執(zhí)行過(guò)程中，使用誰(shuí)的權(quán)限來(lái)執(zhí)行。

• DEFINER：由definer(定義者)指定的用戶的權(quán)限來(lái)執(zhí)行
• INVOKER：由調(diào)用這個(gè)視圖(存儲(chǔ)過(guò)程)的用戶的權(quán)限來(lái)執(zhí)行

默認(rèn)情況下，系統(tǒng)指定為DEFINER。當(dāng)SQL SECURITY屬性為DEFINER時(shí)，數(shù)據(jù)庫(kù)中必須存在DEFINER指定的用戶，并且該用戶擁有對(duì)應(yīng)的操作權(quán)限及引用的相關(guān)對(duì)象的權(quán)限，才能成功執(zhí)行。與當(dāng)前用戶是否有權(quán)限無(wú)關(guān)。當(dāng)SQL SECURITY屬性為INVOKER時(shí)，只要執(zhí)行者有執(zhí)行權(quán)限并且有引用的相關(guān)對(duì)象的權(quán)限，就可以成功執(zhí)行。

了解了上述知識(shí)后，可能你早已明白上述故障發(fā)生的前因后果。在日常生產(chǎn)中，不建議使用INVOKER屬性，因?yàn)閷QL SECURITY定義為INVOKER后，其他用戶想調(diào)用此對(duì)象時(shí)不僅需要有該對(duì)象的執(zhí)行權(quán)限還要有其他引用到的相關(guān)對(duì)象的權(quán)限，極大的增加了運(yùn)維復(fù)雜性。下面回顧整篇文章，整理出一下幾點(diǎn)個(gè)人建議，以供大家參考：

1. 不創(chuàng)建多個(gè)同名不同host的用戶。
2. 不要輕易更改用戶的host。
3. 更改用戶host請(qǐng)用RENAME USER語(yǔ)句，直接更新mysql.user系統(tǒng)表中的host屬性會(huì)使權(quán)限丟失。
4. 更改用戶host后，要注意此用戶下的各個(gè)對(duì)象的DEFINER屬性。
5. 創(chuàng)建視圖、存儲(chǔ)過(guò)程等對(duì)象建議將SQL SECURITY定義為DEFINER。
6. 數(shù)據(jù)庫(kù)遷移時(shí)，要注意新環(huán)境存在相關(guān)對(duì)象定義的DEFINER用戶。

總結(jié)：?

本文從一個(gè)故障出發(fā)，詳細(xì)記錄了故障發(fā)生的原因及背后涉及的知識(shí)，其實(shí)像DEFINER屬性這些細(xì)節(jié)類的東西很容易被忽視，只有遇到問(wèn)題了我們才會(huì)去探究。希望本篇文章能讓你學(xué)到新東西，特別是上面總結(jié)的幾點(diǎn)建議都是筆者日常運(yùn)維總結(jié)出的。原創(chuàng)不易，請(qǐng)大家多多支持！