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
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
|
SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_AUTO_CREATE_USER', '');
create database mysqltest_1;
use mysqltest_1;
grant all on mysqltest_1.* to 'normal'@'%';
grant select on mysqltest_1.* to 'read_only'@'%';
grant select,insert on mysqltest_1.* to 'read_write'@'%';
grant select,insert,alter on mysqltest_1.* to 'alter'@'%';
grant alter on mysqltest_1.* to only_alter@'%';
connect normal,localhost,normal,,mysqltest_1;
connect read_only,localhost,read_only,,mysqltest_1;
connect read_write,localhost,read_write,,mysqltest_1;
connect alter,localhost,alter,,mysqltest_1;
connect only_alter, localhost, only_alter,,mysqltest_1;
connection normal;
create sequence s1;
select next value for s1;
next value for s1
1
alter sequence s1 restart= 11;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
11 1 9223372036854775806 1 1 1000 0 0
connection read_only;
select next value for s1;
ERROR 42000: INSERT command denied to user 'read_only'@'localhost' for table `mysqltest_1`.`s1`
alter sequence s1 restart= 11;
ERROR 42000: ALTER command denied to user 'read_only'@'localhost' for table `mysqltest_1`.`s1`
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
11 1 9223372036854775806 1 1 1000 0 0
connection read_write;
select next value for s1;
next value for s1
11
alter sequence s1 restart= 11;
ERROR 42000: ALTER command denied to user 'read_write'@'localhost' for table `mysqltest_1`.`s1`
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1011 1 9223372036854775806 1 1 1000 0 0
connection alter;
select next value for s1;
next value for s1
12
alter sequence s1 restart= 11;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
11 1 9223372036854775806 1 1 1000 0 0
connection only_alter;
select next value for s1;
ERROR 42000: SELECT, INSERT command denied to user 'only_alter'@'localhost' for table `mysqltest_1`.`s1`
alter sequence s1 restart= 11;
select * from s1;
ERROR 42000: SELECT command denied to user 'only_alter'@'localhost' for table `mysqltest_1`.`s1`
connection default;
drop user 'normal'@'%';
drop user 'read_only'@'%';
drop user 'read_write'@'%';
drop user 'alter'@'%';
drop user 'only_alter'@'%';
drop sequence s1;
#
# MDEV-36413 User without any privileges to a sequence can read from
# it and modify it via column default
#
create sequence s1;
create sequence s2;
select * from s2;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1 1 9223372036854775806 1 1 1000 0 0
create table t2 (a int not null default(nextval(s1)));
insert into t2 values();
create user u;
grant create, insert, select, drop on mysqltest_1.t1 to u;
grant insert, select on mysqltest_1.s1 to u;
grant select on mysqltest_1.t2 to u;
connect con1,localhost,u,,mysqltest_1;
select nextval(s2);
ERROR 42000: SELECT, INSERT command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
show create sequence s2;
ERROR 42000: SHOW command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
create table t1 (a int not null default(nextval(s1)));
drop table t1;
create table t1 (a int not null default(nextval(s1))) select a from t2;
insert into t1 values();
select * from t1;
a
1
2
drop table t1;
create table t1 (a int not null default(nextval(s1))) select a from (select t2.a from t2,t2 as t3 where t2.a=t3.a) as t4;
drop table t1;
create table t1 (a int not null default(nextval(s2)));
ERROR 42000: SELECT, INSERT command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
create table t1 (a int not null default(nextval(s1)),
b int not null default(nextval(s2)));
ERROR 42000: SELECT, INSERT command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
disconnect con1;
connection default;
drop user u;
create user u_alter;
create table t1 (id int);
grant alter on t1 to u_alter;
connect con_alter,localhost,u_alter,,mysqltest_1;
alter table t1 modify id int default nextval(s1);
ERROR 42000: SELECT, INSERT command denied to user 'u_alter'@'localhost' for table `mysqltest_1`.`s1`
connection default;
grant insert, select on s1 to u_alter;
connection con_alter;
alter table t1 modify id int default nextval(s1);
disconnect con_alter;
connection default;
drop user u_alter;
drop database mysqltest_1;
#
# MDEV-36870 Spurious unrelated permission error when selecting from table with default that uses nextval(sequence)
#
create database db1;
use db1;
create sequence s1 cache 0;
create table t1 (id int unsigned default (10+nextval(s1)));
insert t1 values ();
create table t2 (id int unsigned default nextval(s1), b int default(default(id)));
insert t2 values ();
create function f1(x int) returns int sql security invoker
begin
select id+x into x from t1;
return x;
insert t1 values ();
end|
create user u1@localhost;
grant select on db1.* to u1@localhost;
grant execute on db1.* to u1@localhost;
grant all privileges on test.* to u1@localhost;
use test;
create table t3 (id int unsigned default (20+nextval(db1.s1)), b int);
insert t3 values ();
create sequence s2 cache 0;
create table t4 (id int unsigned default (10+nextval(s2)), b int);
insert t4 values ();
connect u1,localhost,u1,,db1;
select * from t1;
id
11
connection default;
flush tables;
connection u1;
select * from t1;
id
11
select default(id) from t1;
ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
select * from t2;
id b
2 3
select f1(100);
f1(100)
111
select column_name, data_type, column_default from information_schema.columns where table_schema='db1' and table_name='t1';
column_name data_type column_default
id int (10 + nextval(`db1`.`s1`))
use test;
insert t3 values ();
ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
insert t4 values ();
insert t3 (b) select 5;
ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
insert t4 (b) select 5;
update t3 set id=default;
ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
update t4 set id=default;
prepare stmt from "update t3 set id=?";
execute stmt using default;
ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
prepare stmt from "update t4 set id=?";
execute stmt using default;
deallocate prepare stmt;
insert t4 (b) values ((select * from db1.t1));
insert t4 (b) values ((select default(id) from db1.t1));
ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
connection default;
disconnect u1;
select nextval(db1.s1) as 'must be 5';
must be 5
5
drop user u1@localhost;
drop database db1;
drop table t3, t4, s2;
# End of 10.6 tests
|
