1 files changed, 401 insertions, 0 deletions
diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test
new file mode 100644
index 00000000000..2fe7a420e66
--- /dev/null
+++ b/mysql-test/t/view_grant.test
@@ -0,0 +1,401 @@
+
+# simple test of grants
+grant create view on test.* to test@localhost;
+show grants for test@localhost;
+revoke create view on test.* from test@localhost;
+show grants for test@localhost;
+
+# grant create view test
+#
+connect (root,localhost,root,,test);
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int);
+create table mysqltest.t2 (a int, b int);
+
+grant select on mysqltest.t1 to mysqltest_1@localhost;
+grant create view,select on test.* to mysqltest_1@localhost;
+
+connect (user1,localhost,mysqltest_1,,test);
+connection user1;
+
+create view v1 as select * from mysqltest.t1;
+# try to modify view without DROP privilege on it
+-- error 1142
+alter view v1 as select * from mysqltest.t1;
+-- error 1142
+create or replace view v1 as select * from mysqltest.t1;
+# no CRETE VIEW privilege
+-- error 1142
+create view mysqltest.v2  as select * from mysqltest.t1;
+# no SELECT privilege
+-- error 1142
+create view v2 as select * from mysqltest.t2;
+
+connection root;
+grant create view,drop,select on test.* to mysqltest_1@localhost;
+
+connection user1;
+# following 'use' command is workaround of bug #9582 and should be removed
+# when that bug will be fixed
+use test;
+alter view v1 as select * from mysqltest.t1;
+create or replace view v1 as select * from mysqltest.t1;
+
+connection root;
+revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
+revoke all privileges on test.* from mysqltest_1@localhost;
+
+drop database mysqltest;
+drop view test.v1;
+
+#
+# grants per columns
+#
+# MERGE algorithm
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int);
+create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
+grant select (c) on mysqltest.v1 to mysqltest_1@localhost;
+
+connection user1;
+select c from mysqltest.v1;
+# there are no privileges on column 'd'
+-- error 1143
+select d from mysqltest.v1;
+
+connection root;
+revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
+delete from mysql.user where user='mysqltest_1';
+drop database mysqltest;
+
+# TEMPORARY TABLE algorithm
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int);
+create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
+grant select (c) on mysqltest.v1 to mysqltest_1@localhost;
+
+connection user1;
+select c from mysqltest.v1;
+# there are no privileges on column 'd'
+-- error 1143
+select d from mysqltest.v1;
+
+connection root;
+revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
+delete from mysql.user where user='mysqltest_1';
+drop database mysqltest;
+
+#
+# EXPLAIN rights
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+#prepare views and tables
+create table mysqltest.t1 (a int, b int);
+create table mysqltest.t2 (a int, b int);
+create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
+create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1;
+create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2;
+create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2;
+grant select on mysqltest.v1 to mysqltest_1@localhost;
+grant select on mysqltest.v2 to mysqltest_1@localhost;
+grant select on mysqltest.v3 to mysqltest_1@localhost;
+grant select on mysqltest.v4 to mysqltest_1@localhost;
+
+connection user1;
+# all selects works
+select c from mysqltest.v1;
+select c from mysqltest.v2;
+select c from mysqltest.v3;
+select c from mysqltest.v4;
+# test of show coluns
+show columns from mysqltest.v1;
+show columns from mysqltest.v2;
+# but explain/show do not
+-- error 1345
+explain select c from mysqltest.v1;
+-- error 1142
+show create view mysqltest.v1;
+-- error 1345
+explain select c from mysqltest.v2;
+-- error 1142
+show create view mysqltest.v2;
+-- error 1345
+explain select c from mysqltest.v3;
+-- error 1142
+show create view mysqltest.v3;
+-- error 1345
+explain select c from mysqltest.v4;
+-- error 1142
+show create view mysqltest.v4;
+
+# allow to see one of underlying table
+connection root;
+grant select on mysqltest.t1 to mysqltest_1@localhost;
+connection user1;
+# EXPLAIN of view on above table works
+explain select c from mysqltest.v1;
+-- error 1142
+show create view mysqltest.v1;
+explain select c from mysqltest.v2;
+-- error 1142
+show create view mysqltest.v2;
+# but other EXPLAINs do not
+-- error 1345
+explain select c from mysqltest.v3;
+-- error 1142
+show create view mysqltest.v3;
+-- error 1345
+explain select c from mysqltest.v4;
+-- error 1142
+show create view mysqltest.v4;
+
+# allow to see any view in mysqltest database
+connection root;
+grant show view on mysqltest.* to mysqltest_1@localhost;
+connection user1;
+explain select c from mysqltest.v1;
+show create view mysqltest.v1;
+explain select c from mysqltest.v2;
+show create view mysqltest.v2;
+explain select c from mysqltest.v3;
+show create view mysqltest.v3;
+explain select c from mysqltest.v4;
+show create view mysqltest.v4;
+
+connection root;
+revoke all privileges on mysqltest.* from mysqltest_1@localhost;
+delete from mysql.user where user='mysqltest_1';
+drop database mysqltest;
+
+#
+# UPDATE privileges on VIEW columns and whole VIEW
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int, primary key(a));
+insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10);
+create table mysqltest.t2 (x int);
+insert into mysqltest.t2 values (3), (4), (5), (6);
+create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
+create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;
+create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1;
+
+grant update (a) on mysqltest.v2 to mysqltest_1@localhost;
+grant update on mysqltest.v1 to mysqltest_1@localhost;
+grant select on mysqltest.* to mysqltest_1@localhost;
+
+connection user1;
+use mysqltest;
+# update with rights on VIEW column
+update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c;
+select * from t1;
+update v1 set a=a+c;
+select * from t1;
+# update with rights on whole VIEW
+update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c;
+select * from t1;
+update v2 set a=a+c;
+select * from t1;
+# no rights on column
+-- error 1143
+update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c;
+-- error 1143
+update v2 set c=a+c;
+# no rights for view
+-- error 1142
+update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c;
+-- error 1142
+update v3 set a=a+c;
+
+use test;
+connection root;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+
+#
+# DELETE privileges on VIEW
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int, primary key(a));
+insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10);
+create table mysqltest.t2 (x int);
+insert into mysqltest.t2 values (3), (4), (5), (6);
+create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
+create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1;
+
+grant delete on mysqltest.v1 to mysqltest_1@localhost;
+grant select on mysqltest.* to mysqltest_1@localhost;
+
+connection user1;
+use mysqltest;
+# update with rights on VIEW column
+delete from v1 where c < 4;
+select * from t1;
+delete v1 from t2,v1 where t2.x=v1.c;
+select * from t1;
+# no rights for view
+-- error 1142
+delete v2 from t2,v2 where t2.x=v2.c;
+-- error 1142
+delete from v2 where c < 4;
+
+use test;
+connection root;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+
+#
+# insert privileges on VIEW
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int, primary key(a));
+insert into mysqltest.t1 values (1,2), (2,3);
+create table mysqltest.t2 (x int, y int);
+insert into mysqltest.t2 values (3,4);
+create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1;
+create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;
+
+grant insert on mysqltest.v1 to mysqltest_1@localhost;
+grant select on mysqltest.* to mysqltest_1@localhost;
+
+connection user1;
+use mysqltest;
+# update with rights on VIEW column
+insert into v1 values (5,6);
+select * from t1;
+insert into v1 select x,y from t2;
+select * from t1;
+# no rights for view
+-- error 1142
+insert into v2 values (5,6);
+-- error 1142
+insert into v2 select x,y from t2;
+
+use test;
+connection root;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+
+#
+# test of CREATE VIEW privileges if we have limited privileges
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int);
+create table mysqltest.t2 (a int, b int);
+
+grant update on mysqltest.t1 to mysqltest_1@localhost;
+grant update(b) on mysqltest.t2 to mysqltest_1@localhost;
+grant create view,update on test.* to mysqltest_1@localhost;
+
+connection user1;
+
+create view v1 as select * from mysqltest.t1;
+create view v2 as select b from mysqltest.t2;
+# There are not rights on mysqltest.v1
+-- error 1142
+create view mysqltest.v1 as select * from mysqltest.t1;
+# There are not any rights on mysqltest.t2.a
+-- error 1143
+create view v3 as select a from mysqltest.t2;
+
+# give CREATE VIEW privileges (without any privileges for result column)
+connection root;
+create table mysqltest.v3 (b int);
+grant create view on mysqltest.v3 to mysqltest_1@localhost;
+drop table mysqltest.v3;
+connection user1;
+create view mysqltest.v3 as select b from mysqltest.t2;
+
+# give UPDATE privileges
+connection root;
+grant create view, update on mysqltest.v3 to mysqltest_1@localhost;
+drop view mysqltest.v3;
+connection user1;
+create view mysqltest.v3 as select b from mysqltest.t2;
+
+# give UPDATE and INSERT privilege (to get more privileges then underlying
+# table)
+connection root;
+grant create view, update, insert on mysqltest.v3 to mysqltest_1@localhost;
+drop view mysqltest.v3;
+connection user1;
+-- error 1143
+create view mysqltest.v3 as select b from mysqltest.t2;
+
+
+# If we would get more privileges on VIEW then we have on
+# underlying tables => creation prohibited
+connection root;
+create table mysqltest.v3 (b int);
+grant select(b) on mysqltest.v3 to mysqltest_1@localhost;
+drop table mysqltest.v3;
+connection user1;
+-- error 1142
+create view mysqltest.v3 as select b from mysqltest.t2;
+
+# Expression need select privileges
+-- error 1143
+create view v4 as select b+1 from mysqltest.t2;
+
+connection root;
+grant create view,update,select on test.* to mysqltest_1@localhost;
+connection user1;
+-- error 1143
+create view v4 as select b+1 from mysqltest.t2;
+
+connection root;
+grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost;
+connection user1;
+create view v4 as select b+1 from mysqltest.t2;
+
+connection root;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+drop view v1,v2,v4;
+
+#
+# user with global DB privileges
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+create table mysqltest.t1 (a int);
+grant all privileges on mysqltest.* to mysqltest_1@localhost;
+
+connection user1;
+use mysqltest;
+create view v1 as select * from t1;
+
+connection root;
+revoke all privileges on mysqltest.* from mysqltest_1@localhost;
+drop database mysqltest;
+