Bug #30468 column level privileges not respected when joining tables
Submitted: 17 Aug 2007 8:35 Modified: 5 Nov 2007 2:55
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S1 (Critical)
Version:5.0.48 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any

[17 Aug 2007 8:35] Shane Bester
Description:
when joining a table using select * from ... join .. using(col), all columns are returned even if a user didn't have access to those columns.

see output from testcase: first 2 are ok. third select reveals too much data!

mysql> select `c3` from `db1_private`.`t2`;
ERROR 1143 (42000): SELECT command denied to user 'user1'@'127.0.0.1' for column 'c3' in table 't2'
mysql> select * from `db1_private`.`t2`;
ERROR 1143 (42000): SELECT command denied to user 'user1'@'127.0.0.1' for column 'c3' in table 't2'
mysql> select * from `db1`.`t1` join `db1_private`.`t2` using (`c2`);
+------+------+-----------+
| c2   | c1   | c3        |
+------+------+-----------+
|    1 |  111 | private!! |
+------+------+-----------+
1 row in set (0.00 sec)

How to repeat:
mysql -uroot 

drop database if exists `db1`;
drop database if exists `db1_private`;
create database `db1`;
create database `db1_private`;

CREATE TABLE `db1`.`t1` (`c1` int,`c2` int) ENGINE=myisam;
insert into `db1`.`t1`(`c2`,`c1`) values (1,111);

CREATE TABLE `db1_private`.`t2` (`c2` int,`c3` varchar(100)) ENGINE=myisam;
insert into `db1_private`.`t2`(`c2`,`c3`) values (1,'private!!');

flush privileges;
drop user 'user1'@'%';
GRANT USAGE ON *.* TO 'user1'@'%' IDENTIFIED BY '12345';
GRANT SELECT ON `db1`.* TO 'user1'@'%';
GRANT SELECT (`c2`) ON `db1_private`.`t2` TO 'user1'@'%';
show grants for 'user1'@'%';
exit;

mysql -uuser1 -p12345
select user(),current_user();
select `c3` from `db1_private`.`t2`;
select * from `db1_private`.`t2`;
select * from `db1`.`t1` join `db1_private`.`t2` using (`c2`);

Suggested fix:
.
[17 Aug 2007 8:38] Shane Bester
rewriting join like this seems to be fine.

mysql> select * from `db1`.`t1`,`db1_private`.`t2` where `t1`.`c2`=`t2`.`c2`;
ERROR 1143 (42000): SELECT command denied to user 'user1'@'127.0.0.1' for column 'c3' in table 't2'
[30 Aug 2007 11:47] 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/33432

ChangeSet@1.2511, 2007-08-30 14:42:26+03:00, gkodinov@magare.gmz +6 -0
  Bug #30468: column level privileges not respected when joining tables
  
  When expanding a * in a USING/NATURAL join the check for table access
  for both tables in the join was done using the grant information of the
  first one.
  Fixed by getting the correct grant information while iterating through
  the columns of the join.
[22 Sep 2007 13:20] 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/34488

ChangeSet@1.2511, 2007-09-22 16:19:01+03:00, gkodinov@magare.gmz +6 -0
  Bug #30468: column level privileges not respected when joining tables
  
  When expanding a * in a USING/NATURAL join the check for table access
  for both tables in the join was done using the grant information of the
  first one.
  Fixed by getting the grant information for the current table while 
  iterating through the columns of the join.
[29 Oct 2007 8:43] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:46] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:50] Bugs System
Pushed into 6.0.4-alpha
[5 Nov 2007 2:55] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

When expanding a * in a USING or NATURAL join, the check for table
access for both tables in the join was done using only the grant
information of the first table.