Debian Codegraph @

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

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