diff options
Diffstat (limited to 'mysql-test/main/view_grant.result')
| -rw-r--r-- | mysql-test/main/view_grant.result | 1937 |
1 files changed, 1937 insertions, 0 deletions
diff --git a/mysql-test/main/view_grant.result b/mysql-test/main/view_grant.result new file mode 100644 index 00000000000..82594128d85 --- /dev/null +++ b/mysql-test/main/view_grant.result @@ -0,0 +1,1937 @@ +drop database if exists mysqltest; +drop view if exists v1,v2,v3; +create user test@localhost; +grant create view on test.* to test@localhost; +show grants for test@localhost; +Grants for test@localhost +GRANT USAGE ON *.* TO 'test'@'localhost' +GRANT CREATE VIEW ON `test`.* TO 'test'@'localhost' +revoke create view on test.* from test@localhost; +show grants for test@localhost; +Grants for test@localhost +GRANT USAGE ON *.* TO 'test'@'localhost' +drop user test@localhost; +connect root,localhost,root,,test; +connection root; +create database mysqltest; +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; +create definer=root@localhost view v1 as select * from mysqltest.t1; +ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +create view v1 as select * from mysqltest.t1; +alter view v1 as select * from mysqltest.t1; +ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' +create or replace view v1 as select * from mysqltest.t1; +ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' +create view mysqltest.v2 as select * from mysqltest.t1; +ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' +create view v2 as select * from mysqltest.t2; +ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 't2' +connection root; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` latin1 latin1_swedish_ci +grant create view,drop,select on test.* to mysqltest_1@localhost; +connection user1; +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; +create database mysqltest; +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; +c +select d from mysqltest.v1; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' +connection root; +revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +create database mysqltest; +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; +c +select d from mysqltest.v1; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' +connection root; +revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +connection root; +create database mysqltest; +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; +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; +select c from mysqltest.v1; +c +select c from mysqltest.v2; +c +select c from mysqltest.v3; +c +select c from mysqltest.v4; +c +select c from mysqltest.v5; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v5' +show columns from mysqltest.v1; +Field Type Null Key Default Extra +c bigint(12) YES NULL +d bigint(12) YES NULL +show columns from mysqltest.v2; +Field Type Null Key Default Extra +c bigint(12) YES NULL +d bigint(12) YES NULL +explain select c from mysqltest.v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v1; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' +explain select c from mysqltest.v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v2; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' +explain select c from mysqltest.v3; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v3; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' +explain select c from mysqltest.v4; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v4; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' +explain select c from mysqltest.v5; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v5' +show create view mysqltest.v5; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v5' +connection root; +grant select on mysqltest.v5 to mysqltest_1@localhost; +connection user1; +show create view mysqltest.v5; +View Create View character_set_client collation_connection +v5 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v5` AS select `mysqltest`.`t1`.`a` + 1 AS `c`,`mysqltest`.`t1`.`b` + 1 AS `d` from `mysqltest`.`t1` latin1 latin1_swedish_ci +explain select c from mysqltest.v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v1; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' +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 select c from mysqltest.v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found +show create view mysqltest.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v1` AS select `mysqltest`.`t1`.`a` + 1 AS `c`,`mysqltest`.`t1`.`b` + 1 AS `d` from `mysqltest`.`t1` latin1 latin1_swedish_ci +explain select c from mysqltest.v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v2; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' +explain select c from mysqltest.v3; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v3; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' +explain select c from mysqltest.v4; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v4; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' +explain select c from mysqltest.v5; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v5' +connection root; +grant show view on mysqltest.* to mysqltest_1@localhost; +connection user1; +explain select c from mysqltest.v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found +show create view mysqltest.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v1` AS select `mysqltest`.`t1`.`a` + 1 AS `c`,`mysqltest`.`t1`.`b` + 1 AS `d` from `mysqltest`.`t1` latin1 latin1_swedish_ci +explain select c from mysqltest.v2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 Const row not found +2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +show create view mysqltest.v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v2` AS select `mysqltest`.`t1`.`a` + 1 AS `c`,`mysqltest`.`t1`.`b` + 1 AS `d` from `mysqltest`.`t1` latin1 latin1_swedish_ci +explain select c from mysqltest.v3; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v3; +View Create View character_set_client collation_connection +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v3` AS select `mysqltest`.`t2`.`a` + 1 AS `c`,`mysqltest`.`t2`.`b` + 1 AS `d` from `mysqltest`.`t2` latin1 latin1_swedish_ci +explain select c from mysqltest.v4; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v4; +View Create View character_set_client collation_connection +v4 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v4` AS select `mysqltest`.`t2`.`a` + 1 AS `c`,`mysqltest`.`t2`.`b` + 1 AS `d` from `mysqltest`.`t2` latin1 latin1_swedish_ci +connection root; +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +connection root; +create database mysqltest; +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 t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c; +select * from t1; +a b +13 2 +24 3 +35 4 +46 5 +50 10 +update v1 set a=a+c; +select * from t1; +a b +16 2 +28 3 +40 4 +52 5 +61 10 +update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c; +select * from t1; +a b +16 2 +31 3 +44 4 +57 5 +61 10 +update v2 set a=a+c; +select * from t1; +a b +18 2 +34 3 +48 4 +62 5 +71 10 +update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' +update v2 set c=a+c; +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' +update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' +update v3 set a=a+c; +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' +use test; +connection root; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +connection root; +create database mysqltest; +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; +delete from v1 where c < 4; +select * from t1; +a b +2 3 +3 4 +4 5 +5 10 +delete v1 from t2,v1 where t2.x=v1.c; +select * from t1; +a b +5 10 +delete v2 from t2,v2 where t2.x=v2.c; +ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' +delete from v2 where c < 4; +ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' +use test; +connection root; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +connection root; +create database mysqltest; +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; +insert into v1 values (5,6); +select * from t1; +a b +1 2 +2 3 +5 6 +insert into v1 select x,y from t2; +select * from t1; +a b +1 2 +2 3 +5 6 +3 4 +insert into v2 values (5,6); +ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' +insert into v2 select x,y from t2; +ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' +use test; +connection root; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +connection root; +create database mysqltest; +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; +create view mysqltest.v1 as select * from mysqltest.t1; +ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' +create view v3 as select a from mysqltest.t2; +ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for column 'a' in table 't2' +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; +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; +create view v4 as select b+1 from mysqltest.t2; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't2' +connection root; +grant create view,update,select on test.* to mysqltest_1@localhost; +connection user1; +create view v4 as select b+1 from mysqltest.t2; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table '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; +connection root; +create database mysqltest; +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; +connection root; +create database mysqltest; +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; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` latin1 latin1_swedish_ci +revoke select on mysqltest.t1 from mysqltest_1@localhost; +select * from v1; +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +grant select on mysqltest.t1 to mysqltest_1@localhost; +select * from v1; +a b +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop view v1; +drop database mysqltest; +connection root; +create database mysqltest; +use mysqltest; +create table t1 (a int); +insert into t1 values (1); +create table t2 (s1 int); +drop function if exists f2; +create function f2 () returns int begin declare v int; select s1 from t2 +into v; return v; end// +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; +f2() +NULL +select * from v2; +f2() +NULL +select * from v3; +ERROR HY000: View 'mysqltest.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +select * from v4; +ERROR HY000: View 'mysqltest.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +select * from v5; +ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +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; +connection root; +create database mysqltest; +use mysqltest; +create table t1 (a int); +insert into t1 values (1); +create table t2 (s1 int); +drop function if exists f2; +create function f2 () returns int begin declare v int; select s1 from t2 +into v; return v; end// +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; +select * from v1; +ERROR HY000: View 'mysqltest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +select * from v2; +ERROR HY000: View 'mysqltest.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +select * from v3; +f2() +NULL +select * from v4; +f2() +NULL +select * from v5; +ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +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; +connection root; +create database mysqltest; +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; +select * from v1; +ERROR HY000: View 'mysqltest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +select * from v2; +ERROR HY000: View 'mysqltest.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +select * from v3; +a b +1 1 +select * from v4; +a b +1 1 +select * from v5; +ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +drop table t1; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop database mysqltest; +connection root; +create database mysqltest; +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; +a b +1 1 +select * from v2; +a b +1 1 +select * from v3; +ERROR HY000: View 'mysqltest.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +select * from v4; +ERROR HY000: View 'mysqltest.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +select * from v5; +ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +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; +drop view if exists v1; +drop table if exists t1; +create table t1 as select * from mysql.user where user=''; +delete from mysql.user where user=''; +flush privileges; +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; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` latin1 latin1_swedish_ci +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; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` latin1 latin1_swedish_ci +select definer into @v1def2 from information_schema.views +where table_schema = 'test' and table_name='v1'; +drop view v1; +select @v1def1, @v1def2, @v1def1=@v1def2; +@v1def1 @v1def2 @v1def1=@v1def2 +test14256@% test14256@% 1 +connection root; +disconnect test14256; +drop user test14256; +insert into mysql.user select * from t1; +flush privileges; +drop table t1; +connection root; +create database mysqltest; +use mysqltest; +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci +create user mysqltest_1@localhost; +GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; +connection user1; +use mysqltest; +LOCK TABLES v1 READ; +SHOW CREATE TABLE v1; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' +UNLOCK TABLES; +use test; +connection root; +use test; +drop user mysqltest_1@localhost; +drop database mysqltest; +disconnect user1; +disconnect root; +connection default; +create definer=some_user@`` sql security invoker view v1 as select 1; +Warnings: +Note 1449 The user specified as a definer ('some_user'@'%') does not exist +create definer=some_user@localhost sql security invoker view v2 as select 1; +Warnings: +Note 1449 The user specified as a definer ('some_user'@'localhost') does not exist +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`%` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select 1 AS `1` latin1 latin1_swedish_ci +drop view v1; +drop view v2; +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; +SELECT * FROM mysqltest1.v_t1; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +INSERT INTO mysqltest1.v_t1 VALUES(4); +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DELETE FROM mysqltest1.v_t1 WHERE x = 1; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +UPDATE mysqltest1.v_t1 SET x = 3; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DELETE FROM mysqltest1.v_t1; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SELECT 1 FROM mysqltest1.v_t1; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SELECT * FROM mysqltest1.t1; +ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 't1' +SELECT * FROM mysqltest1.v_ts; +x +1 +2 +SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x; +ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 't1' +SELECT * FROM mysqltest1.v_ti; +ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 'v_ti' +INSERT INTO mysqltest1.v_ts VALUES (100); +ERROR 42000: INSERT command denied to user 'readonly'@'localhost' for table 'v_ts' +INSERT INTO mysqltest1.v_ti VALUES (100); +UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100; +ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table 'v_ts' +UPDATE mysqltest1.v_ts SET x= 200; +ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table 'v_ts' +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; +DELETE FROM mysqltest1.v_ts WHERE x= 200; +ERROR 42000: DELETE command denied to user 'readonly'@'localhost' for table 'v_ts' +DELETE FROM mysqltest1.v_ts; +ERROR 42000: DELETE command denied to user 'readonly'@'localhost' for table 'v_ts' +DELETE FROM mysqltest1.v_td WHERE x= 200; +ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for column 'x' in table 'v_td' +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; +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; +Warnings: +Note 1449 The user specified as a definer ('no-such-user'@'localhost') does not exist +SHOW CREATE VIEW v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`no-such-user`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `test`.`t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +Warnings: +Note 1449 The user specified as a definer ('no-such-user'@'localhost') does not exist +SELECT * FROM v; +ERROR HY000: The user specified as a definer ('no-such-user'@'localhost') does not exist +DROP VIEW v; +DROP TABLE t1; +USE test; +CREATE USER mysqltest_db1@localhost identified by 'PWD'; +GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION; +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; +View Create View character_set_client collation_connection +view1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci +CREATE VIEW view2 AS +SELECT * FROM view1; +# Here comes a suspicious warning +SHOW CREATE VIEW view2; +View Create View character_set_client collation_connection +view2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view2` AS select `view1`.`f1` AS `f1` from `view1` latin1 latin1_swedish_ci +# But the view view2 is usable +SELECT * FROM view2; +f1 +CREATE VIEW view3 AS +SELECT * FROM view2; +SELECT * from view3; +f1 +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; +CREATE DATABASE test1; +CREATE DATABASE test2; +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; +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; +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(); +CREATE FUNCTION f2() RETURNS VARCHAR(77) SQL SECURITY INVOKER +BEGIN +DECLARE cu VARCHAR(77); +CALL p1(cu); +RETURN cu; +END| +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,,; + +The following tests should all return 1. + +SELECT CURRENT_USER() = 'mysqltest_u1@localhost'; +CURRENT_USER() = 'mysqltest_u1@localhost' +1 +SELECT f1() = 'mysqltest_u1@localhost'; +f1() = 'mysqltest_u1@localhost' +1 +CALL p1(@cu); +SELECT @cu = 'mysqltest_u1@localhost'; +@cu = 'mysqltest_u1@localhost' +1 +SELECT f2() = 'mysqltest_u1@localhost'; +f2() = 'mysqltest_u1@localhost' +1 +SELECT cu = 'root@localhost' FROM v1; +cu = 'root@localhost' +1 +SELECT cu = 'root@localhost' FROM v2; +cu = 'root@localhost' +1 +SELECT cu = 'root@localhost' FROM v3; +cu = 'root@localhost' +1 +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; +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; +for a user +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'inv_17254'@'localhost' for table 'v1' +connection root; +for a superuser +SELECT * FROM v1; +ERROR HY000: The user specified as a definer ('def_17254'@'localhost') does not exist +DROP USER inv_17254@localhost; +DROP DATABASE db17254; +disconnect def; +disconnect inv; +DROP DATABASE IF EXISTS mysqltest_db1; +DROP DATABASE IF EXISTS mysqltest_db2; +DROP USER mysqltest_u1; +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); +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; +i s +1 public +PREPARE stmt1 FROM "SELECT * FROM mysqltest_db1.t2"; +EXECUTE stmt1; +s +public +PREPARE stmt2 FROM "SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2"; +EXECUTE stmt2; +i s +1 public +connection conn1; +REVOKE SELECT ON t2 FROM mysqltest_u2@localhost; +UPDATE t2 SET s = 'private' WHERE s = 'public'; +connection conn2; +SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2; +ERROR 42000: SELECT command denied to user 'mysqltest_u2'@'localhost' for table 't2' +EXECUTE stmt1; +ERROR 42000: SELECT command denied to user 'mysqltest_u2'@'localhost' for table 't2' +EXECUTE stmt2; +ERROR 42000: SELECT command denied to user 'mysqltest_u2'@'localhost' for table 't2' +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; +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; +ALTER VIEW v1 AS SELECT f2 FROM t1; +ERROR 42000: CREATE VIEW command denied to user 'u26813'@'localhost' for table 'v1' +ALTER VIEW v2 AS SELECT f2 FROM t1; +ERROR 42000: DROP command denied to user 'u26813'@'localhost' for table 'v2' +ALTER VIEW v3 AS SELECT f2 FROM t1; +ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +connection root; +SHOW CREATE VIEW v3; +View Create View character_set_client collation_connection +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci +DROP USER u26813@localhost; +DROP DATABASE db26813; +disconnect u1; +# +# Bug#29908 A user can gain additional access through the ALTER VIEW. +# +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; +ALTER VIEW v1 AS SELECT f2 FROM t1; +ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +ALTER VIEW v2 AS SELECT f2 FROM t1; +ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +SHOW CREATE VIEW v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci +connect u1,localhost,u29908_1,,mysqltest_29908; +ALTER VIEW v1 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f2` AS `f2` from `t1` latin1 latin1_swedish_ci +ALTER VIEW v2 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f2` AS `f2` from `t1` latin1 latin1_swedish_ci +connection root; +ALTER VIEW v1 AS SELECT f1 FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci +ALTER VIEW v2 AS SELECT f1 FROM t1; +SHOW CREATE VIEW v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci +DROP USER u29908_1@localhost; +DROP USER u29908_2@localhost; +DROP DATABASE mysqltest_29908; +disconnect u1; +disconnect u2; +####################################################################### +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +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; +c1 +11 +12 +13 +14 +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; +c1 +11 +12 +13 +14 +INSERT INTO v2 VALUES(26); +UPDATE v3 SET c3 = 332 WHERE c3 = 32; +DELETE FROM v4 WHERE c4 = 43; +CREATE VIEW v12 AS SELECT c1, c2 FROM mysqltest1.t1, mysqltest1.t2; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v12' +CREATE VIEW v13 AS SELECT c1, c3 FROM mysqltest1.t1, mysqltest1.t3; +CREATE VIEW v14 AS SELECT c1, c4 FROM mysqltest1.t1, mysqltest1.t4; +CREATE VIEW v21 AS SELECT c2, c1 FROM mysqltest1.t2, mysqltest1.t1; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c1' in table 'v21' +CREATE VIEW v23 AS SELECT c2, c3 FROM mysqltest1.t2, mysqltest1.t3; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c3' in table 'v23' +CREATE VIEW v24 AS SELECT c2, c4 FROM mysqltest1.t2, mysqltest1.t4; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c4' in table 'v24' +CREATE VIEW v31 AS SELECT c3, c1 FROM mysqltest1.t3, mysqltest1.t1; +CREATE VIEW v32 AS SELECT c3, c2 FROM mysqltest1.t3, mysqltest1.t2; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v32' +CREATE VIEW v34 AS SELECT c3, c4 FROM mysqltest1.t3, mysqltest1.t4; +CREATE VIEW v41 AS SELECT c4, c1 FROM mysqltest1.t4, mysqltest1.t1; +CREATE VIEW v42 AS SELECT c4, c2 FROM mysqltest1.t4, mysqltest1.t2; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v42' +CREATE VIEW v43 AS SELECT c4, c3 FROM mysqltest1.t4, mysqltest1.t3; +connection default; +SELECT * FROM mysqltest1.t1; +c1 +11 +12 +13 +14 +SELECT * FROM mysqltest1.t2; +c2 +21 +22 +23 +24 +25 +26 +SELECT * FROM mysqltest1.t3; +c3 +331 +332 +33 +34 +SELECT * FROM mysqltest1.t4; +c4 +41 +42 +disconnect bug24040_con; +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +DROP USER mysqltest_u1@localhost; +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; +f1 +SELECT f2 FROM t1; +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'f2' in table 't1' +SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1' +SELECT f1 FROM v1; +f1 +SELECT f2 FROM v1; +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'f2' in table 'v1' +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table '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; +Bug #11765687/#58677: +No privilege on table/view, but can know #rows / underlying table's name +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; +... 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; +... as bob +connect test11765687,localhost,bob,,mysqltest1; +connection test11765687; +select * from v1; +i +explain select * from v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +disconnect test11765687; +... as cecil +connect test11765687,localhost,cecil,,mysqltest1; +connection test11765687; +select * from v1; +ERROR 42000: SELECT command denied to user 'cecil'@'localhost' for table 'v1' +explain select * from v1; +ERROR 42000: SELECT command denied to user 'cecil'@'localhost' for table 'v1' +disconnect test11765687; +... as dan +connect test11765687,localhost,dan,,mysqltest1; +connection test11765687; +select * from v1; +i +explain select * from v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found +disconnect test11765687; +... as eugene +connect test11765687,localhost,eugene,,mysqltest1; +connection test11765687; +select * from v1; +i +explain select * from v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +disconnect test11765687; +... as fiona +connect test11765687,localhost,fiona,,mysqltest1; +connection test11765687; +select * from v2; +i j +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`alice`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `v1`.`i` AS `i`,`t2`.`j` AS `j` from (`v1` join `t2`) latin1 latin1_swedish_ci +explain select * from t1; +ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 't1' +explain select * from v1; +ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 'v1' +explain select * from t2; +ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 't2' +explain select * from v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +disconnect test11765687; +... as greg +connect test11765687,localhost,greg,,mysqltest1; +connection test11765687; +select * from v2; +i j +explain select * from v1; +ERROR 42000: SELECT command denied to user 'greg'@'localhost' for table 'v1' +explain select * from v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +disconnect test11765687; +... as han +connect test11765687,localhost,han,,mysqltest1; +connection test11765687; +select * from t3; +ERROR 42000: SELECT command denied to user 'han'@'localhost' for table 't3' +explain select * from t3; +ERROR 42000: SELECT command denied to user 'han'@'localhost' for table 't3' +select k from t3; +k +explain select k from t3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 system NULL NULL NULL NULL 0 Const row not found +select * from v3; +k +explain select * from v3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 system NULL NULL NULL NULL 0 Const row not found +disconnect test11765687; +... as inga +connect test11765687,localhost,inga,,mysqltest1; +connection test11765687; +select * from v2; +i j +explain select * from v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +disconnect test11765687; +... as jamie +connect test11765687,localhost,jamie,,mysqltest1; +connection test11765687; +select * from v2; +i j +explain select * from v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +disconnect test11765687; +... as karl +connect test11765687,localhost,karl,,mysqltest1; +connection test11765687; +select * from v2; +i j +explain select * from v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +disconnect test11765687; +... as lena +connect test11765687,localhost,lena,,mysqltest1; +connection test11765687; +select * from v2; +ERROR 42000: SELECT command denied to user 'lena'@'localhost' for table 'v2' +explain select * from v2; +ERROR 42000: SELECT command denied to user 'lena'@'localhost' for table 'v2' +disconnect test11765687; +... as mhairi +connect test11765687,localhost,mhairi,,mysqltest1; +connection test11765687; +select * from v2; +i j +explain select * from v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found +1 SIMPLE t2 system NULL NULL NULL NULL 0 Const row not found +disconnect test11765687; +... as noam +connect test11765687,localhost,noam,,mysqltest1; +connection test11765687; +select * from v2; +i j +explain select * from v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +disconnect test11765687; +... as olga +connect test11765687,localhost,olga,,mysqltest1; +connection test11765687; +select * from v2; +i j +explain select * from v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +disconnect test11765687; +... as pjotr +connect test11765687,localhost,pjotr,,mysqltest1; +connection test11765687; +select * from v2; +i j +explain select * from v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +disconnect test11765687; +... as quintessa +connect test11765687,localhost,quintessa,,mysqltest1; +connection test11765687; +select * from v1; +i +explain select * from v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +disconnect test11765687; +... 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; +select * from information_schema.table_privileges; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +End of 5.0 tests. +connection default; +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1; +ALTER VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci +ALTER DEFINER=no_such@user_1 VIEW v1 AS SELECT * FROM t1; +Warnings: +Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci +Warnings: +Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist +ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +Warnings: +Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=MERGE DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci +Warnings: +Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist +ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; +Warnings: +Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`no_such`@`user_2` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci +Warnings: +Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist +DROP VIEW v1; +DROP TABLE t1; +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; +This would lead to failed assertion. +CREATE VIEW v1 AS SELECT a, b FROM t1, t2; +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 'v1' +SELECT b FROM v1; +ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 'v1' +disconnect connection1; +connection default; +DROP TABLE t1, t2; +DROP VIEW v1; +DROP DATABASE mysqltest1; +DROP USER mysqluser1@localhost; +USE test; +End of 5.1 tests. +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; +SELECT * FROM mysqltest1.v1; +ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 'v1' +CREATE VIEW v1 AS SELECT * FROM mysqltest1.t2; +ERROR 42000: ANY command denied to user 'mysqluser1'@'localhost' for table 't2' +disconnect connection1; +connection default; +DROP TABLE t1, t2; +DROP VIEW v1; +DROP DATABASE mysqltest1; +DROP USER mysqluser1@localhost; +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; +EXECUTE stmt_v1; +ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 'v1' +EXECUTE stmt_v2; +ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 'v2' +disconnect connection1; +connect connection2, localhost, mysqluser1,,; +PREPARE stmt FROM "SELECT a FROM v3"; +EXECUTE stmt; +a +1 +disconnect connection2; +connection default; +DROP VIEW v1, v2; +DROP DATABASE mysqltest1; +DROP VIEW test.v3; +DROP USER mysqluser1@localhost; +USE test; +# +# Bug#35996: SELECT + SHOW VIEW should be enough to display view +# definition +# +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; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_t2; +View Create View character_set_client collation_connection +v_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v2; +View Create View character_set_client collation_connection +v_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t2; +View Create View character_set_client collation_connection +v_mysqluser1_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v2; +View Create View character_set_client collation_connection +v_mysqluser1_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +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; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_t2; +View Create View character_set_client collation_connection +v_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v2; +View Create View character_set_client collation_connection +v_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t2; +View Create View character_set_client collation_connection +v_mysqluser1_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v2; +View Create View character_set_client collation_connection +v_mysqluser1_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +connection default; +# Testing the case when the views reference missing objects. +# Obviously, there are no privileges to check for, so we +# need only each object type once. +DROP TABLE t1; +DROP FUNCTION f1; +DROP VIEW v1; +connection connection1; +SHOW CREATE VIEW mysqltest1.v_t1; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +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; +SHOW CREATE VIEW mysqltest1.v_t1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_t1' +SHOW CREATE VIEW mysqltest1.v_f1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_f1' +SHOW CREATE VIEW mysqltest1.v_v1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_v1' +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +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; +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +DROP TABLE t1; +DROP VIEW v1; +# +# Bug #46019: ERROR 1356 When selecting from within another +# view that has Group By +# +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; +a +SELECT a FROM v2; +a +connection default; +disconnect mysqluser1; +DROP USER mysqluser1; +DROP DATABASE mysqltest1; +USE test; +# +# Bug#47734: Assertion failed: ! is_set() when locking a view with non-existing definer +# +DROP VIEW IF EXISTS v1; +CREATE DEFINER=`unknown`@`unknown` SQL SECURITY DEFINER VIEW v1 AS SELECT 1; +Warnings: +Note 1449 The user specified as a definer ('unknown'@'unknown') does not exist +LOCK TABLES v1 READ; +ERROR HY000: The user specified as a definer ('unknown'@'unknown') does not exist +DROP VIEW v1; +# +# Bug #58499 "DEFINER-security view selecting from INVOKER-security view +# access check wrong". +# +# Check that we correctly handle privileges for various combinations +# of INVOKER and DEFINER-security views using each other. +DROP DATABASE IF EXISTS mysqltest1; +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); +# +# 1) DEFINER-security view uses INVOKER-security view (covers +# 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'@'%'; +# 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'@'%'; +# Make 'mysqluser1' unable to access t2. +REVOKE SELECT ON t2 FROM 'mysqluser1'@'%'; +connect mysqluser2, localhost, mysqluser2,,mysqltest1; +# The below statement should succeed thanks to suid nature of v2_uses_t1. +SELECT * FROM v2_uses_t1; +i +1 +# The below statement should fail due to suid nature of v2_uses_t2. +SELECT * FROM v2_uses_t2; +ERROR HY000: View 'mysqltest1.v2_uses_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +# +# 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; +# For both versions of 'v2' 'mysqluser1' privileges should be used. +SELECT * FROM v2_uses_t1; +i +1 +SELECT * FROM v2_uses_t2; +ERROR HY000: View 'mysqltest1.v2_uses_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +connection mysqluser2; +# And now for both versions of 'v2' 'mysqluser2' privileges should +# be used. +SELECT * FROM v2_uses_t1; +ERROR HY000: View 'mysqltest1.v2_uses_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SELECT * FROM v2_uses_t2; +j +2 +# +# 3) INVOKER-security view uses DEFINER-security view. +connection default; +DROP VIEW v1_uses_t1, v1_uses_t2; +# 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; +# Make 'mysqluser1' unable to access t2. +REVOKE SELECT ON t2 FROM 'mysqluser1'@'%'; +connection mysqluser2; +# Due to suid nature of v1_uses_t1 and v1_uses_t2 the first +# select should succeed and the second select should fail. +SELECT * FROM v2_uses_t1; +i +1 +SELECT * FROM v2_uses_t2; +ERROR HY000: View 'mysqltest1.v2_uses_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +# +# 4) DEFINER-security view uses DEFINER-security view. +connection default; +DROP VIEW v2_uses_t1, v2_uses_t2; +# 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; +# Make 'mysqluser1' unable to access t2. +REVOKE SELECT ON t2 FROM 'mysqluser1'@'%'; +connection mysqluser2; +# Again privileges of creator of innermost views should apply. +SELECT * FROM v2_uses_t1; +i +1 +SELECT * FROM v2_uses_t2; +ERROR HY000: View 'mysqltest1.v2_uses_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +disconnect mysqluser1; +disconnect mysqluser2; +connection default; +USE test; +DROP DATABASE mysqltest1; +DROP USER 'mysqluser1'@'%'; +DROP USER 'mysqluser2'@'%'; +# +# Test for bug #11766767 - "59957: VIEW USING MERGE PERMISSIONS +# IN MULTI-TABLE UPDATE". +# +drop database if exists mysqltest1; +drop database if exists mysqltest2; +# +# 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,,; +# +# A) Check how we handle privilege checking in multi-update for +# directly used views. +# +# A.1) Originally reported problem, view is used in read-only mode. +# This should work with only SELECT privilege for both mergeable +# 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; +# +# A.2) If view is updated an UPDATE privilege on it is required. +# Temptable views can't be updated. +update mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 set v1.val= 'test5' + where t2.id= v1.id; +ERROR 42000: UPDATE command denied to user 'user_11766767'@'localhost' for table 'v1_merge' +update mysqltest1.t1 as t1, mysqltest2.v2_merge as v2 set v2.val= 'test6' + where t1.id= v2.id; +# +# Note that the below error is OK even though user lacks UPDATE +# privilege on v1_temp since he/she still has SELECT privilege on +# this view. +update mysqltest2.t2 as t2, mysqltest1.v1_temp as v1 set v1.val= 'test7' + where t2.id= v1.id; +ERROR HY000: The target table v1 of the UPDATE is not updatable +update mysqltest1.t1 as t1, mysqltest2.v2_temp as v2 set v2.val= 'test8' + where t1.id= v2.id; +ERROR HY000: The target table v2 of the UPDATE is not updatable +# +# A.3) This also works for correctly for multi-table views. +# 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; +# When one of view's tables is updated, UPDATE is required +# on a view. +update mysqltest2.t2 as t2, mysqltest1.v11_merge as v11 set v11.val= 'test11' + where t2.id= v11.id; +ERROR 42000: UPDATE command denied to user 'user_11766767'@'localhost' for table 'v11_merge' +update mysqltest1.t1 as t1, mysqltest2.v21_merge as v21 set v21.val= 'test12' + where t1.id= v21.id; +# As before, temptable views are not updateable. +update mysqltest2.t2 as t2, mysqltest1.v11_temp as v11 set v11.val= 'test13' + where t2.id= v11.id; +ERROR HY000: The target table v11 of the UPDATE is not updatable +update mysqltest1.t1 as t1, mysqltest2.v21_temp as v21 set v21.val= 'test14' + where t1.id= v21.id; +ERROR HY000: The target table v21 of the UPDATE is not updatable +# +# B) Now check that correct privileges are required on underlying +# tables. To simplify this part of test we will use SECURITY +# INVOKER views in it. +# +# B.1) In case when view is used for read only it is enough to have +# 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; +# +# B.2) If view is updated, UPDATE privilege on the table being updated +# is required (since we already checked that temptable views are +# not updateable we don't test them here). +update mysqltest2.t2 as t2, mysqltest2.v3_merge as v3 set v3.val= 'test17' + where t2.id= v3.id; +ERROR HY000: View 'mysqltest2.v3_merge' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +update mysqltest1.t11 as t11, mysqltest2.v31 as v31 set v31.val= 'test18' + where t11.id= v31.id; +# +# C) Finally, check how we handle privilege checking in case when +# view is used through another view. Again we will use SECURITY +# INVOKER views for simplicity. +# +# C.1) As usual, when a view used by another view is going to be used +# 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; +# +# C.2) If one of underlying tables of the view is updated then +# UPDATE on a view is necessary. +update mysqltest1.t11 as t11, mysqltest2.v41 as v4 set v4.val= 'test20' + where t11.id= v4.id; +ERROR HY000: View 'mysqltest2.v41' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +update mysqltest1.t11 as t11, mysqltest2.v42 as v4 set v4.val= 'test20' + where t11.id= v4.id; +# +# Clean-up. +# +disconnect conn_11766767; +connection default; +drop user user_11766767; +drop database mysqltest1; +drop database mysqltest2; |