Edit File: view_grant.result
drop database if exists mysqltest; drop view if exists v1,v2,v3; 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; create database mysqltest; create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; 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' 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; use test; alter view v1 as select * from mysqltest.t1; create or replace view v1 as select * from mysqltest.t1; 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; 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' revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; 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; grant select (c) on mysqltest.v1 to mysqltest_1@localhost; 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' revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; drop database mysqltest; 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; 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; 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: 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: 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: 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: 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' grant select on mysqltest.v5 to mysqltest_1@localhost; 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: 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' 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; 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: 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: 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: 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' grant show view on mysqltest.* to mysqltest_1@localhost; 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: 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: 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 revoke all privileges on mysqltest.* from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; drop database mysqltest; 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; 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; 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; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; 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; grant delete on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; 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; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; 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; grant insert on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; 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; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; create database mysqltest; create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); grant update on mysqltest.t1 to mysqltest_1@localhost; grant update(b) on mysqltest.t2 to mysqltest_1@localhost; grant create view,update on test.* to mysqltest_1@localhost; 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' create table mysqltest.v3 (b int); grant create view on mysqltest.v3 to mysqltest_1@localhost; drop table mysqltest.v3; create view mysqltest.v3 as select b from mysqltest.t2; grant create view, update on mysqltest.v3 to mysqltest_1@localhost; drop view mysqltest.v3; 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' grant create view,update,select on test.* to mysqltest_1@localhost; 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' grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost; create view v4 as select b+1 from mysqltest.t2; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; drop view v1,v2,v4; create database mysqltest; create table mysqltest.t1 (a int); grant all privileges on mysqltest.* to mysqltest_1@localhost; use mysqltest; create view v1 as select * from t1; use test; revoke all privileges on mysqltest.* from mysqltest_1@localhost; drop database mysqltest; create database mysqltest; create table mysqltest.t1 (a int, b int); grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; create view v1 as select * from mysqltest.t1; 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 view v1; drop database mysqltest; 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; 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; 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; 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 database mysqltest; 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// 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; 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; 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 database mysqltest; create database mysqltest; use mysqltest; create table t1 (a int); create table v1 (a int); insert into t1 values (1); 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; 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; 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 database mysqltest; 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; 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; 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; drop view v1, v2, v3, v4, v5; drop table t1; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 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; grant all on test.* to '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 drop user test14256; insert into mysql.user select * from t1; flush privileges; drop table t1; 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 GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; 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; use test; drop user mysqltest_1@localhost; drop database mysqltest; 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; 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; 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; 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 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; 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 DROP VIEW v3; DROP FUNCTION f2; DROP PROCEDURE p1; DROP FUNCTION f1; DROP VIEW v2; DROP VIEW v1; DROP USER mysqltest_u1@localhost; 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; CREATE VIEW v1 AS SELECT * FROM t1; DROP USER def_17254@localhost; for a user SELECT * FROM v1; ERROR 42000: SELECT command denied to user 'inv_17254'@'localhost' for table 'v1' 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; 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; 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; 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 REVOKE SELECT ON t2 FROM mysqltest_u2@localhost; UPDATE t2 SET s = 'private' WHERE s = 'public'; 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' 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; 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; 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 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; # # Bug#29908 A user can gain additional access through the ALTER VIEW. # 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; 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 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 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; ####################################################################### 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); 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; ---> connection: bug24040_con 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 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; GRANT SELECT (f1) ON t1 TO foo; GRANT SELECT (f1) ON v1 TO foo; 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' 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; Bug #11765687/#58677: No privilege on table/view, but can know #rows / underlying table's name 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 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; ... as bob select * from v1; i explain select * from v1; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ... as cecil 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' ... as dan 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 ... as eugene select * from v1; i explain select * from v1; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ... as fiona 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: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ... as greg 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: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ... as han 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 ... as inga select * from v2; i j explain select * from v2; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ... as jamie select * from v2; i j explain select * from v2; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ... as karl select * from v2; i j explain select * from v2; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ... as lena 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' ... as mhairi 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 ... as noam select * from v2; i j explain select * from v2; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ... as olga select * from v2; i j explain select * from v2; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ... as pjotr select * from v2; i j explain select * from v2; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ... as quintessa select * from v1; i explain select * from v1; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ... as root again at last: clean-up time! 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; End of 5.0 tests. 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; 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' 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; 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' 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; GRANT SELECT ON mysqltest1.* to mysqluser1@localhost; GRANT ALL ON test.* TO mysqluser1@localhost; PREPARE stmt_v1 FROM "SELECT * FROM mysqltest1.v1"; PREPARE stmt_v2 FROM "SELECT * FROM mysqltest1.v2"; REVOKE SELECT ON mysqltest1.* FROM mysqluser1@localhost; 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' PREPARE stmt FROM "SELECT a FROM v3"; EXECUTE stmt; a 1 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; 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 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; 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 # 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; 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 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; 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 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; SELECT a FROM v1; a SELECT a FROM v2; a 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'@'%'; # # Connection 'mysqluser1'. 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'@'%'; # # Connection 'mysqluser2'. # 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 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 as 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. # # Switching to connection 'default'. drop user user_11766767; drop database mysqltest1; drop database mysqltest2;