diff options
Diffstat (limited to 'mysql-test/t/view.test')
| -rw-r--r-- | mysql-test/t/view.test | 853 |
1 files changed, 853 insertions, 0 deletions
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test new file mode 100644 index 00000000000..a37de0904c5 --- /dev/null +++ b/mysql-test/t/view.test @@ -0,0 +1,853 @@ +--disable_warnings +drop table if exists t1,t2; +drop view if exists v1,v2,v3,v4,v5,v6; +drop database if exists mysqltest; +--enable_warnings +use test; + +# +# some basic test of views and its functionality +# + +# create view on unexistence table +-- error 1146 +create view v1 (c,d) as select a,b from t1; + +create temporary table t1 (a int, b int); +#view on temporary table +-- error 1350 +create view v1 (c) as select b+1 from t1; +drop table t1; + +create table t1 (a int, b int); +insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); + +#view with variable +-- error 1349 +create view v1 (c,d) as select a,b+@@global.max_user_connections from t1; + +# simple view +create view v1 (c) as select b+1 from t1; +select c from v1; + +#tamporary table should not shade (hide) table of view +create temporary table t1 (a int, b int); +# this is empty +select * from t1; +# but this based on normal t1 +select c from v1; +show create table v1; +show create view v1; +-- error 1345 +show create view t1; +drop table t1; + +# try to use fields from underlaid table +-- error 1054 +select a from v1; +-- error 1054 +select v1.a from v1; +-- error 1054 +select b from v1; +-- error 1054 +select v1.b from v1; + +# view with different algorithms (explain out put are differ) +explain extended select c from v1; +create algorithm=temptable view v2 (c) as select b+1 from t1; +show create table v2; +select c from v2; +explain extended select c from v2; + +# try to use underlaid table fields in VIEW creation process +-- error 1054 +create view v3 (c) as select a+1 from v1; +-- error 1054 +create view v3 (c) as select b+1 from v1; + + +# VIEW on VIEW test with mixing different algorithms on different order +create view v3 (c) as select c+1 from v1; +select c from v3; +explain extended select c from v3; +create algorithm=temptable view v4 (c) as select c+1 from v2; +select c from v4; +explain extended select c from v4; +create view v5 (c) as select c+1 from v2; +select c from v5; +explain extended select c from v5; +create algorithm=temptable view v6 (c) as select c+1 from v1; +select c from v6; +explain extended select c from v6; + +# show table/table status test +show tables; +--replace_column 12 # 13 # +show table status; + +drop view v1,v2,v3,v4,v5,v6; + +# +# alter/create view test +# + +# view with subqueries of different types +create view v1 (c,d,e,f) as select a,b, +a in (select a+2 from t1), a = all (select a from t1) from t1; +create view v2 as select c, d from v1; +select * from v1; +select * from v2; + +# try to create VIEW with name of existing VIEW +-- error 1050 +create view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1; + +# 'or replace' should work in this case +create or replace view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1; + +# try to ALTER unexisting VIEW +drop view v2; +-- error 1146 +alter view v2 as select c, d from v1; + +# 'or replace' on unexisting view +create or replace view v2 as select c, d from v1; + +# alter view on existing view +alter view v1 (c,d) as select a,max(b) from t1 group by a; + +# check that created view works +select * from v1; +select * from v2; + +# 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; + +#try to drop unexisten VIEW +-- error 1051 +drop view v100; + +#try to drop table with DROP VIEW +-- error 1345 +drop view t1; + +#try to drop VIEW with DROP TABLE +-- error 1051 +drop table v1; + +#try to drop table with DROP VIEW + +drop view v1,v2; +drop table t1; + +# +# outer left join with merged views +# +create table t1 (a int); +insert into t1 values (1), (2), (3); + +create view v1 (a) as select a+1 from t1; +create view v2 (a) as select a-1 from t1; + +select * from t1 natural left join v1; +select * from v2 natural left join t1; +select * from v2 natural left join v1; + +drop view v1, v2; +drop table t1; + + +# +# 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; +# 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; +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 not privilege ob 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 not privilege ob 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 1343 +explain select c from mysqltest.v1; +-- error 1343 +show create table mysqltest.v1; +-- error 1343 +explain select c from mysqltest.v2; +-- error 1343 +show create table mysqltest.v2; +-- error 1343 +explain select c from mysqltest.v3; +-- error 1343 +show create table mysqltest.v3; +-- error 1343 +explain select c from mysqltest.v4; +-- error 1343 +show create table mysqltest.v4; + +# allow to see one of underlaing 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; +show create table mysqltest.v1; +explain select c from mysqltest.v2; +show create table mysqltest.v2; +# but other EXPLAINs do not +-- error 1343 +explain select c from mysqltest.v3; +-- error 1343 +show create table mysqltest.v3; +-- error 1343 +explain select c from mysqltest.v4; +-- error 1343 +show create table 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 table mysqltest.v1; +explain select c from mysqltest.v2; +show create table mysqltest.v2; +explain select c from mysqltest.v3; +show create table mysqltest.v3; +explain select c from mysqltest.v4; +show create table mysqltest.v4; + +connection root; +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +drop database mysqltest; + +# +# QUERY CHECHE options for VIEWs +# +set GLOBAL query_cache_size=1355776; +flush status; +create table t1 (a int, b int); + +# queries with following views should not be in query cache +create view v1 (c,d) as select sql_no_cache a,b from t1; +create view v2 (c,d) as select a+rand(),b from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +select * from v2; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +select * from v2; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +drop view v1,v2; + +# SQL_CACHE option +set query_cache_type=demand; +flush status; +# query with view will be cached, but direct acess to table will not +create view v1 (c,d) as select sql_cache a,b from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +drop view v1; +set query_cache_type=default; + +drop table t1; +set GLOBAL query_cache_size=default; + + +# +# DISTINCT option for VIEW +# +create table t1 (a int); +insert into t1 values (1), (2), (3), (1), (2), (3); +create view v1 as select distinct a from t1; +select * from v1; +explain select * from v1; +select * from t1; +drop view v1; +drop table t1; + +# +# syntax compatibility +# +create table t1 (a int); +create view v1 as select distinct a from t1 WITH CHECK OPTION; +create view v2 as select distinct a from t1 WITH CASCADED CHECK OPTION; +create view v3 as select distinct a from t1 WITH LOCAL CHECK OPTION; +drop view v3 RESTRICT; +drop view v2 CASCADE; +drop view v1; +drop table t1; + +# +# aliases +# +create table t1 (a int, b int); +insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); +create view v1 (c) as select b+1 from t1; +select test.c from v1 test; +create algorithm=temptable view v2 (c) as select b+1 from t1; +select test.c from v2 test; +select test1.* from v1 test1, v2 test2 where test1.c=test2.c; +select test2.* from v1 test1, v2 test2 where test1.c=test2.c; +drop table t1; +drop view v1,v2; + +# +# LIMIT clasuse test +# +create table t1 (a int); +insert into t1 values (1), (2), (3), (4); +create view v1 as select a+1 from t1 order by 1 desc limit 2; +select * from v1; +explain select * from v1; +drop view v1; +drop table t1; + +# +# CREATE ... SELECT view test +# +create table t1 (a int); +insert into t1 values (1), (2), (3), (4); +create view v1 as select a+1 from t1; +create table t2 select * from v1; +show columns from t2; +select * from t2; +drop view v1; +drop table t1,t2; + +# +# simple view + simple update +# +create table t1 (a int, b int, primary key(a)); +insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); +create view v1 (a,c) as select a, b+1 from t1; +create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; +# try to update expression +-- error 1346 +update v1 set c=a+c; +# try to update VIEW with forced TEMPORARY TABLE algorithm +-- error 1288 +update v2 set a=a+c; +# updatable field of updateable view +update v1 set a=a+c; +select * from v1; +select * from t1; +drop table t1; +drop view v1,v2; + +# +# simple view + simple multi-update +# +create table t1 (a int, b int, primary key(a)); +insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); +create table t2 (x int); +insert into t2 values (10), (20); +create view v1 (a,c) as select a, b+1 from t1; +create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; +# try to update expression +-- error 1346 +update t2,v1 set v1.c=v1.a+v1.c where t2.x=v1.a; +# try to update VIEW with forced TEMPORARY TABLE algorithm +-- error 1288 +update t2,v2 set v2.a=v2.v2.a+c where t2.x=v2.a; +# updatable field of updateable view +update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.a; +select * from v1; +select * from t1; +drop table t1,t2; +drop view v1,v2; + +# +# 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 1143 +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; + +# +# MEREGE VIEW with WHERE clause +# +create table t1 (a int, b int, primary key(b)); +insert into t1 values (1,20), (2,30), (3,40), (4,50), (5,100); +create view v1 (c) as select b from t1 where a<3; +# simple select and explaint to be sure that it is MERGE +select * from v1; +explain extended select * from v1; +# update test +update v1 set c=c+1; +select * from t1; +# join of such VIEWs test +create view v2 (c) as select b from t1 where a>=3; +select * from v1, v2; +drop view v1, v2; +drop table t1; + +# +# simple view + simple delete +# +create table t1 (a int, b int, primary key(a)); +insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10); +create view v1 (a,c) as select a, b+1 from t1; +create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; +# try to update VIEW with forced TEMPORARY TABLE algorithm +-- error 1288 +delete from v2 where c < 4; +# updatable field of updateable view +delete from v1 where c < 4; +select * from v1; +select * from t1; +drop table t1; +drop view v1,v2; + +# +# simple view + simple multi-delete +# +create table t1 (a int, b int, primary key(a)); +insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10); +create table t2 (x int); +insert into t2 values (1), (2), (3), (4); +create view v1 (a,c) as select a, b+1 from t1; +create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; +# try to update VIEW with forced TEMPORARY TABLE algorithm +-- error 1288 +delete v2 from t2,v2 where t2.x=v2.a; +# updatable field of updateable view +delete v1 from t2,v1 where t2.x=v1.a; +select * from v1; +select * from t1; +drop table t1,t2; +drop view v1,v2; + +# +# 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; + +# +# key presence check +# +create table t1 (a int, b int, c int, primary key(a,b)); +insert into t1 values (10,2,-1), (20,3,-2), (30,4,-3), (40,5,-4), (50,10,-5); +create view v1 (x,y) as select a, b from t1; +create view v2 (x,y) as select a, c from t1; +set sql_updatable_view_key=YES; +update v1 set x=x+1; +-- error 1288 +update v2 set x=x+1; +set sql_updatable_view_key=LIMIT1; +update v1 set x=x+1; +update v2 set x=x+1; +update v1 set x=x+1 limit 1; +-- error 1288 +update v2 set x=x+1 limit 1; +set sql_updatable_view_key=NO; +update v1 set x=x+1 limit 1; +update v2 set x=x+1 limit 1; +set sql_updatable_view_key=DEFAULT; +select * from t1; +drop table t1; +drop view v1,v2; + +# +# simple insert +# +create table t1 (a int, b int, c int, primary key(a,b)); +insert into t1 values (10,2,-1), (20,3,-2); +create view v1 (x,y,z) as select c, b, a from t1; +create view v2 (x,y) as select b, a from t1; +create view v3 (x,y,z) as select b, a, b from t1; +create view v4 (x,y,z) as select c+1, b, a from t1; +create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; +# try insert to VIEW with fields duplicate +-- error 1288 +insert into v3 values (-60,4,30); +# try insert to VIEW with expression in SELECT list +-- error 1288 +insert into v4 values (-60,4,30); +# try insert to VIEW using temporary table algorithm +-- error 1288 +insert into v5 values (-60,4,30); +insert into v1 values (-60,4,30); +insert into v1 (z,y,x) values (50,6,-100); +insert into v2 values (5,40); +select * from t1; +drop table t1; +drop view v1,v2,v3,v4,v5; + +# +# insert ... select +# +create table t1 (a int, b int, c int, primary key(a,b)); +insert into t1 values (10,2,-1), (20,3,-2); +create table t2 (a int, b int, c int, primary key(a,b)); +insert into t2 values (30,4,-60); +create view v1 (x,y,z) as select c, b, a from t1; +create view v2 (x,y) as select b, a from t1; +create view v3 (x,y,z) as select b, a, b from t1; +create view v4 (x,y,z) as select c+1, b, a from t1; +create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; +# try insert to VIEW with fields duplicate +-- error 1288 +insert into v3 select c, b, a from t2; +# try insert to VIEW with expression in SELECT list +-- error 1288 +insert into v4 select c, b, a from t2; +# try insert to VIEW using temporary table algorithm +-- error 1288 +insert into v5 select c, b, a from t2; +insert into v1 select c, b, a from t2; +insert into v1 (z,y,x) select a+20,b+2,-100 from t2; +insert into v2 select b+1, a+10 from t2; +select * from t1; +drop table t1; +drop view v1,v2,v3,v4,v5; + +# +# 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; + +# +# outer join based on VIEW with WHERE clause +# +create table t1 (a int, primary key(a)); +insert into t1 values (1), (2), (3); +create view v1 (x) as select a from t1 where a > 1; +select t1.a, v1.x from t1 left join v1 on (t1.a= v1.x); +drop table t1; +drop view v1; + +# +# merging WHERE condition on VIEW on VIEW +# +create table t1 (a int, primary key(a)); +insert into t1 values (1), (2), (3), (200); +create view v1 (x) as select a from t1 where a > 1; +create view v2 (y) as select x from v1 where x < 100; +select * from v2; +drop table t1; +drop view v1,v2; + +# +# VIEW on non-updatable view +# +create table t1 (a int, primary key(a)); +insert into t1 values (1), (2), (3), (200); +create ALGORITHM=TEMPTABLE view v1 (x) as select a from t1; +create view v2 (y) as select x from v1; +-- error 1288 +update v2 set y=10 where y=2; +drop table t1; +drop view v1,v2; + +# +# auto_increment field out of VIEW +# +create table t1 (a int not null auto_increment, b int not null, primary key(a), unique(b)); +create view v1 (x) as select b from t1; +insert into v1 values (1); +select last_insert_id(); +insert into t1 (b) values (2); +select last_insert_id(); +select * from t1; +drop view v1; +drop table t1; + +# +# 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 CRETEA VIEW privileges but without any privileges for result colemn +connection root; +create table mysqltest.v3 (b int); +grant create view on mysqltest.v3 to mysqltest_1@localhost; +drop table mysqltest.v3; +connection user1; +-- error 1143 +create view mysqltest.v3 as select b from mysqltest.t2; + +# give UPDATE privileges -> create works +connection root; +create table mysqltest.v3 (b int); +grant create view, update on mysqltest.v3 to mysqltest_1@localhost; +drop table mysqltest.v3; +connection user1; +create view mysqltest.v3 as select b from mysqltest.t2; + + +# If give other privileges for VIEW then underlaying table have => +# creation prohibited +connection root; +grant select(b) on mysqltest.v3 to mysqltest_1@localhost; +drop view 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; + |