您好,登錄后才能下訂單哦!
前言:?
我們?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),為你講述前因后果。
當(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
其實(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)
?
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í)行。
默認(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è)人建議,以供大家參考:
總結(jié):?
本文從一個(gè)故障出發(fā),詳細(xì)記錄了故障發(fā)生的原因及背后涉及的知識(shí),其實(shí)像DEFINER屬性這些細(xì)節(jié)類的東西很容易被忽視,只有遇到問(wèn)題了我們才會(huì)去探究。希望本篇文章能讓你學(xué)到新東西,特別是上面總結(jié)的幾點(diǎn)建議都是筆者日常運(yùn)維總結(jié)出的。原創(chuàng)不易,請(qǐng)大家多多支持!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。