diff options
Diffstat (limited to 'mysql-test/main/view_grant.test')
| -rw-r--r-- | mysql-test/main/view_grant.test | 2230 |
1 files changed, 2230 insertions, 0 deletions
diff --git a/mysql-test/main/view_grant.test b/mysql-test/main/view_grant.test new file mode 100644 index 00000000..9094c616 --- /dev/null +++ b/mysql-test/main/view_grant.test @@ -0,0 +1,2230 @@ +# Can't test with embedded server +-- source include/not_embedded.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +--disable_warnings +drop database if exists mysqltest; +drop view if exists v1,v2,v3; +--enable_warnings + + +# simple test of grants +create user test@localhost; +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; +# The grant above creates a new user test@localhost, delete it +drop user 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); + +create user mysqltest_1@localhost; +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; + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +create definer=root@localhost view v1 as select * from mysqltest.t1; +create view v1 as select * from mysqltest.t1; +# try to modify view without DROP privilege on it +--error ER_TABLEACCESS_DENIED_ERROR +alter view v1 as select * from mysqltest.t1; +--error ER_TABLEACCESS_DENIED_ERROR +create or replace view v1 as select * from mysqltest.t1; +# no CRETE VIEW privilege +--error ER_TABLEACCESS_DENIED_ERROR +create view mysqltest.v2 as select * from mysqltest.t1; +# no SELECT privilege +--error ER_TABLEACCESS_DENIED_ERROR +create view v2 as select * from mysqltest.t2; + +connection root; +# check view definer information +show create view v1; + +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; +disconnect user1; + +# +# 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; + +connect (user1,localhost,mysqltest_1,,mysqltest); +select c from mysqltest.v1; +# there are no privileges on column 'd' +--error ER_COLUMNACCESS_DENIED_ERROR +select d from mysqltest.v1; + +connection root; +revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; +drop user mysqltest_1@localhost; +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; +create user mysqltest_1@localhost; +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 ER_COLUMNACCESS_DENIED_ERROR +select d from mysqltest.v1; + +connection root; +disconnect user1; +revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; +drop user mysqltest_1@localhost; +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; +# v5: SHOW VIEW, but no SELECT +create view mysqltest.v5 (c,d) as select a+1,b+1 from mysqltest.t1; +create user mysqltest_1@localhost; +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; +grant show view on mysqltest.v5 to mysqltest_1@localhost; + +connect (user1,localhost,mysqltest_1,,mysqltest); +# all SELECTs works, except v5 which lacks SELECT privs +select c from mysqltest.v1; +select c from mysqltest.v2; +select c from mysqltest.v3; +select c from mysqltest.v4; +--error ER_TABLEACCESS_DENIED_ERROR +select c from mysqltest.v5; +# test of show coluns +show columns from mysqltest.v1; +show columns from mysqltest.v2; +# explain/show fail +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v1; +--error ER_TABLEACCESS_DENIED_ERROR +show create view mysqltest.v1; +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v2; +--error ER_TABLEACCESS_DENIED_ERROR +show create view mysqltest.v2; +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v3; +--error ER_TABLEACCESS_DENIED_ERROR +show create view mysqltest.v3; +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v4; +--error ER_TABLEACCESS_DENIED_ERROR +show create view mysqltest.v4; +--error ER_TABLEACCESS_DENIED_ERROR +explain select c from mysqltest.v5; +# new in 5.5: SHOW CREATE VIEW needs SELECT now (MySQL Bug#27145) +--error ER_TABLEACCESS_DENIED_ERROR +show create view mysqltest.v5; +connection root; +grant select on mysqltest.v5 to mysqltest_1@localhost; +connection user1; +show create view mysqltest.v5; + +# missing SELECT on underlying t1, no SHOW VIEW on v1 either. +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v1; +# missing SHOW VIEW +--error ER_TABLEACCESS_DENIED_ERROR +show create view mysqltest.v1; +# allow to see one of underlying table +connection root; +grant show view on mysqltest.v1 to mysqltest_1@localhost; +grant select on mysqltest.t1 to mysqltest_1@localhost; +revoke select on mysqltest.v5 from mysqltest_1@localhost; +disconnect user1; +connect (user1,localhost,mysqltest_1,,mysqltest); +# EXPLAIN works +explain select c from mysqltest.v1; +show create view mysqltest.v1; +# missing SHOW VIEW +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v2; +--error ER_TABLEACCESS_DENIED_ERROR +show create view mysqltest.v2; +# but other EXPLAINs do not +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v3; +--error ER_TABLEACCESS_DENIED_ERROR +show create view mysqltest.v3; +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v4; +--error ER_TABLEACCESS_DENIED_ERROR +show create view mysqltest.v4; +# we have SHOW VIEW on v5, and SELECT on t1 -- not enough +--error ER_TABLEACCESS_DENIED_ERROR +explain select c from mysqltest.v5; + +# allow to see any view in mysqltest database +connection root; +grant show view on mysqltest.* to mysqltest_1@localhost; +disconnect user1; +connect (user1,localhost,mysqltest_1,,mysqltest); +explain select c from mysqltest.v1; +show create view mysqltest.v1; +explain select c from mysqltest.v2; +show create view mysqltest.v2; +# have SHOW VIEW | SELECT on v3, but no SELECT on t2 +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v3; +show create view mysqltest.v3; +# have SHOW VIEW | SELECT on v4, but no SELECT on t2 +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v4; +show create view mysqltest.v4; + +connection root; +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +disconnect user1; + +# +# 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 table mysqltest.t3 (x int); +insert into mysqltest.t3 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; + +create user mysqltest_1@localhost; +grant update (a) on mysqltest.v2 to mysqltest_1@localhost; +grant update on mysqltest.v1 to mysqltest_1@localhost; +grant update on mysqltest.t3 to mysqltest_1@localhost; +grant select on mysqltest.* to mysqltest_1@localhost; + +connect (user1,localhost,mysqltest_1,,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; +# update a table, select only on view +update t3,v3 set t3.x=t3.x+v3.c where t3.x=v3.c; +# no rights on column +--error ER_COLUMNACCESS_DENIED_ERROR +update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; +--error ER_COLUMNACCESS_DENIED_ERROR +update v2 set c=a+c; +# no rights for view +--error ER_TABLEACCESS_DENIED_ERROR +update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; +--error ER_TABLEACCESS_DENIED_ERROR +update v3 set a=a+c; + +connection root; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +disconnect user1; + +# +# 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; + +create user mysqltest_1@localhost; +grant delete on mysqltest.v1 to mysqltest_1@localhost; +grant select on mysqltest.* to mysqltest_1@localhost; + +connect (user1,localhost,mysqltest_1,,mysqltest); +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 ER_TABLEACCESS_DENIED_ERROR +delete v2 from t2,v2 where t2.x=v2.c; +--error ER_TABLEACCESS_DENIED_ERROR +delete from v2 where c < 4; + +connection root; +disconnect user1; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user 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; + +create user mysqltest_1@localhost; +grant insert on mysqltest.v1 to mysqltest_1@localhost; +grant select on mysqltest.* to mysqltest_1@localhost; + +connect (user1,localhost,mysqltest_1,,mysqltest); +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 ER_TABLEACCESS_DENIED_ERROR +insert into v2 values (5,6); +--error ER_TABLEACCESS_DENIED_ERROR +insert into v2 select x,y from t2; + +connection root; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +disconnect user1; + +# +# 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); + +create user mysqltest_1@localhost; +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; + +connect (user1,localhost,mysqltest_1,,test); + +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 ER_TABLEACCESS_DENIED_ERROR +create view mysqltest.v1 as select * from mysqltest.t1; +# There are not any rights on mysqltest.t2.a +--error ER_COLUMNACCESS_DENIED_ERROR +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; + + +# Expression need select privileges +--error ER_COLUMNACCESS_DENIED_ERROR +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 ER_COLUMNACCESS_DENIED_ERROR +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 user mysqltest_1@localhost; +drop database mysqltest; +drop view v1,v2,v4; +disconnect user1; + +# +# user with global DB privileges +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings +create table mysqltest.t1 (a int); +create user mysqltest_1@localhost; +grant all privileges on mysqltest.* to mysqltest_1@localhost; + +connect (user1,localhost,mysqltest_1,,mysqltest); +create view v1 as select * from t1; + +connection root; +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +disconnect user1; + +# +# view definer grants revoking +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +create table mysqltest.t1 (a int, b int); + +create user mysqltest_1@localhost; +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); + +create view v1 as select * from mysqltest.t1; + +connection root; +# check view definer information +show create view v1; +revoke select on mysqltest.t1 from mysqltest_1@localhost; +--error ER_VIEW_INVALID +select * from v1; +grant select on mysqltest.t1 to mysqltest_1@localhost; +select * from v1; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop view v1; +drop database mysqltest; +disconnect user1; + +# +# rights on execution of view underlying functiond (Bug#9505) +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +use mysqltest; +create table t1 (a int); +insert into t1 values (1); +create table t2 (s1 int); +--disable_warnings +drop function if exists f2; +--enable_warnings +--enable_prepare_warnings +delimiter //; +create function f2 () returns int begin declare v int; select s1 from t2 +into v; return v; end// +delimiter ;// +--disable_prepare_warnings +create algorithm=TEMPTABLE view v1 as select f2() from t1; +create algorithm=MERGE view v2 as select f2() from t1; +create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; +create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; +create SQL SECURITY INVOKER view v5 as select * from v4; +create user mysqltest_1@localhost; +grant select on v1 to mysqltest_1@localhost; +grant select on v2 to mysqltest_1@localhost; +grant select on v3 to mysqltest_1@localhost; +grant select on v4 to mysqltest_1@localhost; +grant select on v5 to mysqltest_1@localhost; + +connect (user1,localhost,mysqltest_1,,mysqltest); +select * from v1; +select * from v2; +--error ER_VIEW_INVALID +select * from v3; +--error ER_VIEW_INVALID +select * from v4; +--error ER_VIEW_INVALID +select * from v5; + +connection root; +drop view v1, v2, v3, v4, v5; +drop function f2; +drop table t1, t2; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +disconnect user1; + +# +# revertion of previous test, definer of view lost his/her rights to execute +# function +# + +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +use mysqltest; +create table t1 (a int); +insert into t1 values (1); +create table t2 (s1 int); +--disable_warnings +drop function if exists f2; +--enable_warnings +--enable_prepare_warnings +delimiter //; +create function f2 () returns int begin declare v int; select s1 from t2 +into v; return v; end// +delimiter ;// +--disable_prepare_warnings +create user mysqltest_1@localhost; +grant select on t1 to mysqltest_1@localhost; +grant execute on function f2 to mysqltest_1@localhost; +grant create view on mysqltest.* to mysqltest_1@localhost; + +connect (user1,localhost,mysqltest_1,,mysqltest); +create algorithm=TEMPTABLE view v1 as select f2() from t1; +create algorithm=MERGE view v2 as select f2() from t1; +create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; +create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; + +connection root; +create view v5 as select * from v1; +revoke execute on function f2 from mysqltest_1@localhost; +--error ER_VIEW_INVALID +select * from v1; +--error ER_VIEW_INVALID +select * from v2; +select * from v3; +select * from v4; +--error ER_VIEW_INVALID +select * from v5; + +drop view v1, v2, v3, v4, v5; +drop function f2; +drop table t1, t2; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +disconnect user1; + +# +# definer/invoker rights for columns +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +use mysqltest; +create table t1 (a int); +create table v1 (a int); +insert into t1 values (1); +create user mysqltest_1@localhost; +grant select on t1 to mysqltest_1@localhost; +grant select on v1 to mysqltest_1@localhost; +grant create view on mysqltest.* to mysqltest_1@localhost; +drop table v1; + +connect (user1,localhost,mysqltest_1,,mysqltest); +create algorithm=TEMPTABLE view v1 as select *, a as b from t1; +create algorithm=MERGE view v2 as select *, a as b from t1; +create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; +create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; +create view v5 as select * from v1; + +connection root; +revoke select on t1 from mysqltest_1@localhost; +--error ER_VIEW_INVALID +select * from v1; +--error ER_VIEW_INVALID +select * from v2; +select * from v3; +select * from v4; +--error ER_VIEW_INVALID +select * from v5; + +#drop view v1, v2, v3, v4, v5; +drop table t1; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +disconnect user1; + + +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +use mysqltest; +create table t1 (a int); +insert into t1 values (1); +create algorithm=TEMPTABLE view v1 as select *, a as b from t1; +create algorithm=MERGE view v2 as select *, a as b from t1; +create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; +create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; +create SQL SECURITY INVOKER view v5 as select * from v4; +create user mysqltest_1@localhost; +grant select on v1 to mysqltest_1@localhost; +grant select on v2 to mysqltest_1@localhost; +grant select on v3 to mysqltest_1@localhost; +grant select on v4 to mysqltest_1@localhost; +grant select on v5 to mysqltest_1@localhost; + +connect (user1,localhost,mysqltest_1,,mysqltest); +select * from v1; +select * from v2; +--error ER_VIEW_INVALID +select * from v3; +--error ER_VIEW_INVALID +select * from v4; +--error ER_VIEW_INVALID +select * from v5; + +connection root; +drop view v1, v2, v3, v4, v5; +drop table t1; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +disconnect user1; + +# +# Bug#14256 definer in view definition is not fully qualified +# + +# Create the test user +create user 'test14256'@'%'; +grant all on test.* to 'test14256'@'%'; + +connect (test14256,localhost,test14256,,test); +connection test14256; +use test; + +create view v1 as select 42; +show create view v1; + +select definer into @v1def1 from information_schema.views + where table_schema = 'test' and table_name='v1'; +drop view v1; + +create definer=`test14256`@`%` view v1 as select 42; +show create view v1; + +select definer into @v1def2 from information_schema.views + where table_schema = 'test' and table_name='v1'; +drop view v1; + +select @v1def1, @v1def2, @v1def1=@v1def2; + +connection root; +disconnect test14256; +drop user test14256; + +# +# Bug#14726 freeing stack variable in case of an error of opening a view when +# we have locked tables with LOCK TABLES statement. +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +use mysqltest; +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +create user mysqltest_1@localhost; +GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; + +connect (user1,localhost,mysqltest_1,,mysqltest); + +LOCK TABLES v1 READ; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE TABLE v1; +UNLOCK TABLES; + +connection root; +use test; +drop user mysqltest_1@localhost; +drop database mysqltest; + +# +# switch to default connection +# +disconnect user1; +disconnect root; +connection default; + +# +# DEFINER information check +# +create definer=some_user@`` sql security invoker view v1 as select 1; +create definer=some_user@localhost sql security invoker view v2 as select 1; +show create view v1; +show create view v2; +drop view v1; +drop view v2; + +# +# Bug#18681 View privileges are broken +# +CREATE DATABASE mysqltest1; +CREATE USER readonly@localhost; +CREATE TABLE mysqltest1.t1 (x INT); +INSERT INTO mysqltest1.t1 VALUES (1), (2); +CREATE SQL SECURITY INVOKER VIEW mysqltest1.v_t1 AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ts AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ti AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tu AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tus AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_td AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tds AS SELECT * FROM mysqltest1.t1; +GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly@localhost; +GRANT SELECT ON mysqltest1.v_ts TO readonly@localhost; +GRANT INSERT ON mysqltest1.v_ti TO readonly@localhost; +GRANT UPDATE ON mysqltest1.v_tu TO readonly@localhost; +GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly@localhost; +GRANT DELETE ON mysqltest1.v_td TO readonly@localhost; +GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly@localhost; + +connect (n1,localhost,readonly,,mysqltest1); +connection n1; + +--error ER_VIEW_INVALID +SELECT * FROM mysqltest1.v_t1; +--error ER_VIEW_INVALID +INSERT INTO mysqltest1.v_t1 VALUES(4); +--error ER_VIEW_INVALID +DELETE FROM mysqltest1.v_t1 WHERE x = 1; +--error ER_VIEW_INVALID +UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2; +--error ER_VIEW_INVALID +UPDATE mysqltest1.v_t1 SET x = 3; +--error ER_VIEW_INVALID +DELETE FROM mysqltest1.v_t1; +--error ER_VIEW_INVALID +SELECT 1 FROM mysqltest1.v_t1; +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM mysqltest1.t1; + +SELECT * FROM mysqltest1.v_ts; +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x; +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM mysqltest1.v_ti; + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO mysqltest1.v_ts VALUES (100); +INSERT INTO mysqltest1.v_ti VALUES (100); + +--error ER_TABLEACCESS_DENIED_ERROR +UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100; +--error ER_TABLEACCESS_DENIED_ERROR +UPDATE mysqltest1.v_ts SET x= 200; +UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100; +UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100; +UPDATE mysqltest1.v_tu SET x= 200; + +--error ER_TABLEACCESS_DENIED_ERROR +DELETE FROM mysqltest1.v_ts WHERE x= 200; +--error ER_TABLEACCESS_DENIED_ERROR +DELETE FROM mysqltest1.v_ts; +--error ER_COLUMNACCESS_DENIED_ERROR +DELETE FROM mysqltest1.v_td WHERE x= 200; +DELETE FROM mysqltest1.v_tds WHERE x= 200; +DELETE FROM mysqltest1.v_td; + +connection default; +disconnect n1; +DROP VIEW mysqltest1.v_tds; +DROP VIEW mysqltest1.v_td; +DROP VIEW mysqltest1.v_tus; +DROP VIEW mysqltest1.v_tu; +DROP VIEW mysqltest1.v_ti; +DROP VIEW mysqltest1.v_ts; +DROP VIEW mysqltest1.v_t1; +DROP TABLE mysqltest1.t1; +DROP USER readonly@localhost; +DROP DATABASE mysqltest1; + +# +# Bug#14875 Bad view DEFINER makes SHOW CREATE VIEW fail +# +CREATE TABLE t1 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1; +#--warning ER_VIEW_OTHER_USER +SHOW CREATE VIEW v; +--error ER_NO_SUCH_USER +SELECT * FROM v; +DROP VIEW v; +DROP TABLE t1; +USE test; + +# +# Bug#20363 Create view on just created view is now denied +# +eval CREATE USER mysqltest_db1@localhost identified by 'PWD'; +eval GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION; + +# The session with the non root user is needed. +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (session1,localhost,mysqltest_db1,PWD,"*NO-ONE*"); + +CREATE SCHEMA mysqltest_db1 ; +USE mysqltest_db1 ; + +CREATE TABLE t1 (f1 INTEGER); + +CREATE VIEW view1 AS +SELECT * FROM t1; +SHOW CREATE VIEW view1; + +CREATE VIEW view2 AS +SELECT * FROM view1; +--echo # Here comes a suspicious warning +SHOW CREATE VIEW view2; +--echo # But the view view2 is usable +SELECT * FROM view2; + +CREATE VIEW view3 AS +SELECT * FROM view2; + +SELECT * from view3; + +connection default; +disconnect session1; +DROP VIEW mysqltest_db1.view3; +DROP VIEW mysqltest_db1.view2; +DROP VIEW mysqltest_db1.view1; +DROP TABLE mysqltest_db1.t1; +DROP SCHEMA mysqltest_db1; +DROP USER mysqltest_db1@localhost; +# +# Bug#20482 failure on Create join view with sources views/tables +# in different schemas +# +--disable_warnings +CREATE DATABASE test1; +CREATE DATABASE test2; +--enable_warnings + +CREATE TABLE test1.t0 (a VARCHAR(20)); +CREATE TABLE test2.t1 (a VARCHAR(20)); +CREATE VIEW test2.t3 AS SELECT * FROM test1.t0; +CREATE OR REPLACE VIEW test.v1 AS + SELECT ta.a AS col1, tb.a AS col2 FROM test2.t3 ta, test2.t1 tb; + +DROP VIEW test.v1; +DROP VIEW test2.t3; +DROP TABLE test2.t1, test1.t0; +DROP DATABASE test2; +DROP DATABASE test1; + + +# +# Bug#20570 CURRENT_USER() in a VIEW with SQL SECURITY DEFINER returns +# invoker name +# +--disable_warnings +DROP VIEW IF EXISTS v1; +DROP VIEW IF EXISTS v2; +DROP VIEW IF EXISTS v3; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT CURRENT_USER() AS cu; + +CREATE FUNCTION f1() RETURNS VARCHAR(77) SQL SECURITY INVOKER + RETURN CURRENT_USER(); +CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT f1() AS cu; + +CREATE PROCEDURE p1(OUT cu VARCHAR(77)) SQL SECURITY INVOKER + SET cu= CURRENT_USER(); +delimiter |; +CREATE FUNCTION f2() RETURNS VARCHAR(77) SQL SECURITY INVOKER +BEGIN + DECLARE cu VARCHAR(77); + CALL p1(cu); + RETURN cu; +END| +delimiter ;| +CREATE SQL SECURITY DEFINER VIEW v3 AS SELECT f2() AS cu; + +CREATE USER mysqltest_u1@localhost; +GRANT ALL ON test.* TO mysqltest_u1@localhost; + +connect (conn1, localhost, mysqltest_u1,,); + +--echo +--echo The following tests should all return 1. +--echo +SELECT CURRENT_USER() = 'mysqltest_u1@localhost'; +SELECT f1() = 'mysqltest_u1@localhost'; +CALL p1(@cu); +SELECT @cu = 'mysqltest_u1@localhost'; +SELECT f2() = 'mysqltest_u1@localhost'; +SELECT cu = 'root@localhost' FROM v1; +SELECT cu = 'root@localhost' FROM v2; +SELECT cu = 'root@localhost' FROM v3; + +disconnect conn1; +connection default; + +DROP VIEW v3; +DROP FUNCTION f2; +DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP VIEW v2; +DROP VIEW v1; +DROP USER mysqltest_u1@localhost; + + +# +# Bug#17254 Error for DEFINER security on VIEW provides too much info +# +connect (root,localhost,root,,); +connection root; +CREATE DATABASE db17254; +USE db17254; +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (10),(20); +CREATE USER def_17254@localhost; +GRANT SELECT ON db17254.* TO def_17254@localhost; +CREATE USER inv_17254@localhost; +GRANT SELECT ON db17254.t1 TO inv_17254@localhost; +GRANT CREATE VIEW ON db17254.* TO def_17254@localhost; + +connect (def,localhost,def_17254,,db17254); +connection def; +CREATE VIEW v1 AS SELECT * FROM t1; + +connection root; +GRANT SELECT ON db17254.v1 TO inv_17254@localhost; +DROP USER def_17254@localhost; + +connect (inv,localhost,inv_17254,,db17254); +connection inv; +--echo for a user without SET USER +--error ER_ACCESS_DENIED_ERROR +SELECT * FROM v1; +disconnect inv; + +connection root; +GRANT SET USER ON *.* TO inv_17254@localhost; + +connect (inv2,localhost,inv_17254,,db17254); +connection inv2; +SHOW GRANTS; +SELECT CURRENT_USER, SYSTEM_USER(), USER(); +--echo for a user with SET USER +--error ER_NO_SUCH_USER +SELECT * FROM v1; +disconnect inv2; + +connection root; +--echo for a superuser +--error ER_NO_SUCH_USER +SELECT * FROM v1; +DROP USER inv_17254@localhost; +DROP DATABASE db17254; +disconnect def; + + +# +# Bug#24404 strange bug with view+permission+prepared statement +# +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db1; +DROP DATABASE IF EXISTS mysqltest_db2; +--enable_warnings +--error 0,ER_CANNOT_USER +DROP USER mysqltest_u1; +--error 0,ER_CANNOT_USER +DROP USER mysqltest_u2; + +CREATE USER mysqltest_u1@localhost; +CREATE USER mysqltest_u2@localhost; + +CREATE DATABASE mysqltest_db1; +CREATE DATABASE mysqltest_db2; + +GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost WITH GRANT OPTION; +GRANT ALL ON mysqltest_db2.* TO mysqltest_u2@localhost; + +connect (conn1, localhost, mysqltest_u1, , mysqltest_db1); + +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1); + +# Use view with subquery for better coverage. +CREATE VIEW v1 AS SELECT i FROM t1 WHERE 1 IN (SELECT * FROM t1); + +CREATE TABLE t2 (s CHAR(7)); +INSERT INTO t2 VALUES ('public'); + +GRANT SELECT ON v1 TO mysqltest_u2@localhost; +GRANT SELECT ON t2 TO mysqltest_u2@localhost; + +connect (conn2, localhost, mysqltest_u2, , mysqltest_db2); + +SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2; +PREPARE stmt1 FROM "SELECT * FROM mysqltest_db1.t2"; +EXECUTE stmt1; +PREPARE stmt2 FROM "SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2"; +EXECUTE stmt2; + +connection conn1; +# Make table 't2' private. +REVOKE SELECT ON t2 FROM mysqltest_u2@localhost; +UPDATE t2 SET s = 'private' WHERE s = 'public'; + +connection conn2; +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2; +--error ER_TABLEACCESS_DENIED_ERROR +EXECUTE stmt1; +# Original bug was here: the statement didn't fail. +--error ER_TABLEACCESS_DENIED_ERROR +EXECUTE stmt2; + +# Cleanup. +disconnect conn2; +disconnect conn1; +connection default; +REVOKE ALL ON mysqltest_db1.* FROM mysqltest_u1@localhost; +REVOKE ALL ON mysqltest_db2.* FROM mysqltest_u2@localhost; +DROP DATABASE mysqltest_db1; +DROP DATABASE mysqltest_db2; +DROP USER mysqltest_u1@localhost; +DROP USER mysqltest_u2@localhost; + +# +# Bug#26813 The SUPER privilege is wrongly required to alter a view created +# by another user. +# +connection root; +CREATE DATABASE db26813; +USE db26813; +CREATE TABLE t1(f1 INT, f2 INT); +CREATE VIEW v1 AS SELECT f1 FROM t1; +CREATE VIEW v2 AS SELECT f1 FROM t1; +CREATE VIEW v3 AS SELECT f1 FROM t1; +CREATE USER u26813@localhost; +GRANT DROP ON db26813.v1 TO u26813@localhost; +GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost; +GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost; +GRANT SELECT ON db26813.t1 TO u26813@localhost; + +connect (u1,localhost,u26813,,db26813); +connection u1; +--error ER_TABLEACCESS_DENIED_ERROR +ALTER VIEW v1 AS SELECT f2 FROM t1; +--error ER_TABLEACCESS_DENIED_ERROR +ALTER VIEW v2 AS SELECT f2 FROM t1; +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +ALTER VIEW v3 AS SELECT f2 FROM t1; + +connection root; +SHOW CREATE VIEW v3; + +DROP USER u26813@localhost; +DROP DATABASE db26813; +disconnect u1; + +--echo # +--echo # Bug#29908 A user can gain additional access through the ALTER VIEW. +--echo # +connection root; +CREATE DATABASE mysqltest_29908; +USE mysqltest_29908; +CREATE TABLE t1(f1 INT, f2 INT); +CREATE USER u29908_1@localhost; +CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1; +CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS + SELECT f1 FROM t1; +GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost; +GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost; +GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost; +CREATE USER u29908_2@localhost; +GRANT SELECT, DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost; +GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost; +GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost; + +connect (u2,localhost,u29908_2,,mysqltest_29908); +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +ALTER VIEW v1 AS SELECT f2 FROM t1; +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +ALTER VIEW v2 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v2; + +connect (u1,localhost,u29908_1,,mysqltest_29908); +ALTER VIEW v1 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v1; +ALTER VIEW v2 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v2; + +connection root; +ALTER VIEW v1 AS SELECT f1 FROM t1; +SHOW CREATE VIEW v1; +ALTER VIEW v2 AS SELECT f1 FROM t1; +SHOW CREATE VIEW v2; + +DROP USER u29908_1@localhost; +DROP USER u29908_2@localhost; +DROP DATABASE mysqltest_29908; +disconnect u1; +disconnect u2; +--echo ####################################################################### + +# +# Bug#24040 Create View don't succed with "all privileges" on a database. +# + +# Prepare. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +--enable_warnings + +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; + +# Test. + +CREATE TABLE mysqltest1.t1(c1 INT); +CREATE TABLE mysqltest1.t2(c2 INT); +CREATE TABLE mysqltest1.t3(c3 INT); +CREATE TABLE mysqltest1.t4(c4 INT); + +INSERT INTO mysqltest1.t1 VALUES (11), (12), (13), (14); +INSERT INTO mysqltest1.t2 VALUES (21), (22), (23), (24); +INSERT INTO mysqltest1.t3 VALUES (31), (32), (33), (34); +INSERT INTO mysqltest1.t4 VALUES (41), (42), (43), (44); + +CREATE USER mysqltest_u1@localhost; +GRANT SELECT ON mysqltest1.t1 TO mysqltest_u1@localhost; +GRANT INSERT ON mysqltest1.t2 TO mysqltest_u1@localhost; +GRANT SELECT, UPDATE ON mysqltest1.t3 TO mysqltest_u1@localhost; +GRANT SELECT, DELETE ON mysqltest1.t4 TO mysqltest_u1@localhost; + +GRANT ALL PRIVILEGES ON mysqltest2.* TO mysqltest_u1@localhost; + +--connect (bug24040_con,localhost,mysqltest_u1,,mysqltest2) + +SELECT * FROM mysqltest1.t1; +INSERT INTO mysqltest1.t2 VALUES(25); +UPDATE mysqltest1.t3 SET c3 = 331 WHERE c3 = 31; +DELETE FROM mysqltest1.t4 WHERE c4 = 44; + +CREATE VIEW v1 AS SELECT * FROM mysqltest1.t1; +CREATE VIEW v2 AS SELECT * FROM mysqltest1.t2; +CREATE VIEW v3 AS SELECT * FROM mysqltest1.t3; +CREATE VIEW v4 AS SELECT * FROM mysqltest1.t4; + +SELECT * FROM v1; +INSERT INTO v2 VALUES(26); +UPDATE v3 SET c3 = 332 WHERE c3 = 32; +DELETE FROM v4 WHERE c4 = 43; + +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v12 AS SELECT c1, c2 FROM mysqltest1.t1, mysqltest1.t2; +CREATE VIEW v13 AS SELECT c1, c3 FROM mysqltest1.t1, mysqltest1.t3; +CREATE VIEW v14 AS SELECT c1, c4 FROM mysqltest1.t1, mysqltest1.t4; + +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v21 AS SELECT c2, c1 FROM mysqltest1.t2, mysqltest1.t1; +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v23 AS SELECT c2, c3 FROM mysqltest1.t2, mysqltest1.t3; +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v24 AS SELECT c2, c4 FROM mysqltest1.t2, mysqltest1.t4; + +CREATE VIEW v31 AS SELECT c3, c1 FROM mysqltest1.t3, mysqltest1.t1; +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v32 AS SELECT c3, c2 FROM mysqltest1.t3, mysqltest1.t2; +CREATE VIEW v34 AS SELECT c3, c4 FROM mysqltest1.t3, mysqltest1.t4; + +CREATE VIEW v41 AS SELECT c4, c1 FROM mysqltest1.t4, mysqltest1.t1; +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v42 AS SELECT c4, c2 FROM mysqltest1.t4, mysqltest1.t2; +CREATE VIEW v43 AS SELECT c4, c3 FROM mysqltest1.t4, mysqltest1.t3; + +--connection default + +SELECT * FROM mysqltest1.t1; +SELECT * FROM mysqltest1.t2; +SELECT * FROM mysqltest1.t3; +SELECT * FROM mysqltest1.t4; + +# Cleanup. + +disconnect bug24040_con; + +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +DROP USER mysqltest_u1@localhost; + + +# +# Bug#41354 Access control is bypassed when all columns of a view are +# selected by * wildcard + +CREATE DATABASE db1; +USE db1; +CREATE TABLE t1(f1 INT, f2 INT); +CREATE VIEW v1 AS SELECT f1, f2 FROM t1; + +CREATE USER foo; +GRANT SELECT (f1) ON t1 TO foo; +GRANT SELECT (f1) ON v1 TO foo; + +connect (addconfoo, localhost, foo,,db1); + +SELECT f1 FROM t1; +--error ER_COLUMNACCESS_DENIED_ERROR +SELECT f2 FROM t1; +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM t1; + +SELECT f1 FROM v1; +--error ER_COLUMNACCESS_DENIED_ERROR +SELECT f2 FROM v1; +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM v1; + +connection default; +disconnect root; +disconnect addconfoo; +USE test; +REVOKE SELECT (f1) ON db1.t1 FROM foo; +REVOKE SELECT (f1) ON db1.v1 FROM foo; +DROP USER foo; +DROP VIEW db1.v1; +DROP TABLE db1.t1; +DROP DATABASE db1; + +connection default; + + +--echo Bug #11765687/#58677: +--echo No privilege on table/view, but can know #rows / underlying table's name + +# As a root-like user +connect (root,localhost,root,,test); +connection root; + +create database mysqltest1; +create table mysqltest1.t1 (i int); +create table mysqltest1.t2 (j int); +create table mysqltest1.t3 (k int, secret int); + +create user alice@localhost; +create user bob@localhost; +create user cecil@localhost; +create user dan@localhost; +create user eugene@localhost; +create user fiona@localhost; +create user greg@localhost; +create user han@localhost; +create user inga@localhost; +create user jamie@localhost; +create user karl@localhost; +create user lena@localhost; +create user mhairi@localhost; +create user noam@localhost; +create user olga@localhost; +create user pjotr@localhost; +create user quintessa@localhost; + +grant all privileges on mysqltest1.* to alice@localhost with grant option; + +# +--echo ... as alice +connect (test11765687,localhost,alice,,mysqltest1); +connection test11765687; + +create view v1 as select * from t1; +create view v2 as select * from v1, t2; +create view v3 as select k from t3; + +grant select on mysqltest1.v1 to bob@localhost; + +grant show view on mysqltest1.v1 to cecil@localhost; + +grant select, show view on mysqltest1.v1 to dan@localhost; +grant select on mysqltest1.t1 to dan@localhost; + +grant select on mysqltest1.* to eugene@localhost; + +grant select, show view on mysqltest1.v2 to fiona@localhost; + +grant select, show view on mysqltest1.v2 to greg@localhost; +grant show view on mysqltest1.v1 to greg@localhost; + +grant select(k) on mysqltest1.t3 to han@localhost; +grant select, show view on mysqltest1.v3 to han@localhost; + +grant select on mysqltest1.t1 to inga@localhost; +grant select on mysqltest1.t2 to inga@localhost; +grant select on mysqltest1.v1 to inga@localhost; +grant select, show view on mysqltest1.v2 to inga@localhost; + +grant select on mysqltest1.t1 to jamie@localhost; +grant select on mysqltest1.t2 to jamie@localhost; +grant show view on mysqltest1.v1 to jamie@localhost; +grant select, show view on mysqltest1.v2 to jamie@localhost; + +grant select on mysqltest1.t1 to karl@localhost; +grant select on mysqltest1.t2 to karl@localhost; +grant select, show view on mysqltest1.v1 to karl@localhost; +grant select on mysqltest1.v2 to karl@localhost; + +grant select on mysqltest1.t1 to lena@localhost; +grant select on mysqltest1.t2 to lena@localhost; +grant select, show view on mysqltest1.v1 to lena@localhost; +grant show view on mysqltest1.v2 to lena@localhost; + +grant select on mysqltest1.t1 to mhairi@localhost; +grant select on mysqltest1.t2 to mhairi@localhost; +grant select, show view on mysqltest1.v1 to mhairi@localhost; +grant select, show view on mysqltest1.v2 to mhairi@localhost; + +grant select on mysqltest1.t1 to noam@localhost; +grant select, show view on mysqltest1.v1 to noam@localhost; +grant select, show view on mysqltest1.v2 to noam@localhost; + +grant select on mysqltest1.t2 to olga@localhost; +grant select, show view on mysqltest1.v1 to olga@localhost; +grant select, show view on mysqltest1.v2 to olga@localhost; + +grant select on mysqltest1.t1 to pjotr@localhost; +grant select on mysqltest1.t2 to pjotr@localhost; +grant select, show view on mysqltest1.v2 to pjotr@localhost; + +grant select, show view on mysqltest1.v1 to quintessa@localhost; + +disconnect test11765687; + +# +--echo ... as bob +connect (test11765687,localhost,bob,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +--error ER_VIEW_NO_EXPLAIN +explain select * from v1; # fail, no SHOW_VIEW + +disconnect test11765687; + +# +--echo ... as cecil +connect (test11765687,localhost,cecil,,mysqltest1); +connection test11765687; + +--error ER_TABLEACCESS_DENIED_ERROR +select * from v1; # fail, no SELECT +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from v1; # fail, no SELECT + +disconnect test11765687; + +# +--echo ... as dan +connect (test11765687,localhost,dan,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +explain select * from v1; # Should succeed. + +disconnect test11765687; + +# +--echo ... as eugene +connect (test11765687,localhost,eugene,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +--error ER_VIEW_NO_EXPLAIN +explain select * from v1; # fail, no SHOW_VIEW + +disconnect test11765687; + +# +--echo ... as fiona +connect (test11765687,localhost,fiona,,mysqltest1); +connection test11765687; + +select * from v2; # Should succeed. +show create view v2; # Should succeed, but... +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from t1; # fail, shouldn't see t1! +--error ER_TABLEACCESS_DENIED_ERROR +# err msg must give view name, no table names!! +explain select * from v1; # fail, have no privs on v1! +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from t2; # fail, have no privs on t2! +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; # fail, shouldn't see t2! + +disconnect test11765687; + +# +--echo ... as greg +connect (test11765687,localhost,greg,,mysqltest1); +connection test11765687; + +select * from v2; # Should succeed. +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from v1; # fail; no SELECT on v1! +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; # fail; no SELECT on v1! + +disconnect test11765687; + +# +--echo ... as han +connect (test11765687,localhost,han,,mysqltest1); +connection test11765687; + +--error ER_TABLEACCESS_DENIED_ERROR +select * from t3; # don't have privs on all columns, +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from t3; # so EXPLAIN on "forbidden" columns should fail. +select k from t3; # but we do have SELECT on column k though, +explain select k from t3; # so EXPLAIN just on k should work, +select * from v3; # and so should SELECT on view only using allowed columns +explain select * from v3; # as should the associated EXPLAIN + +disconnect test11765687; + +# +--echo ... as inga +connect (test11765687,localhost,inga,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel on t1/t2, only sel v1 +# fail: lacks show on v1 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as jamie +connect (test11765687,localhost,jamie,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel on t1/t2, only show v1 +# fail: lacks sel on v1 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as karl +connect (test11765687,localhost,karl,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel only on v2, sel on t1/t2, sel/show v1 +# fail: lacks show on v2 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as lena + +connect (test11765687,localhost,lena,,mysqltest1); +connection test11765687; +--error ER_TABLEACCESS_DENIED_ERROR +select * from v2; +# has show only on v2, sel on t1/t2, sel/show v1 +# fail: lacks sel on v2 +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from v2; +disconnect test11765687; + +# +--echo ... as mhairi +connect (test11765687,localhost,mhairi,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel on t1/t2, sel/show v1 +explain select * from v2; +disconnect test11765687; + +# +--echo ... as noam +connect (test11765687,localhost,noam,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel only on t1, sel/show v1 (no sel on t2!) +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as olga +connect (test11765687,localhost,olga,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel only on t2, sel/show v1 (no sel on t1!) +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as pjotr +connect (test11765687,localhost,pjotr,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel only on t2, nothing on v1 +# fail: lacks show on v1 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as quintessa +connect (test11765687,localhost,quintessa,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +--error ER_VIEW_NO_EXPLAIN +explain select * from v1; # fail: lacks select on t1 + +disconnect test11765687; + +# cleanup + +# +--echo ... as root again at last: clean-up time! +connection root; + +drop user alice@localhost; +drop user bob@localhost; +drop user cecil@localhost; +drop user dan@localhost; +drop user eugene@localhost; +drop user fiona@localhost; +drop user greg@localhost; +drop user han@localhost; +drop user inga@localhost; +drop user jamie@localhost; +drop user karl@localhost; +drop user lena@localhost; +drop user mhairi@localhost; +drop user noam@localhost; +drop user olga@localhost; +drop user pjotr@localhost; +drop user quintessa@localhost; + +drop database mysqltest1; + +disconnect root; + +connection default; + +# +# MDEV-4951 drop user leaves privileges +# +#verify that no privileges were left after the above +select * from information_schema.table_privileges; + +--echo End of 5.0 tests. + + +# +# Test that ALTER VIEW accepts DEFINER and ALGORITHM, see bug#16425. +# +connection default; +--disable_warnings +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1; + +ALTER VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +ALTER DEFINER=no_such@user_1 VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; + +DROP VIEW v1; +DROP TABLE t1; + +# +# Bug#37191: Failed assertion in CREATE VIEW +# +CREATE USER mysqluser1@localhost; +CREATE DATABASE mysqltest1; + +USE mysqltest1; + +CREATE TABLE t1 ( a INT ); +CREATE TABLE t2 ( b INT ); + +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1), (2); + +GRANT CREATE VIEW ON mysqltest1.* TO mysqluser1@localhost; + +GRANT SELECT ON t1 TO mysqluser1@localhost; +GRANT INSERT ON t2 TO mysqluser1@localhost; + +--connect (connection1, localhost, mysqluser1, , mysqltest1) + +--echo This would lead to failed assertion. +CREATE VIEW v1 AS SELECT a, b FROM t1, t2; + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM v1; +--error ER_TABLEACCESS_DENIED_ERROR +SELECT b FROM v1; + +--disconnect connection1 +--connection default + +DROP TABLE t1, t2; +DROP VIEW v1; +DROP DATABASE mysqltest1; +DROP USER mysqluser1@localhost; +USE test; + +--echo End of 5.1 tests. + +# +# Bug#36086: SELECT * from views don't check column grants +# +CREATE USER mysqluser1@localhost; +CREATE DATABASE mysqltest1; + +USE mysqltest1; + +CREATE TABLE t1 ( a INT, b INT ); +CREATE TABLE t2 ( a INT, b INT ); + +CREATE VIEW v1 AS SELECT a, b FROM t1; + +GRANT SELECT( a ) ON v1 TO mysqluser1@localhost; +GRANT UPDATE( b ) ON t2 TO mysqluser1@localhost; + +--connect (connection1, localhost, mysqluser1, ,mysqltest1) + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM mysqltest1.v1; + +--error ER_TABLEACCESS_DENIED_ERROR +CREATE VIEW v1 AS SELECT * FROM mysqltest1.t2; + +--disconnect connection1 + +--connection default + +DROP TABLE t1, t2; +DROP VIEW v1; +DROP DATABASE mysqltest1; +DROP USER mysqluser1@localhost; + +# +# Bug#35600 Security breach via view, I_S table and prepared +# statement/stored procedure +# +CREATE USER mysqluser1@localhost; +CREATE DATABASE mysqltest1; + +USE mysqltest1; + +CREATE VIEW v1 AS SELECT * FROM information_schema.tables LIMIT 1; +CREATE ALGORITHM = TEMPTABLE VIEW v2 AS SELECT 1 AS A; + +CREATE VIEW test.v3 AS SELECT 1 AS a; + +--connection default +GRANT SELECT ON mysqltest1.* to mysqluser1@localhost; +GRANT ALL ON test.* TO mysqluser1@localhost; + +--connect (connection1, localhost, mysqluser1, , test) +PREPARE stmt_v1 FROM "SELECT * FROM mysqltest1.v1"; +PREPARE stmt_v2 FROM "SELECT * FROM mysqltest1.v2"; + +--connection default +REVOKE SELECT ON mysqltest1.* FROM mysqluser1@localhost; + +--connection connection1 + +--error ER_TABLEACCESS_DENIED_ERROR +EXECUTE stmt_v1; +--error ER_TABLEACCESS_DENIED_ERROR +EXECUTE stmt_v2; +--disconnect connection1 + +--connect (connection2, localhost, mysqluser1,,) +PREPARE stmt FROM "SELECT a FROM v3"; +EXECUTE stmt; +--disconnect connection2 + +--connection default +DROP VIEW v1, v2; +DROP DATABASE mysqltest1; +DROP VIEW test.v3; +DROP USER mysqluser1@localhost; +USE test; + +--echo # +--echo # Bug#35996: SELECT + SHOW VIEW should be enough to display view +--echo # definition +--echo # +-- source include/not_embedded.inc +CREATE USER mysqluser1@localhost; +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +GRANT USAGE, SELECT, CREATE VIEW, SHOW VIEW +ON mysqltest2.* TO mysqluser1@localhost; + +USE mysqltest1; + +CREATE TABLE t1( a INT ); +CREATE TABLE t2( a INT, b INT ); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE VIEW v1 AS SELECT 1 AS a; +CREATE VIEW v2 AS SELECT 1 AS a, 2 AS b; + +GRANT SELECT ON TABLE t1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE t2 TO mysqluser1@localhost; +GRANT EXECUTE ON FUNCTION f1 TO mysqluser1@localhost; +GRANT SELECT ON TABLE v1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE v2 TO mysqluser1@localhost; + +CREATE VIEW v_t1 AS SELECT * FROM t1; +CREATE VIEW v_t2 AS SELECT * FROM t2; +CREATE VIEW v_f1 AS SELECT f1() AS a; +CREATE VIEW v_v1 AS SELECT * FROM v1; +CREATE VIEW v_v2 AS SELECT * FROM v2; + +GRANT SELECT, SHOW VIEW ON v_t1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_t2 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_f1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v2 TO mysqluser1@localhost; + +--connect (connection1, localhost, mysqluser1,, mysqltest2) +CREATE VIEW v_mysqluser1_t1 AS SELECT * FROM mysqltest1.t1; +CREATE VIEW v_mysqluser1_t2 AS SELECT * FROM mysqltest1.t2; +CREATE VIEW v_mysqluser1_f1 AS SELECT mysqltest1.f1() AS a; +CREATE VIEW v_mysqluser1_v1 AS SELECT * FROM mysqltest1.v1; +CREATE VIEW v_mysqluser1_v2 AS SELECT * FROM mysqltest1.v2; + +SHOW CREATE VIEW mysqltest1.v_t1; +SHOW CREATE VIEW mysqltest1.v_t2; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW mysqltest1.v_v2; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_t2; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; +SHOW CREATE VIEW v_mysqluser1_v2; + +--connection default +REVOKE SELECT ON TABLE t1 FROM mysqluser1@localhost; +REVOKE SELECT (a) ON TABLE t2 FROM mysqluser1@localhost; +REVOKE EXECUTE ON FUNCTION f1 FROM mysqluser1@localhost; +REVOKE SELECT ON TABLE v1 FROM mysqluser1@localhost; + +--connection connection1 +SHOW CREATE VIEW mysqltest1.v_t1; +SHOW CREATE VIEW mysqltest1.v_t2; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW mysqltest1.v_v2; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_t2; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; +SHOW CREATE VIEW v_mysqluser1_v2; + +--connection default +--echo # Testing the case when the views reference missing objects. +--echo # Obviously, there are no privileges to check for, so we +--echo # need only each object type once. +DROP TABLE t1; +DROP FUNCTION f1; +DROP VIEW v1; + +--connection connection1 +SHOW CREATE VIEW mysqltest1.v_t1; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; + +--connection default +REVOKE SHOW VIEW ON v_t1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_f1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_v1 FROM mysqluser1@localhost; + +--connection connection1 +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_t1; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_f1; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; + +--disconnect connection1 +--connection default +DROP USER mysqluser1@localhost; +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +USE test; + +CREATE TABLE t1( a INT ); +CREATE DEFINER = no_such_user@no_such_host VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +DROP TABLE t1; +DROP VIEW v1; + + +--echo # +--echo # Bug #46019: ERROR 1356 When selecting from within another +--echo # view that has Group By +--echo # +CREATE DATABASE mysqltest1; +USE mysqltest1; + +CREATE TABLE t1 (a INT); + +CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT a FROM t1 GROUP BY a; +CREATE SQL SECURITY INVOKER VIEW v2 AS SELECT a FROM v1; + +CREATE USER mysqluser1; + +GRANT SELECT ON TABLE t1 TO mysqluser1; +GRANT SELECT, SHOW VIEW ON TABLE v1 TO mysqluser1; +GRANT SELECT, SHOW VIEW ON TABLE v2 TO mysqluser1; + +--connect (mysqluser1, localhost, mysqluser1,,mysqltest1) +SELECT a FROM v1; +SELECT a FROM v2; + +--connection default +--disconnect mysqluser1 +DROP USER mysqluser1; +DROP DATABASE mysqltest1; +USE test; + +--echo # +--echo # Bug#47734: Assertion failed: ! is_set() when locking a view with non-existing definer +--echo # + +--disable_warnings +DROP VIEW IF EXISTS v1; +--enable_warnings + +CREATE DEFINER=`unknown`@`unknown` SQL SECURITY DEFINER VIEW v1 AS SELECT 1; +--error ER_NO_SUCH_USER +LOCK TABLES v1 READ; +DROP VIEW v1; + + +--echo # +--echo # Bug #58499 "DEFINER-security view selecting from INVOKER-security view +--echo # access check wrong". +--echo # +--echo # Check that we correctly handle privileges for various combinations +--echo # of INVOKER and DEFINER-security views using each other. +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +--enable_warnings +CREATE DATABASE mysqltest1; +USE mysqltest1; +CREATE TABLE t1 (i INT); +CREATE TABLE t2 (j INT); +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +--echo # +--echo # 1) DEFINER-security view uses INVOKER-security view (covers +--echo # scenario originally described in the bug report). +CREATE SQL SECURITY INVOKER VIEW v1_uses_t1 AS SELECT * FROM t1; +CREATE SQL SECURITY INVOKER VIEW v1_uses_t2 AS SELECT * FROM t2; +CREATE USER 'mysqluser1'@'%'; +GRANT CREATE VIEW ON mysqltest1.* TO 'mysqluser1'@'%'; +GRANT SELECT ON t1 TO 'mysqluser1'@'%'; +--echo # To be able create 'v2_uses_t2' we also need select on t2. +GRANT SELECT ON t2 TO 'mysqluser1'@'%'; +GRANT SELECT ON v1_uses_t1 TO 'mysqluser1'@'%'; +GRANT SELECT ON v1_uses_t2 TO 'mysqluser1'@'%'; +--connect (mysqluser1, localhost, mysqluser1,,mysqltest1) +CREATE SQL SECURITY DEFINER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1; +CREATE SQL SECURITY DEFINER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2; +--connection default +CREATE USER 'mysqluser2'@'%'; +GRANT SELECT ON v2_uses_t1 TO 'mysqluser2'@'%'; +GRANT SELECT ON v2_uses_t2 TO 'mysqluser2'@'%'; +GRANT SELECT ON t2 TO 'mysqluser2'@'%'; +GRANT CREATE VIEW ON mysqltest1.* TO 'mysqluser2'@'%'; +--echo # Make 'mysqluser1' unable to access t2. +REVOKE SELECT ON t2 FROM 'mysqluser1'@'%'; +--connect (mysqluser2, localhost, mysqluser2,,mysqltest1) +--echo # The below statement should succeed thanks to suid nature of v2_uses_t1. +SELECT * FROM v2_uses_t1; +--echo # The below statement should fail due to suid nature of v2_uses_t2. +--error ER_VIEW_INVALID +SELECT * FROM v2_uses_t2; +--echo # +--echo # 2) INVOKER-security view uses INVOKER-security view. +--connection default +DROP VIEW v2_uses_t1, v2_uses_t2; +CREATE SQL SECURITY INVOKER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1; +CREATE SQL SECURITY INVOKER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2; +GRANT SELECT ON v2_uses_t1 TO 'mysqluser1'@'%'; +GRANT SELECT ON v2_uses_t2 TO 'mysqluser1'@'%'; +GRANT SELECT ON v1_uses_t1 TO 'mysqluser2'@'%'; +GRANT SELECT ON v1_uses_t2 TO 'mysqluser2'@'%'; +--connection mysqluser1 +--echo # For both versions of 'v2' 'mysqluser1' privileges should be used. +SELECT * FROM v2_uses_t1; +--error ER_VIEW_INVALID +SELECT * FROM v2_uses_t2; +--connection mysqluser2 +--echo # And now for both versions of 'v2' 'mysqluser2' privileges should +--echo # be used. +--error ER_VIEW_INVALID +SELECT * FROM v2_uses_t1; +SELECT * FROM v2_uses_t2; +--echo # +--echo # 3) INVOKER-security view uses DEFINER-security view. +--connection default +DROP VIEW v1_uses_t1, v1_uses_t2; +--echo # To be able create 'v1_uses_t2' we also need select on t2. +GRANT SELECT ON t2 TO 'mysqluser1'@'%'; +--connection mysqluser1 +CREATE SQL SECURITY DEFINER VIEW v1_uses_t1 AS SELECT * FROM t1; +CREATE SQL SECURITY DEFINER VIEW v1_uses_t2 AS SELECT * FROM t2; +--connection default +--echo # Make 'mysqluser1' unable to access t2. +REVOKE SELECT ON t2 FROM 'mysqluser1'@'%'; +--connection mysqluser2 +--echo # Due to suid nature of v1_uses_t1 and v1_uses_t2 the first +--echo # select should succeed and the second select should fail. +SELECT * FROM v2_uses_t1; +--error ER_VIEW_INVALID +SELECT * FROM v2_uses_t2; +--echo # +--echo # 4) DEFINER-security view uses DEFINER-security view. +--connection default +DROP VIEW v2_uses_t1, v2_uses_t2; +--echo # To be able create 'v2_uses_t2' we also need select on t2. +GRANT SELECT ON t2 TO 'mysqluser1'@'%'; +--connection mysqluser2 +CREATE SQL SECURITY DEFINER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1; +CREATE SQL SECURITY DEFINER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2; +--connection default +--echo # Make 'mysqluser1' unable to access t2. +REVOKE SELECT ON t2 FROM 'mysqluser1'@'%'; +--connection mysqluser2 +--echo # Again privileges of creator of innermost views should apply. +SELECT * FROM v2_uses_t1; +--error ER_VIEW_INVALID +SELECT * FROM v2_uses_t2; + +--disconnect mysqluser1 +--disconnect mysqluser2 +--connection default +USE test; +DROP DATABASE mysqltest1; +DROP USER 'mysqluser1'@'%'; +DROP USER 'mysqluser2'@'%'; + +--echo # +--echo # Test for bug #11766767 - "59957: VIEW USING MERGE PERMISSIONS +--echo # IN MULTI-TABLE UPDATE". +--echo # +--disable_warnings +drop database if exists mysqltest1; +drop database if exists mysqltest2; +--enable_warnings +--echo # +--echo # Prepare playground. +create database mysqltest1; +create database mysqltest2; +create user user_11766767; +grant select on mysqltest1.* to user_11766767; +grant all on mysqltest2.* to user_11766767; +use mysqltest1; +create table t1 (id int primary key, val varchar(20)); +insert into t1 values (1, 'test1'); +create table t11 (id int primary key); +insert into t11 values (1); +create algorithm=temptable view v1_temp as select * from t1; +create algorithm=merge view v1_merge as select * from t1; +create algorithm=temptable view v11_temp as + select t1.id as id, t1.val as val from t1, t11 where t1.id= t11.id; +create algorithm=merge view v11_merge as + select t1.id as id, t1.val as val from t1, t11 where t1.id= t11.id; +use mysqltest2; +create table t2 (id int primary key, val varchar(20)); +insert into t2 values (1, 'test2'); +create table t21 (id int primary key); +insert into t21 values (1); +create algorithm=temptable view v2_temp as select * from t2; +create algorithm=merge view v2_merge as select * from t2; +create algorithm=temptable view v21_temp as + select t2.id as id, t2.val as val from t2, t21 where t2.id= t21.id; +create algorithm=merge view v21_merge as + select t2.id as id, t2.val as val from t2, t21 where t2.id= t21.id; +create algorithm=temptable sql security invoker view v3_temp as + select t1.id as id, t1.val as val from mysqltest1.t1 as t1, mysqltest1.t11 as t11 + where t1.id = t11.id; +create algorithm=merge sql security invoker view v3_merge as + select t1.id as id, t1.val as val from mysqltest1.t1 as t1, mysqltest1.t11 as t11 + where t1.id = t11.id; +create sql security invoker view v31 as + select t2.id as id, t2.val as val from mysqltest2.t2 as t2, mysqltest1.t11 as t11 + where t2.id = t11.id; +create sql security invoker view v4 as + select t2.id as id, t2.val as val from mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 + where t2.id = v1.id; +create sql security invoker view v41 as + select v1.id as id, v1.val as val from mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 + where t2.id = v1.id; +create sql security invoker view v42 as + select v2.id as id, v2.val as val from mysqltest2.t2 as t2, mysqltest2.v2_merge as v2 + where t2.id = v2.id; + + +connect (conn_11766767, localhost, user_11766767,,mysqltest2); + +--echo # +--echo # A) Check how we handle privilege checking in multi-update for +--echo # directly used views. +--echo # +--echo # A.1) Originally reported problem, view is used in read-only mode. +--echo # This should work with only SELECT privilege for both mergeable +--echo # and temptable algorithms. +update mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 set t2.val= 'test3' + where t2.id= v1.id; +update mysqltest2.t2 as t2, mysqltest1.v1_temp as v1 set t2.val= 'test4' + where t2.id= v1.id; +--echo # +--echo # A.2) If view is updated an UPDATE privilege on it is required. +--echo # Temptable views can't be updated. +--error ER_TABLEACCESS_DENIED_ERROR +update mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 set v1.val= 'test5' + where t2.id= v1.id; +update mysqltest1.t1 as t1, mysqltest2.v2_merge as v2 set v2.val= 'test6' + where t1.id= v2.id; +--echo # +--echo # Note that the below error is OK even though user lacks UPDATE +--echo # privilege on v1_temp since he/she still has SELECT privilege on +--echo # this view. +--error ER_NON_UPDATABLE_TABLE +update mysqltest2.t2 as t2, mysqltest1.v1_temp as v1 set v1.val= 'test7' + where t2.id= v1.id; +--error ER_NON_UPDATABLE_TABLE +update mysqltest1.t1 as t1, mysqltest2.v2_temp as v2 set v2.val= 'test8' + where t1.id= v2.id; +--echo # +--echo # A.3) This also works for correctly for multi-table views. +--echo # When usage is read-only SELECT is enough. +update mysqltest2.t2 as t2, mysqltest1.v11_merge as v11 set t2.val= 'test9' + where t2.id= v11.id; +update mysqltest2.t2 as t2, mysqltest1.v11_temp as v11 set t2.val= 'test10' + where t2.id= v11.id; +--echo # When one of view's tables is updated, UPDATE is required +--echo # on a view. +--error ER_TABLEACCESS_DENIED_ERROR +update mysqltest2.t2 as t2, mysqltest1.v11_merge as v11 set v11.val= 'test11' + where t2.id= v11.id; +update mysqltest1.t1 as t1, mysqltest2.v21_merge as v21 set v21.val= 'test12' + where t1.id= v21.id; +--echo # As before, temptable views are not updateable. +--error ER_NON_UPDATABLE_TABLE +update mysqltest2.t2 as t2, mysqltest1.v11_temp as v11 set v11.val= 'test13' + where t2.id= v11.id; +--error ER_NON_UPDATABLE_TABLE +update mysqltest1.t1 as t1, mysqltest2.v21_temp as v21 set v21.val= 'test14' + where t1.id= v21.id; + +--echo # +--echo # B) Now check that correct privileges are required on underlying +--echo # tables. To simplify this part of test we will use SECURITY +--echo # INVOKER views in it. +--echo # +--echo # B.1) In case when view is used for read only it is enough to have +--echo # SELECT on its underlying tables. +update mysqltest2.t2 as t2, mysqltest2.v3_merge as v3 set t2.val= 'test15' + where t2.id= v3.id; +update mysqltest2.t2 as t2, mysqltest2.v3_temp as v3 set t2.val= 'test16' + where t2.id= v3.id; +--echo # +--echo # B.2) If view is updated, UPDATE privilege on the table being updated +--echo # is required (since we already checked that temptable views are +--echo # not updateable we don't test them here). +--error ER_VIEW_INVALID +update mysqltest2.t2 as t2, mysqltest2.v3_merge as v3 set v3.val= 'test17' + where t2.id= v3.id; +update mysqltest1.t11 as t11, mysqltest2.v31 as v31 set v31.val= 'test18' + where t11.id= v31.id; + +--disable_ps_protocol +--echo # +--echo # C) Finally, check how we handle privilege checking in case when +--echo # view is used through another view. Again we will use SECURITY +--echo # INVOKER views for simplicity. +--echo # +--echo # C.1) As usual, when a view used by another view is going to be used +--echo # in read-only fashion, only SELECT privilege is necessary. +update mysqltest1.t11 as t11, mysqltest2.v4 as v4 set v4.val= 'test19' + where t11.id= v4.id; +--echo # +--echo # C.2) If one of underlying tables of the view is updated then +--echo # UPDATE on a view is necessary. +--error ER_VIEW_INVALID +update mysqltest1.t11 as t11, mysqltest2.v41 as v4 set v4.val= 'test20' + where t11.id= v4.id; +update mysqltest1.t11 as t11, mysqltest2.v42 as v4 set v4.val= 'test20' + where t11.id= v4.id; +--enable_ps_protocol + +--echo # +--echo # Clean-up. +--echo # +disconnect conn_11766767; +connection default; +drop user user_11766767; +drop database mysqltest1; +drop database mysqltest2; + +--echo # Check that a user without access to the schema 'foo' cannot query +--echo # a JSON_TABLE view in that schema. +CREATE SCHEMA foo; +CREATE VIEW foo.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt; + +CREATE USER foo@localhost; +GRANT SELECT on test.* to foo@localhost; +connect (con1,localhost,foo,,); +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM foo.v; + +--echo # +--echo # Clean-up. +--echo # +connection default; +disconnect con1; +drop user foo@localhost; +drop schema foo; + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc + |