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