689
技術社區[雲棲]
應用中拋出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的
實為吾之愚見,望諸君酌之!聞過則喜，與君共勉
第一章 準備環境
創建數據測試數據
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> create database test3;
Query OK, 1 row affected (0.00 sec)
mysql> create user uptest1@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> create user uptest2@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> create table test1.updatetest(a int,b int);
Query OK, 0 rows affected (0.00 sec)
mysql> create table test2.updatetest(a int,b int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table test3.updatetest(a int,b int);
Query OK, 0 rows affected (0.00 sec)
分別授權不同更新權限
mysql> grant update on test1.* to uptest1@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant update on test2.* to uptest1@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for uptest1@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for uptest1@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'uptest1'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT UPDATE ON "test1".* TO 'uptest1'@'%' |
| GRANT UPDATE ON "test2".* TO 'uptest1'@'%' |
+--------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> grant update on test2.* to uptest2@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant update on test1.* to uptest2@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant update on test3.* to uptest2@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for uptest2@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for uptest2@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'uptest2'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT UPDATE ON "test1".* TO 'uptest2'@'%' |
| GRANT UPDATE ON "test2".* TO 'uptest2'@'%' |
| GRANT UPDATE ON "test3".* TO 'uptest2'@'%' |
+--------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
創建了兩個賬號，3個數據庫，每個數據庫創建一個測試表，賬號權限如下表：
|
|
test1 |
test2 |
test3 |
|
uptest1@’%’ |
update |
update |
null |
|
uptest2@’%’ |
update |
update |
update |
第二章 現象描述
SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx'的錯誤大部分出現在應用程序中，有時用客戶端登錄mysql後執行操作也會遇到，後者更容易排查，前者由於涉及應用邏輯以及對象權限等，會比較難排除
第三章 問題分析
其中前半部分描述主要是應用邏輯問題導致的該現象，也是98%的可能的原因，後半部分說明某些及其特別的情況也會導致，占比很低
3.1 可能原因1:數據庫不存在或者數據庫錯誤（權限不足）
表麵現象是數據庫不存在或更新中使用的數據庫名稱錯誤，數據庫名稱錯誤屬於應用代碼層麵的邏輯錯誤導致，根本原因是權限不足導致，簡單測試如下：
抓包數據如下：
在該測試中，uptest1@’%’隻有test1和test2的更新權限，但是更新test4時，由於test4不存在會出現ERROR 1142 (42000): UPDATE command denied to user 'uptest1'@'10.26.254.217' for table 'updatetest'，這裏需要說明下，沒有出現Table 'XXXX' doesn't exist的錯誤提示，下麵用super賬號登錄測試：
通過上麵的截圖可以看到，super賬號沒有出現ERROR 1142 (42000): UPDATE command denied to user 'uptest1'@'10.26.254.217' for table 'updatetest'，而是出現了ERROR 1146 (42S02): Table 'test4.updatetest' doesn't exist，可以大概的判斷出來：因為uptest1@’%’隻有有限的權限，所以它無法判斷test4數據庫是否存在，也可以認為當一個賬號有辨別對象是否存在的權限時，才會提示Table 'XXXX' doesn't exist，如下，使用uptest1@‘%’來更新test1數據庫不存在的表：
因為update1@'%'有test1數據庫的更新權限，更新不存在的表時，提示的是ERROR 1146 (42S02): Table 'test1.updatetest1' doesn't exist
3.2 可能原因2:對象存在，賬號權限不足測試
這是指所用賬號的權限不足，本身不支持更新這個對象，這也是比較常見的原因
如下測試截圖：
對應的抓包截圖如下：
在該測試中，uptest1@’%’隻有test1和test2的更新權限，所以更新test1和test2是正常的，但是更新test3是異常的,當使用有權限的uptest2@’%’時，更新正常：
3.3 可能原因3:非高權限賬號的授權失效（可能性較低）
該問題可能需要滿足：
1，rds for mysql是非高權限賬號
2，數據庫名字中有下劃線，且show grants for出現\的情況
測試rds實例的數據庫和授權如下：
如上截圖，在控製台創建了mh_test_db和test-2為名稱的數據庫，授權給了賬號mh_test_rds，使用mh_test_rds登錄後，執行show grants for mh_test_rds@’%’，結果如下：
如上圖，下劃線的數據庫名稱前麵加了反斜線，下麵嚐試更新一個表，進行測試：
更新測試正常,長達40次測試也未見異常，都沒有出現UPDATE command denied類的錯誤，正常情況下，不會出現本文描述的問題，在特定情況下可能會出現偶發性的update command類問題，需要重新授權才可解決，概率較低
3.4 可能原因4:賬號認證混淆（可能性很低）
這種原因可能性極低，也是出現在特定條件下的
創建兩個賬號，分別進行授權，如下：
mysql> create user uptest3@'10.26.254.217' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> create user uptest3@'172.29.25.21' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant update on test1.* to uptest3@'10.26.254.217';
Query OK, 0 rows affected (0.00 sec)
mysql> grant update on test2.* to uptest3@'172.29.25.21';
Query OK, 0 rows affected (0.00 sec)
當使用uptest3@'10.26.254.217'賬號去更新test1的數據庫的對象時，正常情況下是不會出錯的，如下：
即使更新出錯，比如更新test2裏麵的對象，錯誤提示會是如下情況：
錯誤提示是ERROR 1142 (42000): UPDATE command denied to user 'uptest3'@'10.26.254.217' for table 'updatetest'
這類的拋錯也是正常的（後麵提示的賬號是'uptest3'@'10.26.254.217'，其中ip是client的來源ip，select user()可見），因為是正常的權限問題導致，但是當更新test1數據庫出現的錯誤變成下麵這種錯誤時就很奇怪了：
如上的現象，同樣是使用uptest3@'10.26.254.217'登錄更新test1的表，本來是沒有錯誤的，卻莫名其妙的出現了：
ERROR 1142 (42000): UPDATE command denied to user 'uptest3'@'172.29.25.21' for table 'updatetest'的錯誤，並且提示的user是'uptest3'@'172.29.25.21'（來源ip並不是172.29.25.21），而不是'uptest3'@'10.26.254.217'，從抓包看，是相同的tcp flow，如下(由於無法複現，下麵的報文隻是模擬問題發生的類似報文)：
5.6.16-log.<E..`-^q?&?K...!...............<*[n7cp)yH<2.mysql_native_password.............!.......................uptest3...5c.........h[`.@[.Cmysql_native_password.o._os.linux-glibc2.5._client_name.libmysql._pid.29870._client_version.5.6.35 _platform.x86_64.program_name.mysql...........!....select @@version_comment limit 1.....'....def....@@version_comment..!.9.......................Source distribution......... ....update test1.updatetest set a=2W....v.#42000UPDATE command denied to user 'uptest3'@'172.29.25.21' for table 'updatetest'.....N...
簡單描述下現象即：應用在主機10.26.254.217上，使用uptest2的用戶通過內網連接到了rds
，然後執行update test1.updatetest set a=2的操作，在權限正常的情況下，出現了UPDATE command denied to user 'uptest3'@'172.29.25.21' for table 'updatetest'的錯誤，即不是UPDATE command denied to user 'uptest3'@'10.26.254.217' for table 'updatetest'，也不是更新成功，所以這類情況是比較奇怪且幾率極低的
3.5 可能原因5：rds for mysql實例鎖定
有很多情況下，實例空間滿導致的鎖定，以及實例過期導致的鎖定會出現這種情況，所以此處也把這類原因列出來，通過測試無法複現，目前實例鎖定都會出現ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement的拋錯了，不會出現本篇描述的錯誤，如下：
最後更新：2017-11-28 14:34:35