MySQL Bugs: #30468: column level privileges not respected when joining tables

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

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:
.

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'

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.

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.

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

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.