Debian Codegraph @

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