作者：手辨
實為吾之愚見,望諸君酌之!聞過則喜，與君共勉
第一章 準備環境
建立資料測試資料，以下測試多是基于自建mysql進行
mysql>
create database test1;
Query
OK, 1 row affected (0.00 sec)
create database test2;
create database test3;
create user uptest1@'%' identified by '123';
OK, 0 rows affected (0.01 sec)
create user uptest2@'%' identified by '123';
OK, 0 rows affected (0.00 sec)
create table test1.updatetest(a int,b int);
create table test2.updatetest(a int,b int);
create table test3.updatetest(a int,b int);
分别授權不同更新權限
grant update on test1.* to uptest1@'%';
grant update on test2.* to uptest1@'%';
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)
grant update on test2.* to uptest2@'%';
grant update on test1.* to uptest2@'%';
grant update on test3.* to uptest2@'%';
show grants for uptest2@'%';
Grants for uptest2@%
|
GRANT USAGE ON . TO 'uptest2'@'%' IDENTIFIED BY PASSWORD
GRANT UPDATE ON "test1".* TO 'uptest2'@'%'
GRANT UPDATE ON "test2".* TO 'uptest2'@'%'
GRANT UPDATE ON "test3".* TO 'uptest2'@'%' |
4 rows
建立了兩個賬号，3個資料庫，每個資料庫建立一個測試表，賬号權限如下表：
| test1 | test2 | test3 | |
| uptest1@’%’ | update | null | |
| uptest2@’%’ |
第二章 現象描述
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:賬号認證混淆（可能性很低）
這種原因可能性極低，也是出現在特定條件下的
建立兩個賬号，分别進行授權，如下：
create user uptest3@'10.26.254.217' identified by '123';
create user uptest3@'172.29.25.21' identified by '123';
grant update on test1.* to uptest3@'10.26.254.217';
grant update on test2.* to uptest3@'172.29.25.21';
當使用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的抛錯了，不會出現本篇描述的錯誤，如下：