Debian Codegraph @

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