摘要： SELECT/UPDATE/INSERT/DELETE command denied to user 'XXX'@'XXX.XXX.XXX.XXX' for table 'xxx'的错误大部分出现在应用程序中，有时用客户端登录mysql后执行操作也会遇到，后者更容易排查，前者由于涉及应用逻辑以及对象...
实为吾之愚见,望诸君酌之!闻过则喜，与君共勉
第一章 准备环境
创建数据测试数据
mysql>create database test1;
QueryOK, 1 row affected (0.00 sec)
mysql>create database test2;
QueryOK, 1 row affected (0.00 sec)
mysql>create database test3;
QueryOK, 1 row affected (0.00 sec)
mysql>create user uptest1@'%' identified by '123';
QueryOK, 0 rows affected (0.01 sec)
mysql>create user uptest2@'%' identified by '123';
QueryOK, 0 rows affected (0.00 sec)
mysql>create table test1.updatetest(a int,b int);
QueryOK, 0 rows affected (0.00 sec)
mysql>create table test2.updatetest(a int,b int);
QueryOK, 0 rows affected (0.01 sec)
mysql>create table test3.updatetest(a int,b int);
QueryOK, 0 rows affected (0.00 sec)
分别授权不同更新权限
mysql>grant update on test1.* to uptest1@'%';
QueryOK, 0 rows affected (0.00 sec)
mysql>grant update on test2.* to uptest1@'%';
QueryOK, 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 rowsin set (0.00 sec)
mysql>grant update on test2.* to uptest2@'%';
QueryOK, 0 rows affected (0.00 sec)
mysql>grant update on test1.* to uptest2@'%';
QueryOK, 0 rows affected (0.00 sec)
mysql>grant update on test3.* to uptest2@'%';
QueryOK, 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 rowsin set (0.00 sec)
创建了两个账号，3个数据库，每个数据库创建一个测试表，账号权限如下表：