Debian Codegraph @

mysql-test/suite/sql_sequence/grant.test

raw
1 #
2 # Test some grants with sequences
3 # Note that replication.test also does some grant testing
4 #
5
6 # Grant tests not performed with embedded server
7 -- source include/not_embedded.inc
8
9
10 SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_AUTO_CREATE_USER', '');
11 create database mysqltest_1;
12 use mysqltest_1;
13 grant all on mysqltest_1.* to 'normal'@'%';
14 grant select on mysqltest_1.* to 'read_only'@'%';
15 grant select,insert on mysqltest_1.* to 'read_write'@'%';
16 grant select,insert,alter on mysqltest_1.* to 'alter'@'%';
17 grant alter on mysqltest_1.* to only_alter@'%';
18
19 connect(normal,localhost,normal,,mysqltest_1);
20 connect(read_only,localhost,read_only,,mysqltest_1);
21 connect(read_write,localhost,read_write,,mysqltest_1);
22 connect(alter,localhost,alter,,mysqltest_1);
23 connect(only_alter, localhost, only_alter,,mysqltest_1);
24
25 --disable_ps2_protocol
26 connection normal;
27 create sequence s1;
28 select next value for s1;
29 alter sequence s1 restart= 11;
30 select * from s1;
31
32 connection read_only;
33 --error ER_TABLEACCESS_DENIED_ERROR
34 select next value for s1;
35 --error ER_TABLEACCESS_DENIED_ERROR
36 alter sequence s1 restart= 11;
37 select * from s1;
38
39 connection read_write;
40 select next value for s1;
41 --error ER_TABLEACCESS_DENIED_ERROR
42 alter sequence s1 restart= 11;
43 select * from s1;
44
45 connection alter;
46 select next value for s1;
47 alter sequence s1 restart= 11;
48 select * from s1;
49
50 connection only_alter;
51 --error ER_TABLEACCESS_DENIED_ERROR
52 select next value for s1;
53 alter sequence s1 restart= 11;
54 --error ER_TABLEACCESS_DENIED_ERROR
55 select * from s1;
56 --enable_ps2_protocol
57
58 #
59 # Cleanup
60 #
61
62 connection default;
63 drop user 'normal'@'%';
64 drop user 'read_only'@'%';
65 drop user 'read_write'@'%';
66 drop user 'alter'@'%';
67 drop user 'only_alter'@'%';
68 drop sequence s1;
69
70 --echo #
71 --echo # MDEV-36413 User without any privileges to a sequence can read from
72 --echo # it and modify it via column default
73 --echo #
74
75 create sequence s1;
76 create sequence s2;
77 select * from s2;
78 create table t2 (a int not null default(nextval(s1)));
79 insert into t2 values();
80
81 create user u;
82 grant create, insert, select, drop on mysqltest_1.t1 to u;
83 grant insert, select on mysqltest_1.s1 to u;
84 grant select on mysqltest_1.t2 to u;
85
86 --connect(con1,localhost,u,,mysqltest_1)
87 --error ER_TABLEACCESS_DENIED_ERROR
88 select nextval(s2);
89 --error ER_TABLEACCESS_DENIED_ERROR
90 show create sequence s2;
91
92 create table t1 (a int not null default(nextval(s1)));
93 drop table t1;
94 create table t1 (a int not null default(nextval(s1))) select a from t2;
95 insert into t1 values();
96 select * from t1;
97 drop table t1;
98 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;
99 drop table t1;
100 --error ER_TABLEACCESS_DENIED_ERROR
101 create table t1 (a int not null default(nextval(s2)));
102 --error ER_TABLEACCESS_DENIED_ERROR
103 create table t1 (a int not null default(nextval(s1)),
104 b int not null default(nextval(s2)));
105 --disconnect con1
106 --connection default
107 drop user u;
108
109 # ALTER for table with DEFAULT NEXTVAL(seq) column needs INSERT/SELECT on seq
110 # just like CREATE does in the example above
111 create user u_alter;
112 create table t1 (id int);
113 grant alter on t1 to u_alter;
114 --connect(con_alter,localhost,u_alter,,mysqltest_1)
115 --error ER_TABLEACCESS_DENIED_ERROR
116 alter table t1 modify id int default nextval(s1);
117 --connection default
118 grant insert, select on s1 to u_alter;
119 --connection con_alter
120 alter table t1 modify id int default nextval(s1);
121 --disconnect con_alter
122 --connection default
123 drop user u_alter;
124 drop database mysqltest_1;
125
126 --echo #
127 --echo # MDEV-36870 Spurious unrelated permission error when selecting from table with default that uses nextval(sequence)
128 --echo #
129
130 # various tests for permission checking on sequences
131 create database db1;
132 use db1;
133 create sequence s1 cache 0;
134 create table t1 (id int unsigned default (10+nextval(s1)));
135 insert t1 values ();
136
137 create table t2 (id int unsigned default nextval(s1), b int default(default(id)));
138 insert t2 values ();
139
140 # INSERT affects prelocking, but is never actually executed
141 delimiter |;
142 create function f1(x int) returns int sql security invoker
143 begin
144 select id+x into x from t1;
145 return x;
146 insert t1 values ();
147 end|
148 delimiter ;|
149
150 create user u1@localhost;
151 grant select on db1.* to u1@localhost;
152 grant execute on db1.* to u1@localhost;
153 grant all privileges on test.* to u1@localhost;
154
155 use test;
156 create table t3 (id int unsigned default (20+nextval(db1.s1)), b int);
157 insert t3 values ();
158
159 create sequence s2 cache 0;
160 create table t4 (id int unsigned default (10+nextval(s2)), b int);
161 insert t4 values ();
162
163 connect u1,localhost,u1,,db1;
164
165 # table already in the cache. must be re-fixed
166 # SELECT * - no error
167 select * from t1;
168
169 # not in cache
170 connection default;
171 flush tables;
172 connection u1;
173 # SELECT * - no error
174 select * from t1;
175
176 # SELECT DEFAULT() - error
177 --error ER_TABLEACCESS_DENIED_ERROR
178 select default(id) from t1;
179
180 # default(default(nextval))
181 select * from t2;
182
183 # SELECT but table has TL_WRITE because of prelocking
184 select f1(100);
185
186 # opening the table for I_S
187 select column_name, data_type, column_default from information_schema.columns where table_schema='db1' and table_name='t1';
188
189 use test;
190 # insert
191 --error ER_TABLEACCESS_DENIED_ERROR
192 insert t3 values ();
193 insert t4 values ();
194 #insert select
195 --error ER_TABLEACCESS_DENIED_ERROR
196 insert t3 (b) select 5;
197 insert t4 (b) select 5;
198 #update
199 --error ER_TABLEACCESS_DENIED_ERROR
200 update t3 set id=default;
201 update t4 set id=default;
202
203 # PS UPDATE with ? = DEFAULT
204 prepare stmt from "update t3 set id=?";
205 --error ER_TABLEACCESS_DENIED_ERROR
206 execute stmt using default;
207 prepare stmt from "update t4 set id=?";
208 execute stmt using default;
209 deallocate prepare stmt;
210
211 # SELECT * in a subquery, like INSERT t3 VALUES ((SELECT * FROM t1));
212 # with sequences both on t3 and t1
213 insert t4 (b) values ((select * from db1.t1));
214 --error ER_TABLEACCESS_DENIED_ERROR
215 insert t4 (b) values ((select default(id) from db1.t1));
216
217 connection default;
218 disconnect u1;
219 --disable_ps2_protocol
220 select nextval(db1.s1) as 'must be 5';
221 --enable_ps2_protocol
222 drop user u1@localhost;
223 drop database db1;
224 drop table t3, t4, s2;
225
226 --echo # End of 10.6 tests