MySQL Bugs: #41354: Access control is bypassed when all columns of a view are selected by * wildcard

Bug #41354	Access control is bypassed when all columns of a view are selected by * wildcard
Submitted:	10 Dec 2008 12:25	Modified:	18 Mar 2009 14:53
Reporter:	Marcel Brouillet	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.0.44-, 5.0 bzr	OS:	Linux (5.0.44-log Gentoo Linux mysql-5.0.44-r2 on linux 2.6.21.5-grsec-xxxx-grs-ipv4-32 )
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	Security definer, select privileges, Views

Description:
When a user has limited rights to columns of a view, these limits are bypassed when the select uses the * wildcard.

Select * from myview
lists all columns of the view while

Select unallowed_column from myview
raises (as expected) the error 1143
ERROR 1143 (42000): SELECT command denied to user 'xxx'@'zzz' for column 'unallowed_column' in table 'myview'

How to repeat:
<as root>
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `myview` AS select ....

<as unprivileged user xxx@zzz>
mysql> show grants for xxx@zzz;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for xxx@zzz                                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xxx'@'zzz' IDENTIFIED BY PASSWORD '......'
| GRANT SELECT (availability) ON `mybd`.`myview` TO 'xxx'@'zzz'                                                                         
| [...user has not right on other columns of myview, nor has it on all the columns that compose the view (hence the SECURITY DEFINER in the view definition)...]

mysql> select * from myview limit 1;
[... full set of data ...]
1 row in set (0.03 sec)

mysql> select page from myview limit 1;
ERROR 1143 (42000): SELECT command denied to user 'xxx'@'zzz' for column 'page' in table 'myview'

mysql> select availability from myview limit 1;
+--------------+
| availability |
+--------------+
| available    | 
+--------------+
1 row in set (0.03 sec)

Thank you for the report.

Verified as described.

Bug is only repeatable with version 5.0.

Test case:

create database db1;
use db1;

create table t1(f1 int, f2 int);
create view v1 as select f1, f2 from t1;

grant select (f1) on db1.t1 to foo@localhost;
grant select (f1) on db1.v1 to foo@localhost;

connect (addconfoo, localhost, foo,,);
connection addconfoo;

use db1;

select f1 from t1;
--error 1142, 1143
select f2 from t1;
--error 1142, 1143
select * from t1;

select f1 from v1;
--error 1142, 1143
select f2 from v1;
--error 1142, 1143
select * from v1;

Fixed by bug#36086

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/67503

2761 Georgi Kodinov	2009-02-25
      Bug #41354: Access control is bypassed when all columns 
      of a view are selected by * wildcard
      
      Backported a part of the fix for 36086 to 5.0
     @ mysql-test/r/view_grant.result
        Bug #41354: test case
     @ mysql-test/t/view_grant.test
        Bug #41354: test case
     @ sql/sql_base.cc
        Bug #41354: backported the check in bug 36086 to 5.0

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/67675

2761 Georgi Kodinov	2009-02-26
      Bug #41354: Access control is bypassed when all columns 
      of a view are selected by * wildcard
      
      Backported a part of the fix for 36086 to 5.0
     @ mysql-test/r/view_grant.result
        Bug #41354: test case
     @ mysql-test/t/view_grant.test
        Bug #41354: test case
     @ sql/sql_acl.cc
        Bug #41354: return table error when no access and *
     @ sql/sql_base.cc
        Bug #41354: backported the check in bug 36086 to 5.0

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/67781

2768 Georgi Kodinov	2009-02-27
      addendum to the fix for bug #41354: fixed the error returned by SELECT *

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/67784

2767 Georgi Kodinov	2009-02-26
      Bug #41354: Access control is bypassed when all columns 
      of a view are selected by * wildcard
      
      Backported a part of the fix for 36086 to 5.0
     @ mysql-test/r/view_grant.result
        Bug #41354: test case
     @ mysql-test/t/view_grant.test
        Bug #41354: test case
     @ sql/sql_acl.cc
        Bug #41354: return table error when no access and *
     @ sql/sql_base.cc
        Bug #41354: backported the check in bug 36086 to 5.0

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/67786

2825 Georgi Kodinov	2009-02-26 [merge]
      null-merged the fix and kept the test for bug #41354 from 5.0-bugteam

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/67787

2767 Georgi Kodinov	2009-02-26
      Bug #41354: Access control is bypassed when all columns 
      of a view are selected by * wildcard
      
      Backported a part of the fix for 36086 to 5.0
     @ mysql-test/r/view_grant.result
        Bug #41354: test case
     @ mysql-test/t/view_grant.test
        Bug #41354: test case
     @ sql/sql_acl.cc
        Bug #41354: return table error when no access and *
     @ sql/sql_base.cc
        Bug #41354: backported the check in bug 36086 to 5.0

Pushed into 5.0.79 (revid:joro@sun.com-20090309135922-a0di9ebkxoj4d4wv) (version source revid:staale.smedseng@sun.com-20090227160758-td4jot2la75f9zy1) (merge vers: 5.0.79) (pib:6)

Noted in 5.0.79 changelog.

Use of SELECT * allowed users with rights to only some columns of a
view to access all columns.  

Setting report to NDI pending push into 5.1.x/6.0.x.

Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:staale.smedseng@sun.com-20090227160332-3k1kc0rao6y07cbp) (merge vers: 5.1.33) (pib:6)

Noted in 5.1.33 changelog.

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:patrick.crews@sun.com-20090227093122-zzn4eiwz9m2k6c9j) (merge vers: 6.0.10-alpha) (pib:6)

Noted in 6.0.11 changelog.

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)