**前言：**

我們在建立資料庫使用者的時候都會指定host，即一個完整的使用者可描述為 'username'@'host' 。建立使用者時不顯式指定host則預設為%，%代表所有ip段都可以使用這個使用者，我們也可以指定host為某個ip或ip段，這樣會僅允許在指定的ip主機使用該資料庫使用者。不過你也應該明白 'username'@'%' 和 'username'@'192.168.6.%' 是兩個毫無關聯的使用者，這兩個使用者可以有不同的密碼和許可權，這裡不建議建立多個同名不同host的使用者，還有不要輕易更改使用者的host，筆者曾經遇到過因為更改使用者host引發的故障，下面將其分享出來，為你講述前因後果。

#### 1.故障模擬

當時為了規範安全，將某個程式使用者的host由%改為了應用伺服器ip段，過段時間業務反饋某些功能報錯，經排查發現是因為無法呼叫儲存過程（大家可以先思考下原因），下面模擬下故障操作。

```sql
# 原有使用者、表、儲存過程模擬建立
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使用者呼叫儲存過程 呼叫正常
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使用者呼叫儲存過程 無法呼叫 出現故障
mysql> call select_students_count();
ERROR 1449 (HY000): The user specified as a definer ('testuser'@'%') does not exist
```

#### 2.故障排查與解決

其實我們手動呼叫下儲存過程後，從報錯內容明顯可以看出是因為'testuser@'%'使用者不存在的問題。因為該儲存過程的定義者是'testuser@'%'，而我們將此使用者的host改成了192.168.6.%，那麼當我們之後呼叫該儲存過程時，系統判別到此儲存過程的屬主使用者不存在，因此係統拒絕請求並丟擲異常。

當知道上述原因後，解決方法就會明朗許多，我們只需要將該儲存過程的屬主改為新的使用者即可。其實更改過使用者後，該使用者下的檢視、儲存過程、函式、觸發器、事件都會受到影響，當我們定義檢視、儲存過程、函式時使用 `DEFINER` 屬性時，若呼叫這些物件，系統會首先判別此物件的屬主使用者是否存在，不存在會直接丟擲錯誤。

此問題的解決方案有兩種，一是將此儲存過程的安全屬性由 `DEFINER` 改為 `INVOKER` ，個人不推薦這個方案，至於 `DEFINER` 和 `INVOKER` 的區別，下個章節會額外講解。二是更改此儲存過程的屬主，下面給出更改方法並加以驗證：

```sql
# 透過系統表更改儲存過程的屬主
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使用者呼叫驗證 呼叫成功
mysql> call select_students_count();
+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)
```
 
#### 3.DEFINER與INVOKER擴充知識

MySQL中，建立檢視(view)、函式(function)、儲存過程(procedure)、觸發器(trigger)、事件(event)時，可以指定安全驗證方式(也就是SQL SECURITY)屬性，其值可以為DEFINER或INVOKER，表示在執行過程中，使用誰的許可權來執行。

- DEFINER：由definer(定義者)指定的使用者的許可權來執行
- INVOKER：由呼叫這個檢視(儲存過程)的使用者的許可權來執行

預設情況下，系統指定為DEFINER。當SQL SECURITY屬性為DEFINER時，資料庫中必須存在DEFINER指定的使用者，並且該使用者擁有對應的操作許可權及引用的相關物件的許可權，才能成功執行。與當前使用者是否有許可權無關。當SQL SECURITY屬性為INVOKER時，只要執行者有執行許可權並且有引用的相關物件的許可權，就可以成功執行。

瞭解了上述知識後，可能你早已明白上述故障發生的前因後果。在日常生產中，不建議使用INVOKER屬性，因為將SQL SECURITY定義為INVOKER後，其他使用者想呼叫此物件時不僅需要有該物件的執行許可權還要有其他引用到的相關物件的許可權，極大的增加了運維複雜性。下面回顧整篇文章，整理出一下幾點個人建議，以供大家參考：

1. 不建立多個同名不同host的使用者。
1. 不要輕易更改使用者的host。
1. 更改使用者host請用RENAME USER語句，直接更新mysql.user系統表中的host屬性會使許可權丟失。
1. 更改使用者host後，要注意此使用者下的各個物件的DEFINER屬性。
1. 建立檢視、儲存過程等物件建議將SQL SECURITY定義為DEFINER。
1. 資料庫遷移時，要注意新環境存在相關物件定義的DEFINER使用者。

**總結：**

本文從一個故障出發，詳細記錄了故障發生的原因及背後涉及的知識，其實像DEFINER屬性這些細節類的東西很容易被忽視，只有遇到問題了我們才會去探究。希望本篇文章能讓你學到新東西，特別是上面總結的幾點建議都是筆者日常運維總結出的。原創不易，請大家多多支援！

![WeChat](https://img-blog.csdnimg.cn/20200410155158638.jpeg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdrdW5fag==,size_16,color_FFFFFF,t_70#pic_center)

更改使用者host留下的坑

相關文章