应用中抛出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种原因

简介: SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx'的错误大部分出现在应用程序中,有时用客户端登录mysql后执行操作也会遇到,后者更容易排查,前者由于涉及应用逻辑以及对象.

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


第一章 准备环境

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

902fce50b942680ebd84cfce6f0a5a192eeec9a2

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)


分别授权不同更新权限

fa36cbb13a8d19bf580a1d7bb6709e9042b83637

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)

 

5e1ee077d6155e41b609641a4188f765bf72e7db

 

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

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

bee662f6d1682619f5e2c118af93d3a961d6a7ed

抓包数据如下:

79f5af7d39a527f9b624463ff15f4bf603150653

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

3e15798800d488dea1ca17808413a576a043f670

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

ea217c91890207dff58cf54f9122d113558dfa9b

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

 

 

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

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

如下测试截图:

8b24cbf8bcd1f48c406406f7d38b473ea2ae5a70

对应的抓包截图如下:

2bd3b99758b34c09d57b078148dca1b76e841763

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

ac704efaac998f1bf3050a9a8a411e124c214c50

 

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

该问题可能需要满足:

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

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

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


ba01892cb88e35a12f43879eb3e8e29b4749c8a9

66a6a8380a721bc73009a3a01b19282ffeee0395

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

9543b7ca02575ae9853bd7f8317bf453b60b2846

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

54c61a9b2ea186f102757c495d8941f206621e85

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

350875f0a9f522ad62daf5fd5146dfa6abfdda27

8a97d7a8a94c4b6a01aa235a5a5f5e995290b9ae


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

8eb1185ee2f8e7d18bb30fd9b6237468f67f698d

8a97d7a8a94c4b6a01aa235a5a5f5e995290b9ae


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

9d12ac319fcdc205423168ff2390e288e6233ee9

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


0ad19125e15daa7abb79d03fba30c7c4b7b6e574

44737479905daf81ea4abb8afd566c563f746891

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


 90db8f2c880f86292962c1e84fdc55b97cdccca3


 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:

分享阿里云支持与服务团队最佳实践、经典案例与故障排查。

官方博客
文档