Create 'main' test directory and move 't' and 'r' there

1 files changed, 0 insertions, 2207 deletions

diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test
deleted file mode 100644
index a70241138aa..00000000000
--- a/mysql-test/t/view_grant.test
+++ /dev/null
@@ -1,2207 +0,0 @@
-# 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;
-
-#
-# grants per columns
-#
-# MERGE algorithm
---disable_warnings
-create database mysqltest;
---enable_warnings
-
-create table mysqltest.t1 (a int, b int);
-create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
-grant select (c) on mysqltest.v1 to mysqltest_1@localhost;
-
-connection user1;
-select c from mysqltest.v1;
-# there are no privileges on column 'd'
---error 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;
-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;
-
-connection user1;
-# 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;
-connection user1;
-# 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;
-connection user1;
-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;
-
-#
-# 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;
-
-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 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 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;
-
-use test;
-connection root;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
-drop user mysqltest_1@localhost;
-drop database mysqltest;
-
-#
-# DELETE privileges on VIEW
-#
-connection root;
---disable_warnings
-create database mysqltest;
---enable_warnings
-
-create table mysqltest.t1 (a int, b int, primary key(a));
-insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10);
-create table mysqltest.t2 (x int);
-insert into mysqltest.t2 values (3), (4), (5), (6);
-create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
-create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1;
-
-create user mysqltest_1@localhost;
-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 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;
-
-use test;
-connection root;
-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;
-
-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 ER_TABLEACCESS_DENIED_ERROR
-insert into v2 values (5,6);
---error ER_TABLEACCESS_DENIED_ERROR
-insert into v2 select x,y from t2;
-
-use test;
-connection root;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
-drop user mysqltest_1@localhost;
-drop database mysqltest;
-
-#
-# test of CREATE VIEW privileges if we have limited privileges
-#
-connection root;
---disable_warnings
-create database mysqltest;
---enable_warnings
-
-create table mysqltest.t1 (a int, b int);
-create table mysqltest.t2 (a int, b int);
-
-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;
-
-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 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;
-
-#
-# 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;
-
-connection user1;
-use mysqltest;
-create view v1 as select * from t1;
-use test;
-
-connection root;
-revoke all privileges on mysqltest.* from mysqltest_1@localhost;
-drop user mysqltest_1@localhost;
-drop database mysqltest;
-
-#
-# 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;
-
-connection user1;
-
-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;
-
-#
-# 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
-delimiter //;
-create function f2 () returns int begin declare v int; select s1 from t2
-into v; return v; end//
-delimiter ;//
-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;
-
-connection user1;
-use 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;
-use test;
-
-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;
-
-#
-# 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
-delimiter //;
-create function f2 () returns int begin declare v int; select s1 from t2
-into v; return v; end//
-delimiter ;//
-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;
-
-connection user1;
-use 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;
-use test;
-
-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;
-
-#
-# 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;
-
-connection user1;
-use 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;
-use test;
-
-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;
-
-
-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;
-
-connection user1;
-use 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;
-use test;
-
-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;
-
-#
-# Bug#14256 definer in view definition is not fully qualified
-#
---disable_warnings
-drop view if exists v1;
-drop table if exists t1;
---enable_warnings
-
-# Backup anonymous users and remove them. (They get in the way of
-# the one we test with here otherwise.)
-create table t1 as select * from mysql.user where user='';
-delete from mysql.user where user='';
-flush privileges;
-
-# 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;
-
-# Restore the anonymous users.
-insert into mysql.user select * from t1;
-flush privileges;
-
-drop table t1;
-
-#
-# 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;
-
-connection user1;
-
-use mysqltest;
-LOCK TABLES v1 READ;
---error ER_TABLEACCESS_DENIED_ERROR
-SHOW CREATE TABLE v1;
-UNLOCK TABLES;
-use test;
-
-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,,);
-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,test);
-
-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;
-DROP USER def_17254@localhost;
-
-connect (inv,localhost,inv_17254,,db17254);
-connection inv;
---echo for a user
---error ER_TABLEACCESS_DENIED_ERROR
-SELECT * FROM v1;
-
-connection root;
---echo for a superuser
---error ER_NO_SUCH_USER
-SELECT * FROM v1;
-DROP USER inv_17254@localhost;
-DROP DATABASE db17254;
-disconnect def;
-disconnect inv;
-
-
-#
-# 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,,);
-connection addconfoo;
-USE 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, , test)
-
---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,,);
-
---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;
-
-# Wait till we reached the initial number of concurrent sessions
---source include/wait_until_count_sessions.inc