Bug #7391 Cross-database multi-table UPDATE uses active database privileges
Submitted: 17 Dec 2004 19:10 Modified: 3 Aug 2006 17:13
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.20 forward OS:Microsoft Windows (Windows 2000 SP 4)
Assigned to: Sergey Vojtovich CPU Architecture:Any

[17 Dec 2004 19:10] Dean Ellis
Description:
With particular privilegs, cross-database multi-table UPDATE statements can allow a user with all privileges on active database to update tables for which user has no UPDATE privileges in another database.

How to repeat:
CREATE DATABASE testsecure;
CREATE DATABASE testuser;
GRANT ALL ON testuser.* TO testuser@localhost;
GRANT SELECT ON *.* TO testuser@localhost;
CREATE TABLE testsecure.t1 SELECT 1 a;
CREATE TABLE testsecure.t2 SELECT 1 b;

as testuser:

USE testsecure;
UPDATE testsecure.t1, testsecure.t2 SET a=100,b=200;
USE testuser;
UPDATE testsecure.t1, testsecure.t2 SET a=100,b=200;

as root:

REVOKE SELECT ON *.* FROM testuser@localhost;

as testuser:

USE testsecure;
UPDATE testsecure.t1, testsecure.t2 SET a=100,b=200;
USE testuser;
UPDATE testsecure.t1, testsecure.t2 SET a=100,b=200;

Suggested fix:
n/a
[18 Dec 2004 17:41] Antony Curtis
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix committed to 4.0 and 4.1 source repositories.
[28 Jun 2006 12:53] Sergey Vojtovich
Matthew, I wasn't able to repeat this problem with provided data set on linux with current 4.1.21 and 4.1.20:
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connection root;
CREATE DATABASE testsecure;
CREATE DATABASE testuser;
GRANT ALL ON testuser.* TO testuser@localhost;
GRANT SELECT ON *.* TO testuser@localhost;
CREATE TABLE testsecure.t1 SELECT 1 a;
CREATE TABLE testsecure.t2 SELECT 1 b;

connect (testuser,localhost,testuser,,test,$MASTER_MYPORT,$MASTER_MYSOCK);

connection testuser;
USE testsecure;
--error 1143
UPDATE testsecure.t1, testsecure.t2 SET a=100,b=200;
USE testuser;
--error 1044
UPDATE testsecure.t1, testsecure.t2 SET a=100,b=200;

connection root;
REVOKE SELECT ON *.* FROM testuser@localhost;

connection testuser;
USE testsecure;
--error 1143
UPDATE testsecure.t1, testsecure.t2 SET a=100,b=200;
USE testuser;
--error 1044
UPDATE testsecure.t1, testsecure.t2 SET a=100,b=200;

Could you confirm that I use correct test case?
[3 Jul 2006 15:03] Miguel Solorzano
I was unable to repeat on Windows XP with 4.0.27 released version.
[13 Jul 2006 15:44] Dean Ellis
Verified, using the original test case, against 4.1.21 on Linux:

mysql> use testsecure;
Database changed
mysql> update testsecure.t1, testsecure.t2 set a=100,b=200;
ERROR 1143 (42000): UPDATE command denied to user 'testuser'@'localhost' for column 'a' in table 't1'
mysql> use testuser;
Database changed
mysql> update testsecure.t1, testsecure.t2 set a=100,b=200;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
[13 Jul 2006 19:20] Dean Ellis
The bug requires some privilege (it does not have to be related to the user(s) int he test) in tables_priv.

Add one to t/grant.test and you will see this in action:

--- grant.test  2006-07-13 14:18:18.000000000 -0500
+++ /home/dellis/grant.test     2006-07-13 14:20:05.000000000 -0500
@@ -313,6 +313,9 @@
 create table mysqltest_2.t1 select 1 c, 2 s;
 create table mysqltest_2.t2 select 1 d, 2 t;
 
+#dummy table privilege to trigger the bug
+grant select on mysql.host to nobody@localhost;
+
 #test the column privileges
 grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost;
 grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost;
[27 Jul 2006 14:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9669
[3 Aug 2006 8:44] Sergey Vojtovich
Patch was approved by Serg.
[3 Aug 2006 9:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9995

ChangeSet@1.2529, 2006-08-03 14:03:08+05:00, svoj@may.pils.ru +3 -0
  BUG#7391 - Cross-database multi-table UPDATE uses active database
             privileges
  
  This problem is 4.1 specific. It doesn't affect 4.0 and was fixed
  in 5.x before.
  
  Having any mysql user who is allowed to issue multi table update
  statement and any column/table grants, allows this user to update
  any table on a server (mysql grant tables are not exception).
  
  check_grant() accepts number of tables (in table list) to be checked
  in 5-th param. While checking grants for multi table update, number
  of tables must be 1. It must never be 0 (actually we have
  DBUG_ASSERT(number > 0) in 5.x in grant_check() function).
[3 Aug 2006 11:37] Sergey Vojtovich
Fixed in 4.1.22. Setting bug status to documenting since no other mysql version is affected.
[3 Aug 2006 17:13] Paul Dubois
Noted in 4.1.22 changelog.
[11 Aug 2006 9:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10301

ChangeSet@1.2250, 2006-08-11 14:41:07+05:00, svoj@may.pils.ru +2 -0
  Extended a test case for bug#7391.