Description:
A user can have the update(column) privilege for a view.
This privilege can exist and can be used, with no need for the user to have a select privilege for that column.
However in order to grant that privilege the user must (temporarily) have a select privilege. This is even if the grantor is the super user.
The ability to grant a privilege should only depend on the privileges of the grantor, not on the privileges of the grantee. (Especially if the required privilege has no influence on the usability of the new privilege)
From the doc (http://dev.mysql.com/doc/refman/5.1/en/grant.html):
> To use GRANT, you must have the GRANT OPTION privilege,
> and you must have the privileges that you are granting.
The Super user has both.
This only applies to column privileges on views.
This can not be reproduced with tables (as there is no "sql security definer"), nor does it happen with unlimited (not limited to column) privileges.
Since it works for view-wide privileges it is also inconsistent
How to repeat:
# Login as user root (or superuser)
create database db1; # "test" may have anonymous privileges
use db1;
drop table if exists t1; drop view if exists v1;
create table t1 (a int, b INT);
insert into t1 values (1,10),(2,20),(3,30);
drop user u1; # only if exists
create user u1;
grant create view on db1.* to u1;
grant update(a) on db1.t1 to u1;
# Login as u1
use db1;
create ALGORITHM=MERGE sql security definer view db1.v1 as select a from t1;
# the update will fail,
# because the user does not yet have privileges on the view
update v1 set a =1;
# Login as root
grant update(a) on db1.v1 to u1;
# ERROR 1356 (HY000):
# View 'db1.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
#BUT
update v1 set a = 3; # works
# superuser has the update privilege and the grant option
grant select(a) on db1.t1 to u1; # grant to table (temporarily)
grant update(a) on db1.v1 to u1; # now works
revoke select(a) on db1.t1 from u1; # revoke from table
# Login as u1
update v1 set a = 3; #works
---
So the privileges still works. The select(a) on t1 was only needed to create the grant. It is not needed to use the grant.
Suggested fix:
-
