1connect root,localhost,root,,test; 2connection root; 3create database mysqltest; 4create user mysqltest_1@localhost; 5connect user1,localhost,mysqltest_1,,test; 6connection user1; 7connection root; 8create table mysqltest.t1 (a int, b int); 9insert into mysqltest.t1 values (2,10), (1,30); 10create table mysqltest.t2 (c int, d char(32)); 11insert into mysqltest.t2 values (1,'xxx'), (1,'zzz'); 12grant select on mysqltest.t1 to mysqltest_1@localhost; 13grant select (c) on mysqltest.t2 to mysqltest_1@localhost; 14connection user1; 15with t as (select c from mysqltest.t2 where c < 2) 16select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a; 17c b 181 30 191 30 20select t.c,t.d,t1.b 21from (select c,d from mysqltest.t2 where c < 2) as t, mysqltest.t1 22where t.c=t1.a; 23ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 't2' 24with t as (select c,d from mysqltest.t2 where c < 2) 25select t.c,t.d,t1.b from t,mysqltest.t1 where t.c=t1.a; 26ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 't2' 27connection root; 28create view mysqltest.v1(f1,f2) as 29with t as (select c from mysqltest.t2 where c < 2) 30select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a; 31create view mysqltest.v2(c,d) as 32with t as (select a from mysqltest.t1 where a>=3) 33select t.a,b from t,mysqltest.t1 where mysqltest.t1.a = t.a; 34grant select on mysqltest.v1 to mysqltest_1@localhost; 35grant select (c) on mysqltest.v2 to mysqltest_1@localhost; 36grant create view on mysqltest.* to mysqltest_1@localhost; 37connection user1; 38create view mysqltest.v3(c,d) as 39with t as (select c from mysqltest.t2 where c < 2) 40select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a; 41create view mysqltest.v4(f1,f2,f3) as 42with t as (select c,d from mysqltest.t2 where c < 2) 43select t.c,t.d,t1.b from t,mysqltest.t1 where t.c=t1.a; 44ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 't2' 45select * from mysqltest.v1; 46f1 f2 471 30 481 30 49select c from mysqltest.v2; 50c 51select d from mysqltest.v2; 52ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v2' 53select * from mysqltest.v3; 54ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v3' 55connection root; 56grant select on mysqltest.v3 to mysqltest_1@localhost; 57connection user1; 58select * from mysqltest.v3; 59c d 601 30 611 30 62connection root; 63revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; 64drop user mysqltest_1@localhost; 65drop database mysqltest; 66# 67# MDEV-13453: privileges checking for CTE 68# 69create database db; 70use db; 71create table t1 (i int); 72insert into t1 73values (3), (7), (1), (4), (2), (3), (1); 74create table t2 (a int, b int); 75insert into t2 76values (3,10), (7,11), (1,17), (4,15), (2,11), (3,10), (1,15); 77create user foo@localhost; 78grant SELECT on db.t1 to foo@localhost; 79grant SELECT(a) on db.t2 to foo@localhost; 80connect con1,localhost,foo,,; 81use db; 82with cte as (select * from t1 where i < 4) 83select * from cte; 84i 853 861 872 883 891 90with cte as (select * from t1 where i < 4 group by i) 91select * from cte; 92i 931 942 953 96with cte as (select * from t1 where i < 4) 97select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; 98i 991 1003 101with cte as (select * from t1 where i < 4 group by i) 102select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; 103i 1041 1053 106with cte as (select b from t2 where a < 4) 107select * from cte cte1 where b < 15 union select * from cte cte2 where b > 15; 108ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'b' in table 't2' 109with cte as (select a from t2 where a < 4) 110select * from cte cte1 where a < 2 union select * from cte cte2 where a > 2; 111a 1121 1133 114connection default; 115revoke SELECT on db.t1 from foo@localhost; 116connection con1; 117with cte as (select * from t1 where i < 4) 118select * from cte; 119ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1' 120disconnect con1; 121connection default; 122drop database db; 123drop user foo@localhost; 124