1drop database if exists mysqltest; 2drop view if exists v1,v2,v3; 3grant create view on test.* to test@localhost; 4show grants for test@localhost; 5Grants for test@localhost 6GRANT USAGE ON *.* TO 'test'@'localhost' 7GRANT CREATE VIEW ON `test`.* TO 'test'@'localhost' 8revoke create view on test.* from test@localhost; 9show grants for test@localhost; 10Grants for test@localhost 11GRANT USAGE ON *.* TO 'test'@'localhost' 12drop user test@localhost; 13create database mysqltest; 14create table mysqltest.t1 (a int, b int); 15create table mysqltest.t2 (a int, b int); 16grant select on mysqltest.t1 to mysqltest_1@localhost; 17grant create view,select on test.* to mysqltest_1@localhost; 18create definer=root@localhost view v1 as select * from mysqltest.t1; 19ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 20create view v1 as select * from mysqltest.t1; 21alter view v1 as select * from mysqltest.t1; 22ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' 23create or replace view v1 as select * from mysqltest.t1; 24ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' 25create view mysqltest.v2 as select * from mysqltest.t1; 26ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' 27create view v2 as select * from mysqltest.t2; 28ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 't2' 29show create view v1; 30View Create View character_set_client collation_connection 31v1 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 32grant create view,drop,select on test.* to mysqltest_1@localhost; 33use test; 34alter view v1 as select * from mysqltest.t1; 35create or replace view v1 as select * from mysqltest.t1; 36revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; 37revoke all privileges on test.* from mysqltest_1@localhost; 38drop database mysqltest; 39drop view test.v1; 40create database mysqltest; 41create table mysqltest.t1 (a int, b int); 42create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; 43grant select (c) on mysqltest.v1 to mysqltest_1@localhost; 44select c from mysqltest.v1; 45c 46select d from mysqltest.v1; 47ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' 48revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; 49delete from mysql.user where user='mysqltest_1'; 50drop database mysqltest; 51create database mysqltest; 52create table mysqltest.t1 (a int, b int); 53create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; 54grant select (c) on mysqltest.v1 to mysqltest_1@localhost; 55select c from mysqltest.v1; 56c 57select d from mysqltest.v1; 58ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' 59revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; 60delete from mysql.user where user='mysqltest_1'; 61drop database mysqltest; 62create database mysqltest; 63create table mysqltest.t1 (a int, b int); 64create table mysqltest.t2 (a int, b int); 65create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; 66create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1; 67create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; 68create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; 69create view mysqltest.v5 (c,d) as select a+1,b+1 from mysqltest.t1; 70grant select on mysqltest.v1 to mysqltest_1@localhost; 71grant select on mysqltest.v2 to mysqltest_1@localhost; 72grant select on mysqltest.v3 to mysqltest_1@localhost; 73grant select on mysqltest.v4 to mysqltest_1@localhost; 74grant show view on mysqltest.v5 to mysqltest_1@localhost; 75select c from mysqltest.v1; 76c 77select c from mysqltest.v2; 78c 79select c from mysqltest.v3; 80c 81select c from mysqltest.v4; 82c 83select c from mysqltest.v5; 84ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v5' 85show columns from mysqltest.v1; 86Field Type Null Key Default Extra 87c bigint(12) YES NULL 88d bigint(12) YES NULL 89show columns from mysqltest.v2; 90Field Type Null Key Default Extra 91c bigint(12) YES NULL 92d bigint(12) YES NULL 93explain select c from mysqltest.v1; 94ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 95show create view mysqltest.v1; 96ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' 97explain select c from mysqltest.v2; 98ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 99show create view mysqltest.v2; 100ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' 101explain select c from mysqltest.v3; 102ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 103show create view mysqltest.v3; 104ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' 105explain select c from mysqltest.v4; 106ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 107show create view mysqltest.v4; 108ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' 109explain select c from mysqltest.v5; 110ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v5' 111show create view mysqltest.v5; 112ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v5' 113grant select on mysqltest.v5 to mysqltest_1@localhost; 114show create view mysqltest.v5; 115View Create View character_set_client collation_connection 116v5 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 117explain select c from mysqltest.v1; 118ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 119show create view mysqltest.v1; 120ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' 121grant show view on mysqltest.v1 to mysqltest_1@localhost; 122grant select on mysqltest.t1 to mysqltest_1@localhost; 123revoke select on mysqltest.v5 from mysqltest_1@localhost; 124explain select c from mysqltest.v1; 125id select_type table type possible_keys key key_len ref rows Extra 1261 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found 127show create view mysqltest.v1; 128View Create View character_set_client collation_connection 129v1 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 130explain select c from mysqltest.v2; 131ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 132show create view mysqltest.v2; 133ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' 134explain select c from mysqltest.v3; 135ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 136show create view mysqltest.v3; 137ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' 138explain select c from mysqltest.v4; 139ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 140show create view mysqltest.v4; 141ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' 142explain select c from mysqltest.v5; 143ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v5' 144grant show view on mysqltest.* to mysqltest_1@localhost; 145explain select c from mysqltest.v1; 146id select_type table type possible_keys key key_len ref rows Extra 1471 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found 148show create view mysqltest.v1; 149View Create View character_set_client collation_connection 150v1 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 151explain select c from mysqltest.v2; 152id select_type table type possible_keys key key_len ref rows Extra 1531 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 1542 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 155show create view mysqltest.v2; 156View Create View character_set_client collation_connection 157v2 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 158explain select c from mysqltest.v3; 159ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 160show create view mysqltest.v3; 161View Create View character_set_client collation_connection 162v3 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 163explain select c from mysqltest.v4; 164ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 165show create view mysqltest.v4; 166View Create View character_set_client collation_connection 167v4 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 168revoke all privileges on mysqltest.* from mysqltest_1@localhost; 169delete from mysql.user where user='mysqltest_1'; 170drop database mysqltest; 171create database mysqltest; 172create table mysqltest.t1 (a int, b int, primary key(a)); 173insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10); 174create table mysqltest.t2 (x int); 175insert into mysqltest.t2 values (3), (4), (5), (6); 176create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; 177create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; 178create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; 179grant update (a) on mysqltest.v2 to mysqltest_1@localhost; 180grant update on mysqltest.v1 to mysqltest_1@localhost; 181grant select on mysqltest.* to mysqltest_1@localhost; 182use mysqltest; 183update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c; 184select * from t1; 185a b 18613 2 18724 3 18835 4 18946 5 19050 10 191update v1 set a=a+c; 192select * from t1; 193a b 19416 2 19528 3 19640 4 19752 5 19861 10 199update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c; 200select * from t1; 201a b 20216 2 20331 3 20444 4 20557 5 20661 10 207update v2 set a=a+c; 208select * from t1; 209a b 21018 2 21134 3 21248 4 21362 5 21471 10 215update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; 216ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' 217update v2 set c=a+c; 218ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' 219update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; 220ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' 221update v3 set a=a+c; 222ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' 223use test; 224REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; 225drop database mysqltest; 226create database mysqltest; 227create table mysqltest.t1 (a int, b int, primary key(a)); 228insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10); 229create table mysqltest.t2 (x int); 230insert into mysqltest.t2 values (3), (4), (5), (6); 231create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; 232create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1; 233grant delete on mysqltest.v1 to mysqltest_1@localhost; 234grant select on mysqltest.* to mysqltest_1@localhost; 235use mysqltest; 236delete from v1 where c < 4; 237select * from t1; 238a b 2392 3 2403 4 2414 5 2425 10 243delete v1 from t2,v1 where t2.x=v1.c; 244select * from t1; 245a b 2465 10 247delete v2 from t2,v2 where t2.x=v2.c; 248ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' 249delete from v2 where c < 4; 250ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' 251use test; 252REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; 253drop database mysqltest; 254create database mysqltest; 255create table mysqltest.t1 (a int, b int, primary key(a)); 256insert into mysqltest.t1 values (1,2), (2,3); 257create table mysqltest.t2 (x int, y int); 258insert into mysqltest.t2 values (3,4); 259create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1; 260create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; 261grant insert on mysqltest.v1 to mysqltest_1@localhost; 262grant select on mysqltest.* to mysqltest_1@localhost; 263use mysqltest; 264insert into v1 values (5,6); 265select * from t1; 266a b 2671 2 2682 3 2695 6 270insert into v1 select x,y from t2; 271select * from t1; 272a b 2731 2 2742 3 2755 6 2763 4 277insert into v2 values (5,6); 278ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' 279insert into v2 select x,y from t2; 280ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' 281use test; 282REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; 283drop database mysqltest; 284create database mysqltest; 285create table mysqltest.t1 (a int, b int); 286create table mysqltest.t2 (a int, b int); 287grant update on mysqltest.t1 to mysqltest_1@localhost; 288grant update(b) on mysqltest.t2 to mysqltest_1@localhost; 289grant create view,update on test.* to mysqltest_1@localhost; 290create view v1 as select * from mysqltest.t1; 291create view v2 as select b from mysqltest.t2; 292create view mysqltest.v1 as select * from mysqltest.t1; 293ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' 294create view v3 as select a from mysqltest.t2; 295ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for column 'a' in table 't2' 296create table mysqltest.v3 (b int); 297grant create view on mysqltest.v3 to mysqltest_1@localhost; 298drop table mysqltest.v3; 299create view mysqltest.v3 as select b from mysqltest.t2; 300grant create view, update on mysqltest.v3 to mysqltest_1@localhost; 301drop view mysqltest.v3; 302create view mysqltest.v3 as select b from mysqltest.t2; 303create view v4 as select b+1 from mysqltest.t2; 304ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't2' 305grant create view,update,select on test.* to mysqltest_1@localhost; 306create view v4 as select b+1 from mysqltest.t2; 307ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't2' 308grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost; 309create view v4 as select b+1 from mysqltest.t2; 310REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; 311drop database mysqltest; 312drop view v1,v2,v4; 313create database mysqltest; 314create table mysqltest.t1 (a int); 315grant all privileges on mysqltest.* to mysqltest_1@localhost; 316use mysqltest; 317create view v1 as select * from t1; 318use test; 319revoke all privileges on mysqltest.* from mysqltest_1@localhost; 320drop database mysqltest; 321create database mysqltest; 322create table mysqltest.t1 (a int, b int); 323grant select on mysqltest.t1 to mysqltest_1@localhost; 324grant create view,select on test.* to mysqltest_1@localhost; 325create view v1 as select * from mysqltest.t1; 326show create view v1; 327View Create View character_set_client collation_connection 328v1 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 329revoke select on mysqltest.t1 from mysqltest_1@localhost; 330select * from v1; 331ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 332grant select on mysqltest.t1 to mysqltest_1@localhost; 333select * from v1; 334a b 335REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; 336drop view v1; 337drop database mysqltest; 338create database mysqltest; 339use mysqltest; 340create table t1 (a int); 341insert into t1 values (1); 342create table t2 (s1 int); 343drop function if exists f2; 344create function f2 () returns int begin declare v int; select s1 from t2 345into v; return v; end// 346create algorithm=TEMPTABLE view v1 as select f2() from t1; 347create algorithm=MERGE view v2 as select f2() from t1; 348create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; 349create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; 350create SQL SECURITY INVOKER view v5 as select * from v4; 351grant select on v1 to mysqltest_1@localhost; 352grant select on v2 to mysqltest_1@localhost; 353grant select on v3 to mysqltest_1@localhost; 354grant select on v4 to mysqltest_1@localhost; 355grant select on v5 to mysqltest_1@localhost; 356use mysqltest; 357select * from v1; 358f2() 359NULL 360select * from v2; 361f2() 362NULL 363select * from v3; 364ERROR HY000: View 'mysqltest.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 365select * from v4; 366ERROR HY000: View 'mysqltest.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 367select * from v5; 368ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 369use test; 370drop view v1, v2, v3, v4, v5; 371drop function f2; 372drop table t1, t2; 373use test; 374REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; 375drop database mysqltest; 376create database mysqltest; 377use mysqltest; 378create table t1 (a int); 379insert into t1 values (1); 380create table t2 (s1 int); 381drop function if exists f2; 382create function f2 () returns int begin declare v int; select s1 from t2 383into v; return v; end// 384grant select on t1 to mysqltest_1@localhost; 385grant execute on function f2 to mysqltest_1@localhost; 386grant create view on mysqltest.* to mysqltest_1@localhost; 387use mysqltest; 388create algorithm=TEMPTABLE view v1 as select f2() from t1; 389create algorithm=MERGE view v2 as select f2() from t1; 390create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; 391create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; 392use test; 393create view v5 as select * from v1; 394revoke execute on function f2 from mysqltest_1@localhost; 395select * from v1; 396ERROR HY000: View 'mysqltest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 397select * from v2; 398ERROR HY000: View 'mysqltest.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 399select * from v3; 400f2() 401NULL 402select * from v4; 403f2() 404NULL 405select * from v5; 406ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 407drop view v1, v2, v3, v4, v5; 408drop function f2; 409drop table t1, t2; 410use test; 411REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; 412drop database mysqltest; 413create database mysqltest; 414use mysqltest; 415create table t1 (a int); 416create table v1 (a int); 417insert into t1 values (1); 418grant select on t1 to mysqltest_1@localhost; 419grant select on v1 to mysqltest_1@localhost; 420grant create view on mysqltest.* to mysqltest_1@localhost; 421drop table v1; 422use mysqltest; 423create algorithm=TEMPTABLE view v1 as select *, a as b from t1; 424create algorithm=MERGE view v2 as select *, a as b from t1; 425create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; 426create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; 427create view v5 as select * from v1; 428use test; 429revoke select on t1 from mysqltest_1@localhost; 430select * from v1; 431ERROR HY000: View 'mysqltest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 432select * from v2; 433ERROR HY000: View 'mysqltest.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 434select * from v3; 435a b 4361 1 437select * from v4; 438a b 4391 1 440select * from v5; 441ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 442drop table t1; 443use test; 444REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; 445drop database mysqltest; 446create database mysqltest; 447use mysqltest; 448create table t1 (a int); 449insert into t1 values (1); 450create algorithm=TEMPTABLE view v1 as select *, a as b from t1; 451create algorithm=MERGE view v2 as select *, a as b from t1; 452create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; 453create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; 454create SQL SECURITY INVOKER view v5 as select * from v4; 455grant select on v1 to mysqltest_1@localhost; 456grant select on v2 to mysqltest_1@localhost; 457grant select on v3 to mysqltest_1@localhost; 458grant select on v4 to mysqltest_1@localhost; 459grant select on v5 to mysqltest_1@localhost; 460use mysqltest; 461select * from v1; 462a b 4631 1 464select * from v2; 465a b 4661 1 467select * from v3; 468ERROR HY000: View 'mysqltest.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 469select * from v4; 470ERROR HY000: View 'mysqltest.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 471select * from v5; 472ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 473use test; 474drop view v1, v2, v3, v4, v5; 475drop table t1; 476use test; 477REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; 478drop database mysqltest; 479drop view if exists v1; 480drop table if exists t1; 481create table t1 as select * from mysql.user where user=''; 482delete from mysql.user where user=''; 483flush privileges; 484grant all on test.* to 'test14256'@'%'; 485use test; 486create view v1 as select 42; 487show create view v1; 488View Create View character_set_client collation_connection 489v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` latin1 latin1_swedish_ci 490select definer into @v1def1 from information_schema.views 491where table_schema = 'test' and table_name='v1'; 492drop view v1; 493create definer=`test14256`@`%` view v1 as select 42; 494show create view v1; 495View Create View character_set_client collation_connection 496v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` latin1 latin1_swedish_ci 497select definer into @v1def2 from information_schema.views 498where table_schema = 'test' and table_name='v1'; 499drop view v1; 500select @v1def1, @v1def2, @v1def1=@v1def2; 501@v1def1 @v1def2 @v1def1=@v1def2 502test14256@% test14256@% 1 503drop user test14256; 504insert into mysql.user select * from t1; 505flush privileges; 506drop table t1; 507create database mysqltest; 508use mysqltest; 509CREATE TABLE t1 (i INT); 510CREATE VIEW v1 AS SELECT * FROM t1; 511SHOW CREATE VIEW v1; 512View Create View character_set_client collation_connection 513v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci 514GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; 515use mysqltest; 516LOCK TABLES v1 READ; 517SHOW CREATE TABLE v1; 518ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' 519UNLOCK TABLES; 520use test; 521use test; 522drop user mysqltest_1@localhost; 523drop database mysqltest; 524create definer=some_user@`` sql security invoker view v1 as select 1; 525Warnings: 526Note 1449 The user specified as a definer ('some_user'@'') does not exist 527create definer=some_user@localhost sql security invoker view v2 as select 1; 528Warnings: 529Note 1449 The user specified as a definer ('some_user'@'localhost') does not exist 530show create view v1; 531View Create View character_set_client collation_connection 532v1 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci 533show create view v2; 534View Create View character_set_client collation_connection 535v2 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select 1 AS `1` latin1 latin1_swedish_ci 536drop view v1; 537drop view v2; 538CREATE DATABASE mysqltest1; 539CREATE USER readonly@localhost; 540CREATE TABLE mysqltest1.t1 (x INT); 541INSERT INTO mysqltest1.t1 VALUES (1), (2); 542CREATE SQL SECURITY INVOKER VIEW mysqltest1.v_t1 AS SELECT * FROM mysqltest1.t1; 543CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ts AS SELECT * FROM mysqltest1.t1; 544CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ti AS SELECT * FROM mysqltest1.t1; 545CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tu AS SELECT * FROM mysqltest1.t1; 546CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tus AS SELECT * FROM mysqltest1.t1; 547CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_td AS SELECT * FROM mysqltest1.t1; 548CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tds AS SELECT * FROM mysqltest1.t1; 549GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly@localhost; 550GRANT SELECT ON mysqltest1.v_ts TO readonly@localhost; 551GRANT INSERT ON mysqltest1.v_ti TO readonly@localhost; 552GRANT UPDATE ON mysqltest1.v_tu TO readonly@localhost; 553GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly@localhost; 554GRANT DELETE ON mysqltest1.v_td TO readonly@localhost; 555GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly@localhost; 556SELECT * FROM mysqltest1.v_t1; 557ERROR 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 558INSERT INTO mysqltest1.v_t1 VALUES(4); 559ERROR 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 560DELETE FROM mysqltest1.v_t1 WHERE x = 1; 561ERROR 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 562UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2; 563ERROR 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 564UPDATE mysqltest1.v_t1 SET x = 3; 565ERROR 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 566DELETE FROM mysqltest1.v_t1; 567ERROR 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 568SELECT 1 FROM mysqltest1.v_t1; 569ERROR 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 570SELECT * FROM mysqltest1.t1; 571ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 't1' 572SELECT * FROM mysqltest1.v_ts; 573x 5741 5752 576SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x; 577ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 't1' 578SELECT * FROM mysqltest1.v_ti; 579ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 'v_ti' 580INSERT INTO mysqltest1.v_ts VALUES (100); 581ERROR 42000: INSERT command denied to user 'readonly'@'localhost' for table 'v_ts' 582INSERT INTO mysqltest1.v_ti VALUES (100); 583UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100; 584ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table 'v_ts' 585UPDATE mysqltest1.v_ts SET x= 200; 586ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table 'v_ts' 587UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100; 588UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100; 589UPDATE mysqltest1.v_tu SET x= 200; 590DELETE FROM mysqltest1.v_ts WHERE x= 200; 591ERROR 42000: DELETE command denied to user 'readonly'@'localhost' for table 'v_ts' 592DELETE FROM mysqltest1.v_ts; 593ERROR 42000: DELETE command denied to user 'readonly'@'localhost' for table 'v_ts' 594DELETE FROM mysqltest1.v_td WHERE x= 200; 595ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for column 'x' in table 'v_td' 596DELETE FROM mysqltest1.v_tds WHERE x= 200; 597DELETE FROM mysqltest1.v_td; 598DROP VIEW mysqltest1.v_tds; 599DROP VIEW mysqltest1.v_td; 600DROP VIEW mysqltest1.v_tus; 601DROP VIEW mysqltest1.v_tu; 602DROP VIEW mysqltest1.v_ti; 603DROP VIEW mysqltest1.v_ts; 604DROP VIEW mysqltest1.v_t1; 605DROP TABLE mysqltest1.t1; 606DROP USER readonly@localhost; 607DROP DATABASE mysqltest1; 608CREATE TABLE t1 (a INT PRIMARY KEY); 609INSERT INTO t1 VALUES (1), (2), (3); 610CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1; 611Warnings: 612Note 1449 The user specified as a definer ('no-such-user'@'localhost') does not exist 613SHOW CREATE VIEW v; 614View Create View character_set_client collation_connection 615v 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 616Warnings: 617Note 1449 The user specified as a definer ('no-such-user'@'localhost') does not exist 618SELECT * FROM v; 619ERROR HY000: The user specified as a definer ('no-such-user'@'localhost') does not exist 620DROP VIEW v; 621DROP TABLE t1; 622USE test; 623CREATE USER mysqltest_db1@localhost identified by 'PWD'; 624GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION; 625CREATE SCHEMA mysqltest_db1 ; 626USE mysqltest_db1 ; 627CREATE TABLE t1 (f1 INTEGER); 628CREATE VIEW view1 AS 629SELECT * FROM t1; 630SHOW CREATE VIEW view1; 631View Create View character_set_client collation_connection 632view1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci 633CREATE VIEW view2 AS 634SELECT * FROM view1; 635# Here comes a suspicious warning 636SHOW CREATE VIEW view2; 637View Create View character_set_client collation_connection 638view2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view2` AS select `view1`.`f1` AS `f1` from `view1` latin1 latin1_swedish_ci 639# But the view view2 is usable 640SELECT * FROM view2; 641f1 642CREATE VIEW view3 AS 643SELECT * FROM view2; 644SELECT * from view3; 645f1 646DROP VIEW mysqltest_db1.view3; 647DROP VIEW mysqltest_db1.view2; 648DROP VIEW mysqltest_db1.view1; 649DROP TABLE mysqltest_db1.t1; 650DROP SCHEMA mysqltest_db1; 651DROP USER mysqltest_db1@localhost; 652CREATE DATABASE test1; 653CREATE DATABASE test2; 654CREATE TABLE test1.t0 (a VARCHAR(20)); 655CREATE TABLE test2.t1 (a VARCHAR(20)); 656CREATE VIEW test2.t3 AS SELECT * FROM test1.t0; 657CREATE OR REPLACE VIEW test.v1 AS 658SELECT ta.a AS col1, tb.a AS col2 FROM test2.t3 ta, test2.t1 tb; 659DROP VIEW test.v1; 660DROP VIEW test2.t3; 661DROP TABLE test2.t1, test1.t0; 662DROP DATABASE test2; 663DROP DATABASE test1; 664DROP VIEW IF EXISTS v1; 665DROP VIEW IF EXISTS v2; 666DROP VIEW IF EXISTS v3; 667DROP FUNCTION IF EXISTS f1; 668DROP FUNCTION IF EXISTS f2; 669DROP PROCEDURE IF EXISTS p1; 670CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT CURRENT_USER() AS cu; 671CREATE FUNCTION f1() RETURNS VARCHAR(77) SQL SECURITY INVOKER 672RETURN CURRENT_USER(); 673CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT f1() AS cu; 674CREATE PROCEDURE p1(OUT cu VARCHAR(77)) SQL SECURITY INVOKER 675SET cu= CURRENT_USER(); 676CREATE FUNCTION f2() RETURNS VARCHAR(77) SQL SECURITY INVOKER 677BEGIN 678DECLARE cu VARCHAR(77); 679CALL p1(cu); 680RETURN cu; 681END| 682CREATE SQL SECURITY DEFINER VIEW v3 AS SELECT f2() AS cu; 683CREATE USER mysqltest_u1@localhost; 684GRANT ALL ON test.* TO mysqltest_u1@localhost; 685 686The following tests should all return 1. 687 688SELECT CURRENT_USER() = 'mysqltest_u1@localhost'; 689CURRENT_USER() = 'mysqltest_u1@localhost' 6901 691SELECT f1() = 'mysqltest_u1@localhost'; 692f1() = 'mysqltest_u1@localhost' 6931 694CALL p1(@cu); 695SELECT @cu = 'mysqltest_u1@localhost'; 696@cu = 'mysqltest_u1@localhost' 6971 698SELECT f2() = 'mysqltest_u1@localhost'; 699f2() = 'mysqltest_u1@localhost' 7001 701SELECT cu = 'root@localhost' FROM v1; 702cu = 'root@localhost' 7031 704SELECT cu = 'root@localhost' FROM v2; 705cu = 'root@localhost' 7061 707SELECT cu = 'root@localhost' FROM v3; 708cu = 'root@localhost' 7091 710DROP VIEW v3; 711DROP FUNCTION f2; 712DROP PROCEDURE p1; 713DROP FUNCTION f1; 714DROP VIEW v2; 715DROP VIEW v1; 716DROP USER mysqltest_u1@localhost; 717CREATE DATABASE db17254; 718USE db17254; 719CREATE TABLE t1 (f1 INT); 720INSERT INTO t1 VALUES (10),(20); 721CREATE USER def_17254@localhost; 722GRANT SELECT ON db17254.* TO def_17254@localhost; 723CREATE USER inv_17254@localhost; 724GRANT SELECT ON db17254.t1 TO inv_17254@localhost; 725GRANT CREATE VIEW ON db17254.* TO def_17254@localhost; 726CREATE VIEW v1 AS SELECT * FROM t1; 727DROP USER def_17254@localhost; 728for a user 729SELECT * FROM v1; 730ERROR 42000: SELECT command denied to user 'inv_17254'@'localhost' for table 'v1' 731for a superuser 732SELECT * FROM v1; 733ERROR HY000: The user specified as a definer ('def_17254'@'localhost') does not exist 734DROP USER inv_17254@localhost; 735DROP DATABASE db17254; 736DROP DATABASE IF EXISTS mysqltest_db1; 737DROP DATABASE IF EXISTS mysqltest_db2; 738DROP USER mysqltest_u1; 739DROP USER mysqltest_u2; 740CREATE USER mysqltest_u1@localhost; 741CREATE USER mysqltest_u2@localhost; 742CREATE DATABASE mysqltest_db1; 743CREATE DATABASE mysqltest_db2; 744GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost WITH GRANT OPTION; 745GRANT ALL ON mysqltest_db2.* TO mysqltest_u2@localhost; 746CREATE TABLE t1 (i INT); 747INSERT INTO t1 VALUES (1); 748CREATE VIEW v1 AS SELECT i FROM t1 WHERE 1 IN (SELECT * FROM t1); 749CREATE TABLE t2 (s CHAR(7)); 750INSERT INTO t2 VALUES ('public'); 751GRANT SELECT ON v1 TO mysqltest_u2@localhost; 752GRANT SELECT ON t2 TO mysqltest_u2@localhost; 753SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2; 754i s 7551 public 756PREPARE stmt1 FROM "SELECT * FROM mysqltest_db1.t2"; 757EXECUTE stmt1; 758s 759public 760PREPARE stmt2 FROM "SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2"; 761EXECUTE stmt2; 762i s 7631 public 764REVOKE SELECT ON t2 FROM mysqltest_u2@localhost; 765UPDATE t2 SET s = 'private' WHERE s = 'public'; 766SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2; 767ERROR 42000: SELECT command denied to user 'mysqltest_u2'@'localhost' for table 't2' 768EXECUTE stmt1; 769ERROR 42000: SELECT command denied to user 'mysqltest_u2'@'localhost' for table 't2' 770EXECUTE stmt2; 771ERROR 42000: SELECT command denied to user 'mysqltest_u2'@'localhost' for table 't2' 772REVOKE ALL ON mysqltest_db1.* FROM mysqltest_u1@localhost; 773REVOKE ALL ON mysqltest_db2.* FROM mysqltest_u2@localhost; 774DROP DATABASE mysqltest_db1; 775DROP DATABASE mysqltest_db2; 776DROP USER mysqltest_u1@localhost; 777DROP USER mysqltest_u2@localhost; 778CREATE DATABASE db26813; 779USE db26813; 780CREATE TABLE t1(f1 INT, f2 INT); 781CREATE VIEW v1 AS SELECT f1 FROM t1; 782CREATE VIEW v2 AS SELECT f1 FROM t1; 783CREATE VIEW v3 AS SELECT f1 FROM t1; 784CREATE USER u26813@localhost; 785GRANT DROP ON db26813.v1 TO u26813@localhost; 786GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost; 787GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost; 788GRANT SELECT ON db26813.t1 TO u26813@localhost; 789ALTER VIEW v1 AS SELECT f2 FROM t1; 790ERROR 42000: CREATE VIEW command denied to user 'u26813'@'localhost' for table 'v1' 791ALTER VIEW v2 AS SELECT f2 FROM t1; 792ERROR 42000: DROP command denied to user 'u26813'@'localhost' for table 'v2' 793ALTER VIEW v3 AS SELECT f2 FROM t1; 794ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 795SHOW CREATE VIEW v3; 796View Create View character_set_client collation_connection 797v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci 798DROP USER u26813@localhost; 799DROP DATABASE db26813; 800# 801# Bug#29908 A user can gain additional access through the ALTER VIEW. 802# 803CREATE DATABASE mysqltest_29908; 804USE mysqltest_29908; 805CREATE TABLE t1(f1 INT, f2 INT); 806CREATE USER u29908_1@localhost; 807CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1; 808CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS 809SELECT f1 FROM t1; 810GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost; 811GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost; 812GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost; 813CREATE USER u29908_2@localhost; 814GRANT SELECT, DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost; 815GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost; 816GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost; 817ALTER VIEW v1 AS SELECT f2 FROM t1; 818ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 819ALTER VIEW v2 AS SELECT f2 FROM t1; 820ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 821SHOW CREATE VIEW v2; 822View Create View character_set_client collation_connection 823v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci 824ALTER VIEW v1 AS SELECT f2 FROM t1; 825SHOW CREATE VIEW v1; 826View Create View character_set_client collation_connection 827v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f2` AS `f2` from `t1` latin1 latin1_swedish_ci 828ALTER VIEW v2 AS SELECT f2 FROM t1; 829SHOW CREATE VIEW v2; 830View Create View character_set_client collation_connection 831v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f2` AS `f2` from `t1` latin1 latin1_swedish_ci 832ALTER VIEW v1 AS SELECT f1 FROM t1; 833SHOW CREATE VIEW v1; 834View Create View character_set_client collation_connection 835v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci 836ALTER VIEW v2 AS SELECT f1 FROM t1; 837SHOW CREATE VIEW v2; 838View Create View character_set_client collation_connection 839v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci 840DROP USER u29908_1@localhost; 841DROP USER u29908_2@localhost; 842DROP DATABASE mysqltest_29908; 843####################################################################### 844DROP DATABASE IF EXISTS mysqltest1; 845DROP DATABASE IF EXISTS mysqltest2; 846CREATE DATABASE mysqltest1; 847CREATE DATABASE mysqltest2; 848CREATE TABLE mysqltest1.t1(c1 INT); 849CREATE TABLE mysqltest1.t2(c2 INT); 850CREATE TABLE mysqltest1.t3(c3 INT); 851CREATE TABLE mysqltest1.t4(c4 INT); 852INSERT INTO mysqltest1.t1 VALUES (11), (12), (13), (14); 853INSERT INTO mysqltest1.t2 VALUES (21), (22), (23), (24); 854INSERT INTO mysqltest1.t3 VALUES (31), (32), (33), (34); 855INSERT INTO mysqltest1.t4 VALUES (41), (42), (43), (44); 856GRANT SELECT ON mysqltest1.t1 TO mysqltest_u1@localhost; 857GRANT INSERT ON mysqltest1.t2 TO mysqltest_u1@localhost; 858GRANT SELECT, UPDATE ON mysqltest1.t3 TO mysqltest_u1@localhost; 859GRANT SELECT, DELETE ON mysqltest1.t4 TO mysqltest_u1@localhost; 860GRANT ALL PRIVILEGES ON mysqltest2.* TO mysqltest_u1@localhost; 861 862---> connection: bug24040_con 863SELECT * FROM mysqltest1.t1; 864c1 86511 86612 86713 86814 869INSERT INTO mysqltest1.t2 VALUES(25); 870UPDATE mysqltest1.t3 SET c3 = 331 WHERE c3 = 31; 871DELETE FROM mysqltest1.t4 WHERE c4 = 44; 872CREATE VIEW v1 AS SELECT * FROM mysqltest1.t1; 873CREATE VIEW v2 AS SELECT * FROM mysqltest1.t2; 874CREATE VIEW v3 AS SELECT * FROM mysqltest1.t3; 875CREATE VIEW v4 AS SELECT * FROM mysqltest1.t4; 876SELECT * FROM v1; 877c1 87811 87912 88013 88114 882INSERT INTO v2 VALUES(26); 883UPDATE v3 SET c3 = 332 WHERE c3 = 32; 884DELETE FROM v4 WHERE c4 = 43; 885CREATE VIEW v12 AS SELECT c1, c2 FROM mysqltest1.t1, mysqltest1.t2; 886ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v12' 887CREATE VIEW v13 AS SELECT c1, c3 FROM mysqltest1.t1, mysqltest1.t3; 888CREATE VIEW v14 AS SELECT c1, c4 FROM mysqltest1.t1, mysqltest1.t4; 889CREATE VIEW v21 AS SELECT c2, c1 FROM mysqltest1.t2, mysqltest1.t1; 890ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c1' in table 'v21' 891CREATE VIEW v23 AS SELECT c2, c3 FROM mysqltest1.t2, mysqltest1.t3; 892ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c3' in table 'v23' 893CREATE VIEW v24 AS SELECT c2, c4 FROM mysqltest1.t2, mysqltest1.t4; 894ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c4' in table 'v24' 895CREATE VIEW v31 AS SELECT c3, c1 FROM mysqltest1.t3, mysqltest1.t1; 896CREATE VIEW v32 AS SELECT c3, c2 FROM mysqltest1.t3, mysqltest1.t2; 897ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v32' 898CREATE VIEW v34 AS SELECT c3, c4 FROM mysqltest1.t3, mysqltest1.t4; 899CREATE VIEW v41 AS SELECT c4, c1 FROM mysqltest1.t4, mysqltest1.t1; 900CREATE VIEW v42 AS SELECT c4, c2 FROM mysqltest1.t4, mysqltest1.t2; 901ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v42' 902CREATE VIEW v43 AS SELECT c4, c3 FROM mysqltest1.t4, mysqltest1.t3; 903 904---> connection: default 905SELECT * FROM mysqltest1.t1; 906c1 90711 90812 90913 91014 911SELECT * FROM mysqltest1.t2; 912c2 91321 91422 91523 91624 91725 91826 919SELECT * FROM mysqltest1.t3; 920c3 921331 922332 92333 92434 925SELECT * FROM mysqltest1.t4; 926c4 92741 92842 929DROP DATABASE mysqltest1; 930DROP DATABASE mysqltest2; 931DROP USER mysqltest_u1@localhost; 932CREATE DATABASE db1; 933USE db1; 934CREATE TABLE t1(f1 INT, f2 INT); 935CREATE VIEW v1 AS SELECT f1, f2 FROM t1; 936GRANT SELECT (f1) ON t1 TO foo; 937GRANT SELECT (f1) ON v1 TO foo; 938USE db1; 939SELECT f1 FROM t1; 940f1 941SELECT f2 FROM t1; 942ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'f2' in table 't1' 943SELECT * FROM t1; 944ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1' 945SELECT f1 FROM v1; 946f1 947SELECT f2 FROM v1; 948ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'f2' in table 'v1' 949SELECT * FROM v1; 950ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1' 951USE test; 952REVOKE SELECT (f1) ON db1.t1 FROM foo; 953REVOKE SELECT (f1) ON db1.v1 FROM foo; 954DROP USER foo; 955DROP VIEW db1.v1; 956DROP TABLE db1.t1; 957DROP DATABASE db1; 958Bug #11765687/#58677: 959No privilege on table/view, but can know #rows / underlying table's name 960create database mysqltest1; 961create table mysqltest1.t1 (i int); 962create table mysqltest1.t2 (j int); 963create table mysqltest1.t3 (k int, secret int); 964create user alice@localhost; 965create user bob@localhost; 966create user cecil@localhost; 967create user dan@localhost; 968create user eugene@localhost; 969create user fiona@localhost; 970create user greg@localhost; 971create user han@localhost; 972create user inga@localhost; 973create user jamie@localhost; 974create user karl@localhost; 975create user lena@localhost; 976create user mhairi@localhost; 977create user noam@localhost; 978create user olga@localhost; 979create user pjotr@localhost; 980create user quintessa@localhost; 981grant all privileges on mysqltest1.* to alice@localhost with grant option; 982... as alice 983create view v1 as select * from t1; 984create view v2 as select * from v1, t2; 985create view v3 as select k from t3; 986grant select on mysqltest1.v1 to bob@localhost; 987grant show view on mysqltest1.v1 to cecil@localhost; 988grant select, show view on mysqltest1.v1 to dan@localhost; 989grant select on mysqltest1.t1 to dan@localhost; 990grant select on mysqltest1.* to eugene@localhost; 991grant select, show view on mysqltest1.v2 to fiona@localhost; 992grant select, show view on mysqltest1.v2 to greg@localhost; 993grant show view on mysqltest1.v1 to greg@localhost; 994grant select(k) on mysqltest1.t3 to han@localhost; 995grant select, show view on mysqltest1.v3 to han@localhost; 996grant select on mysqltest1.t1 to inga@localhost; 997grant select on mysqltest1.t2 to inga@localhost; 998grant select on mysqltest1.v1 to inga@localhost; 999grant select, show view on mysqltest1.v2 to inga@localhost; 1000grant select on mysqltest1.t1 to jamie@localhost; 1001grant select on mysqltest1.t2 to jamie@localhost; 1002grant show view on mysqltest1.v1 to jamie@localhost; 1003grant select, show view on mysqltest1.v2 to jamie@localhost; 1004grant select on mysqltest1.t1 to karl@localhost; 1005grant select on mysqltest1.t2 to karl@localhost; 1006grant select, show view on mysqltest1.v1 to karl@localhost; 1007grant select on mysqltest1.v2 to karl@localhost; 1008grant select on mysqltest1.t1 to lena@localhost; 1009grant select on mysqltest1.t2 to lena@localhost; 1010grant select, show view on mysqltest1.v1 to lena@localhost; 1011grant show view on mysqltest1.v2 to lena@localhost; 1012grant select on mysqltest1.t1 to mhairi@localhost; 1013grant select on mysqltest1.t2 to mhairi@localhost; 1014grant select, show view on mysqltest1.v1 to mhairi@localhost; 1015grant select, show view on mysqltest1.v2 to mhairi@localhost; 1016grant select on mysqltest1.t1 to noam@localhost; 1017grant select, show view on mysqltest1.v1 to noam@localhost; 1018grant select, show view on mysqltest1.v2 to noam@localhost; 1019grant select on mysqltest1.t2 to olga@localhost; 1020grant select, show view on mysqltest1.v1 to olga@localhost; 1021grant select, show view on mysqltest1.v2 to olga@localhost; 1022grant select on mysqltest1.t1 to pjotr@localhost; 1023grant select on mysqltest1.t2 to pjotr@localhost; 1024grant select, show view on mysqltest1.v2 to pjotr@localhost; 1025grant select, show view on mysqltest1.v1 to quintessa@localhost; 1026... as bob 1027select * from v1; 1028i 1029explain select * from v1; 1030ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 1031... as cecil 1032select * from v1; 1033ERROR 42000: SELECT command denied to user 'cecil'@'localhost' for table 'v1' 1034explain select * from v1; 1035ERROR 42000: SELECT command denied to user 'cecil'@'localhost' for table 'v1' 1036... as dan 1037select * from v1; 1038i 1039explain select * from v1; 1040id select_type table type possible_keys key key_len ref rows Extra 10411 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found 1042... as eugene 1043select * from v1; 1044i 1045explain select * from v1; 1046ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 1047... as fiona 1048select * from v2; 1049i j 1050show create view v2; 1051View Create View character_set_client collation_connection 1052v2 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 1053explain select * from t1; 1054ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 't1' 1055explain select * from v1; 1056ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 'v1' 1057explain select * from t2; 1058ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 't2' 1059explain select * from v2; 1060ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 1061... as greg 1062select * from v2; 1063i j 1064explain select * from v1; 1065ERROR 42000: SELECT command denied to user 'greg'@'localhost' for table 'v1' 1066explain select * from v2; 1067ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 1068... as han 1069select * from t3; 1070ERROR 42000: SELECT command denied to user 'han'@'localhost' for table 't3' 1071explain select * from t3; 1072ERROR 42000: SELECT command denied to user 'han'@'localhost' for table 't3' 1073select k from t3; 1074k 1075explain select k from t3; 1076id select_type table type possible_keys key key_len ref rows Extra 10771 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found 1078select * from v3; 1079k 1080explain select * from v3; 1081id select_type table type possible_keys key key_len ref rows Extra 10821 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found 1083... as inga 1084select * from v2; 1085i j 1086explain select * from v2; 1087ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 1088... as jamie 1089select * from v2; 1090i j 1091explain select * from v2; 1092ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 1093... as karl 1094select * from v2; 1095i j 1096explain select * from v2; 1097ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 1098... as lena 1099select * from v2; 1100ERROR 42000: SELECT command denied to user 'lena'@'localhost' for table 'v2' 1101explain select * from v2; 1102ERROR 42000: SELECT command denied to user 'lena'@'localhost' for table 'v2' 1103... as mhairi 1104select * from v2; 1105i j 1106explain select * from v2; 1107id select_type table type possible_keys key key_len ref rows Extra 11081 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found 11091 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found 1110... as noam 1111select * from v2; 1112i j 1113explain select * from v2; 1114ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 1115... as olga 1116select * from v2; 1117i j 1118explain select * from v2; 1119ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 1120... as pjotr 1121select * from v2; 1122i j 1123explain select * from v2; 1124ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 1125... as quintessa 1126select * from v1; 1127i 1128explain select * from v1; 1129ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 1130... as root again at last: clean-up time! 1131drop user alice@localhost; 1132drop user bob@localhost; 1133drop user cecil@localhost; 1134drop user dan@localhost; 1135drop user eugene@localhost; 1136drop user fiona@localhost; 1137drop user greg@localhost; 1138drop user han@localhost; 1139drop user inga@localhost; 1140drop user jamie@localhost; 1141drop user karl@localhost; 1142drop user lena@localhost; 1143drop user mhairi@localhost; 1144drop user noam@localhost; 1145drop user olga@localhost; 1146drop user pjotr@localhost; 1147drop user quintessa@localhost; 1148drop database mysqltest1; 1149End of 5.0 tests. 1150DROP VIEW IF EXISTS v1; 1151DROP TABLE IF EXISTS t1; 1152CREATE TABLE t1 (i INT); 1153CREATE VIEW v1 AS SELECT * FROM t1; 1154ALTER VIEW v1 AS SELECT * FROM t1; 1155SHOW CREATE VIEW v1; 1156View Create View character_set_client collation_connection 1157v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci 1158ALTER DEFINER=no_such@user_1 VIEW v1 AS SELECT * FROM t1; 1159Warnings: 1160Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist 1161SHOW CREATE VIEW v1; 1162View Create View character_set_client collation_connection 1163v1 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 1164Warnings: 1165Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist 1166ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; 1167Warnings: 1168Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist 1169SHOW CREATE VIEW v1; 1170View Create View character_set_client collation_connection 1171v1 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 1172Warnings: 1173Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist 1174ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; 1175Warnings: 1176Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist 1177SHOW CREATE VIEW v1; 1178View Create View character_set_client collation_connection 1179v1 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 1180Warnings: 1181Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist 1182DROP VIEW v1; 1183DROP TABLE t1; 1184CREATE USER mysqluser1@localhost; 1185CREATE DATABASE mysqltest1; 1186USE mysqltest1; 1187CREATE TABLE t1 ( a INT ); 1188CREATE TABLE t2 ( b INT ); 1189INSERT INTO t1 VALUES (1), (2); 1190INSERT INTO t2 VALUES (1), (2); 1191GRANT CREATE VIEW ON mysqltest1.* TO mysqluser1@localhost; 1192GRANT SELECT ON t1 TO mysqluser1@localhost; 1193GRANT INSERT ON t2 TO mysqluser1@localhost; 1194This would lead to failed assertion. 1195CREATE VIEW v1 AS SELECT a, b FROM t1, t2; 1196SELECT * FROM v1; 1197ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 'v1' 1198SELECT b FROM v1; 1199ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 'v1' 1200DROP TABLE t1, t2; 1201DROP VIEW v1; 1202DROP DATABASE mysqltest1; 1203DROP USER mysqluser1@localhost; 1204USE test; 1205End of 5.1 tests. 1206CREATE USER mysqluser1@localhost; 1207CREATE DATABASE mysqltest1; 1208USE mysqltest1; 1209CREATE TABLE t1 ( a INT, b INT ); 1210CREATE TABLE t2 ( a INT, b INT ); 1211CREATE VIEW v1 AS SELECT a, b FROM t1; 1212GRANT SELECT( a ) ON v1 TO mysqluser1@localhost; 1213GRANT UPDATE( b ) ON t2 TO mysqluser1@localhost; 1214SELECT * FROM mysqltest1.v1; 1215ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 'v1' 1216CREATE VIEW v1 AS SELECT * FROM mysqltest1.t2; 1217ERROR 42000: ANY command denied to user 'mysqluser1'@'localhost' for table 't2' 1218DROP TABLE t1, t2; 1219DROP VIEW v1; 1220DROP DATABASE mysqltest1; 1221DROP USER mysqluser1@localhost; 1222CREATE USER mysqluser1@localhost; 1223CREATE DATABASE mysqltest1; 1224USE mysqltest1; 1225CREATE VIEW v1 AS SELECT * FROM information_schema.tables LIMIT 1; 1226CREATE ALGORITHM = TEMPTABLE VIEW v2 AS SELECT 1 AS A; 1227CREATE VIEW test.v3 AS SELECT 1 AS a; 1228GRANT SELECT ON mysqltest1.* to mysqluser1@localhost; 1229GRANT ALL ON test.* TO mysqluser1@localhost; 1230PREPARE stmt_v1 FROM "SELECT * FROM mysqltest1.v1"; 1231PREPARE stmt_v2 FROM "SELECT * FROM mysqltest1.v2"; 1232REVOKE SELECT ON mysqltest1.* FROM mysqluser1@localhost; 1233EXECUTE stmt_v1; 1234ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 'v1' 1235EXECUTE stmt_v2; 1236ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 'v2' 1237PREPARE stmt FROM "SELECT a FROM v3"; 1238EXECUTE stmt; 1239a 12401 1241DROP VIEW v1, v2; 1242DROP DATABASE mysqltest1; 1243DROP VIEW test.v3; 1244DROP USER mysqluser1@localhost; 1245USE test; 1246# 1247# Bug#35996: SELECT + SHOW VIEW should be enough to display view 1248# definition 1249# 1250CREATE USER mysqluser1@localhost; 1251CREATE DATABASE mysqltest1; 1252CREATE DATABASE mysqltest2; 1253GRANT USAGE, SELECT, CREATE VIEW, SHOW VIEW 1254ON mysqltest2.* TO mysqluser1@localhost; 1255USE mysqltest1; 1256CREATE TABLE t1( a INT ); 1257CREATE TABLE t2( a INT, b INT ); 1258CREATE FUNCTION f1() RETURNS INT RETURN 1; 1259CREATE VIEW v1 AS SELECT 1 AS a; 1260CREATE VIEW v2 AS SELECT 1 AS a, 2 AS b; 1261GRANT SELECT ON TABLE t1 TO mysqluser1@localhost; 1262GRANT SELECT (a, b) ON TABLE t2 TO mysqluser1@localhost; 1263GRANT EXECUTE ON FUNCTION f1 TO mysqluser1@localhost; 1264GRANT SELECT ON TABLE v1 TO mysqluser1@localhost; 1265GRANT SELECT (a, b) ON TABLE v2 TO mysqluser1@localhost; 1266CREATE VIEW v_t1 AS SELECT * FROM t1; 1267CREATE VIEW v_t2 AS SELECT * FROM t2; 1268CREATE VIEW v_f1 AS SELECT f1() AS a; 1269CREATE VIEW v_v1 AS SELECT * FROM v1; 1270CREATE VIEW v_v2 AS SELECT * FROM v2; 1271GRANT SELECT, SHOW VIEW ON v_t1 TO mysqluser1@localhost; 1272GRANT SELECT, SHOW VIEW ON v_t2 TO mysqluser1@localhost; 1273GRANT SELECT, SHOW VIEW ON v_f1 TO mysqluser1@localhost; 1274GRANT SELECT, SHOW VIEW ON v_v1 TO mysqluser1@localhost; 1275GRANT SELECT, SHOW VIEW ON v_v2 TO mysqluser1@localhost; 1276CREATE VIEW v_mysqluser1_t1 AS SELECT * FROM mysqltest1.t1; 1277CREATE VIEW v_mysqluser1_t2 AS SELECT * FROM mysqltest1.t2; 1278CREATE VIEW v_mysqluser1_f1 AS SELECT mysqltest1.f1() AS a; 1279CREATE VIEW v_mysqluser1_v1 AS SELECT * FROM mysqltest1.v1; 1280CREATE VIEW v_mysqluser1_v2 AS SELECT * FROM mysqltest1.v2; 1281SHOW CREATE VIEW mysqltest1.v_t1; 1282View Create View character_set_client collation_connection 1283v_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 1284SHOW CREATE VIEW mysqltest1.v_t2; 1285View Create View character_set_client collation_connection 1286v_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 1287SHOW CREATE VIEW mysqltest1.v_f1; 1288View Create View character_set_client collation_connection 1289v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci 1290SHOW CREATE VIEW mysqltest1.v_v1; 1291View Create View character_set_client collation_connection 1292v_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 1293SHOW CREATE VIEW mysqltest1.v_v2; 1294View Create View character_set_client collation_connection 1295v_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 1296SHOW CREATE VIEW v_mysqluser1_t1; 1297View Create View character_set_client collation_connection 1298v_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 1299SHOW CREATE VIEW v_mysqluser1_t2; 1300View Create View character_set_client collation_connection 1301v_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 1302SHOW CREATE VIEW v_mysqluser1_f1; 1303View Create View character_set_client collation_connection 1304v_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 1305SHOW CREATE VIEW v_mysqluser1_v1; 1306View Create View character_set_client collation_connection 1307v_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 1308SHOW CREATE VIEW v_mysqluser1_v2; 1309View Create View character_set_client collation_connection 1310v_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 1311REVOKE SELECT ON TABLE t1 FROM mysqluser1@localhost; 1312REVOKE SELECT (a) ON TABLE t2 FROM mysqluser1@localhost; 1313REVOKE EXECUTE ON FUNCTION f1 FROM mysqluser1@localhost; 1314REVOKE SELECT ON TABLE v1 FROM mysqluser1@localhost; 1315SHOW CREATE VIEW mysqltest1.v_t1; 1316View Create View character_set_client collation_connection 1317v_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 1318SHOW CREATE VIEW mysqltest1.v_t2; 1319View Create View character_set_client collation_connection 1320v_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 1321SHOW CREATE VIEW mysqltest1.v_f1; 1322View Create View character_set_client collation_connection 1323v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci 1324SHOW CREATE VIEW mysqltest1.v_v1; 1325View Create View character_set_client collation_connection 1326v_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 1327SHOW CREATE VIEW mysqltest1.v_v2; 1328View Create View character_set_client collation_connection 1329v_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 1330SHOW CREATE VIEW v_mysqluser1_t1; 1331View Create View character_set_client collation_connection 1332v_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 1333SHOW CREATE VIEW v_mysqluser1_t2; 1334View Create View character_set_client collation_connection 1335v_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 1336SHOW CREATE VIEW v_mysqluser1_f1; 1337View Create View character_set_client collation_connection 1338v_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 1339SHOW CREATE VIEW v_mysqluser1_v1; 1340View Create View character_set_client collation_connection 1341v_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 1342SHOW CREATE VIEW v_mysqluser1_v2; 1343View Create View character_set_client collation_connection 1344v_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 1345# Testing the case when the views reference missing objects. 1346# Obviously, there are no privileges to check for, so we 1347# need only each object type once. 1348DROP TABLE t1; 1349DROP FUNCTION f1; 1350DROP VIEW v1; 1351SHOW CREATE VIEW mysqltest1.v_t1; 1352View Create View character_set_client collation_connection 1353v_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 1354Warnings: 1355Warning 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 1356SHOW CREATE VIEW mysqltest1.v_f1; 1357View Create View character_set_client collation_connection 1358v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci 1359Warnings: 1360Warning 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 1361SHOW CREATE VIEW mysqltest1.v_v1; 1362View Create View character_set_client collation_connection 1363v_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 1364Warnings: 1365Warning 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 1366SHOW CREATE VIEW v_mysqluser1_t1; 1367View Create View character_set_client collation_connection 1368v_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 1369Warnings: 1370Warning 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 1371SHOW CREATE VIEW v_mysqluser1_f1; 1372View Create View character_set_client collation_connection 1373v_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 1374Warnings: 1375Warning 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 1376SHOW CREATE VIEW v_mysqluser1_v1; 1377View Create View character_set_client collation_connection 1378v_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 1379Warnings: 1380Warning 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 1381REVOKE SHOW VIEW ON v_t1 FROM mysqluser1@localhost; 1382REVOKE SHOW VIEW ON v_f1 FROM mysqluser1@localhost; 1383REVOKE SHOW VIEW ON v_v1 FROM mysqluser1@localhost; 1384SHOW CREATE VIEW mysqltest1.v_t1; 1385ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_t1' 1386SHOW CREATE VIEW mysqltest1.v_f1; 1387ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_f1' 1388SHOW CREATE VIEW mysqltest1.v_v1; 1389ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_v1' 1390SHOW CREATE VIEW v_mysqluser1_t1; 1391View Create View character_set_client collation_connection 1392v_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 1393Warnings: 1394Warning 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 1395SHOW CREATE VIEW v_mysqluser1_f1; 1396View Create View character_set_client collation_connection 1397v_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 1398Warnings: 1399Warning 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 1400SHOW CREATE VIEW v_mysqluser1_v1; 1401View Create View character_set_client collation_connection 1402v_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 1403Warnings: 1404Warning 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 1405DROP USER mysqluser1@localhost; 1406DROP DATABASE mysqltest1; 1407DROP DATABASE mysqltest2; 1408USE test; 1409CREATE TABLE t1( a INT ); 1410CREATE DEFINER = no_such_user@no_such_host VIEW v1 AS SELECT * FROM t1; 1411Warnings: 1412Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist 1413SHOW CREATE VIEW v1; 1414View Create View character_set_client collation_connection 1415v1 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 1416Warnings: 1417Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist 1418DROP TABLE t1; 1419DROP VIEW v1; 1420# 1421# Bug #46019: ERROR 1356 When selecting from within another 1422# view that has Group By 1423# 1424CREATE DATABASE mysqltest1; 1425USE mysqltest1; 1426CREATE TABLE t1 (a INT); 1427CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT a FROM t1 GROUP BY a; 1428CREATE SQL SECURITY INVOKER VIEW v2 AS SELECT a FROM v1; 1429CREATE USER mysqluser1; 1430GRANT SELECT ON TABLE t1 TO mysqluser1; 1431GRANT SELECT, SHOW VIEW ON TABLE v1 TO mysqluser1; 1432GRANT SELECT, SHOW VIEW ON TABLE v2 TO mysqluser1; 1433SELECT a FROM v1; 1434a 1435SELECT a FROM v2; 1436a 1437DROP USER mysqluser1; 1438DROP DATABASE mysqltest1; 1439USE test; 1440# 1441# Bug#47734: Assertion failed: ! is_set() when locking a view with non-existing definer 1442# 1443DROP VIEW IF EXISTS v1; 1444CREATE DEFINER=`unknown`@`unknown` SQL SECURITY DEFINER VIEW v1 AS SELECT 1; 1445Warnings: 1446Note 1449 The user specified as a definer ('unknown'@'unknown') does not exist 1447LOCK TABLES v1 READ; 1448ERROR HY000: The user specified as a definer ('unknown'@'unknown') does not exist 1449DROP VIEW v1; 1450# 1451# Bug #58499 "DEFINER-security view selecting from INVOKER-security view 1452# access check wrong". 1453# 1454# Check that we correctly handle privileges for various combinations 1455# of INVOKER and DEFINER-security views using each other. 1456DROP DATABASE IF EXISTS mysqltest1; 1457CREATE DATABASE mysqltest1; 1458USE mysqltest1; 1459CREATE TABLE t1 (i INT); 1460CREATE TABLE t2 (j INT); 1461INSERT INTO t1 VALUES (1); 1462INSERT INTO t2 VALUES (2); 1463# 1464# 1) DEFINER-security view uses INVOKER-security view (covers 1465# scenario originally described in the bug report). 1466CREATE SQL SECURITY INVOKER VIEW v1_uses_t1 AS SELECT * FROM t1; 1467CREATE SQL SECURITY INVOKER VIEW v1_uses_t2 AS SELECT * FROM t2; 1468CREATE USER 'mysqluser1'@'%'; 1469GRANT CREATE VIEW ON mysqltest1.* TO 'mysqluser1'@'%'; 1470GRANT SELECT ON t1 TO 'mysqluser1'@'%'; 1471# To be able create 'v2_uses_t2' we also need select on t2. 1472GRANT SELECT ON t2 TO 'mysqluser1'@'%'; 1473GRANT SELECT ON v1_uses_t1 TO 'mysqluser1'@'%'; 1474GRANT SELECT ON v1_uses_t2 TO 'mysqluser1'@'%'; 1475# 1476# Connection 'mysqluser1'. 1477CREATE SQL SECURITY DEFINER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1; 1478CREATE SQL SECURITY DEFINER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2; 1479# 1480# Connection 'default'. 1481CREATE USER 'mysqluser2'@'%'; 1482GRANT SELECT ON v2_uses_t1 TO 'mysqluser2'@'%'; 1483GRANT SELECT ON v2_uses_t2 TO 'mysqluser2'@'%'; 1484GRANT SELECT ON t2 TO 'mysqluser2'@'%'; 1485GRANT CREATE VIEW ON mysqltest1.* TO 'mysqluser2'@'%'; 1486# Make 'mysqluser1' unable to access t2. 1487REVOKE SELECT ON t2 FROM 'mysqluser1'@'%'; 1488# 1489# Connection 'mysqluser2'. 1490# The below statement should succeed thanks to suid nature of v2_uses_t1. 1491SELECT * FROM v2_uses_t1; 1492i 14931 1494# The below statement should fail due to suid nature of v2_uses_t2. 1495SELECT * FROM v2_uses_t2; 1496ERROR 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 1497# 1498# 2) INVOKER-security view uses INVOKER-security view. 1499# 1500# Connection 'default'. 1501DROP VIEW v2_uses_t1, v2_uses_t2; 1502CREATE SQL SECURITY INVOKER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1; 1503CREATE SQL SECURITY INVOKER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2; 1504GRANT SELECT ON v2_uses_t1 TO 'mysqluser1'@'%'; 1505GRANT SELECT ON v2_uses_t2 TO 'mysqluser1'@'%'; 1506GRANT SELECT ON v1_uses_t1 TO 'mysqluser2'@'%'; 1507GRANT SELECT ON v1_uses_t2 TO 'mysqluser2'@'%'; 1508# 1509# Connection 'mysqluser1'. 1510# For both versions of 'v2' 'mysqluser1' privileges should be used. 1511SELECT * FROM v2_uses_t1; 1512i 15131 1514SELECT * FROM v2_uses_t2; 1515ERROR 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 1516# 1517# Connection 'mysqluser2'. 1518# And now for both versions of 'v2' 'mysqluser2' privileges should 1519# be used. 1520SELECT * FROM v2_uses_t1; 1521ERROR 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 1522SELECT * FROM v2_uses_t2; 1523j 15242 1525# 1526# 3) INVOKER-security view uses DEFINER-security view. 1527# 1528# Connection 'default'. 1529DROP VIEW v1_uses_t1, v1_uses_t2; 1530# To be able create 'v1_uses_t2' we also need select on t2. 1531GRANT SELECT ON t2 TO 'mysqluser1'@'%'; 1532# 1533# Connection 'mysqluser1'. 1534CREATE SQL SECURITY DEFINER VIEW v1_uses_t1 AS SELECT * FROM t1; 1535CREATE SQL SECURITY DEFINER VIEW v1_uses_t2 AS SELECT * FROM t2; 1536# 1537# Connection 'default'. 1538# Make 'mysqluser1' unable to access t2. 1539REVOKE SELECT ON t2 FROM 'mysqluser1'@'%'; 1540# 1541# Connection 'mysqluser2'. 1542# Due to suid nature of v1_uses_t1 and v1_uses_t2 the first 1543# select should succeed and the second select should fail. 1544SELECT * FROM v2_uses_t1; 1545i 15461 1547SELECT * FROM v2_uses_t2; 1548ERROR 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 1549# 1550# 4) DEFINER-security view uses DEFINER-security view. 1551# 1552# Connection 'default'. 1553DROP VIEW v2_uses_t1, v2_uses_t2; 1554# To be able create 'v2_uses_t2' we also need select on t2. 1555GRANT SELECT ON t2 TO 'mysqluser1'@'%'; 1556# 1557# Connection 'mysqluser2'. 1558CREATE SQL SECURITY DEFINER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1; 1559CREATE SQL SECURITY DEFINER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2; 1560# 1561# Connection 'default'. 1562# Make 'mysqluser1' unable to access t2. 1563REVOKE SELECT ON t2 FROM 'mysqluser1'@'%'; 1564# 1565# Connection 'mysqluser2'. 1566# Again privileges of creator of innermost views should apply. 1567SELECT * FROM v2_uses_t1; 1568i 15691 1570SELECT * FROM v2_uses_t2; 1571ERROR 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 1572USE test; 1573DROP DATABASE mysqltest1; 1574DROP USER 'mysqluser1'@'%'; 1575DROP USER 'mysqluser2'@'%'; 1576# 1577# Test for bug #11766767 - "59957: VIEW USING MERGE PERMISSIONS 1578# IN MULTI-TABLE UPDATE". 1579# 1580drop database if exists mysqltest1; 1581drop database if exists mysqltest2; 1582# 1583# Prepare playground. 1584create database mysqltest1; 1585create database mysqltest2; 1586create user user_11766767; 1587grant select on mysqltest1.* to user_11766767; 1588grant all on mysqltest2.* to user_11766767; 1589use mysqltest1; 1590create table t1 (id int primary key, val varchar(20)); 1591insert into t1 values (1, 'test1'); 1592create table t11 (id int primary key); 1593insert into t11 values (1); 1594create algorithm=temptable view v1_temp as select * from t1; 1595create algorithm=merge view v1_merge as select * from t1; 1596create algorithm=temptable view v11_temp as 1597select t1.id as id, t1.val as val from t1, t11 where t1.id= t11.id; 1598create algorithm=merge view v11_merge as 1599select t1.id as id, t1.val as val from t1, t11 where t1.id= t11.id; 1600use mysqltest2; 1601create table t2 (id int primary key, val varchar(20)); 1602insert into t2 values (1, 'test2'); 1603create table t21 (id int primary key); 1604insert into t21 values (1); 1605create algorithm=temptable view v2_temp as select * from t2; 1606create algorithm=merge view v2_merge as select * from t2; 1607create algorithm=temptable view v21_temp as 1608select t2.id as id, t2.val as val from t2, t21 where t2.id= t21.id; 1609create algorithm=merge view v21_merge as 1610select t2.id as id, t2.val as val from t2, t21 where t2.id= t21.id; 1611create algorithm=temptable sql security invoker view v3_temp as 1612select t1.id as id, t1.val as val from mysqltest1.t1 as t1, mysqltest1.t11 as t11 1613where t1.id = t11.id; 1614create algorithm=merge sql security invoker view v3_merge as 1615select t1.id as id, t1.val as val from mysqltest1.t1 as t1, mysqltest1.t11 as t11 1616where t1.id = t11.id; 1617create sql security invoker view v31 as 1618select t2.id as id, t2.val as val from mysqltest2.t2 as t2, mysqltest1.t11 as t11 1619where t2.id = t11.id; 1620create sql security invoker view v4 as 1621select t2.id as id, t2.val as val from mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 1622where t2.id = v1.id; 1623create sql security invoker view v41 as 1624select v1.id as id, v1.val as val from mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 1625where t2.id = v1.id; 1626create sql security invoker view v42 as 1627select v2.id as id, v2.val as val from mysqltest2.t2 as t2, mysqltest2.v2_merge as v2 1628where t2.id = v2.id; 1629# 1630# Connect as user_11766767 1631# 1632# A) Check how we handle privilege checking in multi-update for 1633# directly used views. 1634# 1635# A.1) Originally reported problem, view is used in read-only mode. 1636# This should work with only SELECT privilege for both mergeable 1637# and temptable algorithms. 1638update mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 set t2.val= 'test3' 1639 where t2.id= v1.id; 1640update mysqltest2.t2 as t2, mysqltest1.v1_temp as v1 set t2.val= 'test4' 1641 where t2.id= v1.id; 1642# 1643# A.2) If view is updated an UPDATE privilege on it is required. 1644# Temptable views can't be updated. 1645update mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 set v1.val= 'test5' 1646 where t2.id= v1.id; 1647ERROR 42000: UPDATE command denied to user 'user_11766767'@'localhost' for table 'v1_merge' 1648update mysqltest1.t1 as t1, mysqltest2.v2_merge as v2 set v2.val= 'test6' 1649 where t1.id= v2.id; 1650# 1651# Note that the below error is OK even though user lacks UPDATE 1652# privilege on v1_temp since he/she still has SELECT privilege on 1653# this view. 1654update mysqltest2.t2 as t2, mysqltest1.v1_temp as v1 set v1.val= 'test7' 1655 where t2.id= v1.id; 1656ERROR HY000: The target table v1 of the UPDATE is not updatable 1657update mysqltest1.t1 as t1, mysqltest2.v2_temp as v2 set v2.val= 'test8' 1658 where t1.id= v2.id; 1659ERROR HY000: The target table v2 of the UPDATE is not updatable 1660# 1661# A.3) This also works for correctly for multi-table views. 1662# When usage is read-only SELECT is enough. 1663update mysqltest2.t2 as t2, mysqltest1.v11_merge as v11 set t2.val= 'test9' 1664 where t2.id= v11.id; 1665update mysqltest2.t2 as t2, mysqltest1.v11_temp as v11 set t2.val= 'test10' 1666 where t2.id= v11.id; 1667# When one of view's tables is updated, UPDATE is required 1668# on a view. 1669update mysqltest2.t2 as t2, mysqltest1.v11_merge as v11 set v11.val= 'test11' 1670 where t2.id= v11.id; 1671ERROR 42000: UPDATE command denied to user 'user_11766767'@'localhost' for table 'v11_merge' 1672update mysqltest1.t1 as t1, mysqltest2.v21_merge as v21 set v21.val= 'test12' 1673 where t1.id= v21.id; 1674# As before, temptable views are not updateable. 1675update mysqltest2.t2 as t2, mysqltest1.v11_temp as v11 set v11.val= 'test13' 1676 where t2.id= v11.id; 1677ERROR HY000: The target table v11 of the UPDATE is not updatable 1678update mysqltest1.t1 as t1, mysqltest2.v21_temp as v21 set v21.val= 'test14' 1679 where t1.id= v21.id; 1680ERROR HY000: The target table v21 of the UPDATE is not updatable 1681# 1682# B) Now check that correct privileges are required on underlying 1683# tables. To simplify this part of test we will use SECURITY 1684# INVOKER views in it. 1685# 1686# B.1) In case when view is used for read only it is enough to have 1687# SELECT on its underlying tables. 1688update mysqltest2.t2 as t2, mysqltest2.v3_merge as v3 set t2.val= 'test15' 1689 where t2.id= v3.id; 1690update mysqltest2.t2 as t2, mysqltest2.v3_temp as v3 set t2.val= 'test16' 1691 where t2.id= v3.id; 1692# 1693# B.2) If view is updated, UPDATE privilege on the table being updated 1694# is required (since we already checked that temptable views are 1695# not updateable we don't test them here). 1696update mysqltest2.t2 as t2, mysqltest2.v3_merge as v3 set v3.val= 'test17' 1697 where t2.id= v3.id; 1698ERROR 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 1699update mysqltest1.t11 as t11, mysqltest2.v31 as v31 set v31.val= 'test18' 1700 where t11.id= v31.id; 1701# 1702# C) Finally, check how we handle privilege checking in case when 1703# view is used through another view. Again we will use SECURITY 1704# INVOKER views for simplicity. 1705# 1706# C.1) As usual, when a view used by another view is going to be used 1707# in read-only fashion, only SELECT privilege is necessary. 1708update mysqltest1.t11 as t11, mysqltest2.v4 as v4 set v4.val= 'test19' 1709 where t11.id= v4.id; 1710# 1711# C.2) If one of underlying tables of the view is updated then 1712# UPDATE on a view is necessary. 1713update mysqltest1.t11 as t11, mysqltest2.v41 as v4 set v4.val= 'test20' 1714 where t11.id= v4.id; 1715ERROR HY000: View 'mysqltest2.v41' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 1716update mysqltest1.t11 as t11, mysqltest2.v42 as v4 set v4.val= 'test20' 1717 where t11.id= v4.id; 1718# 1719# Clean-up. 1720# 1721# Switching to connection 'default'. 1722drop user user_11766767; 1723drop database mysqltest1; 1724drop database mysqltest2; 1725