Debian Codegraph @

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