Debian Codegraph @

mysql-test/suite/encryption/t/alter_table.test

raw
1 --echo #
2 --echo # WL#12261 Control (enforce and disable) table encryption
3 --echo #
4 --source include/have_debug.inc
5
6 --echo # Pre-define user u1, which is used in different tests below.
7 CREATE USER u1@localhost;
8 GRANT ALL ON db1.* TO u1@localhost;
9 GRANT CREATE TABLESPACE, PROCESS, SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
10 SET GLOBAL debug= '+d,skip_table_encryption_admin_check_for_set';
11 connect (con1, localhost, u1);
12
13 --echo # This test run ALTER TABLE in different configurations,
14 --echo #
15 --echo # - Setting table_encryption_privilege_check to true/false.
16 --echo # - Setting default_table_encryption to true/false.
17 --echo # - With and without user holding TABLE_ENCRYPTION_ADMIN privilege.
18 --echo # - Test SHOW CREATE TABLE
19 --echo # - Test INFORMATION_SCHEMA.TABLES.CREATE_OPTIONS
20 --echo # - Check for warnings generated.
21 --echo #
22 --echo # See comments in alter_table.inc for more details.
23
24 --echo `````````````````````````````````````````````````````````
25 --echo # Test using user tablespace 'ts1'
26 --let alter_tablespace_name=ts1
27 --let expected_error1=0
28 --let expected_error2=0
29 --let expected_error3=0
30 --let expected_error4=0
31 --let caseno=0
32
33 --echo `````````````````````````````````````````````````````````
34 --echo # table_encryption_privilege_check=OFF and default per-db encryption OFF
35 --let privilege_check=false
36 --let database_encryption='n'
37
38 --let table_encryption='n'
39 --let alter_encryption='n'
40 --source ./alter_table.inc
41 --let alter_encryption='y'
42 --source ./alter_table.inc
43
44 --echo `````````````````````````````````````````````````````````
45 --echo # Moving encrypted tablespace to unencrypted tablespace is rejected.
46 --let expected_error2=ER_TARGET_TABLESPACE_UNENCRYPTED
47 --let expected_error4=ER_TARGET_TABLESPACE_UNENCRYPTED
48 --let table_encryption='y'
49 --let alter_encryption='n'
50 --source ./alter_table.inc
51
52 --let alter_encryption='y'
53 --source ./alter_table.inc
54
55 --echo `````````````````````````````````````````````````````````
56 --echo # table_encryption_privilege_check=OFF and default per-db encryption ON
57 --let database_encryption='y'
58
59 --let table_encryption='n'
60 --let alter_encryption='n'
61 --source ./alter_table.inc
62 --let alter_encryption='y'
63 --source ./alter_table.inc
64
65 --echo `````````````````````````````````````````````````````````
66 --echo # Moving encrypted tablespace to unencrypted tablespace is rejected.
67 --let expected_error2=ER_TARGET_TABLESPACE_UNENCRYPTED
68 --let expected_error4=ER_TARGET_TABLESPACE_UNENCRYPTED
69 --let table_encryption='y'
70 --let alter_encryption='n'
71 --source ./alter_table.inc
72
73 --let alter_encryption='y'
74 --source ./alter_table.inc
75
76 --echo `````````````````````````````````````````````````````````
77 --echo # table_encryption_privilege_check=ON and default per-db encryption OFF
78 --let privilege_check=true
79 --let database_encryption='n'
80
81 --let table_encryption='n'
82 --let alter_encryption='n'
83 --source ./alter_table.inc
84
85 --echo `````````````````````````````````````````````````````````
86 --echo # Request to create encrypted table to unencrypted database is rejected.
87 --let expected_error1=ER_CANNOT_SET_TABLE_ENCRYPTION
88 --let expected_error2=ER_CANNOT_SET_TABLE_ENCRYPTION
89 --let expected_error3=ER_CANNOT_SET_TABLE_ENCRYPTION
90 --let expected_error4=ER_CANNOT_SET_TABLE_ENCRYPTION
91 --let alter_encryption='y'
92 --source ./alter_table.inc
93
94 --let has_grant=true
95 --source ./alter_table.inc
96 --let has_grant=false
97
98 --echo `````````````````````````````````````````````````````````
99 --echo # Moving encrypted tablespace to unencrypted tablespace is rejected.
100 --let table_encryption='y'
101 --let alter_encryption='n'
102 --let expected_error2=ER_TARGET_TABLESPACE_UNENCRYPTED
103 --let expected_error4=ER_TARGET_TABLESPACE_UNENCRYPTED
104 --source ./alter_table.inc
105
106 --let alter_encryption='y'
107 --source ./alter_table.inc
108
109 --echo `````````````````````````````````````````````````````````
110 --echo # table_encryption_privilege_check=ON and default per-db encryption ON
111 --let database_encryption='y'
112
113 --let table_encryption='n'
114 --let alter_encryption='n'
115 --source ./alter_table.inc
116 --let alter_encryption='y'
117 --source ./alter_table.inc
118
119 --let expected_error1=ER_CANNOT_SET_TABLE_ENCRYPTION
120 --let expected_error2=ER_TARGET_TABLESPACE_UNENCRYPTED
121 --let expected_error3=ER_CANNOT_SET_TABLE_ENCRYPTION
122 --let expected_error4=ER_TARGET_TABLESPACE_UNENCRYPTED
123 --let table_encryption='y'
124 --let alter_encryption='n'
125 --source ./alter_table.inc
126
127 --echo `````````````````````````````````````````````````````````
128 --echo # Moving encrypted tablespace to unencrypted tablespace is rejected.
129 --let has_grant=true
130 --let expected_error2=ER_TARGET_TABLESPACE_UNENCRYPTED
131 --let expected_error4=ER_TARGET_TABLESPACE_UNENCRYPTED
132 --source ./alter_table.inc
133
134 --let has_grant=false
135 --let alter_encryption='y'
136 --source ./alter_table.inc
137
138 --echo `````````````````````````````````````````````````````````
139 --echo # Using 'innodb_system' tablespace
140 --let alter_tablespace_name=innodb_system
141 --let expected_error1=0
142 --let expected_error2=0
143 --let expected_error3=0
144 --let expected_error4=0
145
146 --echo `````````````````````````````````````````````````````````
147 --echo # table_encryption_privilege_check=OFF and default per-db encryption OFF
148 --let privilege_check=false
149 --let database_encryption='n'
150
151 --let table_encryption='n'
152 --let alter_encryption='n'
153 --source ./alter_table.inc
154
155 --echo `````````````````````````````````````````````````````````
156 --echo # Creating a encrypted table is rejected in system tablespace
157 --let alter_encryption='y'
158 --let expected_error2=ER_INVALID_ENCRYPTION_REQUEST
159 --let expected_error4=ER_INVALID_ENCRYPTION_REQUEST
160 --source ./alter_table.inc
161
162 --let table_encryption='y'
163 --let alter_encryption='n'
164 --let expected_error2=ER_TARGET_TABLESPACE_UNENCRYPTED
165 --let expected_error4=ER_TARGET_TABLESPACE_UNENCRYPTED
166 --source ./alter_table.inc
167
168 --echo `````````````````````````````````````````````````````````
169 --echo # Creating a encrypted table is rejected in system tablespace
170 --let alter_encryption='y'
171 --let expected_error2=ER_INVALID_ENCRYPTION_REQUEST
172 --let expected_error4=ER_INVALID_ENCRYPTION_REQUEST
173 --source ./alter_table.inc
174
175 --echo `````````````````````````````````````````````````````````
176 --echo # table_encryption_privilege_check=OFF and default per-db encryption ON
177 --let database_encryption='y'
178
179 --let table_encryption='n'
180 --let alter_encryption='n'
181 --source ./alter_table.inc
182
183 --echo `````````````````````````````````````````````````````````
184 --echo # Creating a encrypted table is rejected in system tablespace
185 --let alter_encryption='y'
186 --let expected_error2=ER_INVALID_ENCRYPTION_REQUEST
187 --let expected_error4=ER_INVALID_ENCRYPTION_REQUEST
188 --source ./alter_table.inc
189
190 --let table_encryption='y'
191 --let alter_encryption='n'
192 --let expected_error2=ER_TARGET_TABLESPACE_UNENCRYPTED
193 --let expected_error4=ER_TARGET_TABLESPACE_UNENCRYPTED
194 --source ./alter_table.inc
195
196 --echo `````````````````````````````````````````````````````````
197 --echo # Creating a encrypted table is rejected in system tablespace
198 --let alter_encryption='y'
199 --let expected_error2=ER_INVALID_ENCRYPTION_REQUEST
200 --let expected_error4=ER_INVALID_ENCRYPTION_REQUEST
201 --source ./alter_table.inc
202
203 --echo `````````````````````````````````````````````````````````
204 --echo # table_encryption_privilege_check=ON and default per-db encryption OFF
205 --let privilege_check=true
206 --let database_encryption='n'
207
208 --let table_encryption='n'
209 --let alter_encryption='n'
210 --source ./alter_table.inc
211
212 --echo `````````````````````````````````````````````````````````
213 --echo # Request to create encrypted table to unencrypted database is rejected.
214 --let alter_encryption='y'
215 --let expected_error1=ER_CANNOT_SET_TABLE_ENCRYPTION
216 --let expected_error2=ER_INVALID_ENCRYPTION_REQUEST
217 --let expected_error3=ER_CANNOT_SET_TABLE_ENCRYPTION
218 --let expected_error4=ER_INVALID_ENCRYPTION_REQUEST
219 --source ./alter_table.inc
220
221 --echo `````````````````````````````````````````````````````````
222 --echo # Creating a encrypted table is rejected in system tablespace
223 --let has_grant=true
224 --let expected_error2=ER_INVALID_ENCRYPTION_REQUEST
225 --let expected_error4=ER_INVALID_ENCRYPTION_REQUEST
226 --source ./alter_table.inc
227 --let has_grant=false
228
229 --let table_encryption='y'
230 --let alter_encryption='n'
231 --let expected_error2=ER_TARGET_TABLESPACE_UNENCRYPTED
232 --let expected_error4=ER_TARGET_TABLESPACE_UNENCRYPTED
233 --source ./alter_table.inc
234
235 --echo `````````````````````````````````````````````````````````
236 --echo # Creating a encrypted table is rejected in system tablespace
237 --let alter_encryption='y'
238 --let expected_error2=ER_INVALID_ENCRYPTION_REQUEST
239 --let expected_error4=ER_INVALID_ENCRYPTION_REQUEST
240 --source ./alter_table.inc
241
242 --echo `````````````````````````````````````````````````````````
243 --echo # table_encryption_privilege_check=ON and default per-db encryption ON
244 --let database_encryption='y'
245
246 --let table_encryption='n'
247 --let alter_encryption='n'
248 --source ./alter_table.inc
249
250 --echo `````````````````````````````````````````````````````````
251 --echo # Creating a encrypted table is rejected in system tablespace
252 --let alter_encryption='y'
253 --let expected_error2=ER_INVALID_ENCRYPTION_REQUEST
254 --let expected_error4=ER_INVALID_ENCRYPTION_REQUEST
255 --source ./alter_table.inc
256
257 --echo `````````````````````````````````````````````````````````
258 --echo # Request to create unencrypted table under database with default
259 --echo # encryption=ON is rejected.
260 --let table_encryption='y'
261 --let alter_encryption='n'
262 --let expected_error1=ER_CANNOT_SET_TABLE_ENCRYPTION
263 --let expected_error2=ER_TARGET_TABLESPACE_UNENCRYPTED
264 --let expected_error3=ER_CANNOT_SET_TABLE_ENCRYPTION
265 --let expected_error4=ER_TARGET_TABLESPACE_UNENCRYPTED
266 --source ./alter_table.inc
267 --let has_grant=true
268 --let expected_error2=ER_TARGET_TABLESPACE_UNENCRYPTED
269 --let expected_error4=ER_TARGET_TABLESPACE_UNENCRYPTED
270 --source ./alter_table.inc
271 --let has_grant=false
272
273 --echo `````````````````````````````````````````````````````````
274 --echo # Creating a encrypted table is rejected in system tablespace
275 --let alter_encryption='y'
276 --let expected_error2=ER_INVALID_ENCRYPTION_REQUEST
277 --let expected_error4=ER_INVALID_ENCRYPTION_REQUEST
278 --source ./alter_table.inc
279
280 --echo `````````````````````````````````````````````````````````
281 --echo # Test using 'innodb_file_per_table'
282 --let alter_tablespace_name=innodb_file_per_table
283 --let expected_error1=0
284 --let expected_error2=0
285 --let expected_error3=0
286 --let expected_error4=0
287
288 --echo `````````````````````````````````````````````````````````
289 --echo # table_encryption_privilege_check=OFF and default per-db encryption OFF
290 --let privilege_check=false
291 --let database_encryption='n'
292
293 --let table_encryption='n'
294 --let alter_encryption='n'
295 --source ./alter_table.inc
296 --let alter_encryption='y'
297 --source ./alter_table.inc
298
299 --let table_encryption='y'
300 --let alter_encryption='n'
301 --source ./alter_table.inc
302 --let alter_encryption='y'
303 --source ./alter_table.inc
304
305 --echo `````````````````````````````````````````````````````````
306 --echo # table_encryption_privilege_check=OFF and default per-db encryption ON
307 --let database_encryption='y'
308
309 --let table_encryption='n'
310 --let alter_encryption='n'
311 --source ./alter_table.inc
312 --let alter_encryption='y'
313 --source ./alter_table.inc
314
315 --let table_encryption='y'
316 --let alter_encryption='n'
317 --source ./alter_table.inc
318 --let alter_encryption='y'
319 --source ./alter_table.inc
320
321 --echo `````````````````````````````````````````````````````````
322 --echo # table_encryption_privilege_check=ON and default per-db encryption OFF
323 --let privilege_check=true
324 --let database_encryption='n'
325
326 --let table_encryption='n'
327 --let alter_encryption='n'
328 --source ./alter_table.inc
329
330 --echo `````````````````````````````````````````````````````````
331 --echo # Request to create encrypted table to database with default
332 --echo # encryption=OFF is rejected.
333 --let alter_encryption='y'
334 --let expected_error1=ER_CANNOT_SET_TABLE_ENCRYPTION
335 --let expected_error2=ER_CANNOT_SET_TABLE_ENCRYPTION
336 --let expected_error3=ER_CANNOT_SET_TABLE_ENCRYPTION
337 --let expected_error4=ER_CANNOT_SET_TABLE_ENCRYPTION
338 --source ./alter_table.inc
339 --let has_grant=true
340 --source ./alter_table.inc
341 --let has_grant=false
342
343 --let table_encryption='y'
344 --let alter_encryption='n'
345 --source ./alter_table.inc
346 --let alter_encryption='y'
347 --source ./alter_table.inc
348
349 --echo `````````````````````````````````````````````````````````
350 --echo # table_encryption_privilege_check=ON and default per-db encryption ON
351 --let database_encryption='y'
352
353 --let table_encryption='n'
354 --let alter_encryption='n'
355 --source ./alter_table.inc
356 --let alter_encryption='y'
357 --source ./alter_table.inc
358
359 --echo `````````````````````````````````````````````````````````
360 --echo # Request to create unencrypted table to database with default
361 --echo # encryption=ON is rejected.
362 --let table_encryption='y'
363 --let alter_encryption='n'
364 --let expected_error1=ER_CANNOT_SET_TABLE_ENCRYPTION
365 --let expected_error2=ER_CANNOT_SET_TABLE_ENCRYPTION
366 --let expected_error3=ER_CANNOT_SET_TABLE_ENCRYPTION
367 --let expected_error4=ER_CANNOT_SET_TABLE_ENCRYPTION
368 --source ./alter_table.inc
369
370 --let has_grant=true
371 --source ./alter_table.inc
372 --let has_grant=false
373
374 --let alter_encryption='y'
375 --source ./alter_table.inc
376
377 --echo # Cleanup
378 disconnect con1;
379 connection default;
380 DROP USER u1@localhost;
381 SET GLOBAL debug= '-d,skip_table_encryption_admin_check_for_set';