Debian Codegraph @

mysql-test/suite/component_keyring_file/r/tablespace.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 # This test run CREATE/ALTER TABLESPACE in different configurations,
16 #
17 # - Setting table_encryption_privilege_check to true/false.
18 # - Setting default_table_encryption to true/false.
19 # - With and without ENCRYPTION clause.
20 # - With and without user holding TABLE_ENCRYPTION_ADMIN privilege.
21 # - Test INFORMATION_SCHEMA.INNODB_TABLESPACES
22 # - Check for warnings generated.
23 #
24 `````````````````````````````````````````````````````````
25 # CREATE TABLESPACE with explicit ENCRYPTION clause
26 # table_encryption_privilege_check=false
27 # [CREATE TABLESPACE] Case 1 )
28 `````````````````````````````````````````````````````````
29 SET GLOBAL table_encryption_privilege_check=false;
30 SET SESSION default_table_encryption=false;
31 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
32 SHOW WARNINGS;
33 Level Code Message
34 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
35 WHERE NAME LIKE 'ts1';
36 NAME ENCRYPTION
37 ts1 N
38 DROP TABLESPACE ts1;
39 SET GLOBAL table_encryption_privilege_check=false;
40 SET SESSION default_table_encryption=false;
41 # [CREATE TABLESPACE] Case 2 )
42 `````````````````````````````````````````````````````````
43 SET GLOBAL table_encryption_privilege_check=false;
44 SET SESSION default_table_encryption=true;
45 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
46 SHOW WARNINGS;
47 Level Code Message
48 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
49 WHERE NAME LIKE 'ts1';
50 NAME ENCRYPTION
51 ts1 Y
52 DROP TABLESPACE ts1;
53 SET GLOBAL table_encryption_privilege_check=false;
54 SET SESSION default_table_encryption=false;
55 `````````````````````````````````````````````````````````
56 # table_encryption_privilege_check=true
57 # [CREATE TABLESPACE] Case 3 )
58 `````````````````````````````````````````````````````````
59 SET GLOBAL table_encryption_privilege_check=true;
60 SET SESSION default_table_encryption=false;
61 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
62 SHOW WARNINGS;
63 Level Code Message
64 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
65 WHERE NAME LIKE 'ts1';
66 NAME ENCRYPTION
67 ts1 N
68 DROP TABLESPACE ts1;
69 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
70 # Grant user with TABLE_ENCRYPTION_ADMIN
71 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
72 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
73 SHOW WARNINGS;
74 Level Code Message
75 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
76 WHERE NAME LIKE 'ts1';
77 NAME ENCRYPTION
78 ts1 N
79 DROP TABLESPACE ts1;
80 # Revoke TABLE_ENCRYPTION_ADMIN from user.
81 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
82 SET GLOBAL table_encryption_privilege_check=false;
83 SET SESSION default_table_encryption=false;
84 # [CREATE TABLESPACE] Case 4 )
85 `````````````````````````````````````````````````````````
86 SET GLOBAL table_encryption_privilege_check=true;
87 SET SESSION default_table_encryption=true;
88 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
89 SHOW WARNINGS;
90 Level Code Message
91 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
92 WHERE NAME LIKE 'ts1';
93 NAME ENCRYPTION
94 ts1 Y
95 DROP TABLESPACE ts1;
96 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
97 # Grant user with TABLE_ENCRYPTION_ADMIN
98 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
99 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
100 SHOW WARNINGS;
101 Level Code Message
102 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
103 WHERE NAME LIKE 'ts1';
104 NAME ENCRYPTION
105 ts1 Y
106 DROP TABLESPACE ts1;
107 # Revoke TABLE_ENCRYPTION_ADMIN from user.
108 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
109 SET GLOBAL table_encryption_privilege_check=false;
110 SET SESSION default_table_encryption=false;
111 `````````````````````````````````````````````````````````
112 # CREATE TABLESPACE with explicit ENCRYPTION clause
113 # table_encryption_privilege_check=false
114 # [CREATE TABLESPACE] Case 5 )
115 `````````````````````````````````````````````````````````
116 SET GLOBAL table_encryption_privilege_check=false;
117 SET SESSION default_table_encryption=false;
118 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
119 SHOW WARNINGS;
120 Level Code Message
121 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
122 WHERE NAME LIKE 'ts1';
123 NAME ENCRYPTION
124 ts1 Y
125 DROP TABLESPACE ts1;
126 SET GLOBAL table_encryption_privilege_check=false;
127 SET SESSION default_table_encryption=false;
128 # [CREATE TABLESPACE] Case 6 )
129 `````````````````````````````````````````````````````````
130 SET GLOBAL table_encryption_privilege_check=false;
131 SET SESSION default_table_encryption=false;
132 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
133 SHOW WARNINGS;
134 Level Code Message
135 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
136 WHERE NAME LIKE 'ts1';
137 NAME ENCRYPTION
138 ts1 N
139 DROP TABLESPACE ts1;
140 SET GLOBAL table_encryption_privilege_check=false;
141 SET SESSION default_table_encryption=false;
142 # [CREATE TABLESPACE] Case 7 )
143 `````````````````````````````````````````````````````````
144 SET GLOBAL table_encryption_privilege_check=false;
145 SET SESSION default_table_encryption=true;
146 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
147 SHOW WARNINGS;
148 Level Code Message
149 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
150 WHERE NAME LIKE 'ts1';
151 NAME ENCRYPTION
152 ts1 Y
153 DROP TABLESPACE ts1;
154 SET GLOBAL table_encryption_privilege_check=false;
155 SET SESSION default_table_encryption=false;
156 # [CREATE TABLESPACE] Case 8 )
157 `````````````````````````````````````````````````````````
158 SET GLOBAL table_encryption_privilege_check=false;
159 SET SESSION default_table_encryption=true;
160 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
161 SHOW WARNINGS;
162 Level Code Message
163 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
164 WHERE NAME LIKE 'ts1';
165 NAME ENCRYPTION
166 ts1 N
167 DROP TABLESPACE ts1;
168 SET GLOBAL table_encryption_privilege_check=false;
169 SET SESSION default_table_encryption=false;
170 `````````````````````````````````````````````````````````
171 # table_encryption_privilege_check=true
172 # [CREATE TABLESPACE] Case 9 )
173 `````````````````````````````````````````````````````````
174 SET GLOBAL table_encryption_privilege_check=true;
175 SET SESSION default_table_encryption=false;
176 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
177 SHOW WARNINGS;
178 ERROR HY000: Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
179 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
180 # Grant user with TABLE_ENCRYPTION_ADMIN
181 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
182 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
183 SHOW WARNINGS;
184 Level Code Message
185 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
186 WHERE NAME LIKE 'ts1';
187 NAME ENCRYPTION
188 ts1 Y
189 DROP TABLESPACE ts1;
190 # Revoke TABLE_ENCRYPTION_ADMIN from user.
191 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
192 SET GLOBAL table_encryption_privilege_check=false;
193 SET SESSION default_table_encryption=false;
194 # [CREATE TABLESPACE] Case 10 )
195 `````````````````````````````````````````````````````````
196 SET GLOBAL table_encryption_privilege_check=true;
197 SET SESSION default_table_encryption=false;
198 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
199 SHOW WARNINGS;
200 Level Code Message
201 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
202 WHERE NAME LIKE 'ts1';
203 NAME ENCRYPTION
204 ts1 N
205 DROP TABLESPACE ts1;
206 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
207 # Grant user with TABLE_ENCRYPTION_ADMIN
208 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
209 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
210 SHOW WARNINGS;
211 Level Code Message
212 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
213 WHERE NAME LIKE 'ts1';
214 NAME ENCRYPTION
215 ts1 N
216 DROP TABLESPACE ts1;
217 # Revoke TABLE_ENCRYPTION_ADMIN from user.
218 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
219 SET GLOBAL table_encryption_privilege_check=false;
220 SET SESSION default_table_encryption=false;
221 # [CREATE TABLESPACE] Case 11 )
222 `````````````````````````````````````````````````````````
223 SET GLOBAL table_encryption_privilege_check=true;
224 SET SESSION default_table_encryption=true;
225 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
226 SHOW WARNINGS;
227 Level Code Message
228 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
229 WHERE NAME LIKE 'ts1';
230 NAME ENCRYPTION
231 ts1 Y
232 DROP TABLESPACE ts1;
233 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
234 # Grant user with TABLE_ENCRYPTION_ADMIN
235 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
236 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
237 SHOW WARNINGS;
238 Level Code Message
239 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
240 WHERE NAME LIKE 'ts1';
241 NAME ENCRYPTION
242 ts1 Y
243 DROP TABLESPACE ts1;
244 # Revoke TABLE_ENCRYPTION_ADMIN from user.
245 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
246 SET GLOBAL table_encryption_privilege_check=false;
247 SET SESSION default_table_encryption=false;
248 # [CREATE TABLESPACE] Case 12 )
249 `````````````````````````````````````````````````````````
250 SET GLOBAL table_encryption_privilege_check=true;
251 SET SESSION default_table_encryption=true;
252 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
253 SHOW WARNINGS;
254 ERROR HY000: Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
255 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
256 # Grant user with TABLE_ENCRYPTION_ADMIN
257 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
258 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
259 SHOW WARNINGS;
260 Level Code Message
261 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
262 WHERE NAME LIKE 'ts1';
263 NAME ENCRYPTION
264 ts1 N
265 DROP TABLESPACE ts1;
266 # Revoke TABLE_ENCRYPTION_ADMIN from user.
267 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
268 SET GLOBAL table_encryption_privilege_check=false;
269 SET SESSION default_table_encryption=false;
270 `````````````````````````````````````````````````````````
271 # Check for invalid ENCRYPTION clause value.
272 # [CREATE TABLESPACE] Case 13 )
273 `````````````````````````````````````````````````````````
274 SET GLOBAL table_encryption_privilege_check=false;
275 SET SESSION default_table_encryption=false;
276 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='k';
277 SHOW WARNINGS;
278 ERROR HY000: Invalid encryption option.
279 SET GLOBAL table_encryption_privilege_check=false;
280 SET SESSION default_table_encryption=false;
281 `````````````````````````````````````````````````````````
282 # The test cases run ALTER TABLESPACE (with no tables)
283 # See test tablespace_with_tables.test.
284 `````````````````````````````````````````````````````````
285 # Unencrypted TABLESPACE to Unencrypted TABLESPACE (Nop)
286 #
287 # [ALTER TABLESPACE] Case 1 )
288 `````````````````````````````````````````````````````````
289 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
290 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
291 WHERE NAME LIKE 'ts1';
292 NAME ENCRYPTION
293 ts1 N
294 SET SESSION default_table_encryption=false;
295 SET GLOBAL table_encryption_privilege_check=false;
296 ALTER TABLESPACE ts1 ENCRYPTION='n';
297 SHOW WARNINGS;
298 Level Code Message
299 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
300 WHERE NAME LIKE 'ts1';
301 NAME ENCRYPTION
302 ts1 N
303 DROP TABLESPACE ts1;
304 SET GLOBAL table_encryption_privilege_check=false;
305 SET SESSION default_table_encryption=false;
306 # [ALTER TABLESPACE] Case 2 )
307 `````````````````````````````````````````````````````````
308 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
309 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
310 WHERE NAME LIKE 'ts1';
311 NAME ENCRYPTION
312 ts1 N
313 SET SESSION default_table_encryption=false;
314 SET GLOBAL table_encryption_privilege_check=true;
315 ALTER TABLESPACE ts1 ENCRYPTION='n';
316 SHOW WARNINGS;
317 Level Code Message
318 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
319 WHERE NAME LIKE 'ts1';
320 NAME ENCRYPTION
321 ts1 N
322 DROP TABLESPACE ts1;
323 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
324 # Grant user with TABLE_ENCRYPTION_ADMIN
325 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
326 SET GLOBAL table_encryption_privilege_check=false;
327 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
328 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
329 WHERE NAME LIKE 'ts1';
330 NAME ENCRYPTION
331 ts1 N
332 SET GLOBAL table_encryption_privilege_check=true;
333 ALTER TABLESPACE ts1 ENCRYPTION='n';
334 SHOW WARNINGS;
335 Level Code Message
336 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
337 WHERE NAME LIKE 'ts1';
338 NAME ENCRYPTION
339 ts1 N
340 DROP TABLESPACE ts1;
341 # Revoke TABLE_ENCRYPTION_ADMIN from user.
342 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
343 SET GLOBAL table_encryption_privilege_check=false;
344 SET SESSION default_table_encryption=false;
345 `````````````````````````````````````````````````````````
346 # Encrypted TABLESPACE to Encrypted TABLESPACE (Nop)
347 #
348 # [ALTER TABLESPACE] Case 3 )
349 `````````````````````````````````````````````````````````
350 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
351 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
352 WHERE NAME LIKE 'ts1';
353 NAME ENCRYPTION
354 ts1 Y
355 SET SESSION default_table_encryption=true;
356 SET GLOBAL table_encryption_privilege_check=false;
357 ALTER TABLESPACE ts1 ENCRYPTION='y';
358 SHOW WARNINGS;
359 Level Code Message
360 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
361 WHERE NAME LIKE 'ts1';
362 NAME ENCRYPTION
363 ts1 Y
364 DROP TABLESPACE ts1;
365 SET GLOBAL table_encryption_privilege_check=false;
366 SET SESSION default_table_encryption=false;
367 # [ALTER TABLESPACE] Case 4 )
368 `````````````````````````````````````````````````````````
369 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
370 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
371 WHERE NAME LIKE 'ts1';
372 NAME ENCRYPTION
373 ts1 Y
374 SET SESSION default_table_encryption=true;
375 SET GLOBAL table_encryption_privilege_check=true;
376 ALTER TABLESPACE ts1 ENCRYPTION='y';
377 SHOW WARNINGS;
378 Level Code Message
379 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
380 WHERE NAME LIKE 'ts1';
381 NAME ENCRYPTION
382 ts1 Y
383 DROP TABLESPACE ts1;
384 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
385 # Grant user with TABLE_ENCRYPTION_ADMIN
386 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
387 SET GLOBAL table_encryption_privilege_check=false;
388 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
389 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
390 WHERE NAME LIKE 'ts1';
391 NAME ENCRYPTION
392 ts1 Y
393 SET GLOBAL table_encryption_privilege_check=true;
394 ALTER TABLESPACE ts1 ENCRYPTION='y';
395 SHOW WARNINGS;
396 Level Code Message
397 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
398 WHERE NAME LIKE 'ts1';
399 NAME ENCRYPTION
400 ts1 Y
401 DROP TABLESPACE ts1;
402 # Revoke TABLE_ENCRYPTION_ADMIN from user.
403 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
404 SET GLOBAL table_encryption_privilege_check=false;
405 SET SESSION default_table_encryption=false;
406 `````````````````````````````````````````````````````````
407 # Unencrypted TABLESPACE to encrypted TABLESPACE
408 # with database encryption default 'n'
409 #
410 # [ALTER TABLESPACE] Case 5 )
411 `````````````````````````````````````````````````````````
412 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
413 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
414 WHERE NAME LIKE 'ts1';
415 NAME ENCRYPTION
416 ts1 N
417 SET SESSION default_table_encryption=false;
418 SET GLOBAL table_encryption_privilege_check=false;
419 ALTER TABLESPACE ts1 ENCRYPTION='y';
420 SHOW WARNINGS;
421 Level Code Message
422 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
423 WHERE NAME LIKE 'ts1';
424 NAME ENCRYPTION
425 ts1 Y
426 DROP TABLESPACE ts1;
427 SET GLOBAL table_encryption_privilege_check=false;
428 SET SESSION default_table_encryption=false;
429 # [ALTER TABLESPACE] Case 6 )
430 `````````````````````````````````````````````````````````
431 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
432 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
433 WHERE NAME LIKE 'ts1';
434 NAME ENCRYPTION
435 ts1 N
436 SET SESSION default_table_encryption=false;
437 SET GLOBAL table_encryption_privilege_check=true;
438 ALTER TABLESPACE ts1 ENCRYPTION='y';
439 ERROR HY000: Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
440 SHOW WARNINGS;
441 Level Code Message
442 Error 3828 Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
443 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
444 WHERE NAME LIKE 'ts1';
445 NAME ENCRYPTION
446 ts1 N
447 DROP TABLESPACE ts1;
448 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
449 # Grant user with TABLE_ENCRYPTION_ADMIN
450 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
451 SET GLOBAL table_encryption_privilege_check=false;
452 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
453 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
454 WHERE NAME LIKE 'ts1';
455 NAME ENCRYPTION
456 ts1 N
457 SET GLOBAL table_encryption_privilege_check=true;
458 ALTER TABLESPACE ts1 ENCRYPTION='y';
459 SHOW WARNINGS;
460 Level Code Message
461 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
462 WHERE NAME LIKE 'ts1';
463 NAME ENCRYPTION
464 ts1 Y
465 DROP TABLESPACE ts1;
466 # Revoke TABLE_ENCRYPTION_ADMIN from user.
467 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
468 SET GLOBAL table_encryption_privilege_check=false;
469 SET SESSION default_table_encryption=false;
470 `````````````````````````````````````````````````````````
471 # Unencrypted TABLESPACE to encrypted TABLESPACE
472 # with database encryption default 'y'
473 #
474 # [ALTER TABLESPACE] Case 7 )
475 `````````````````````````````````````````````````````````
476 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
477 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
478 WHERE NAME LIKE 'ts1';
479 NAME ENCRYPTION
480 ts1 N
481 SET SESSION default_table_encryption=true;
482 SET GLOBAL table_encryption_privilege_check=false;
483 ALTER TABLESPACE ts1 ENCRYPTION='y';
484 SHOW WARNINGS;
485 Level Code Message
486 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
487 WHERE NAME LIKE 'ts1';
488 NAME ENCRYPTION
489 ts1 Y
490 DROP TABLESPACE ts1;
491 SET GLOBAL table_encryption_privilege_check=false;
492 SET SESSION default_table_encryption=false;
493 # [ALTER TABLESPACE] Case 8 )
494 `````````````````````````````````````````````````````````
495 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
496 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
497 WHERE NAME LIKE 'ts1';
498 NAME ENCRYPTION
499 ts1 N
500 SET SESSION default_table_encryption=true;
501 SET GLOBAL table_encryption_privilege_check=true;
502 ALTER TABLESPACE ts1 ENCRYPTION='y';
503 SHOW WARNINGS;
504 Level Code Message
505 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
506 WHERE NAME LIKE 'ts1';
507 NAME ENCRYPTION
508 ts1 Y
509 DROP TABLESPACE ts1;
510 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
511 # Grant user with TABLE_ENCRYPTION_ADMIN
512 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
513 SET GLOBAL table_encryption_privilege_check=false;
514 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
515 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
516 WHERE NAME LIKE 'ts1';
517 NAME ENCRYPTION
518 ts1 N
519 SET GLOBAL table_encryption_privilege_check=true;
520 ALTER TABLESPACE ts1 ENCRYPTION='y';
521 SHOW WARNINGS;
522 Level Code Message
523 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
524 WHERE NAME LIKE 'ts1';
525 NAME ENCRYPTION
526 ts1 Y
527 DROP TABLESPACE ts1;
528 # Revoke TABLE_ENCRYPTION_ADMIN from user.
529 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
530 SET GLOBAL table_encryption_privilege_check=false;
531 SET SESSION default_table_encryption=false;
532 `````````````````````````````````````````````````````````
533 # Encrypted TABLESPACE to unencrypted TABLESPACE
534 # with database encryption default 'n'
535 #
536 # [ALTER TABLESPACE] Case 9 )
537 `````````````````````````````````````````````````````````
538 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
539 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
540 WHERE NAME LIKE 'ts1';
541 NAME ENCRYPTION
542 ts1 Y
543 SET SESSION default_table_encryption=false;
544 SET GLOBAL table_encryption_privilege_check=false;
545 ALTER TABLESPACE ts1 ENCRYPTION='n';
546 SHOW WARNINGS;
547 Level Code Message
548 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
549 WHERE NAME LIKE 'ts1';
550 NAME ENCRYPTION
551 ts1 N
552 DROP TABLESPACE ts1;
553 SET GLOBAL table_encryption_privilege_check=false;
554 SET SESSION default_table_encryption=false;
555 # [ALTER TABLESPACE] Case 10 )
556 `````````````````````````````````````````````````````````
557 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
558 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
559 WHERE NAME LIKE 'ts1';
560 NAME ENCRYPTION
561 ts1 Y
562 SET SESSION default_table_encryption=false;
563 SET GLOBAL table_encryption_privilege_check=true;
564 ALTER TABLESPACE ts1 ENCRYPTION='n';
565 SHOW WARNINGS;
566 Level Code Message
567 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
568 WHERE NAME LIKE 'ts1';
569 NAME ENCRYPTION
570 ts1 N
571 DROP TABLESPACE ts1;
572 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
573 # Grant user with TABLE_ENCRYPTION_ADMIN
574 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
575 SET GLOBAL table_encryption_privilege_check=false;
576 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
577 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
578 WHERE NAME LIKE 'ts1';
579 NAME ENCRYPTION
580 ts1 Y
581 SET GLOBAL table_encryption_privilege_check=true;
582 ALTER TABLESPACE ts1 ENCRYPTION='n';
583 SHOW WARNINGS;
584 Level Code Message
585 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
586 WHERE NAME LIKE 'ts1';
587 NAME ENCRYPTION
588 ts1 N
589 DROP TABLESPACE ts1;
590 # Revoke TABLE_ENCRYPTION_ADMIN from user.
591 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
592 SET GLOBAL table_encryption_privilege_check=false;
593 SET SESSION default_table_encryption=false;
594 `````````````````````````````````````````````````````````
595 # Encrypted TABLESPACE to unencrypted TABLESPACE
596 # with database encryption default 'y'
597 #
598 # [ALTER TABLESPACE] Case 11 )
599 `````````````````````````````````````````````````````````
600 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
601 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
602 WHERE NAME LIKE 'ts1';
603 NAME ENCRYPTION
604 ts1 Y
605 SET SESSION default_table_encryption=true;
606 SET GLOBAL table_encryption_privilege_check=false;
607 ALTER TABLESPACE ts1 ENCRYPTION='n';
608 SHOW WARNINGS;
609 Level Code Message
610 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
611 WHERE NAME LIKE 'ts1';
612 NAME ENCRYPTION
613 ts1 N
614 DROP TABLESPACE ts1;
615 SET GLOBAL table_encryption_privilege_check=false;
616 SET SESSION default_table_encryption=false;
617 # [ALTER TABLESPACE] Case 12 )
618 `````````````````````````````````````````````````````````
619 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
620 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
621 WHERE NAME LIKE 'ts1';
622 NAME ENCRYPTION
623 ts1 Y
624 SET SESSION default_table_encryption=true;
625 SET GLOBAL table_encryption_privilege_check=true;
626 ALTER TABLESPACE ts1 ENCRYPTION='n';
627 ERROR HY000: Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
628 SHOW WARNINGS;
629 Level Code Message
630 Error 3828 Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
631 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
632 WHERE NAME LIKE 'ts1';
633 NAME ENCRYPTION
634 ts1 Y
635 DROP TABLESPACE ts1;
636 # Check that we never fail with TABLE_ENCRYPTION_ADMIN
637 # Grant user with TABLE_ENCRYPTION_ADMIN
638 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
639 SET GLOBAL table_encryption_privilege_check=false;
640 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
641 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
642 WHERE NAME LIKE 'ts1';
643 NAME ENCRYPTION
644 ts1 Y
645 SET GLOBAL table_encryption_privilege_check=true;
646 ALTER TABLESPACE ts1 ENCRYPTION='n';
647 SHOW WARNINGS;
648 Level Code Message
649 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
650 WHERE NAME LIKE 'ts1';
651 NAME ENCRYPTION
652 ts1 N
653 DROP TABLESPACE ts1;
654 # Revoke TABLE_ENCRYPTION_ADMIN from user.
655 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
656 SET GLOBAL table_encryption_privilege_check=false;
657 SET SESSION default_table_encryption=false;
658 `````````````````````````````````````````````````````````
659 # Check for Invalid encryption option.
660 # [ALTER TABLESPACE] Case 13 )
661 `````````````````````````````````````````````````````````
662 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
663 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
664 WHERE NAME LIKE 'ts1';
665 NAME ENCRYPTION
666 ts1 Y
667 SET SESSION default_table_encryption=false;
668 SET GLOBAL table_encryption_privilege_check=false;
669 ALTER TABLESPACE ts1 ENCRYPTION='k';
670 ERROR HY000: Invalid encryption option.
671 SHOW WARNINGS;
672 Level Code Message
673 Error 3184 Invalid encryption option.
674 Error 1533 Failed to alter: TABLESPACE ts1
675 Error 1030 Got error 168 - 'Unknown (generic) error from engine' from storage engine
676 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
677 WHERE NAME LIKE 'ts1';
678 NAME ENCRYPTION
679 ts1 Y
680 DROP TABLESPACE ts1;
681 SET GLOBAL table_encryption_privilege_check=false;
682 SET SESSION default_table_encryption=false;
683 # Cleanup
684 DROP USER u1@localhost;
685 SET GLOBAL debug= '-d,skip_table_encryption_admin_check_for_set';
686 # ----------------------------------------------------------------------
687 # Teardown
688 # Removing manifest file for current MySQL server instance
689 # Removing local keyring file for keyring component: component_keyring_file
690 # Removing local configuration file for keyring component: component_keyring_file
691 # Restarting server without the manifest file
692 # ----------------------------------------------------------------------