Debian Codegraph @

mysql-test/suite/encryption/r/tablespace_with_tables.result

raw
1 #
2 # WL#12261 Control (enforce and disable) table encryption
3 #
4 # Pre-define user u1, which is used in different tests below.
5 CREATE USER u1@localhost;
6 GRANT ALL ON db1.* TO u1@localhost;
7 GRANT CREATE TABLESPACE, PROCESS, SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
8 SET GLOBAL debug= '+d,skip_table_encryption_admin_check_for_set';
9 # The test cases run ALTER TABLESPACE to check its encryption mode.
10 # The importance of this test is to check the way ALTER TABLESPACE
11 # updates the ENCRYPTION clause of tables in it.
12 # We run this command in various configuration as,
13 #
14 # - Setting table_encryption_privilege_check to true/false.
15 # - Setting per database default encryption to true/false.
16 # - With and without user holding TABLE_ENCRYPTION_ADMIN privilege.
17 # - Check for warnings generated.
18 #
19 `````````````````````````````````````````````````````````
20 # Unencrypted TABLESPACE to Unencrypted TABLESPACE (Nop)
21 # [ALTER TABLESPACE] Case 1 )
22 `````````````````````````````````````````````````````````
23 # Create required schema to run ALTER TABLESPACE.
24 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
25 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
26 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
27 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
28 SHOW CREATE TABLE db1.t1;
29 Table Create Table
30 t1 CREATE TABLE `t1` (
31 `f1` int DEFAULT NULL
32 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
33 SHOW CREATE TABLE db1.t2;
34 Table Create Table
35 t2 CREATE TABLE `t2` (
36 `f1` int DEFAULT NULL
37 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
38 SET SESSION default_table_encryption=false;
39 # Run ALTER TABLESPACE
40 SET GLOBAL table_encryption_privilege_check=false;
41 ALTER TABLESPACE ts1 ENCRYPTION='n';
42 SHOW WARNINGS;
43 Level Code Message
44 SHOW CREATE TABLE db1.t1;
45 Table Create Table
46 t1 CREATE TABLE `t1` (
47 `f1` int DEFAULT NULL
48 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
49 SHOW CREATE TABLE db1.t2;
50 Table Create Table
51 t2 CREATE TABLE `t2` (
52 `f1` int DEFAULT NULL
53 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
54 SET GLOBAL table_encryption_privilege_check=false;
55 SET SESSION default_table_encryption=false;
56 # clean up
57 DROP DATABASE db1;
58 DROP TABLESPACE ts1;
59 #
60 # [ALTER TABLESPACE] Case 2 )
61 `````````````````````````````````````````````````````````
62 # Create required schema to run ALTER TABLESPACE.
63 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
64 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
65 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
66 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
67 SHOW CREATE TABLE db1.t1;
68 Table Create Table
69 t1 CREATE TABLE `t1` (
70 `f1` int DEFAULT NULL
71 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
72 SHOW CREATE TABLE db1.t2;
73 Table Create Table
74 t2 CREATE TABLE `t2` (
75 `f1` int DEFAULT NULL
76 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
77 SET SESSION default_table_encryption=false;
78 # Run ALTER TABLESPACE
79 SET GLOBAL table_encryption_privilege_check=true;
80 ALTER TABLESPACE ts1 ENCRYPTION='n';
81 SHOW WARNINGS;
82 Level Code Message
83 SHOW CREATE TABLE db1.t1;
84 Table Create Table
85 t1 CREATE TABLE `t1` (
86 `f1` int DEFAULT NULL
87 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
88 SHOW CREATE TABLE db1.t2;
89 Table Create Table
90 t2 CREATE TABLE `t2` (
91 `f1` int DEFAULT NULL
92 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
93 SET GLOBAL table_encryption_privilege_check=false;
94 SET SESSION default_table_encryption=false;
95 # clean up
96 DROP DATABASE db1;
97 DROP TABLESPACE ts1;
98 #
99 `````````````````````````````````````````````````````````
100 # Encrypted TABLESPACE to Encrypted TABLESPACE (Nop)
101 # [ALTER TABLESPACE] Case 3 )
102 `````````````````````````````````````````````````````````
103 # Create required schema to run ALTER TABLESPACE.
104 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='y';
105 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
106 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
107 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
108 SHOW CREATE TABLE db1.t1;
109 Table Create Table
110 t1 CREATE TABLE `t1` (
111 `f1` int DEFAULT NULL
112 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
113 SHOW CREATE TABLE db1.t2;
114 Table Create Table
115 t2 CREATE TABLE `t2` (
116 `f1` int DEFAULT NULL
117 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
118 SET SESSION default_table_encryption=true;
119 # Run ALTER TABLESPACE
120 SET GLOBAL table_encryption_privilege_check=false;
121 ALTER TABLESPACE ts1 ENCRYPTION='y';
122 SHOW WARNINGS;
123 Level Code Message
124 SHOW CREATE TABLE db1.t1;
125 Table Create Table
126 t1 CREATE TABLE `t1` (
127 `f1` int DEFAULT NULL
128 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
129 SHOW CREATE TABLE db1.t2;
130 Table Create Table
131 t2 CREATE TABLE `t2` (
132 `f1` int DEFAULT NULL
133 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
134 SET GLOBAL table_encryption_privilege_check=false;
135 SET SESSION default_table_encryption=false;
136 # clean up
137 DROP DATABASE db1;
138 DROP TABLESPACE ts1;
139 #
140 # [ALTER TABLESPACE] Case 4 )
141 `````````````````````````````````````````````````````````
142 # Create required schema to run ALTER TABLESPACE.
143 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='y';
144 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
145 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
146 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
147 SHOW CREATE TABLE db1.t1;
148 Table Create Table
149 t1 CREATE TABLE `t1` (
150 `f1` int DEFAULT NULL
151 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
152 SHOW CREATE TABLE db1.t2;
153 Table Create Table
154 t2 CREATE TABLE `t2` (
155 `f1` int DEFAULT NULL
156 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
157 SET SESSION default_table_encryption=true;
158 # Run ALTER TABLESPACE
159 SET GLOBAL table_encryption_privilege_check=true;
160 ALTER TABLESPACE ts1 ENCRYPTION='y';
161 SHOW WARNINGS;
162 Level Code Message
163 SHOW CREATE TABLE db1.t1;
164 Table Create Table
165 t1 CREATE TABLE `t1` (
166 `f1` int DEFAULT NULL
167 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
168 SHOW CREATE TABLE db1.t2;
169 Table Create Table
170 t2 CREATE TABLE `t2` (
171 `f1` int DEFAULT NULL
172 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
173 SET GLOBAL table_encryption_privilege_check=false;
174 SET SESSION default_table_encryption=false;
175 # clean up
176 DROP DATABASE db1;
177 DROP TABLESPACE ts1;
178 #
179 # [ALTER TABLESPACE] Case 5 )
180 `````````````````````````````````````````````````````````
181 # Grant user with TABLE_ENCRYPTION_ADMIN
182 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
183 # Create required schema to run ALTER TABLESPACE.
184 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='y';
185 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
186 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
187 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
188 SHOW CREATE TABLE db1.t1;
189 Table Create Table
190 t1 CREATE TABLE `t1` (
191 `f1` int DEFAULT NULL
192 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
193 SHOW CREATE TABLE db1.t2;
194 Table Create Table
195 t2 CREATE TABLE `t2` (
196 `f1` int DEFAULT NULL
197 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
198 SET SESSION default_table_encryption=true;
199 # Run ALTER TABLESPACE
200 SET GLOBAL table_encryption_privilege_check=true;
201 ALTER TABLESPACE ts1 ENCRYPTION='y';
202 SHOW WARNINGS;
203 Level Code Message
204 SHOW CREATE TABLE db1.t1;
205 Table Create Table
206 t1 CREATE TABLE `t1` (
207 `f1` int DEFAULT NULL
208 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
209 SHOW CREATE TABLE db1.t2;
210 Table Create Table
211 t2 CREATE TABLE `t2` (
212 `f1` int DEFAULT NULL
213 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
214 SET GLOBAL table_encryption_privilege_check=false;
215 SET SESSION default_table_encryption=false;
216 # clean up
217 # Revoke TABLE_ENCRYPTION_ADMIN from user
218 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
219 DROP DATABASE db1;
220 DROP TABLESPACE ts1;
221 #
222 `````````````````````````````````````````````````````````
223 # Unencrypted TABLESPACE to encrypted TABLESPACE
224 # with database encryption default 'n'
225 # [ALTER TABLESPACE] Case 6 )
226 `````````````````````````````````````````````````````````
227 # Create required schema to run ALTER TABLESPACE.
228 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
229 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
230 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
231 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
232 SHOW CREATE TABLE db1.t1;
233 Table Create Table
234 t1 CREATE TABLE `t1` (
235 `f1` int DEFAULT NULL
236 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
237 SHOW CREATE TABLE db1.t2;
238 Table Create Table
239 t2 CREATE TABLE `t2` (
240 `f1` int DEFAULT NULL
241 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
242 SET SESSION default_table_encryption=true;
243 # Run ALTER TABLESPACE
244 SET GLOBAL table_encryption_privilege_check=false;
245 ALTER TABLESPACE ts1 ENCRYPTION='y';
246 SHOW WARNINGS;
247 Level Code Message
248 SHOW CREATE TABLE db1.t1;
249 Table Create Table
250 t1 CREATE TABLE `t1` (
251 `f1` int DEFAULT NULL
252 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
253 SHOW CREATE TABLE db1.t2;
254 Table Create Table
255 t2 CREATE TABLE `t2` (
256 `f1` int DEFAULT NULL
257 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
258 SET GLOBAL table_encryption_privilege_check=false;
259 SET SESSION default_table_encryption=false;
260 # clean up
261 DROP DATABASE db1;
262 DROP TABLESPACE ts1;
263 #
264 `````````````````````````````````````````````````````````
265 # Reject creating encrypted table in database with default encryption='n'
266 # [ALTER TABLESPACE] Case 7 )
267 `````````````````````````````````````````````````````````
268 # Create required schema to run ALTER TABLESPACE.
269 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
270 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
271 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
272 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
273 SHOW CREATE TABLE db1.t1;
274 Table Create Table
275 t1 CREATE TABLE `t1` (
276 `f1` int DEFAULT NULL
277 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
278 SHOW CREATE TABLE db1.t2;
279 Table Create Table
280 t2 CREATE TABLE `t2` (
281 `f1` int DEFAULT NULL
282 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
283 SET SESSION default_table_encryption=true;
284 # Run ALTER TABLESPACE
285 SET GLOBAL table_encryption_privilege_check=true;
286 ALTER TABLESPACE ts1 ENCRYPTION='y';
287 ERROR HY000: This tablespace can't be encrypted, because one of table's schema has default encryption OFF and user doesn't have enough privilege.
288 SHOW WARNINGS;
289 Level Code Message
290 Error 3829 This tablespace can't be encrypted, because one of table's schema has default encryption OFF and user doesn't have enough privilege.
291 SHOW CREATE TABLE db1.t1;
292 Table Create Table
293 t1 CREATE TABLE `t1` (
294 `f1` int DEFAULT NULL
295 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
296 SHOW CREATE TABLE db1.t2;
297 Table Create Table
298 t2 CREATE TABLE `t2` (
299 `f1` int DEFAULT NULL
300 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
301 SET GLOBAL table_encryption_privilege_check=false;
302 SET SESSION default_table_encryption=false;
303 # clean up
304 DROP DATABASE db1;
305 DROP TABLESPACE ts1;
306 #
307 # [ALTER TABLESPACE] Case 8 )
308 `````````````````````````````````````````````````````````
309 # Grant user with TABLE_ENCRYPTION_ADMIN
310 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
311 # Create required schema to run ALTER TABLESPACE.
312 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
313 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
314 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
315 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
316 SHOW CREATE TABLE db1.t1;
317 Table Create Table
318 t1 CREATE TABLE `t1` (
319 `f1` int DEFAULT NULL
320 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
321 SHOW CREATE TABLE db1.t2;
322 Table Create Table
323 t2 CREATE TABLE `t2` (
324 `f1` int DEFAULT NULL
325 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
326 SET SESSION default_table_encryption=true;
327 # Run ALTER TABLESPACE
328 SET GLOBAL table_encryption_privilege_check=true;
329 ALTER TABLESPACE ts1 ENCRYPTION='y';
330 SHOW WARNINGS;
331 Level Code Message
332 SHOW CREATE TABLE db1.t1;
333 Table Create Table
334 t1 CREATE TABLE `t1` (
335 `f1` int DEFAULT NULL
336 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
337 SHOW CREATE TABLE db1.t2;
338 Table Create Table
339 t2 CREATE TABLE `t2` (
340 `f1` int DEFAULT NULL
341 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
342 SET GLOBAL table_encryption_privilege_check=false;
343 SET SESSION default_table_encryption=false;
344 # clean up
345 # Revoke TABLE_ENCRYPTION_ADMIN from user
346 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
347 DROP DATABASE db1;
348 DROP TABLESPACE ts1;
349 #
350 # With some tables without ENCRYPTION clause;
351 # [ALTER TABLESPACE] Case 9 )
352 `````````````````````````````````````````````````````````
353 # Create required schema to run ALTER TABLESPACE.
354 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
355 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
356 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
357 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1;
358 SHOW CREATE TABLE db1.t1;
359 Table Create Table
360 t1 CREATE TABLE `t1` (
361 `f1` int DEFAULT NULL
362 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
363 SHOW CREATE TABLE db1.t2;
364 Table Create Table
365 t2 CREATE TABLE `t2` (
366 `f1` int DEFAULT NULL
367 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
368 SET SESSION default_table_encryption=true;
369 # Run ALTER TABLESPACE
370 SET GLOBAL table_encryption_privilege_check=false;
371 ALTER TABLESPACE ts1 ENCRYPTION='y';
372 SHOW WARNINGS;
373 Level Code Message
374 SHOW CREATE TABLE db1.t1;
375 Table Create Table
376 t1 CREATE TABLE `t1` (
377 `f1` int DEFAULT NULL
378 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
379 SHOW CREATE TABLE db1.t2;
380 Table Create Table
381 t2 CREATE TABLE `t2` (
382 `f1` int DEFAULT NULL
383 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
384 SET GLOBAL table_encryption_privilege_check=false;
385 SET SESSION default_table_encryption=false;
386 # clean up
387 DROP DATABASE db1;
388 DROP TABLESPACE ts1;
389 #
390 `````````````````````````````````````````````````````````
391 # Reject creating encrypted table in database with default encryption='n'
392 # [ALTER TABLESPACE] Case 10 )
393 `````````````````````````````````````````````````````````
394 # Create required schema to run ALTER TABLESPACE.
395 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
396 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
397 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
398 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1;
399 SHOW CREATE TABLE db1.t1;
400 Table Create Table
401 t1 CREATE TABLE `t1` (
402 `f1` int DEFAULT NULL
403 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
404 SHOW CREATE TABLE db1.t2;
405 Table Create Table
406 t2 CREATE TABLE `t2` (
407 `f1` int DEFAULT NULL
408 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
409 SET SESSION default_table_encryption=true;
410 # Run ALTER TABLESPACE
411 SET GLOBAL table_encryption_privilege_check=true;
412 ALTER TABLESPACE ts1 ENCRYPTION='y';
413 ERROR HY000: This tablespace can't be encrypted, because one of table's schema has default encryption OFF and user doesn't have enough privilege.
414 SHOW WARNINGS;
415 Level Code Message
416 Error 3829 This tablespace can't be encrypted, because one of table's schema has default encryption OFF and user doesn't have enough privilege.
417 SHOW CREATE TABLE db1.t1;
418 Table Create Table
419 t1 CREATE TABLE `t1` (
420 `f1` int DEFAULT NULL
421 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
422 SHOW CREATE TABLE db1.t2;
423 Table Create Table
424 t2 CREATE TABLE `t2` (
425 `f1` int DEFAULT NULL
426 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
427 SET GLOBAL table_encryption_privilege_check=false;
428 SET SESSION default_table_encryption=false;
429 # clean up
430 DROP DATABASE db1;
431 DROP TABLESPACE ts1;
432 #
433 # [ALTER TABLESPACE] Case 11 )
434 `````````````````````````````````````````````````````````
435 # Grant user with TABLE_ENCRYPTION_ADMIN
436 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
437 # Create required schema to run ALTER TABLESPACE.
438 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
439 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
440 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
441 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1;
442 SHOW CREATE TABLE db1.t1;
443 Table Create Table
444 t1 CREATE TABLE `t1` (
445 `f1` int DEFAULT NULL
446 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
447 SHOW CREATE TABLE db1.t2;
448 Table Create Table
449 t2 CREATE TABLE `t2` (
450 `f1` int DEFAULT NULL
451 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
452 SET SESSION default_table_encryption=true;
453 # Run ALTER TABLESPACE
454 SET GLOBAL table_encryption_privilege_check=true;
455 ALTER TABLESPACE ts1 ENCRYPTION='y';
456 SHOW WARNINGS;
457 Level Code Message
458 SHOW CREATE TABLE db1.t1;
459 Table Create Table
460 t1 CREATE TABLE `t1` (
461 `f1` int DEFAULT NULL
462 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
463 SHOW CREATE TABLE db1.t2;
464 Table Create Table
465 t2 CREATE TABLE `t2` (
466 `f1` int DEFAULT NULL
467 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
468 SET GLOBAL table_encryption_privilege_check=false;
469 SET SESSION default_table_encryption=false;
470 # clean up
471 # Revoke TABLE_ENCRYPTION_ADMIN from user
472 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
473 DROP DATABASE db1;
474 DROP TABLESPACE ts1;
475 #
476 # With all tables without ENCRYPTION clause;
477 # [ALTER TABLESPACE] Case 12 )
478 `````````````````````````````````````````````````````````
479 # Create required schema to run ALTER TABLESPACE.
480 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
481 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
482 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1;
483 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1;
484 SHOW CREATE TABLE db1.t1;
485 Table Create Table
486 t1 CREATE TABLE `t1` (
487 `f1` int DEFAULT NULL
488 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
489 SHOW CREATE TABLE db1.t2;
490 Table Create Table
491 t2 CREATE TABLE `t2` (
492 `f1` int DEFAULT NULL
493 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
494 SET SESSION default_table_encryption=true;
495 # Run ALTER TABLESPACE
496 SET GLOBAL table_encryption_privilege_check=false;
497 ALTER TABLESPACE ts1 ENCRYPTION='y';
498 SHOW WARNINGS;
499 Level Code Message
500 SHOW CREATE TABLE db1.t1;
501 Table Create Table
502 t1 CREATE TABLE `t1` (
503 `f1` int DEFAULT NULL
504 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
505 SHOW CREATE TABLE db1.t2;
506 Table Create Table
507 t2 CREATE TABLE `t2` (
508 `f1` int DEFAULT NULL
509 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
510 SET GLOBAL table_encryption_privilege_check=false;
511 SET SESSION default_table_encryption=false;
512 # clean up
513 DROP DATABASE db1;
514 DROP TABLESPACE ts1;
515 #
516 `````````````````````````````````````````````````````````
517 # Request to create encrypted tablespace with default_table_encryption='n'
518 # [ALTER TABLESPACE] Case 13 )
519 `````````````````````````````````````````````````````````
520 # Create required schema to run ALTER TABLESPACE.
521 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
522 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
523 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1;
524 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1;
525 SHOW CREATE TABLE db1.t1;
526 Table Create Table
527 t1 CREATE TABLE `t1` (
528 `f1` int DEFAULT NULL
529 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
530 SHOW CREATE TABLE db1.t2;
531 Table Create Table
532 t2 CREATE TABLE `t2` (
533 `f1` int DEFAULT NULL
534 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
535 SET SESSION default_table_encryption=true;
536 # Run ALTER TABLESPACE
537 SET GLOBAL table_encryption_privilege_check=true;
538 ALTER TABLESPACE ts1 ENCRYPTION='y';
539 ERROR HY000: This tablespace can't be encrypted, because one of table's schema has default encryption OFF and user doesn't have enough privilege.
540 SHOW WARNINGS;
541 Level Code Message
542 Error 3829 This tablespace can't be encrypted, because one of table's schema has default encryption OFF and user doesn't have enough privilege.
543 SHOW CREATE TABLE db1.t1;
544 Table Create Table
545 t1 CREATE TABLE `t1` (
546 `f1` int DEFAULT NULL
547 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
548 SHOW CREATE TABLE db1.t2;
549 Table Create Table
550 t2 CREATE TABLE `t2` (
551 `f1` int DEFAULT NULL
552 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
553 SET GLOBAL table_encryption_privilege_check=false;
554 SET SESSION default_table_encryption=false;
555 # clean up
556 DROP DATABASE db1;
557 DROP TABLESPACE ts1;
558 #
559 # [ALTER TABLESPACE] Case 14 )
560 `````````````````````````````````````````````````````````
561 # Grant user with TABLE_ENCRYPTION_ADMIN
562 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
563 # Create required schema to run ALTER TABLESPACE.
564 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
565 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
566 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1;
567 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1;
568 SHOW CREATE TABLE db1.t1;
569 Table Create Table
570 t1 CREATE TABLE `t1` (
571 `f1` int DEFAULT NULL
572 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
573 SHOW CREATE TABLE db1.t2;
574 Table Create Table
575 t2 CREATE TABLE `t2` (
576 `f1` int DEFAULT NULL
577 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
578 SET SESSION default_table_encryption=true;
579 # Run ALTER TABLESPACE
580 SET GLOBAL table_encryption_privilege_check=true;
581 ALTER TABLESPACE ts1 ENCRYPTION='y';
582 SHOW WARNINGS;
583 Level Code Message
584 SHOW CREATE TABLE db1.t1;
585 Table Create Table
586 t1 CREATE TABLE `t1` (
587 `f1` int DEFAULT NULL
588 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
589 SHOW CREATE TABLE db1.t2;
590 Table Create Table
591 t2 CREATE TABLE `t2` (
592 `f1` int DEFAULT NULL
593 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
594 SET GLOBAL table_encryption_privilege_check=false;
595 SET SESSION default_table_encryption=false;
596 # clean up
597 # Revoke TABLE_ENCRYPTION_ADMIN from user
598 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
599 DROP DATABASE db1;
600 DROP TABLESPACE ts1;
601 #
602 `````````````````````````````````````````````````````````
603 # Unencrypted TABLESPACE to encrypted TABLESPACE
604 # with database encryption 'y'
605 # [ALTER TABLESPACE] Case 15 )
606 `````````````````````````````````````````````````````````
607 # Create required schema to run ALTER TABLESPACE.
608 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
609 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
610 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
611 Warnings:
612 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
613 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
614 Warnings:
615 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
616 SHOW CREATE TABLE db1.t1;
617 Table Create Table
618 t1 CREATE TABLE `t1` (
619 `f1` int DEFAULT NULL
620 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
621 SHOW CREATE TABLE db1.t2;
622 Table Create Table
623 t2 CREATE TABLE `t2` (
624 `f1` int DEFAULT NULL
625 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
626 SET SESSION default_table_encryption=true;
627 # Run ALTER TABLESPACE
628 SET GLOBAL table_encryption_privilege_check=false;
629 ALTER TABLESPACE ts1 ENCRYPTION='y';
630 SHOW WARNINGS;
631 Level Code Message
632 SHOW CREATE TABLE db1.t1;
633 Table Create Table
634 t1 CREATE TABLE `t1` (
635 `f1` int DEFAULT NULL
636 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
637 SHOW CREATE TABLE db1.t2;
638 Table Create Table
639 t2 CREATE TABLE `t2` (
640 `f1` int DEFAULT NULL
641 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
642 SET GLOBAL table_encryption_privilege_check=false;
643 SET SESSION default_table_encryption=false;
644 # clean up
645 DROP DATABASE db1;
646 DROP TABLESPACE ts1;
647 #
648 # [ALTER TABLESPACE] Case 16 )
649 `````````````````````````````````````````````````````````
650 # Create required schema to run ALTER TABLESPACE.
651 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
652 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
653 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
654 Warnings:
655 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
656 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
657 Warnings:
658 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
659 SHOW CREATE TABLE db1.t1;
660 Table Create Table
661 t1 CREATE TABLE `t1` (
662 `f1` int DEFAULT NULL
663 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
664 SHOW CREATE TABLE db1.t2;
665 Table Create Table
666 t2 CREATE TABLE `t2` (
667 `f1` int DEFAULT NULL
668 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
669 SET SESSION default_table_encryption=true;
670 # Run ALTER TABLESPACE
671 SET GLOBAL table_encryption_privilege_check=true;
672 ALTER TABLESPACE ts1 ENCRYPTION='y';
673 SHOW WARNINGS;
674 Level Code Message
675 SHOW CREATE TABLE db1.t1;
676 Table Create Table
677 t1 CREATE TABLE `t1` (
678 `f1` int DEFAULT NULL
679 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
680 SHOW CREATE TABLE db1.t2;
681 Table Create Table
682 t2 CREATE TABLE `t2` (
683 `f1` int DEFAULT NULL
684 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
685 SET GLOBAL table_encryption_privilege_check=false;
686 SET SESSION default_table_encryption=false;
687 # clean up
688 DROP DATABASE db1;
689 DROP TABLESPACE ts1;
690 #
691 # [ALTER TABLESPACE] Case 17 )
692 `````````````````````````````````````````````````````````
693 # Grant user with TABLE_ENCRYPTION_ADMIN
694 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
695 # Create required schema to run ALTER TABLESPACE.
696 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='n';
697 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
698 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
699 Warnings:
700 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
701 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='n';
702 Warnings:
703 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
704 SHOW CREATE TABLE db1.t1;
705 Table Create Table
706 t1 CREATE TABLE `t1` (
707 `f1` int DEFAULT NULL
708 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
709 SHOW CREATE TABLE db1.t2;
710 Table Create Table
711 t2 CREATE TABLE `t2` (
712 `f1` int DEFAULT NULL
713 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
714 SET SESSION default_table_encryption=true;
715 # Run ALTER TABLESPACE
716 SET GLOBAL table_encryption_privilege_check=true;
717 ALTER TABLESPACE ts1 ENCRYPTION='y';
718 SHOW WARNINGS;
719 Level Code Message
720 SHOW CREATE TABLE db1.t1;
721 Table Create Table
722 t1 CREATE TABLE `t1` (
723 `f1` int DEFAULT NULL
724 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
725 SHOW CREATE TABLE db1.t2;
726 Table Create Table
727 t2 CREATE TABLE `t2` (
728 `f1` int DEFAULT NULL
729 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
730 SET GLOBAL table_encryption_privilege_check=false;
731 SET SESSION default_table_encryption=false;
732 # clean up
733 # Revoke TABLE_ENCRYPTION_ADMIN from user
734 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
735 DROP DATABASE db1;
736 DROP TABLESPACE ts1;
737 #
738 # Case 17.1 With some tables without ENCRYPTION clause;
739 # CREATE table using unencrypted tablespace without
740 # ENCRYPTION clause would inherit ENCRYPTION from database.
741 # This makes CREATE TABLE fail because the ENCRYPTION clause
742 # and the tablespace encryption type mismatches. The test
743 # encryption.create_table does test this.
744 # Case 17.2 With all tables without ENCRYPTION clause;
745 # Behavior would be same as described in Case 17.1
746 `````````````````````````````````````````````````````````
747 # Encrypted TABLESPACE to unencrypted TABLESPACE
748 # with database encryption 'n'
749 # [ALTER TABLESPACE] Case 18 )
750 `````````````````````````````````````````````````````````
751 # Create required schema to run ALTER TABLESPACE.
752 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='y';
753 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
754 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
755 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
756 SHOW CREATE TABLE db1.t1;
757 Table Create Table
758 t1 CREATE TABLE `t1` (
759 `f1` int DEFAULT NULL
760 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
761 SHOW CREATE TABLE db1.t2;
762 Table Create Table
763 t2 CREATE TABLE `t2` (
764 `f1` int DEFAULT NULL
765 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
766 SET SESSION default_table_encryption=false;
767 # Run ALTER TABLESPACE
768 SET GLOBAL table_encryption_privilege_check=false;
769 ALTER TABLESPACE ts1 ENCRYPTION='n';
770 SHOW WARNINGS;
771 Level Code Message
772 SHOW CREATE TABLE db1.t1;
773 Table Create Table
774 t1 CREATE TABLE `t1` (
775 `f1` int DEFAULT NULL
776 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
777 SHOW CREATE TABLE db1.t2;
778 Table Create Table
779 t2 CREATE TABLE `t2` (
780 `f1` int DEFAULT NULL
781 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
782 SET GLOBAL table_encryption_privilege_check=false;
783 SET SESSION default_table_encryption=false;
784 # clean up
785 DROP DATABASE db1;
786 DROP TABLESPACE ts1;
787 #
788 # [ALTER TABLESPACE] Case 19 )
789 `````````````````````````````````````````````````````````
790 # Create required schema to run ALTER TABLESPACE.
791 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='y';
792 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
793 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
794 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
795 SHOW CREATE TABLE db1.t1;
796 Table Create Table
797 t1 CREATE TABLE `t1` (
798 `f1` int DEFAULT NULL
799 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
800 SHOW CREATE TABLE db1.t2;
801 Table Create Table
802 t2 CREATE TABLE `t2` (
803 `f1` int DEFAULT NULL
804 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
805 SET SESSION default_table_encryption=false;
806 # Run ALTER TABLESPACE
807 SET GLOBAL table_encryption_privilege_check=true;
808 ALTER TABLESPACE ts1 ENCRYPTION='n';
809 SHOW WARNINGS;
810 Level Code Message
811 SHOW CREATE TABLE db1.t1;
812 Table Create Table
813 t1 CREATE TABLE `t1` (
814 `f1` int DEFAULT NULL
815 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
816 SHOW CREATE TABLE db1.t2;
817 Table Create Table
818 t2 CREATE TABLE `t2` (
819 `f1` int DEFAULT NULL
820 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
821 SET GLOBAL table_encryption_privilege_check=false;
822 SET SESSION default_table_encryption=false;
823 # clean up
824 DROP DATABASE db1;
825 DROP TABLESPACE ts1;
826 #
827 # [ALTER TABLESPACE] Case 20 )
828 `````````````````````````````````````````````````````````
829 # Grant user with TABLE_ENCRYPTION_ADMIN
830 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
831 # Create required schema to run ALTER TABLESPACE.
832 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='y';
833 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
834 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
835 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
836 SHOW CREATE TABLE db1.t1;
837 Table Create Table
838 t1 CREATE TABLE `t1` (
839 `f1` int DEFAULT NULL
840 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
841 SHOW CREATE TABLE db1.t2;
842 Table Create Table
843 t2 CREATE TABLE `t2` (
844 `f1` int DEFAULT NULL
845 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
846 SET SESSION default_table_encryption=false;
847 # Run ALTER TABLESPACE
848 SET GLOBAL table_encryption_privilege_check=true;
849 ALTER TABLESPACE ts1 ENCRYPTION='n';
850 SHOW WARNINGS;
851 Level Code Message
852 SHOW CREATE TABLE db1.t1;
853 Table Create Table
854 t1 CREATE TABLE `t1` (
855 `f1` int DEFAULT NULL
856 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
857 SHOW CREATE TABLE db1.t2;
858 Table Create Table
859 t2 CREATE TABLE `t2` (
860 `f1` int DEFAULT NULL
861 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
862 SET GLOBAL table_encryption_privilege_check=false;
863 SET SESSION default_table_encryption=false;
864 # clean up
865 # Revoke TABLE_ENCRYPTION_ADMIN from user
866 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
867 DROP DATABASE db1;
868 DROP TABLESPACE ts1;
869 #
870 `````````````````````````````````````````````````````````
871 # Encrypted TABLESPACE to unencrypted TABLESPACE
872 # with database encryption 'y'
873 # [ALTER TABLESPACE] Case 21 )
874 `````````````````````````````````````````````````````````
875 # Create required schema to run ALTER TABLESPACE.
876 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='y';
877 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
878 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
879 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
880 SHOW CREATE TABLE db1.t1;
881 Table Create Table
882 t1 CREATE TABLE `t1` (
883 `f1` int DEFAULT NULL
884 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
885 SHOW CREATE TABLE db1.t2;
886 Table Create Table
887 t2 CREATE TABLE `t2` (
888 `f1` int DEFAULT NULL
889 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
890 SET SESSION default_table_encryption=false;
891 # Run ALTER TABLESPACE
892 SET GLOBAL table_encryption_privilege_check=false;
893 ALTER TABLESPACE ts1 ENCRYPTION='n';
894 Warnings:
895 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
896 SHOW WARNINGS;
897 Level Code Message
898 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
899 SHOW CREATE TABLE db1.t1;
900 Table Create Table
901 t1 CREATE TABLE `t1` (
902 `f1` int DEFAULT NULL
903 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='N' */
904 SHOW CREATE TABLE db1.t2;
905 Table Create Table
906 t2 CREATE TABLE `t2` (
907 `f1` int DEFAULT NULL
908 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='N' */
909 SET GLOBAL table_encryption_privilege_check=false;
910 SET SESSION default_table_encryption=false;
911 # clean up
912 DROP DATABASE db1;
913 DROP TABLESPACE ts1;
914 #
915 `````````````````````````````````````````````````````````
916 # Request to create unencrypted tablespace with default_table_encryption='y'
917 # [ALTER TABLESPACE] Case 22 )
918 `````````````````````````````````````````````````````````
919 # Create required schema to run ALTER TABLESPACE.
920 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='y';
921 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
922 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
923 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
924 SHOW CREATE TABLE db1.t1;
925 Table Create Table
926 t1 CREATE TABLE `t1` (
927 `f1` int DEFAULT NULL
928 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
929 SHOW CREATE TABLE db1.t2;
930 Table Create Table
931 t2 CREATE TABLE `t2` (
932 `f1` int DEFAULT NULL
933 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
934 SET SESSION default_table_encryption=false;
935 # Run ALTER TABLESPACE
936 SET GLOBAL table_encryption_privilege_check=true;
937 ALTER TABLESPACE ts1 ENCRYPTION='n';
938 ERROR HY000: This tablespace can't be decrypted, because one of table's schema has default encryption ON and user doesn't have enough privilege.
939 SHOW WARNINGS;
940 Level Code Message
941 Error 3830 This tablespace can't be decrypted, because one of table's schema has default encryption ON and user doesn't have enough privilege.
942 SHOW CREATE TABLE db1.t1;
943 Table Create Table
944 t1 CREATE TABLE `t1` (
945 `f1` int DEFAULT NULL
946 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
947 SHOW CREATE TABLE db1.t2;
948 Table Create Table
949 t2 CREATE TABLE `t2` (
950 `f1` int DEFAULT NULL
951 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
952 SET GLOBAL table_encryption_privilege_check=false;
953 SET SESSION default_table_encryption=false;
954 # clean up
955 DROP DATABASE db1;
956 DROP TABLESPACE ts1;
957 #
958 # [ALTER TABLESPACE] Case 23 )
959 `````````````````````````````````````````````````````````
960 # Grant user with TABLE_ENCRYPTION_ADMIN
961 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
962 # Create required schema to run ALTER TABLESPACE.
963 CREATE TABLESPACE ts1 ADD DATAFILE 'df_u.ibd' ENCRYPTION='y';
964 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
965 CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
966 CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts1 ENCRYPTION='y';
967 SHOW CREATE TABLE db1.t1;
968 Table Create Table
969 t1 CREATE TABLE `t1` (
970 `f1` int DEFAULT NULL
971 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
972 SHOW CREATE TABLE db1.t2;
973 Table Create Table
974 t2 CREATE TABLE `t2` (
975 `f1` int DEFAULT NULL
976 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
977 SET SESSION default_table_encryption=false;
978 # Run ALTER TABLESPACE
979 SET GLOBAL table_encryption_privilege_check=true;
980 ALTER TABLESPACE ts1 ENCRYPTION='n';
981 Warnings:
982 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
983 SHOW WARNINGS;
984 Level Code Message
985 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
986 SHOW CREATE TABLE db1.t1;
987 Table Create Table
988 t1 CREATE TABLE `t1` (
989 `f1` int DEFAULT NULL
990 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='N' */
991 SHOW CREATE TABLE db1.t2;
992 Table Create Table
993 t2 CREATE TABLE `t2` (
994 `f1` int DEFAULT NULL
995 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='N' */
996 SET GLOBAL table_encryption_privilege_check=false;
997 SET SESSION default_table_encryption=false;
998 # clean up
999 # Revoke TABLE_ENCRYPTION_ADMIN from user
1000 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
1001 DROP DATABASE db1;
1002 DROP TABLESPACE ts1;
1003 #
1004 # Clean up.
1005 DROP USER u1@localhost;
1006 SET GLOBAL debug= '-d,skip_table_encryption_admin_check_for_set';