MySQL Bugs: #26813: Super privilege required when altering a view

Bug #26813	Super privilege required when altering a view
Submitted:	3 Mar 2007 0:18	Modified:	4 Apr 2007 4:28
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.0.36	OS:	Linux (Linux/ All)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
In order to alter a view, a user must have both the 'create view' and 'drop' privileges (see http://dev.mysql.com/doc/refman/5.0/en/alter-view.html).

However if a user is trying to to alter a view that was created by another user, an error message is returned that the SUPER privilege is required. 

This is different that how we handle other privileges (such as 'drop') that allow a user to delete any view in the scope of their privilege regardless of who created it. 

How to repeat:
Log into the client as root
create user1;
create user2;
create database dbtest;
grant create view, select drop on dbtest.* to user1;
grant create view, select drop on dbtest.* to user2;
create table dbtest.t1 (a int, b int);
insert into t1 values (1,2);
insert into t1 values (3,4);

Logout and log in as user1...
create view dbtest.v1 as select a from t1;

Logout and log in as user2...
alter view dbtest.v1 as select b from t1;
>>> this is where you get the error that SUPER is required.
Notes: 1) user2 has privileges to 'create view' and 'drop' any view in testdb.
       2) issuing a 'drop view v2' by user2 will succed.

see attached test case

Suggested fix:
not require SUPER when altering views regardless of who created them.

Note that the second user can drop the view and recreate it, another reason why the SUPER privilege should not be required.

Observed on Linux (Suse 10.1) but likely to be generic

mysqltest test case

Attachment: bug26813.test (application/octet-stream, text), 1.12 KiB.

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

ChangeSet@1.2486, 2007-03-22 17:35:48+03:00, evgen@moonbone.local +3 -0
  Bug#26813: The SUPER privilege is wrongly required to alter a view created by
  another user.
  
  When the DEFINER clause isn't specified in the ALTER statement then it's loaded
  from the view definition. If the definer differs from the current user then
  the error is thrown because only a super-user can set other users as a definers.
  
  Now if the DEFINER clause is omitted in the ALTER VIEW statement then the
  definer from the original view is used without a check.

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

ChangeSet@1.2486, 2007-03-22 22:05:19+03:00, evgen@moonbone.local +3 -0
  Bug#26813: The SUPER privilege is wrongly required to alter a view created by
  another user.
  
  When the DEFINER clause isn't specified in the ALTER statement then it's loaded
  from the view definition. If the definer differs from the current user then
  the error is thrown because only a super-user can set other users as a definers.
  
  Now if the DEFINER clause is omitted in the ALTER VIEW statement then the
  definer from the original view is used without check.

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

ChangeSet@1.2495, 2007-03-22 23:13:40+03:00, evgen@moonbone.local +1 -0
  sql_view.cc:
    Post-fix for bug#26813.

Pushed in 5.0.40 and 5.1.18

Noted in 5.0.40, 5.1.18 changelogs.

ALTER VIEW requires the CREATE VIEW and DROP privileges for the view.
However, if the view was created by another user, the server
erroneously required the SUPER privilege.