应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

实为吾之愚见,望诸君酌之!闻过则喜,与君共勉 


第一章 准备环境

创建数据测试数据,以下测试多是基于自建mysql进行

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

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)


分别授权不同更新权限

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

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)

 

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

 

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:数据库不存在或者数据库错误(权限不足)

表面现象是数据库不存在或更新中使用的数据库名称错误,数据库名称错误属于应用代码层面的逻辑错误导致,根本原因是权限不足导致,简单测试如下:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

抓包数据如下:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

在该测试中,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账号登录测试:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

通过上面的截图可以看到,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数据库不存在的表:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

因为update1@'%'有test1数据库的更新权限,更新不存在的表时,提示的是ERROR 1146 (42S02): Table 'test1.updatetest1' doesn't exist

 

 

3.2 可能原因2:对象存在,账号权限不足测试

这是指所用账号的权限不足,本身不支持更新这个对象,这也是比较常见的原因

如下测试截图:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

对应的抓包截图如下:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

在该测试中,uptest1@’%’只有test1和test2的更新权限,所以更新test1和test2是正常的,但是更新test3是异常的,当使用有权限的uptest2@’%’时,更新正常:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

 

3.3 可能原因3:非高权限账号的授权失效(可能性较低)

该问题可能需要满足:

1,rds for mysql是非高权限账号

2,数据库名字中有下划线,且show grants for出现\的情况

测试rds实例的数据库和授权如下:


应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

如上截图,在控制台创建了mh_test_db和test-2为名称的数据库,授权给了账号mh_test_rds使用mh_test_rds登录后,执行show grants for mh_test_rds@’%’,结果如下:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

如上图,下划线的数据库名称前面加了反斜线,下面尝试更新一个表,进行测试:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

更新测试正常,长达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的数据库的对象时,正常情况下是不会出错的,如下:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因


即使更新出错,比如更新test2里面的对象,错误提示会是如下情况:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因


错误提示是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数据库出现的错误变成下面这种错误时就很奇怪了:

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

如上的现象,同样是使用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,如下(由于无法复现,下面的报文只是模拟问题发生的类似报文):


应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因

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的抛错了,不会出现本篇描述的错误,如下:


 应用中抛出SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx' 的5种原因


 

上一篇:数据分析2017系统系统设计架构师的通过人数与通过率


下一篇:Centos上安装 OpenNebula Management Console