Debian Codegraph @

mysql-test/suite/encryption/r/alter_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 ALTER 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 user holding TABLE_ENCRYPTION_ADMIN privilege.
14 # - Test SHOW CREATE TABLE
15 # - Test INFORMATION_SCHEMA.TABLES.CREATE_OPTIONS
16 # - Check for warnings generated.
17 #
18 # See comments in alter_table.inc for more details.
19 `````````````````````````````````````````````````````````
20 # Test using user tablespace 'ts1'
21 `````````````````````````````````````````````````````````
22 # table_encryption_privilege_check=OFF and default per-db encryption OFF
23 # [ALTER TABLE] Case 1 )
24 `````````````````````````````````````````````````````````
25 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
26 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
27 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
28 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
29 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
30 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
31 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
32 SET GLOBAL table_encryption_privilege_check=false;
33 # Run ALTER TABLE and check for errors/warnings
34 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
35 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='n';
36 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
37 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='n';
38 SET GLOBAL table_encryption_privilege_check=false;
39 # Verify the ENCRYPTION clause value.
40 SHOW CREATE TABLE db1.t1;
41 Table Create Table
42 t1 CREATE TABLE `t1` (
43 `f1` int DEFAULT NULL
44 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
45 SHOW CREATE TABLE db1.t2;
46 Table Create Table
47 t2 CREATE TABLE `t2` (
48 `f1` int DEFAULT NULL
49 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
50 SHOW CREATE TABLE db1.t3;
51 Table Create Table
52 t3 CREATE TABLE `t3` (
53 `f1` int DEFAULT NULL
54 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
55 SHOW CREATE TABLE db1.t4;
56 Table Create Table
57 t4 CREATE TABLE `t4` (
58 `f1` int DEFAULT NULL
59 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
60 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
61 WHERE TABLE_SCHEMA='db1';
62 TABLE_NAME CREATE_OPTIONS
63 t1
64 t2
65 t3
66 t4
67 # Cleanup
68 DROP DATABASE db1;
69 DROP TABLESPACE tsA;
70 DROP TABLESPACE ts1;
71 # [ALTER TABLE] Case 2 )
72 `````````````````````````````````````````````````````````
73 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
74 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
75 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
76 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
77 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
78 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
79 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
80 SET GLOBAL table_encryption_privilege_check=false;
81 # Run ALTER TABLE and check for errors/warnings
82 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
83 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='y';
84 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
85 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='y';
86 SET GLOBAL table_encryption_privilege_check=false;
87 # Verify the ENCRYPTION clause value.
88 SHOW CREATE TABLE db1.t1;
89 Table Create Table
90 t1 CREATE TABLE `t1` (
91 `f1` int DEFAULT NULL
92 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
93 SHOW CREATE TABLE db1.t2;
94 Table Create Table
95 t2 CREATE TABLE `t2` (
96 `f1` int DEFAULT NULL
97 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
98 SHOW CREATE TABLE db1.t3;
99 Table Create Table
100 t3 CREATE TABLE `t3` (
101 `f1` int DEFAULT NULL
102 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
103 SHOW CREATE TABLE db1.t4;
104 Table Create Table
105 t4 CREATE TABLE `t4` (
106 `f1` int DEFAULT NULL
107 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
108 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
109 WHERE TABLE_SCHEMA='db1';
110 TABLE_NAME CREATE_OPTIONS
111 t1 ENCRYPTION='y'
112 t2 ENCRYPTION='y'
113 t3 ENCRYPTION='y'
114 t4 ENCRYPTION='y'
115 # Cleanup
116 DROP DATABASE db1;
117 DROP TABLESPACE tsA;
118 DROP TABLESPACE ts1;
119 `````````````````````````````````````````````````````````
120 # Moving encrypted tablespace to unencrypted tablespace is rejected.
121 # [ALTER TABLE] Case 3 )
122 `````````````````````````````````````````````````````````
123 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
124 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
125 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
126 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
127 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
128 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
129 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
130 SET GLOBAL table_encryption_privilege_check=false;
131 # Run ALTER TABLE and check for errors/warnings
132 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
133 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='n';
134 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
135 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
136 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='n';
137 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
138 SET GLOBAL table_encryption_privilege_check=false;
139 # Verify the ENCRYPTION clause value.
140 SHOW CREATE TABLE db1.t1;
141 Table Create Table
142 t1 CREATE TABLE `t1` (
143 `f1` int DEFAULT NULL
144 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
145 SHOW CREATE TABLE db1.t2;
146 Table Create Table
147 t2 CREATE TABLE `t2` (
148 `f1` int DEFAULT NULL
149 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
150 SHOW CREATE TABLE db1.t3;
151 Table Create Table
152 t3 CREATE TABLE `t3` (
153 `f1` int DEFAULT NULL
154 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
155 SHOW CREATE TABLE db1.t4;
156 Table Create Table
157 t4 CREATE TABLE `t4` (
158 `f1` int DEFAULT NULL
159 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
160 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
161 WHERE TABLE_SCHEMA='db1';
162 TABLE_NAME CREATE_OPTIONS
163 t1
164 t2 ENCRYPTION='y'
165 t3
166 t4 ENCRYPTION='y'
167 # Cleanup
168 DROP DATABASE db1;
169 DROP TABLESPACE tsA;
170 DROP TABLESPACE ts1;
171 # [ALTER TABLE] Case 4 )
172 `````````````````````````````````````````````````````````
173 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
174 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
175 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
176 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
177 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
178 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
179 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
180 SET GLOBAL table_encryption_privilege_check=false;
181 # Run ALTER TABLE and check for errors/warnings
182 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
183 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='y';
184 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
185 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='y';
186 SET GLOBAL table_encryption_privilege_check=false;
187 # Verify the ENCRYPTION clause value.
188 SHOW CREATE TABLE db1.t1;
189 Table Create Table
190 t1 CREATE TABLE `t1` (
191 `f1` int DEFAULT NULL
192 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
193 SHOW CREATE TABLE db1.t2;
194 Table Create Table
195 t2 CREATE TABLE `t2` (
196 `f1` int DEFAULT NULL
197 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
198 SHOW CREATE TABLE db1.t3;
199 Table Create Table
200 t3 CREATE TABLE `t3` (
201 `f1` int DEFAULT NULL
202 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
203 SHOW CREATE TABLE db1.t4;
204 Table Create Table
205 t4 CREATE TABLE `t4` (
206 `f1` int DEFAULT NULL
207 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
208 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
209 WHERE TABLE_SCHEMA='db1';
210 TABLE_NAME CREATE_OPTIONS
211 t1 ENCRYPTION='y'
212 t2 ENCRYPTION='y'
213 t3 ENCRYPTION='y'
214 t4 ENCRYPTION='y'
215 # Cleanup
216 DROP DATABASE db1;
217 DROP TABLESPACE tsA;
218 DROP TABLESPACE ts1;
219 `````````````````````````````````````````````````````````
220 # table_encryption_privilege_check=OFF and default per-db encryption ON
221 # [ALTER TABLE] Case 5 )
222 `````````````````````````````````````````````````````````
223 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
224 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
225 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
226 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
227 Warnings:
228 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
229 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
230 Warnings:
231 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
232 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
233 Warnings:
234 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
235 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
236 Warnings:
237 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
238 SET GLOBAL table_encryption_privilege_check=false;
239 # Run ALTER TABLE and check for errors/warnings
240 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
241 Warnings:
242 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
243 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='n';
244 Warnings:
245 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
246 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
247 Warnings:
248 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
249 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='n';
250 Warnings:
251 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
252 SET GLOBAL table_encryption_privilege_check=false;
253 # Verify the ENCRYPTION clause value.
254 SHOW CREATE TABLE db1.t1;
255 Table Create Table
256 t1 CREATE TABLE `t1` (
257 `f1` int DEFAULT NULL
258 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
259 SHOW CREATE TABLE db1.t2;
260 Table Create Table
261 t2 CREATE TABLE `t2` (
262 `f1` int DEFAULT NULL
263 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
264 SHOW CREATE TABLE db1.t3;
265 Table Create Table
266 t3 CREATE TABLE `t3` (
267 `f1` int DEFAULT NULL
268 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
269 SHOW CREATE TABLE db1.t4;
270 Table Create Table
271 t4 CREATE TABLE `t4` (
272 `f1` int DEFAULT NULL
273 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
274 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
275 WHERE TABLE_SCHEMA='db1';
276 TABLE_NAME CREATE_OPTIONS
277 t1 ENCRYPTION='n'
278 t2 ENCRYPTION='n'
279 t3 ENCRYPTION='n'
280 t4 ENCRYPTION='n'
281 # Cleanup
282 DROP DATABASE db1;
283 DROP TABLESPACE tsA;
284 DROP TABLESPACE ts1;
285 # [ALTER TABLE] Case 6 )
286 `````````````````````````````````````````````````````````
287 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
288 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
289 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
290 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
291 Warnings:
292 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
293 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
294 Warnings:
295 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
296 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
297 Warnings:
298 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
299 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
300 Warnings:
301 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
302 SET GLOBAL table_encryption_privilege_check=false;
303 # Run ALTER TABLE and check for errors/warnings
304 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
305 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='y';
306 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
307 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='y';
308 SET GLOBAL table_encryption_privilege_check=false;
309 # Verify the ENCRYPTION clause value.
310 SHOW CREATE TABLE db1.t1;
311 Table Create Table
312 t1 CREATE TABLE `t1` (
313 `f1` int DEFAULT NULL
314 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
315 SHOW CREATE TABLE db1.t2;
316 Table Create Table
317 t2 CREATE TABLE `t2` (
318 `f1` int DEFAULT NULL
319 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
320 SHOW CREATE TABLE db1.t3;
321 Table Create Table
322 t3 CREATE TABLE `t3` (
323 `f1` int DEFAULT NULL
324 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
325 SHOW CREATE TABLE db1.t4;
326 Table Create Table
327 t4 CREATE TABLE `t4` (
328 `f1` int DEFAULT NULL
329 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
330 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
331 WHERE TABLE_SCHEMA='db1';
332 TABLE_NAME CREATE_OPTIONS
333 t1 ENCRYPTION='y'
334 t2 ENCRYPTION='y'
335 t3 ENCRYPTION='y'
336 t4 ENCRYPTION='y'
337 # Cleanup
338 DROP DATABASE db1;
339 DROP TABLESPACE tsA;
340 DROP TABLESPACE ts1;
341 `````````````````````````````````````````````````````````
342 # Moving encrypted tablespace to unencrypted tablespace is rejected.
343 # [ALTER TABLE] Case 7 )
344 `````````````````````````````````````````````````````````
345 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
346 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
347 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
348 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
349 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
350 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
351 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
352 SET GLOBAL table_encryption_privilege_check=false;
353 # Run ALTER TABLE and check for errors/warnings
354 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
355 Warnings:
356 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
357 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='n';
358 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
359 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
360 Warnings:
361 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
362 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='n';
363 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
364 SET GLOBAL table_encryption_privilege_check=false;
365 # Verify the ENCRYPTION clause value.
366 SHOW CREATE TABLE db1.t1;
367 Table Create Table
368 t1 CREATE TABLE `t1` (
369 `f1` int DEFAULT NULL
370 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
371 SHOW CREATE TABLE db1.t2;
372 Table Create Table
373 t2 CREATE TABLE `t2` (
374 `f1` int DEFAULT NULL
375 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
376 SHOW CREATE TABLE db1.t3;
377 Table Create Table
378 t3 CREATE TABLE `t3` (
379 `f1` int DEFAULT NULL
380 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
381 SHOW CREATE TABLE db1.t4;
382 Table Create Table
383 t4 CREATE TABLE `t4` (
384 `f1` int DEFAULT NULL
385 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
386 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
387 WHERE TABLE_SCHEMA='db1';
388 TABLE_NAME CREATE_OPTIONS
389 t1 ENCRYPTION='n'
390 t2 ENCRYPTION='y'
391 t3 ENCRYPTION='n'
392 t4 ENCRYPTION='y'
393 # Cleanup
394 DROP DATABASE db1;
395 DROP TABLESPACE tsA;
396 DROP TABLESPACE ts1;
397 # [ALTER TABLE] Case 8 )
398 `````````````````````````````````````````````````````````
399 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
400 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
401 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
402 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
403 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
404 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
405 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
406 SET GLOBAL table_encryption_privilege_check=false;
407 # Run ALTER TABLE and check for errors/warnings
408 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
409 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='y';
410 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
411 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='y';
412 SET GLOBAL table_encryption_privilege_check=false;
413 # Verify the ENCRYPTION clause value.
414 SHOW CREATE TABLE db1.t1;
415 Table Create Table
416 t1 CREATE TABLE `t1` (
417 `f1` int DEFAULT NULL
418 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
419 SHOW CREATE TABLE db1.t2;
420 Table Create Table
421 t2 CREATE TABLE `t2` (
422 `f1` int DEFAULT NULL
423 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
424 SHOW CREATE TABLE db1.t3;
425 Table Create Table
426 t3 CREATE TABLE `t3` (
427 `f1` int DEFAULT NULL
428 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
429 SHOW CREATE TABLE db1.t4;
430 Table Create Table
431 t4 CREATE TABLE `t4` (
432 `f1` int DEFAULT NULL
433 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
434 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
435 WHERE TABLE_SCHEMA='db1';
436 TABLE_NAME CREATE_OPTIONS
437 t1 ENCRYPTION='y'
438 t2 ENCRYPTION='y'
439 t3 ENCRYPTION='y'
440 t4 ENCRYPTION='y'
441 # Cleanup
442 DROP DATABASE db1;
443 DROP TABLESPACE tsA;
444 DROP TABLESPACE ts1;
445 `````````````````````````````````````````````````````````
446 # table_encryption_privilege_check=ON and default per-db encryption OFF
447 # [ALTER TABLE] Case 9 )
448 `````````````````````````````````````````````````````````
449 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
450 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
451 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
452 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
453 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
454 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
455 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
456 SET GLOBAL table_encryption_privilege_check=true;
457 # Run ALTER TABLE and check for errors/warnings
458 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
459 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='n';
460 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
461 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='n';
462 SET GLOBAL table_encryption_privilege_check=false;
463 # Verify the ENCRYPTION clause value.
464 SHOW CREATE TABLE db1.t1;
465 Table Create Table
466 t1 CREATE TABLE `t1` (
467 `f1` int DEFAULT NULL
468 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
469 SHOW CREATE TABLE db1.t2;
470 Table Create Table
471 t2 CREATE TABLE `t2` (
472 `f1` int DEFAULT NULL
473 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
474 SHOW CREATE TABLE db1.t3;
475 Table Create Table
476 t3 CREATE TABLE `t3` (
477 `f1` int DEFAULT NULL
478 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
479 SHOW CREATE TABLE db1.t4;
480 Table Create Table
481 t4 CREATE TABLE `t4` (
482 `f1` int DEFAULT NULL
483 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
484 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
485 WHERE TABLE_SCHEMA='db1';
486 TABLE_NAME CREATE_OPTIONS
487 t1
488 t2
489 t3
490 t4
491 # Cleanup
492 DROP DATABASE db1;
493 DROP TABLESPACE tsA;
494 DROP TABLESPACE ts1;
495 `````````````````````````````````````````````````````````
496 # Request to create encrypted table to unencrypted database is rejected.
497 # [ALTER TABLE] Case 10 )
498 `````````````````````````````````````````````````````````
499 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
500 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
501 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
502 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
503 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
504 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
505 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
506 SET GLOBAL table_encryption_privilege_check=true;
507 # Run ALTER TABLE and check for errors/warnings
508 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
509 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
510 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='y';
511 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
512 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
513 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
514 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='y';
515 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
516 SET GLOBAL table_encryption_privilege_check=false;
517 # Verify the ENCRYPTION clause value.
518 SHOW CREATE TABLE db1.t1;
519 Table Create Table
520 t1 CREATE TABLE `t1` (
521 `f1` int DEFAULT NULL
522 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
523 SHOW CREATE TABLE db1.t2;
524 Table Create Table
525 t2 CREATE TABLE `t2` (
526 `f1` int DEFAULT NULL
527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
528 SHOW CREATE TABLE db1.t3;
529 Table Create Table
530 t3 CREATE TABLE `t3` (
531 `f1` int DEFAULT NULL
532 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
533 SHOW CREATE TABLE db1.t4;
534 Table Create Table
535 t4 CREATE TABLE `t4` (
536 `f1` int DEFAULT NULL
537 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
538 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
539 WHERE TABLE_SCHEMA='db1';
540 TABLE_NAME CREATE_OPTIONS
541 t1
542 t2
543 t3
544 t4
545 # Cleanup
546 DROP DATABASE db1;
547 DROP TABLESPACE tsA;
548 DROP TABLESPACE ts1;
549 # [ALTER TABLE] Case 11 )
550 `````````````````````````````````````````````````````````
551 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
552 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
553 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
554 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
555 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
556 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
557 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
558 # Grant TABLE_ENCRYPTION_ADMIN if requested.
559 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
560 SET GLOBAL table_encryption_privilege_check=true;
561 # Run ALTER TABLE and check for errors/warnings
562 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
563 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='y';
564 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
565 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='y';
566 SET GLOBAL table_encryption_privilege_check=false;
567 # Verify the ENCRYPTION clause value.
568 SHOW CREATE TABLE db1.t1;
569 Table Create Table
570 t1 CREATE TABLE `t1` (
571 `f1` int DEFAULT NULL
572 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
573 SHOW CREATE TABLE db1.t2;
574 Table Create Table
575 t2 CREATE TABLE `t2` (
576 `f1` int DEFAULT NULL
577 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
578 SHOW CREATE TABLE db1.t3;
579 Table Create Table
580 t3 CREATE TABLE `t3` (
581 `f1` int DEFAULT NULL
582 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
583 SHOW CREATE TABLE db1.t4;
584 Table Create Table
585 t4 CREATE TABLE `t4` (
586 `f1` int DEFAULT NULL
587 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
588 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
589 WHERE TABLE_SCHEMA='db1';
590 TABLE_NAME CREATE_OPTIONS
591 t1 ENCRYPTION='y'
592 t2 ENCRYPTION='y'
593 t3 ENCRYPTION='y'
594 t4 ENCRYPTION='y'
595 # Cleanup
596 # REVOKE TABLE_ENCRYPTION_ADMIN from user.
597 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
598 DROP DATABASE db1;
599 DROP TABLESPACE tsA;
600 DROP TABLESPACE ts1;
601 `````````````````````````````````````````````````````````
602 # Moving encrypted tablespace to unencrypted tablespace is rejected.
603 # [ALTER TABLE] Case 12 )
604 `````````````````````````````````````````````````````````
605 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
606 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
607 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
608 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
609 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
610 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
611 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
612 SET GLOBAL table_encryption_privilege_check=true;
613 # Run ALTER TABLE and check for errors/warnings
614 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
615 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='n';
616 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
617 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
618 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='n';
619 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
620 SET GLOBAL table_encryption_privilege_check=false;
621 # Verify the ENCRYPTION clause value.
622 SHOW CREATE TABLE db1.t1;
623 Table Create Table
624 t1 CREATE TABLE `t1` (
625 `f1` int DEFAULT NULL
626 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
627 SHOW CREATE TABLE db1.t2;
628 Table Create Table
629 t2 CREATE TABLE `t2` (
630 `f1` int DEFAULT NULL
631 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
632 SHOW CREATE TABLE db1.t3;
633 Table Create Table
634 t3 CREATE TABLE `t3` (
635 `f1` int DEFAULT NULL
636 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
637 SHOW CREATE TABLE db1.t4;
638 Table Create Table
639 t4 CREATE TABLE `t4` (
640 `f1` int DEFAULT NULL
641 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
642 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
643 WHERE TABLE_SCHEMA='db1';
644 TABLE_NAME CREATE_OPTIONS
645 t1
646 t2 ENCRYPTION='y'
647 t3
648 t4 ENCRYPTION='y'
649 # Cleanup
650 DROP DATABASE db1;
651 DROP TABLESPACE tsA;
652 DROP TABLESPACE ts1;
653 # [ALTER TABLE] Case 13 )
654 `````````````````````````````````````````````````````````
655 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
656 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
657 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
658 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
659 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
660 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
661 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
662 SET GLOBAL table_encryption_privilege_check=true;
663 # Run ALTER TABLE and check for errors/warnings
664 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
665 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='y';
666 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
667 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='y';
668 SET GLOBAL table_encryption_privilege_check=false;
669 # Verify the ENCRYPTION clause value.
670 SHOW CREATE TABLE db1.t1;
671 Table Create Table
672 t1 CREATE TABLE `t1` (
673 `f1` int DEFAULT NULL
674 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
675 SHOW CREATE TABLE db1.t2;
676 Table Create Table
677 t2 CREATE TABLE `t2` (
678 `f1` int DEFAULT NULL
679 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
680 SHOW CREATE TABLE db1.t3;
681 Table Create Table
682 t3 CREATE TABLE `t3` (
683 `f1` int DEFAULT NULL
684 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
685 SHOW CREATE TABLE db1.t4;
686 Table Create Table
687 t4 CREATE TABLE `t4` (
688 `f1` int DEFAULT NULL
689 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
690 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
691 WHERE TABLE_SCHEMA='db1';
692 TABLE_NAME CREATE_OPTIONS
693 t1 ENCRYPTION='y'
694 t2 ENCRYPTION='y'
695 t3 ENCRYPTION='y'
696 t4 ENCRYPTION='y'
697 # Cleanup
698 DROP DATABASE db1;
699 DROP TABLESPACE tsA;
700 DROP TABLESPACE ts1;
701 `````````````````````````````````````````````````````````
702 # table_encryption_privilege_check=ON and default per-db encryption ON
703 # [ALTER TABLE] Case 14 )
704 `````````````````````````````````````````````````````````
705 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
706 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
707 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
708 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
709 Warnings:
710 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
711 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
712 Warnings:
713 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
714 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
715 Warnings:
716 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
717 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
718 Warnings:
719 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
720 SET GLOBAL table_encryption_privilege_check=true;
721 # Run ALTER TABLE and check for errors/warnings
722 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
723 Warnings:
724 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
725 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='n';
726 Warnings:
727 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
728 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
729 Warnings:
730 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
731 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='n';
732 Warnings:
733 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
734 SET GLOBAL table_encryption_privilege_check=false;
735 # Verify the ENCRYPTION clause value.
736 SHOW CREATE TABLE db1.t1;
737 Table Create Table
738 t1 CREATE TABLE `t1` (
739 `f1` int DEFAULT NULL
740 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
741 SHOW CREATE TABLE db1.t2;
742 Table Create Table
743 t2 CREATE TABLE `t2` (
744 `f1` int DEFAULT NULL
745 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
746 SHOW CREATE TABLE db1.t3;
747 Table Create Table
748 t3 CREATE TABLE `t3` (
749 `f1` int DEFAULT NULL
750 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
751 SHOW CREATE TABLE db1.t4;
752 Table Create Table
753 t4 CREATE TABLE `t4` (
754 `f1` int DEFAULT NULL
755 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
756 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
757 WHERE TABLE_SCHEMA='db1';
758 TABLE_NAME CREATE_OPTIONS
759 t1 ENCRYPTION='n'
760 t2 ENCRYPTION='n'
761 t3 ENCRYPTION='n'
762 t4 ENCRYPTION='n'
763 # Cleanup
764 DROP DATABASE db1;
765 DROP TABLESPACE tsA;
766 DROP TABLESPACE ts1;
767 # [ALTER TABLE] Case 15 )
768 `````````````````````````````````````````````````````````
769 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
770 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
771 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
772 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
773 Warnings:
774 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
775 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
776 Warnings:
777 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
778 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
779 Warnings:
780 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
781 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
782 Warnings:
783 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
784 SET GLOBAL table_encryption_privilege_check=true;
785 # Run ALTER TABLE and check for errors/warnings
786 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
787 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='y';
788 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
789 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='y';
790 SET GLOBAL table_encryption_privilege_check=false;
791 # Verify the ENCRYPTION clause value.
792 SHOW CREATE TABLE db1.t1;
793 Table Create Table
794 t1 CREATE TABLE `t1` (
795 `f1` int DEFAULT NULL
796 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
797 SHOW CREATE TABLE db1.t2;
798 Table Create Table
799 t2 CREATE TABLE `t2` (
800 `f1` int DEFAULT NULL
801 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
802 SHOW CREATE TABLE db1.t3;
803 Table Create Table
804 t3 CREATE TABLE `t3` (
805 `f1` int DEFAULT NULL
806 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
807 SHOW CREATE TABLE db1.t4;
808 Table Create Table
809 t4 CREATE TABLE `t4` (
810 `f1` int DEFAULT NULL
811 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
812 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
813 WHERE TABLE_SCHEMA='db1';
814 TABLE_NAME CREATE_OPTIONS
815 t1 ENCRYPTION='y'
816 t2 ENCRYPTION='y'
817 t3 ENCRYPTION='y'
818 t4 ENCRYPTION='y'
819 # Cleanup
820 DROP DATABASE db1;
821 DROP TABLESPACE tsA;
822 DROP TABLESPACE ts1;
823 # [ALTER TABLE] Case 16 )
824 `````````````````````````````````````````````````````````
825 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
826 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
827 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
828 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
829 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
830 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
831 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
832 SET GLOBAL table_encryption_privilege_check=true;
833 # Run ALTER TABLE and check for errors/warnings
834 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
835 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
836 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='n';
837 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
838 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
839 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
840 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='n';
841 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
842 SET GLOBAL table_encryption_privilege_check=false;
843 # Verify the ENCRYPTION clause value.
844 SHOW CREATE TABLE db1.t1;
845 Table Create Table
846 t1 CREATE TABLE `t1` (
847 `f1` int DEFAULT NULL
848 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
849 SHOW CREATE TABLE db1.t2;
850 Table Create Table
851 t2 CREATE TABLE `t2` (
852 `f1` int DEFAULT NULL
853 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
854 SHOW CREATE TABLE db1.t3;
855 Table Create Table
856 t3 CREATE TABLE `t3` (
857 `f1` int DEFAULT NULL
858 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
859 SHOW CREATE TABLE db1.t4;
860 Table Create Table
861 t4 CREATE TABLE `t4` (
862 `f1` int DEFAULT NULL
863 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
864 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
865 WHERE TABLE_SCHEMA='db1';
866 TABLE_NAME CREATE_OPTIONS
867 t1 ENCRYPTION='y'
868 t2 ENCRYPTION='y'
869 t3 ENCRYPTION='y'
870 t4 ENCRYPTION='y'
871 # Cleanup
872 DROP DATABASE db1;
873 DROP TABLESPACE tsA;
874 DROP TABLESPACE ts1;
875 `````````````````````````````````````````````````````````
876 # Moving encrypted tablespace to unencrypted tablespace is rejected.
877 # [ALTER TABLE] Case 17 )
878 `````````````````````````````````````````````````````````
879 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
880 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
881 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
882 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
883 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
884 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
885 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
886 # Grant TABLE_ENCRYPTION_ADMIN if requested.
887 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
888 SET GLOBAL table_encryption_privilege_check=true;
889 # Run ALTER TABLE and check for errors/warnings
890 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
891 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='n';
892 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
893 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
894 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='n';
895 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
896 SET GLOBAL table_encryption_privilege_check=false;
897 # Verify the ENCRYPTION clause value.
898 SHOW CREATE TABLE db1.t1;
899 Table Create Table
900 t1 CREATE TABLE `t1` (
901 `f1` int DEFAULT NULL
902 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
903 SHOW CREATE TABLE db1.t2;
904 Table Create Table
905 t2 CREATE TABLE `t2` (
906 `f1` int DEFAULT NULL
907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
908 SHOW CREATE TABLE db1.t3;
909 Table Create Table
910 t3 CREATE TABLE `t3` (
911 `f1` int DEFAULT NULL
912 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
913 SHOW CREATE TABLE db1.t4;
914 Table Create Table
915 t4 CREATE TABLE `t4` (
916 `f1` int DEFAULT NULL
917 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
918 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
919 WHERE TABLE_SCHEMA='db1';
920 TABLE_NAME CREATE_OPTIONS
921 t1 ENCRYPTION='n'
922 t2 ENCRYPTION='y'
923 t3 ENCRYPTION='n'
924 t4 ENCRYPTION='y'
925 # Cleanup
926 # REVOKE TABLE_ENCRYPTION_ADMIN from user.
927 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
928 DROP DATABASE db1;
929 DROP TABLESPACE tsA;
930 DROP TABLESPACE ts1;
931 # [ALTER TABLE] Case 18 )
932 `````````````````````````````````````````````````````````
933 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
934 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
935 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
936 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
937 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
938 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
939 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
940 SET GLOBAL table_encryption_privilege_check=true;
941 # Run ALTER TABLE and check for errors/warnings
942 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
943 ALTER TABLE db1.t2 TABLESPACE=ts1 ENCRYPTION='y';
944 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
945 ALTER TABLE db1.t4 TABLESPACE=ts1 ENCRYPTION='y';
946 SET GLOBAL table_encryption_privilege_check=false;
947 # Verify the ENCRYPTION clause value.
948 SHOW CREATE TABLE db1.t1;
949 Table Create Table
950 t1 CREATE TABLE `t1` (
951 `f1` int DEFAULT NULL
952 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
953 SHOW CREATE TABLE db1.t2;
954 Table Create Table
955 t2 CREATE TABLE `t2` (
956 `f1` int DEFAULT NULL
957 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
958 SHOW CREATE TABLE db1.t3;
959 Table Create Table
960 t3 CREATE TABLE `t3` (
961 `f1` int DEFAULT NULL
962 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
963 SHOW CREATE TABLE db1.t4;
964 Table Create Table
965 t4 CREATE TABLE `t4` (
966 `f1` int DEFAULT NULL
967 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
968 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
969 WHERE TABLE_SCHEMA='db1';
970 TABLE_NAME CREATE_OPTIONS
971 t1 ENCRYPTION='y'
972 t2 ENCRYPTION='y'
973 t3 ENCRYPTION='y'
974 t4 ENCRYPTION='y'
975 # Cleanup
976 DROP DATABASE db1;
977 DROP TABLESPACE tsA;
978 DROP TABLESPACE ts1;
979 `````````````````````````````````````````````````````````
980 # Using 'innodb_system' tablespace
981 `````````````````````````````````````````````````````````
982 # table_encryption_privilege_check=OFF and default per-db encryption OFF
983 # [ALTER TABLE] Case 19 )
984 `````````````````````````````````````````````````````````
985 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
986 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
987 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
988 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
989 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
990 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
991 SET GLOBAL table_encryption_privilege_check=false;
992 # Run ALTER TABLE and check for errors/warnings
993 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
994 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='n';
995 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
996 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='n';
997 SET GLOBAL table_encryption_privilege_check=false;
998 # Verify the ENCRYPTION clause value.
999 SHOW CREATE TABLE db1.t1;
1000 Table Create Table
1001 t1 CREATE TABLE `t1` (
1002 `f1` int DEFAULT NULL
1003 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1004 SHOW CREATE TABLE db1.t2;
1005 Table Create Table
1006 t2 CREATE TABLE `t2` (
1007 `f1` int DEFAULT NULL
1008 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1009 SHOW CREATE TABLE db1.t3;
1010 Table Create Table
1011 t3 CREATE TABLE `t3` (
1012 `f1` int DEFAULT NULL
1013 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1014 SHOW CREATE TABLE db1.t4;
1015 Table Create Table
1016 t4 CREATE TABLE `t4` (
1017 `f1` int DEFAULT NULL
1018 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1019 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1020 WHERE TABLE_SCHEMA='db1';
1021 TABLE_NAME CREATE_OPTIONS
1022 t1
1023 t2
1024 t3
1025 t4
1026 # Cleanup
1027 DROP DATABASE db1;
1028 DROP TABLESPACE tsA;
1029 `````````````````````````````````````````````````````````
1030 # Creating a encrypted table is rejected in system tablespace
1031 # [ALTER TABLE] Case 20 )
1032 `````````````````````````````````````````````````````````
1033 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
1034 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
1035 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
1036 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
1037 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1038 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1039 SET GLOBAL table_encryption_privilege_check=false;
1040 # Run ALTER TABLE and check for errors/warnings
1041 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1042 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='y';
1043 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1044 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1045 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='y';
1046 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1047 SET GLOBAL table_encryption_privilege_check=false;
1048 # Verify the ENCRYPTION clause value.
1049 SHOW CREATE TABLE db1.t1;
1050 Table Create Table
1051 t1 CREATE TABLE `t1` (
1052 `f1` int DEFAULT NULL
1053 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1054 SHOW CREATE TABLE db1.t2;
1055 Table Create Table
1056 t2 CREATE TABLE `t2` (
1057 `f1` int DEFAULT NULL
1058 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1059 SHOW CREATE TABLE db1.t3;
1060 Table Create Table
1061 t3 CREATE TABLE `t3` (
1062 `f1` int DEFAULT NULL
1063 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1064 SHOW CREATE TABLE db1.t4;
1065 Table Create Table
1066 t4 CREATE TABLE `t4` (
1067 `f1` int DEFAULT NULL
1068 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1069 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1070 WHERE TABLE_SCHEMA='db1';
1071 TABLE_NAME CREATE_OPTIONS
1072 t1 ENCRYPTION='y'
1073 t2
1074 t3 ENCRYPTION='y'
1075 t4
1076 # Cleanup
1077 DROP DATABASE db1;
1078 DROP TABLESPACE tsA;
1079 # [ALTER TABLE] Case 21 )
1080 `````````````````````````````````````````````````````````
1081 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
1082 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
1083 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
1084 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
1085 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1086 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1087 SET GLOBAL table_encryption_privilege_check=false;
1088 # Run ALTER TABLE and check for errors/warnings
1089 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1090 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='n';
1091 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
1092 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1093 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='n';
1094 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
1095 SET GLOBAL table_encryption_privilege_check=false;
1096 # Verify the ENCRYPTION clause value.
1097 SHOW CREATE TABLE db1.t1;
1098 Table Create Table
1099 t1 CREATE TABLE `t1` (
1100 `f1` int DEFAULT NULL
1101 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1102 SHOW CREATE TABLE db1.t2;
1103 Table Create Table
1104 t2 CREATE TABLE `t2` (
1105 `f1` int DEFAULT NULL
1106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1107 SHOW CREATE TABLE db1.t3;
1108 Table Create Table
1109 t3 CREATE TABLE `t3` (
1110 `f1` int DEFAULT NULL
1111 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1112 SHOW CREATE TABLE db1.t4;
1113 Table Create Table
1114 t4 CREATE TABLE `t4` (
1115 `f1` int DEFAULT NULL
1116 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
1117 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1118 WHERE TABLE_SCHEMA='db1';
1119 TABLE_NAME CREATE_OPTIONS
1120 t1
1121 t2 ENCRYPTION='y'
1122 t3
1123 t4 ENCRYPTION='y'
1124 # Cleanup
1125 DROP DATABASE db1;
1126 DROP TABLESPACE tsA;
1127 `````````````````````````````````````````````````````````
1128 # Creating a encrypted table is rejected in system tablespace
1129 # [ALTER TABLE] Case 22 )
1130 `````````````````````````````````````````````````````````
1131 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
1132 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
1133 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
1134 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
1135 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1136 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1137 SET GLOBAL table_encryption_privilege_check=false;
1138 # Run ALTER TABLE and check for errors/warnings
1139 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1140 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='y';
1141 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1142 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1143 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='y';
1144 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1145 SET GLOBAL table_encryption_privilege_check=false;
1146 # Verify the ENCRYPTION clause value.
1147 SHOW CREATE TABLE db1.t1;
1148 Table Create Table
1149 t1 CREATE TABLE `t1` (
1150 `f1` int DEFAULT NULL
1151 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1152 SHOW CREATE TABLE db1.t2;
1153 Table Create Table
1154 t2 CREATE TABLE `t2` (
1155 `f1` int DEFAULT NULL
1156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1157 SHOW CREATE TABLE db1.t3;
1158 Table Create Table
1159 t3 CREATE TABLE `t3` (
1160 `f1` int DEFAULT NULL
1161 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1162 SHOW CREATE TABLE db1.t4;
1163 Table Create Table
1164 t4 CREATE TABLE `t4` (
1165 `f1` int DEFAULT NULL
1166 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
1167 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1168 WHERE TABLE_SCHEMA='db1';
1169 TABLE_NAME CREATE_OPTIONS
1170 t1 ENCRYPTION='y'
1171 t2 ENCRYPTION='y'
1172 t3 ENCRYPTION='y'
1173 t4 ENCRYPTION='y'
1174 # Cleanup
1175 DROP DATABASE db1;
1176 DROP TABLESPACE tsA;
1177 `````````````````````````````````````````````````````````
1178 # table_encryption_privilege_check=OFF and default per-db encryption ON
1179 # [ALTER TABLE] Case 23 )
1180 `````````````````````````````````````````````````````````
1181 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
1182 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
1183 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
1184 Warnings:
1185 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1186 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
1187 Warnings:
1188 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1189 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1190 Warnings:
1191 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1192 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1193 Warnings:
1194 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1195 SET GLOBAL table_encryption_privilege_check=false;
1196 # Run ALTER TABLE and check for errors/warnings
1197 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1198 Warnings:
1199 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1200 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='n';
1201 Warnings:
1202 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1203 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1204 Warnings:
1205 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1206 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='n';
1207 Warnings:
1208 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1209 SET GLOBAL table_encryption_privilege_check=false;
1210 # Verify the ENCRYPTION clause value.
1211 SHOW CREATE TABLE db1.t1;
1212 Table Create Table
1213 t1 CREATE TABLE `t1` (
1214 `f1` int DEFAULT NULL
1215 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
1216 SHOW CREATE TABLE db1.t2;
1217 Table Create Table
1218 t2 CREATE TABLE `t2` (
1219 `f1` int DEFAULT NULL
1220 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
1221 SHOW CREATE TABLE db1.t3;
1222 Table Create Table
1223 t3 CREATE TABLE `t3` (
1224 `f1` int DEFAULT NULL
1225 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
1226 SHOW CREATE TABLE db1.t4;
1227 Table Create Table
1228 t4 CREATE TABLE `t4` (
1229 `f1` int DEFAULT NULL
1230 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
1231 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1232 WHERE TABLE_SCHEMA='db1';
1233 TABLE_NAME CREATE_OPTIONS
1234 t1 ENCRYPTION='n'
1235 t2 ENCRYPTION='n'
1236 t3 ENCRYPTION='n'
1237 t4 ENCRYPTION='n'
1238 # Cleanup
1239 DROP DATABASE db1;
1240 DROP TABLESPACE tsA;
1241 `````````````````````````````````````````````````````````
1242 # Creating a encrypted table is rejected in system tablespace
1243 # [ALTER TABLE] Case 24 )
1244 `````````````````````````````````````````````````````````
1245 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
1246 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
1247 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
1248 Warnings:
1249 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1250 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
1251 Warnings:
1252 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1253 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1254 Warnings:
1255 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1256 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1257 Warnings:
1258 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1259 SET GLOBAL table_encryption_privilege_check=false;
1260 # Run ALTER TABLE and check for errors/warnings
1261 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1262 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='y';
1263 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1264 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1265 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='y';
1266 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1267 SET GLOBAL table_encryption_privilege_check=false;
1268 # Verify the ENCRYPTION clause value.
1269 SHOW CREATE TABLE db1.t1;
1270 Table Create Table
1271 t1 CREATE TABLE `t1` (
1272 `f1` int DEFAULT NULL
1273 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1274 SHOW CREATE TABLE db1.t2;
1275 Table Create Table
1276 t2 CREATE TABLE `t2` (
1277 `f1` int DEFAULT NULL
1278 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
1279 SHOW CREATE TABLE db1.t3;
1280 Table Create Table
1281 t3 CREATE TABLE `t3` (
1282 `f1` int DEFAULT NULL
1283 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1284 SHOW CREATE TABLE db1.t4;
1285 Table Create Table
1286 t4 CREATE TABLE `t4` (
1287 `f1` int DEFAULT NULL
1288 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
1289 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1290 WHERE TABLE_SCHEMA='db1';
1291 TABLE_NAME CREATE_OPTIONS
1292 t1 ENCRYPTION='y'
1293 t2 ENCRYPTION='n'
1294 t3 ENCRYPTION='y'
1295 t4 ENCRYPTION='n'
1296 # Cleanup
1297 DROP DATABASE db1;
1298 DROP TABLESPACE tsA;
1299 # [ALTER TABLE] Case 25 )
1300 `````````````````````````````````````````````````````````
1301 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
1302 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
1303 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
1304 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
1305 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1306 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1307 SET GLOBAL table_encryption_privilege_check=false;
1308 # Run ALTER TABLE and check for errors/warnings
1309 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1310 Warnings:
1311 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1312 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='n';
1313 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
1314 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1315 Warnings:
1316 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1317 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='n';
1318 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
1319 SET GLOBAL table_encryption_privilege_check=false;
1320 # Verify the ENCRYPTION clause value.
1321 SHOW CREATE TABLE db1.t1;
1322 Table Create Table
1323 t1 CREATE TABLE `t1` (
1324 `f1` int DEFAULT NULL
1325 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
1326 SHOW CREATE TABLE db1.t2;
1327 Table Create Table
1328 t2 CREATE TABLE `t2` (
1329 `f1` int DEFAULT NULL
1330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1331 SHOW CREATE TABLE db1.t3;
1332 Table Create Table
1333 t3 CREATE TABLE `t3` (
1334 `f1` int DEFAULT NULL
1335 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
1336 SHOW CREATE TABLE db1.t4;
1337 Table Create Table
1338 t4 CREATE TABLE `t4` (
1339 `f1` int DEFAULT NULL
1340 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
1341 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1342 WHERE TABLE_SCHEMA='db1';
1343 TABLE_NAME CREATE_OPTIONS
1344 t1 ENCRYPTION='n'
1345 t2 ENCRYPTION='y'
1346 t3 ENCRYPTION='n'
1347 t4 ENCRYPTION='y'
1348 # Cleanup
1349 DROP DATABASE db1;
1350 DROP TABLESPACE tsA;
1351 `````````````````````````````````````````````````````````
1352 # Creating a encrypted table is rejected in system tablespace
1353 # [ALTER TABLE] Case 26 )
1354 `````````````````````````````````````````````````````````
1355 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
1356 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
1357 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
1358 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
1359 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1360 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1361 SET GLOBAL table_encryption_privilege_check=false;
1362 # Run ALTER TABLE and check for errors/warnings
1363 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1364 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='y';
1365 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1366 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1367 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='y';
1368 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1369 SET GLOBAL table_encryption_privilege_check=false;
1370 # Verify the ENCRYPTION clause value.
1371 SHOW CREATE TABLE db1.t1;
1372 Table Create Table
1373 t1 CREATE TABLE `t1` (
1374 `f1` int DEFAULT NULL
1375 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1376 SHOW CREATE TABLE db1.t2;
1377 Table Create Table
1378 t2 CREATE TABLE `t2` (
1379 `f1` int DEFAULT NULL
1380 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1381 SHOW CREATE TABLE db1.t3;
1382 Table Create Table
1383 t3 CREATE TABLE `t3` (
1384 `f1` int DEFAULT NULL
1385 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1386 SHOW CREATE TABLE db1.t4;
1387 Table Create Table
1388 t4 CREATE TABLE `t4` (
1389 `f1` int DEFAULT NULL
1390 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
1391 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1392 WHERE TABLE_SCHEMA='db1';
1393 TABLE_NAME CREATE_OPTIONS
1394 t1 ENCRYPTION='y'
1395 t2 ENCRYPTION='y'
1396 t3 ENCRYPTION='y'
1397 t4 ENCRYPTION='y'
1398 # Cleanup
1399 DROP DATABASE db1;
1400 DROP TABLESPACE tsA;
1401 `````````````````````````````````````````````````````````
1402 # table_encryption_privilege_check=ON and default per-db encryption OFF
1403 # [ALTER TABLE] Case 27 )
1404 `````````````````````````````````````````````````````````
1405 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
1406 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
1407 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
1408 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
1409 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1410 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1411 SET GLOBAL table_encryption_privilege_check=true;
1412 # Run ALTER TABLE and check for errors/warnings
1413 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1414 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='n';
1415 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1416 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='n';
1417 SET GLOBAL table_encryption_privilege_check=false;
1418 # Verify the ENCRYPTION clause value.
1419 SHOW CREATE TABLE db1.t1;
1420 Table Create Table
1421 t1 CREATE TABLE `t1` (
1422 `f1` int DEFAULT NULL
1423 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1424 SHOW CREATE TABLE db1.t2;
1425 Table Create Table
1426 t2 CREATE TABLE `t2` (
1427 `f1` int DEFAULT NULL
1428 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1429 SHOW CREATE TABLE db1.t3;
1430 Table Create Table
1431 t3 CREATE TABLE `t3` (
1432 `f1` int DEFAULT NULL
1433 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1434 SHOW CREATE TABLE db1.t4;
1435 Table Create Table
1436 t4 CREATE TABLE `t4` (
1437 `f1` int DEFAULT NULL
1438 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1439 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1440 WHERE TABLE_SCHEMA='db1';
1441 TABLE_NAME CREATE_OPTIONS
1442 t1
1443 t2
1444 t3
1445 t4
1446 # Cleanup
1447 DROP DATABASE db1;
1448 DROP TABLESPACE tsA;
1449 `````````````````````````````````````````````````````````
1450 # Request to create encrypted table to unencrypted database is rejected.
1451 # [ALTER TABLE] Case 28 )
1452 `````````````````````````````````````````````````````````
1453 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
1454 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
1455 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
1456 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
1457 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1458 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1459 SET GLOBAL table_encryption_privilege_check=true;
1460 # Run ALTER TABLE and check for errors/warnings
1461 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1462 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
1463 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='y';
1464 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1465 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1466 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
1467 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='y';
1468 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1469 SET GLOBAL table_encryption_privilege_check=false;
1470 # Verify the ENCRYPTION clause value.
1471 SHOW CREATE TABLE db1.t1;
1472 Table Create Table
1473 t1 CREATE TABLE `t1` (
1474 `f1` int DEFAULT NULL
1475 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1476 SHOW CREATE TABLE db1.t2;
1477 Table Create Table
1478 t2 CREATE TABLE `t2` (
1479 `f1` int DEFAULT NULL
1480 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1481 SHOW CREATE TABLE db1.t3;
1482 Table Create Table
1483 t3 CREATE TABLE `t3` (
1484 `f1` int DEFAULT NULL
1485 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1486 SHOW CREATE TABLE db1.t4;
1487 Table Create Table
1488 t4 CREATE TABLE `t4` (
1489 `f1` int DEFAULT NULL
1490 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1491 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1492 WHERE TABLE_SCHEMA='db1';
1493 TABLE_NAME CREATE_OPTIONS
1494 t1
1495 t2
1496 t3
1497 t4
1498 # Cleanup
1499 DROP DATABASE db1;
1500 DROP TABLESPACE tsA;
1501 `````````````````````````````````````````````````````````
1502 # Creating a encrypted table is rejected in system tablespace
1503 # [ALTER TABLE] Case 29 )
1504 `````````````````````````````````````````````````````````
1505 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
1506 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
1507 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
1508 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
1509 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1510 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1511 # Grant TABLE_ENCRYPTION_ADMIN if requested.
1512 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
1513 SET GLOBAL table_encryption_privilege_check=true;
1514 # Run ALTER TABLE and check for errors/warnings
1515 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1516 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='y';
1517 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1518 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1519 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='y';
1520 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1521 SET GLOBAL table_encryption_privilege_check=false;
1522 # Verify the ENCRYPTION clause value.
1523 SHOW CREATE TABLE db1.t1;
1524 Table Create Table
1525 t1 CREATE TABLE `t1` (
1526 `f1` int DEFAULT NULL
1527 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1528 SHOW CREATE TABLE db1.t2;
1529 Table Create Table
1530 t2 CREATE TABLE `t2` (
1531 `f1` int DEFAULT NULL
1532 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1533 SHOW CREATE TABLE db1.t3;
1534 Table Create Table
1535 t3 CREATE TABLE `t3` (
1536 `f1` int DEFAULT NULL
1537 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1538 SHOW CREATE TABLE db1.t4;
1539 Table Create Table
1540 t4 CREATE TABLE `t4` (
1541 `f1` int DEFAULT NULL
1542 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1543 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1544 WHERE TABLE_SCHEMA='db1';
1545 TABLE_NAME CREATE_OPTIONS
1546 t1 ENCRYPTION='y'
1547 t2
1548 t3 ENCRYPTION='y'
1549 t4
1550 # Cleanup
1551 # REVOKE TABLE_ENCRYPTION_ADMIN from user.
1552 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
1553 DROP DATABASE db1;
1554 DROP TABLESPACE tsA;
1555 # [ALTER TABLE] Case 30 )
1556 `````````````````````````````````````````````````````````
1557 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
1558 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
1559 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
1560 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
1561 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1562 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1563 SET GLOBAL table_encryption_privilege_check=true;
1564 # Run ALTER TABLE and check for errors/warnings
1565 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1566 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='n';
1567 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
1568 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1569 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='n';
1570 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
1571 SET GLOBAL table_encryption_privilege_check=false;
1572 # Verify the ENCRYPTION clause value.
1573 SHOW CREATE TABLE db1.t1;
1574 Table Create Table
1575 t1 CREATE TABLE `t1` (
1576 `f1` int DEFAULT NULL
1577 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1578 SHOW CREATE TABLE db1.t2;
1579 Table Create Table
1580 t2 CREATE TABLE `t2` (
1581 `f1` int DEFAULT NULL
1582 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1583 SHOW CREATE TABLE db1.t3;
1584 Table Create Table
1585 t3 CREATE TABLE `t3` (
1586 `f1` int DEFAULT NULL
1587 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1588 SHOW CREATE TABLE db1.t4;
1589 Table Create Table
1590 t4 CREATE TABLE `t4` (
1591 `f1` int DEFAULT NULL
1592 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
1593 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1594 WHERE TABLE_SCHEMA='db1';
1595 TABLE_NAME CREATE_OPTIONS
1596 t1
1597 t2 ENCRYPTION='y'
1598 t3
1599 t4 ENCRYPTION='y'
1600 # Cleanup
1601 DROP DATABASE db1;
1602 DROP TABLESPACE tsA;
1603 `````````````````````````````````````````````````````````
1604 # Creating a encrypted table is rejected in system tablespace
1605 # [ALTER TABLE] Case 31 )
1606 `````````````````````````````````````````````````````````
1607 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
1608 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
1609 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
1610 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
1611 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1612 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1613 SET GLOBAL table_encryption_privilege_check=true;
1614 # Run ALTER TABLE and check for errors/warnings
1615 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1616 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='y';
1617 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1618 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1619 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='y';
1620 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1621 SET GLOBAL table_encryption_privilege_check=false;
1622 # Verify the ENCRYPTION clause value.
1623 SHOW CREATE TABLE db1.t1;
1624 Table Create Table
1625 t1 CREATE TABLE `t1` (
1626 `f1` int DEFAULT NULL
1627 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1628 SHOW CREATE TABLE db1.t2;
1629 Table Create Table
1630 t2 CREATE TABLE `t2` (
1631 `f1` int DEFAULT NULL
1632 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1633 SHOW CREATE TABLE db1.t3;
1634 Table Create Table
1635 t3 CREATE TABLE `t3` (
1636 `f1` int DEFAULT NULL
1637 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1638 SHOW CREATE TABLE db1.t4;
1639 Table Create Table
1640 t4 CREATE TABLE `t4` (
1641 `f1` int DEFAULT NULL
1642 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
1643 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1644 WHERE TABLE_SCHEMA='db1';
1645 TABLE_NAME CREATE_OPTIONS
1646 t1 ENCRYPTION='y'
1647 t2 ENCRYPTION='y'
1648 t3 ENCRYPTION='y'
1649 t4 ENCRYPTION='y'
1650 # Cleanup
1651 DROP DATABASE db1;
1652 DROP TABLESPACE tsA;
1653 `````````````````````````````````````````````````````````
1654 # table_encryption_privilege_check=ON and default per-db encryption ON
1655 # [ALTER TABLE] Case 32 )
1656 `````````````````````````````````````````````````````````
1657 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
1658 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
1659 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
1660 Warnings:
1661 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1662 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
1663 Warnings:
1664 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1665 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1666 Warnings:
1667 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1668 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1669 Warnings:
1670 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1671 SET GLOBAL table_encryption_privilege_check=true;
1672 # Run ALTER TABLE and check for errors/warnings
1673 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1674 Warnings:
1675 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1676 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='n';
1677 Warnings:
1678 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1679 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1680 Warnings:
1681 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1682 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='n';
1683 Warnings:
1684 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1685 SET GLOBAL table_encryption_privilege_check=false;
1686 # Verify the ENCRYPTION clause value.
1687 SHOW CREATE TABLE db1.t1;
1688 Table Create Table
1689 t1 CREATE TABLE `t1` (
1690 `f1` int DEFAULT NULL
1691 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
1692 SHOW CREATE TABLE db1.t2;
1693 Table Create Table
1694 t2 CREATE TABLE `t2` (
1695 `f1` int DEFAULT NULL
1696 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
1697 SHOW CREATE TABLE db1.t3;
1698 Table Create Table
1699 t3 CREATE TABLE `t3` (
1700 `f1` int DEFAULT NULL
1701 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
1702 SHOW CREATE TABLE db1.t4;
1703 Table Create Table
1704 t4 CREATE TABLE `t4` (
1705 `f1` int DEFAULT NULL
1706 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
1707 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1708 WHERE TABLE_SCHEMA='db1';
1709 TABLE_NAME CREATE_OPTIONS
1710 t1 ENCRYPTION='n'
1711 t2 ENCRYPTION='n'
1712 t3 ENCRYPTION='n'
1713 t4 ENCRYPTION='n'
1714 # Cleanup
1715 DROP DATABASE db1;
1716 DROP TABLESPACE tsA;
1717 `````````````````````````````````````````````````````````
1718 # Creating a encrypted table is rejected in system tablespace
1719 # [ALTER TABLE] Case 33 )
1720 `````````````````````````````````````````````````````````
1721 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
1722 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
1723 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
1724 Warnings:
1725 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1726 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
1727 Warnings:
1728 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1729 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1730 Warnings:
1731 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1732 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1733 Warnings:
1734 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
1735 SET GLOBAL table_encryption_privilege_check=true;
1736 # Run ALTER TABLE and check for errors/warnings
1737 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1738 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='y';
1739 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1740 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1741 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='y';
1742 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1743 SET GLOBAL table_encryption_privilege_check=false;
1744 # Verify the ENCRYPTION clause value.
1745 SHOW CREATE TABLE db1.t1;
1746 Table Create Table
1747 t1 CREATE TABLE `t1` (
1748 `f1` int DEFAULT NULL
1749 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1750 SHOW CREATE TABLE db1.t2;
1751 Table Create Table
1752 t2 CREATE TABLE `t2` (
1753 `f1` int DEFAULT NULL
1754 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
1755 SHOW CREATE TABLE db1.t3;
1756 Table Create Table
1757 t3 CREATE TABLE `t3` (
1758 `f1` int DEFAULT NULL
1759 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1760 SHOW CREATE TABLE db1.t4;
1761 Table Create Table
1762 t4 CREATE TABLE `t4` (
1763 `f1` int DEFAULT NULL
1764 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='n' */
1765 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1766 WHERE TABLE_SCHEMA='db1';
1767 TABLE_NAME CREATE_OPTIONS
1768 t1 ENCRYPTION='y'
1769 t2 ENCRYPTION='n'
1770 t3 ENCRYPTION='y'
1771 t4 ENCRYPTION='n'
1772 # Cleanup
1773 DROP DATABASE db1;
1774 DROP TABLESPACE tsA;
1775 `````````````````````````````````````````````````````````
1776 # Request to create unencrypted table under database with default
1777 # encryption=ON is rejected.
1778 # [ALTER TABLE] Case 34 )
1779 `````````````````````````````````````````````````````````
1780 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
1781 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
1782 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
1783 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
1784 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1785 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1786 SET GLOBAL table_encryption_privilege_check=true;
1787 # Run ALTER TABLE and check for errors/warnings
1788 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1789 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
1790 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='n';
1791 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
1792 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1793 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
1794 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='n';
1795 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
1796 SET GLOBAL table_encryption_privilege_check=false;
1797 # Verify the ENCRYPTION clause value.
1798 SHOW CREATE TABLE db1.t1;
1799 Table Create Table
1800 t1 CREATE TABLE `t1` (
1801 `f1` int DEFAULT NULL
1802 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1803 SHOW CREATE TABLE db1.t2;
1804 Table Create Table
1805 t2 CREATE TABLE `t2` (
1806 `f1` int DEFAULT NULL
1807 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1808 SHOW CREATE TABLE db1.t3;
1809 Table Create Table
1810 t3 CREATE TABLE `t3` (
1811 `f1` int DEFAULT NULL
1812 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
1813 SHOW CREATE TABLE db1.t4;
1814 Table Create Table
1815 t4 CREATE TABLE `t4` (
1816 `f1` int DEFAULT NULL
1817 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
1818 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1819 WHERE TABLE_SCHEMA='db1';
1820 TABLE_NAME CREATE_OPTIONS
1821 t1 ENCRYPTION='y'
1822 t2 ENCRYPTION='y'
1823 t3 ENCRYPTION='y'
1824 t4 ENCRYPTION='y'
1825 # Cleanup
1826 DROP DATABASE db1;
1827 DROP TABLESPACE tsA;
1828 # [ALTER TABLE] Case 35 )
1829 `````````````````````````````````````````````````````````
1830 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
1831 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
1832 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
1833 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
1834 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1835 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1836 # Grant TABLE_ENCRYPTION_ADMIN if requested.
1837 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
1838 SET GLOBAL table_encryption_privilege_check=true;
1839 # Run ALTER TABLE and check for errors/warnings
1840 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1841 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='n';
1842 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
1843 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1844 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='n';
1845 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
1846 SET GLOBAL table_encryption_privilege_check=false;
1847 # Verify the ENCRYPTION clause value.
1848 SHOW CREATE TABLE db1.t1;
1849 Table Create Table
1850 t1 CREATE TABLE `t1` (
1851 `f1` int DEFAULT NULL
1852 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
1853 SHOW CREATE TABLE db1.t2;
1854 Table Create Table
1855 t2 CREATE TABLE `t2` (
1856 `f1` int DEFAULT NULL
1857 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1858 SHOW CREATE TABLE db1.t3;
1859 Table Create Table
1860 t3 CREATE TABLE `t3` (
1861 `f1` int DEFAULT NULL
1862 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
1863 SHOW CREATE TABLE db1.t4;
1864 Table Create Table
1865 t4 CREATE TABLE `t4` (
1866 `f1` int DEFAULT NULL
1867 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
1868 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1869 WHERE TABLE_SCHEMA='db1';
1870 TABLE_NAME CREATE_OPTIONS
1871 t1 ENCRYPTION='n'
1872 t2 ENCRYPTION='y'
1873 t3 ENCRYPTION='n'
1874 t4 ENCRYPTION='y'
1875 # Cleanup
1876 # REVOKE TABLE_ENCRYPTION_ADMIN from user.
1877 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
1878 DROP DATABASE db1;
1879 DROP TABLESPACE tsA;
1880 `````````````````````````````````````````````````````````
1881 # Creating a encrypted table is rejected in system tablespace
1882 # [ALTER TABLE] Case 36 )
1883 `````````````````````````````````````````````````````````
1884 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
1885 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
1886 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
1887 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
1888 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1889 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
1890 SET GLOBAL table_encryption_privilege_check=true;
1891 # Run ALTER TABLE and check for errors/warnings
1892 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1893 ALTER TABLE db1.t2 TABLESPACE=innodb_system ENCRYPTION='y';
1894 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1895 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1896 ALTER TABLE db1.t4 TABLESPACE=innodb_system ENCRYPTION='y';
1897 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
1898 SET GLOBAL table_encryption_privilege_check=false;
1899 # Verify the ENCRYPTION clause value.
1900 SHOW CREATE TABLE db1.t1;
1901 Table Create Table
1902 t1 CREATE TABLE `t1` (
1903 `f1` int DEFAULT NULL
1904 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1905 SHOW CREATE TABLE db1.t2;
1906 Table Create Table
1907 t2 CREATE TABLE `t2` (
1908 `f1` int DEFAULT NULL
1909 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1910 SHOW CREATE TABLE db1.t3;
1911 Table Create Table
1912 t3 CREATE TABLE `t3` (
1913 `f1` int DEFAULT NULL
1914 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
1915 SHOW CREATE TABLE db1.t4;
1916 Table Create Table
1917 t4 CREATE TABLE `t4` (
1918 `f1` int DEFAULT NULL
1919 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
1920 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1921 WHERE TABLE_SCHEMA='db1';
1922 TABLE_NAME CREATE_OPTIONS
1923 t1 ENCRYPTION='y'
1924 t2 ENCRYPTION='y'
1925 t3 ENCRYPTION='y'
1926 t4 ENCRYPTION='y'
1927 # Cleanup
1928 DROP DATABASE db1;
1929 DROP TABLESPACE tsA;
1930 `````````````````````````````````````````````````````````
1931 # Test using 'innodb_file_per_table'
1932 `````````````````````````````````````````````````````````
1933 # table_encryption_privilege_check=OFF and default per-db encryption OFF
1934 # [ALTER TABLE] Case 37 )
1935 `````````````````````````````````````````````````````````
1936 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
1937 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
1938 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
1939 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
1940 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1941 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1942 SET GLOBAL table_encryption_privilege_check=false;
1943 # Run ALTER TABLE and check for errors/warnings
1944 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1945 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1946 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1947 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
1948 SET GLOBAL table_encryption_privilege_check=false;
1949 # Verify the ENCRYPTION clause value.
1950 SHOW CREATE TABLE db1.t1;
1951 Table Create Table
1952 t1 CREATE TABLE `t1` (
1953 `f1` int DEFAULT NULL
1954 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1955 SHOW CREATE TABLE db1.t2;
1956 Table Create Table
1957 t2 CREATE TABLE `t2` (
1958 `f1` int DEFAULT NULL
1959 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1960 SHOW CREATE TABLE db1.t3;
1961 Table Create Table
1962 t3 CREATE TABLE `t3` (
1963 `f1` int DEFAULT NULL
1964 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1965 SHOW CREATE TABLE db1.t4;
1966 Table Create Table
1967 t4 CREATE TABLE `t4` (
1968 `f1` int DEFAULT NULL
1969 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1970 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
1971 WHERE TABLE_SCHEMA='db1';
1972 TABLE_NAME CREATE_OPTIONS
1973 t1
1974 t2
1975 t3
1976 t4
1977 # Cleanup
1978 DROP DATABASE db1;
1979 DROP TABLESPACE tsA;
1980 # [ALTER TABLE] Case 38 )
1981 `````````````````````````````````````````````````````````
1982 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
1983 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
1984 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
1985 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
1986 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1987 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
1988 SET GLOBAL table_encryption_privilege_check=false;
1989 # Run ALTER TABLE and check for errors/warnings
1990 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1991 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1992 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1993 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
1994 SET GLOBAL table_encryption_privilege_check=false;
1995 # Verify the ENCRYPTION clause value.
1996 SHOW CREATE TABLE db1.t1;
1997 Table Create Table
1998 t1 CREATE TABLE `t1` (
1999 `f1` int DEFAULT NULL
2000 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2001 SHOW CREATE TABLE db1.t2;
2002 Table Create Table
2003 t2 CREATE TABLE `t2` (
2004 `f1` int DEFAULT NULL
2005 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2006 SHOW CREATE TABLE db1.t3;
2007 Table Create Table
2008 t3 CREATE TABLE `t3` (
2009 `f1` int DEFAULT NULL
2010 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2011 SHOW CREATE TABLE db1.t4;
2012 Table Create Table
2013 t4 CREATE TABLE `t4` (
2014 `f1` int DEFAULT NULL
2015 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2016 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2017 WHERE TABLE_SCHEMA='db1';
2018 TABLE_NAME CREATE_OPTIONS
2019 t1 ENCRYPTION='y'
2020 t2 ENCRYPTION='y'
2021 t3 ENCRYPTION='y'
2022 t4 ENCRYPTION='y'
2023 # Cleanup
2024 DROP DATABASE db1;
2025 DROP TABLESPACE tsA;
2026 # [ALTER TABLE] Case 39 )
2027 `````````````````````````````````````````````````````````
2028 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
2029 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
2030 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
2031 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
2032 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2033 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2034 SET GLOBAL table_encryption_privilege_check=false;
2035 # Run ALTER TABLE and check for errors/warnings
2036 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2037 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2038 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2039 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2040 SET GLOBAL table_encryption_privilege_check=false;
2041 # Verify the ENCRYPTION clause value.
2042 SHOW CREATE TABLE db1.t1;
2043 Table Create Table
2044 t1 CREATE TABLE `t1` (
2045 `f1` int DEFAULT NULL
2046 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2047 SHOW CREATE TABLE db1.t2;
2048 Table Create Table
2049 t2 CREATE TABLE `t2` (
2050 `f1` int DEFAULT NULL
2051 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2052 SHOW CREATE TABLE db1.t3;
2053 Table Create Table
2054 t3 CREATE TABLE `t3` (
2055 `f1` int DEFAULT NULL
2056 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2057 SHOW CREATE TABLE db1.t4;
2058 Table Create Table
2059 t4 CREATE TABLE `t4` (
2060 `f1` int DEFAULT NULL
2061 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2062 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2063 WHERE TABLE_SCHEMA='db1';
2064 TABLE_NAME CREATE_OPTIONS
2065 t1
2066 t2
2067 t3
2068 t4
2069 # Cleanup
2070 DROP DATABASE db1;
2071 DROP TABLESPACE tsA;
2072 # [ALTER TABLE] Case 40 )
2073 `````````````````````````````````````````````````````````
2074 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
2075 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
2076 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
2077 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
2078 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2079 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2080 SET GLOBAL table_encryption_privilege_check=false;
2081 # Run ALTER TABLE and check for errors/warnings
2082 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2083 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2084 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2085 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2086 SET GLOBAL table_encryption_privilege_check=false;
2087 # Verify the ENCRYPTION clause value.
2088 SHOW CREATE TABLE db1.t1;
2089 Table Create Table
2090 t1 CREATE TABLE `t1` (
2091 `f1` int DEFAULT NULL
2092 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2093 SHOW CREATE TABLE db1.t2;
2094 Table Create Table
2095 t2 CREATE TABLE `t2` (
2096 `f1` int DEFAULT NULL
2097 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2098 SHOW CREATE TABLE db1.t3;
2099 Table Create Table
2100 t3 CREATE TABLE `t3` (
2101 `f1` int DEFAULT NULL
2102 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2103 SHOW CREATE TABLE db1.t4;
2104 Table Create Table
2105 t4 CREATE TABLE `t4` (
2106 `f1` int DEFAULT NULL
2107 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2108 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2109 WHERE TABLE_SCHEMA='db1';
2110 TABLE_NAME CREATE_OPTIONS
2111 t1 ENCRYPTION='y'
2112 t2 ENCRYPTION='y'
2113 t3 ENCRYPTION='y'
2114 t4 ENCRYPTION='y'
2115 # Cleanup
2116 DROP DATABASE db1;
2117 DROP TABLESPACE tsA;
2118 `````````````````````````````````````````````````````````
2119 # table_encryption_privilege_check=OFF and default per-db encryption ON
2120 # [ALTER TABLE] Case 41 )
2121 `````````````````````````````````````````````````````````
2122 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
2123 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
2124 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
2125 Warnings:
2126 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2127 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
2128 Warnings:
2129 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2130 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2131 Warnings:
2132 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2133 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2134 Warnings:
2135 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2136 SET GLOBAL table_encryption_privilege_check=false;
2137 # Run ALTER TABLE and check for errors/warnings
2138 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2139 Warnings:
2140 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2141 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2142 Warnings:
2143 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2144 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2145 Warnings:
2146 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2147 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2148 Warnings:
2149 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2150 SET GLOBAL table_encryption_privilege_check=false;
2151 # Verify the ENCRYPTION clause value.
2152 SHOW CREATE TABLE db1.t1;
2153 Table Create Table
2154 t1 CREATE TABLE `t1` (
2155 `f1` int DEFAULT NULL
2156 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2157 SHOW CREATE TABLE db1.t2;
2158 Table Create Table
2159 t2 CREATE TABLE `t2` (
2160 `f1` int DEFAULT NULL
2161 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2162 SHOW CREATE TABLE db1.t3;
2163 Table Create Table
2164 t3 CREATE TABLE `t3` (
2165 `f1` int DEFAULT NULL
2166 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2167 SHOW CREATE TABLE db1.t4;
2168 Table Create Table
2169 t4 CREATE TABLE `t4` (
2170 `f1` int DEFAULT NULL
2171 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2172 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2173 WHERE TABLE_SCHEMA='db1';
2174 TABLE_NAME CREATE_OPTIONS
2175 t1 ENCRYPTION='n'
2176 t2 ENCRYPTION='n'
2177 t3 ENCRYPTION='n'
2178 t4 ENCRYPTION='n'
2179 # Cleanup
2180 DROP DATABASE db1;
2181 DROP TABLESPACE tsA;
2182 # [ALTER TABLE] Case 42 )
2183 `````````````````````````````````````````````````````````
2184 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
2185 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
2186 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
2187 Warnings:
2188 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2189 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
2190 Warnings:
2191 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2192 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2193 Warnings:
2194 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2195 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2196 Warnings:
2197 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2198 SET GLOBAL table_encryption_privilege_check=false;
2199 # Run ALTER TABLE and check for errors/warnings
2200 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2201 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2202 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2203 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2204 SET GLOBAL table_encryption_privilege_check=false;
2205 # Verify the ENCRYPTION clause value.
2206 SHOW CREATE TABLE db1.t1;
2207 Table Create Table
2208 t1 CREATE TABLE `t1` (
2209 `f1` int DEFAULT NULL
2210 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2211 SHOW CREATE TABLE db1.t2;
2212 Table Create Table
2213 t2 CREATE TABLE `t2` (
2214 `f1` int DEFAULT NULL
2215 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2216 SHOW CREATE TABLE db1.t3;
2217 Table Create Table
2218 t3 CREATE TABLE `t3` (
2219 `f1` int DEFAULT NULL
2220 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2221 SHOW CREATE TABLE db1.t4;
2222 Table Create Table
2223 t4 CREATE TABLE `t4` (
2224 `f1` int DEFAULT NULL
2225 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2226 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2227 WHERE TABLE_SCHEMA='db1';
2228 TABLE_NAME CREATE_OPTIONS
2229 t1 ENCRYPTION='y'
2230 t2 ENCRYPTION='y'
2231 t3 ENCRYPTION='y'
2232 t4 ENCRYPTION='y'
2233 # Cleanup
2234 DROP DATABASE db1;
2235 DROP TABLESPACE tsA;
2236 # [ALTER TABLE] Case 43 )
2237 `````````````````````````````````````````````````````````
2238 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
2239 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
2240 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
2241 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
2242 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2243 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2244 SET GLOBAL table_encryption_privilege_check=false;
2245 # Run ALTER TABLE and check for errors/warnings
2246 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2247 Warnings:
2248 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2249 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2250 Warnings:
2251 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2252 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2253 Warnings:
2254 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2255 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2256 Warnings:
2257 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2258 SET GLOBAL table_encryption_privilege_check=false;
2259 # Verify the ENCRYPTION clause value.
2260 SHOW CREATE TABLE db1.t1;
2261 Table Create Table
2262 t1 CREATE TABLE `t1` (
2263 `f1` int DEFAULT NULL
2264 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2265 SHOW CREATE TABLE db1.t2;
2266 Table Create Table
2267 t2 CREATE TABLE `t2` (
2268 `f1` int DEFAULT NULL
2269 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2270 SHOW CREATE TABLE db1.t3;
2271 Table Create Table
2272 t3 CREATE TABLE `t3` (
2273 `f1` int DEFAULT NULL
2274 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2275 SHOW CREATE TABLE db1.t4;
2276 Table Create Table
2277 t4 CREATE TABLE `t4` (
2278 `f1` int DEFAULT NULL
2279 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2280 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2281 WHERE TABLE_SCHEMA='db1';
2282 TABLE_NAME CREATE_OPTIONS
2283 t1 ENCRYPTION='n'
2284 t2 ENCRYPTION='n'
2285 t3 ENCRYPTION='n'
2286 t4 ENCRYPTION='n'
2287 # Cleanup
2288 DROP DATABASE db1;
2289 DROP TABLESPACE tsA;
2290 # [ALTER TABLE] Case 44 )
2291 `````````````````````````````````````````````````````````
2292 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
2293 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
2294 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
2295 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
2296 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2297 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2298 SET GLOBAL table_encryption_privilege_check=false;
2299 # Run ALTER TABLE and check for errors/warnings
2300 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2301 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2302 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2303 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2304 SET GLOBAL table_encryption_privilege_check=false;
2305 # Verify the ENCRYPTION clause value.
2306 SHOW CREATE TABLE db1.t1;
2307 Table Create Table
2308 t1 CREATE TABLE `t1` (
2309 `f1` int DEFAULT NULL
2310 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2311 SHOW CREATE TABLE db1.t2;
2312 Table Create Table
2313 t2 CREATE TABLE `t2` (
2314 `f1` int DEFAULT NULL
2315 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2316 SHOW CREATE TABLE db1.t3;
2317 Table Create Table
2318 t3 CREATE TABLE `t3` (
2319 `f1` int DEFAULT NULL
2320 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2321 SHOW CREATE TABLE db1.t4;
2322 Table Create Table
2323 t4 CREATE TABLE `t4` (
2324 `f1` int DEFAULT NULL
2325 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2326 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2327 WHERE TABLE_SCHEMA='db1';
2328 TABLE_NAME CREATE_OPTIONS
2329 t1 ENCRYPTION='y'
2330 t2 ENCRYPTION='y'
2331 t3 ENCRYPTION='y'
2332 t4 ENCRYPTION='y'
2333 # Cleanup
2334 DROP DATABASE db1;
2335 DROP TABLESPACE tsA;
2336 `````````````````````````````````````````````````````````
2337 # table_encryption_privilege_check=ON and default per-db encryption OFF
2338 # [ALTER TABLE] Case 45 )
2339 `````````````````````````````````````````````````````````
2340 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
2341 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
2342 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
2343 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
2344 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2345 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2346 SET GLOBAL table_encryption_privilege_check=true;
2347 # Run ALTER TABLE and check for errors/warnings
2348 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2349 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2350 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2351 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2352 SET GLOBAL table_encryption_privilege_check=false;
2353 # Verify the ENCRYPTION clause value.
2354 SHOW CREATE TABLE db1.t1;
2355 Table Create Table
2356 t1 CREATE TABLE `t1` (
2357 `f1` int DEFAULT NULL
2358 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2359 SHOW CREATE TABLE db1.t2;
2360 Table Create Table
2361 t2 CREATE TABLE `t2` (
2362 `f1` int DEFAULT NULL
2363 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2364 SHOW CREATE TABLE db1.t3;
2365 Table Create Table
2366 t3 CREATE TABLE `t3` (
2367 `f1` int DEFAULT NULL
2368 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2369 SHOW CREATE TABLE db1.t4;
2370 Table Create Table
2371 t4 CREATE TABLE `t4` (
2372 `f1` int DEFAULT NULL
2373 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2374 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2375 WHERE TABLE_SCHEMA='db1';
2376 TABLE_NAME CREATE_OPTIONS
2377 t1
2378 t2
2379 t3
2380 t4
2381 # Cleanup
2382 DROP DATABASE db1;
2383 DROP TABLESPACE tsA;
2384 `````````````````````````````````````````````````````````
2385 # Request to create encrypted table to database with default
2386 # encryption=OFF is rejected.
2387 # [ALTER TABLE] Case 46 )
2388 `````````````````````````````````````````````````````````
2389 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
2390 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
2391 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
2392 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
2393 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2394 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2395 SET GLOBAL table_encryption_privilege_check=true;
2396 # Run ALTER TABLE and check for errors/warnings
2397 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2398 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
2399 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2400 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
2401 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2402 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
2403 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2404 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
2405 SET GLOBAL table_encryption_privilege_check=false;
2406 # Verify the ENCRYPTION clause value.
2407 SHOW CREATE TABLE db1.t1;
2408 Table Create Table
2409 t1 CREATE TABLE `t1` (
2410 `f1` int DEFAULT NULL
2411 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2412 SHOW CREATE TABLE db1.t2;
2413 Table Create Table
2414 t2 CREATE TABLE `t2` (
2415 `f1` int DEFAULT NULL
2416 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2417 SHOW CREATE TABLE db1.t3;
2418 Table Create Table
2419 t3 CREATE TABLE `t3` (
2420 `f1` int DEFAULT NULL
2421 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2422 SHOW CREATE TABLE db1.t4;
2423 Table Create Table
2424 t4 CREATE TABLE `t4` (
2425 `f1` int DEFAULT NULL
2426 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2427 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2428 WHERE TABLE_SCHEMA='db1';
2429 TABLE_NAME CREATE_OPTIONS
2430 t1
2431 t2
2432 t3
2433 t4
2434 # Cleanup
2435 DROP DATABASE db1;
2436 DROP TABLESPACE tsA;
2437 # [ALTER TABLE] Case 47 )
2438 `````````````````````````````````````````````````````````
2439 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
2440 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
2441 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
2442 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
2443 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2444 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2445 # Grant TABLE_ENCRYPTION_ADMIN if requested.
2446 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
2447 SET GLOBAL table_encryption_privilege_check=true;
2448 # Run ALTER TABLE and check for errors/warnings
2449 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2450 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2451 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2452 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2453 SET GLOBAL table_encryption_privilege_check=false;
2454 # Verify the ENCRYPTION clause value.
2455 SHOW CREATE TABLE db1.t1;
2456 Table Create Table
2457 t1 CREATE TABLE `t1` (
2458 `f1` int DEFAULT NULL
2459 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2460 SHOW CREATE TABLE db1.t2;
2461 Table Create Table
2462 t2 CREATE TABLE `t2` (
2463 `f1` int DEFAULT NULL
2464 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2465 SHOW CREATE TABLE db1.t3;
2466 Table Create Table
2467 t3 CREATE TABLE `t3` (
2468 `f1` int DEFAULT NULL
2469 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2470 SHOW CREATE TABLE db1.t4;
2471 Table Create Table
2472 t4 CREATE TABLE `t4` (
2473 `f1` int DEFAULT NULL
2474 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2475 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2476 WHERE TABLE_SCHEMA='db1';
2477 TABLE_NAME CREATE_OPTIONS
2478 t1 ENCRYPTION='y'
2479 t2 ENCRYPTION='y'
2480 t3 ENCRYPTION='y'
2481 t4 ENCRYPTION='y'
2482 # Cleanup
2483 # REVOKE TABLE_ENCRYPTION_ADMIN from user.
2484 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
2485 DROP DATABASE db1;
2486 DROP TABLESPACE tsA;
2487 # [ALTER TABLE] Case 48 )
2488 `````````````````````````````````````````````````````````
2489 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
2490 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
2491 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
2492 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
2493 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2494 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2495 SET GLOBAL table_encryption_privilege_check=true;
2496 # Run ALTER TABLE and check for errors/warnings
2497 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2498 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2499 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2500 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2501 SET GLOBAL table_encryption_privilege_check=false;
2502 # Verify the ENCRYPTION clause value.
2503 SHOW CREATE TABLE db1.t1;
2504 Table Create Table
2505 t1 CREATE TABLE `t1` (
2506 `f1` int DEFAULT NULL
2507 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2508 SHOW CREATE TABLE db1.t2;
2509 Table Create Table
2510 t2 CREATE TABLE `t2` (
2511 `f1` int DEFAULT NULL
2512 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2513 SHOW CREATE TABLE db1.t3;
2514 Table Create Table
2515 t3 CREATE TABLE `t3` (
2516 `f1` int DEFAULT NULL
2517 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2518 SHOW CREATE TABLE db1.t4;
2519 Table Create Table
2520 t4 CREATE TABLE `t4` (
2521 `f1` int DEFAULT NULL
2522 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2523 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2524 WHERE TABLE_SCHEMA='db1';
2525 TABLE_NAME CREATE_OPTIONS
2526 t1
2527 t2
2528 t3
2529 t4
2530 # Cleanup
2531 DROP DATABASE db1;
2532 DROP TABLESPACE tsA;
2533 # [ALTER TABLE] Case 49 )
2534 `````````````````````````````````````````````````````````
2535 CREATE DATABASE db1 DEFAULT ENCRYPTION='n';
2536 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
2537 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
2538 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
2539 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2540 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2541 SET GLOBAL table_encryption_privilege_check=true;
2542 # Run ALTER TABLE and check for errors/warnings
2543 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2544 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2545 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2546 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2547 SET GLOBAL table_encryption_privilege_check=false;
2548 # Verify the ENCRYPTION clause value.
2549 SHOW CREATE TABLE db1.t1;
2550 Table Create Table
2551 t1 CREATE TABLE `t1` (
2552 `f1` int DEFAULT NULL
2553 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2554 SHOW CREATE TABLE db1.t2;
2555 Table Create Table
2556 t2 CREATE TABLE `t2` (
2557 `f1` int DEFAULT NULL
2558 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2559 SHOW CREATE TABLE db1.t3;
2560 Table Create Table
2561 t3 CREATE TABLE `t3` (
2562 `f1` int DEFAULT NULL
2563 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2564 SHOW CREATE TABLE db1.t4;
2565 Table Create Table
2566 t4 CREATE TABLE `t4` (
2567 `f1` int DEFAULT NULL
2568 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2569 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2570 WHERE TABLE_SCHEMA='db1';
2571 TABLE_NAME CREATE_OPTIONS
2572 t1 ENCRYPTION='y'
2573 t2 ENCRYPTION='y'
2574 t3 ENCRYPTION='y'
2575 t4 ENCRYPTION='y'
2576 # Cleanup
2577 DROP DATABASE db1;
2578 DROP TABLESPACE tsA;
2579 `````````````````````````````````````````````````````````
2580 # table_encryption_privilege_check=ON and default per-db encryption ON
2581 # [ALTER TABLE] Case 50 )
2582 `````````````````````````````````````````````````````````
2583 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
2584 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
2585 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
2586 Warnings:
2587 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2588 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
2589 Warnings:
2590 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2591 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2592 Warnings:
2593 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2594 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2595 Warnings:
2596 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2597 SET GLOBAL table_encryption_privilege_check=true;
2598 # Run ALTER TABLE and check for errors/warnings
2599 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2600 Warnings:
2601 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2602 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2603 Warnings:
2604 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2605 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2606 Warnings:
2607 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2608 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2609 Warnings:
2610 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2611 SET GLOBAL table_encryption_privilege_check=false;
2612 # Verify the ENCRYPTION clause value.
2613 SHOW CREATE TABLE db1.t1;
2614 Table Create Table
2615 t1 CREATE TABLE `t1` (
2616 `f1` int DEFAULT NULL
2617 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2618 SHOW CREATE TABLE db1.t2;
2619 Table Create Table
2620 t2 CREATE TABLE `t2` (
2621 `f1` int DEFAULT NULL
2622 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2623 SHOW CREATE TABLE db1.t3;
2624 Table Create Table
2625 t3 CREATE TABLE `t3` (
2626 `f1` int DEFAULT NULL
2627 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2628 SHOW CREATE TABLE db1.t4;
2629 Table Create Table
2630 t4 CREATE TABLE `t4` (
2631 `f1` int DEFAULT NULL
2632 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2633 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2634 WHERE TABLE_SCHEMA='db1';
2635 TABLE_NAME CREATE_OPTIONS
2636 t1 ENCRYPTION='n'
2637 t2 ENCRYPTION='n'
2638 t3 ENCRYPTION='n'
2639 t4 ENCRYPTION='n'
2640 # Cleanup
2641 DROP DATABASE db1;
2642 DROP TABLESPACE tsA;
2643 # [ALTER TABLE] Case 51 )
2644 `````````````````````````````````````````````````````````
2645 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
2646 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='n';
2647 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';
2648 Warnings:
2649 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2650 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='n';
2651 Warnings:
2652 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2653 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2654 Warnings:
2655 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2656 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='n';
2657 Warnings:
2658 Warning 3824 Creating an unencrypted table in a database with default encryption enabled.
2659 SET GLOBAL table_encryption_privilege_check=true;
2660 # Run ALTER TABLE and check for errors/warnings
2661 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2662 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2663 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2664 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2665 SET GLOBAL table_encryption_privilege_check=false;
2666 # Verify the ENCRYPTION clause value.
2667 SHOW CREATE TABLE db1.t1;
2668 Table Create Table
2669 t1 CREATE TABLE `t1` (
2670 `f1` int DEFAULT NULL
2671 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2672 SHOW CREATE TABLE db1.t2;
2673 Table Create Table
2674 t2 CREATE TABLE `t2` (
2675 `f1` int DEFAULT NULL
2676 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2677 SHOW CREATE TABLE db1.t3;
2678 Table Create Table
2679 t3 CREATE TABLE `t3` (
2680 `f1` int DEFAULT NULL
2681 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2682 SHOW CREATE TABLE db1.t4;
2683 Table Create Table
2684 t4 CREATE TABLE `t4` (
2685 `f1` int DEFAULT NULL
2686 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2687 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2688 WHERE TABLE_SCHEMA='db1';
2689 TABLE_NAME CREATE_OPTIONS
2690 t1 ENCRYPTION='y'
2691 t2 ENCRYPTION='y'
2692 t3 ENCRYPTION='y'
2693 t4 ENCRYPTION='y'
2694 # Cleanup
2695 DROP DATABASE db1;
2696 DROP TABLESPACE tsA;
2697 `````````````````````````````````````````````````````````
2698 # Request to create unencrypted table to database with default
2699 # encryption=ON is rejected.
2700 # [ALTER TABLE] Case 52 )
2701 `````````````````````````````````````````````````````````
2702 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
2703 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
2704 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
2705 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
2706 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2707 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2708 SET GLOBAL table_encryption_privilege_check=true;
2709 # Run ALTER TABLE and check for errors/warnings
2710 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2711 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
2712 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2713 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
2714 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2715 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
2716 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2717 ERROR HY000: Table encryption differ from its database default encryption, and user doesn't have enough privilege.
2718 SET GLOBAL table_encryption_privilege_check=false;
2719 # Verify the ENCRYPTION clause value.
2720 SHOW CREATE TABLE db1.t1;
2721 Table Create Table
2722 t1 CREATE TABLE `t1` (
2723 `f1` int DEFAULT NULL
2724 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2725 SHOW CREATE TABLE db1.t2;
2726 Table Create Table
2727 t2 CREATE TABLE `t2` (
2728 `f1` int DEFAULT NULL
2729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2730 SHOW CREATE TABLE db1.t3;
2731 Table Create Table
2732 t3 CREATE TABLE `t3` (
2733 `f1` int DEFAULT NULL
2734 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
2735 SHOW CREATE TABLE db1.t4;
2736 Table Create Table
2737 t4 CREATE TABLE `t4` (
2738 `f1` int DEFAULT NULL
2739 ) /*!50100 TABLESPACE `tsA` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
2740 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2741 WHERE TABLE_SCHEMA='db1';
2742 TABLE_NAME CREATE_OPTIONS
2743 t1 ENCRYPTION='y'
2744 t2 ENCRYPTION='y'
2745 t3 ENCRYPTION='y'
2746 t4 ENCRYPTION='y'
2747 # Cleanup
2748 DROP DATABASE db1;
2749 DROP TABLESPACE tsA;
2750 # [ALTER TABLE] Case 53 )
2751 `````````````````````````````````````````````````````````
2752 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
2753 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
2754 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
2755 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
2756 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2757 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2758 # Grant TABLE_ENCRYPTION_ADMIN if requested.
2759 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
2760 SET GLOBAL table_encryption_privilege_check=true;
2761 # Run ALTER TABLE and check for errors/warnings
2762 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2763 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2764 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2765 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='n';
2766 SET GLOBAL table_encryption_privilege_check=false;
2767 # Verify the ENCRYPTION clause value.
2768 SHOW CREATE TABLE db1.t1;
2769 Table Create Table
2770 t1 CREATE TABLE `t1` (
2771 `f1` int DEFAULT NULL
2772 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2773 SHOW CREATE TABLE db1.t2;
2774 Table Create Table
2775 t2 CREATE TABLE `t2` (
2776 `f1` int DEFAULT NULL
2777 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2778 SHOW CREATE TABLE db1.t3;
2779 Table Create Table
2780 t3 CREATE TABLE `t3` (
2781 `f1` int DEFAULT NULL
2782 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2783 SHOW CREATE TABLE db1.t4;
2784 Table Create Table
2785 t4 CREATE TABLE `t4` (
2786 `f1` int DEFAULT NULL
2787 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='n'
2788 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2789 WHERE TABLE_SCHEMA='db1';
2790 TABLE_NAME CREATE_OPTIONS
2791 t1 ENCRYPTION='n'
2792 t2 ENCRYPTION='n'
2793 t3 ENCRYPTION='n'
2794 t4 ENCRYPTION='n'
2795 # Cleanup
2796 # REVOKE TABLE_ENCRYPTION_ADMIN from user.
2797 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
2798 DROP DATABASE db1;
2799 DROP TABLESPACE tsA;
2800 # [ALTER TABLE] Case 54 )
2801 `````````````````````````````````````````````````````````
2802 CREATE DATABASE db1 DEFAULT ENCRYPTION='y';
2803 CREATE TABLESPACE tsA ADD DATAFILE 'ts_a.ibd' ENCRYPTION='y';
2804 CREATE TABLE db1.t1 (f1 int) ENCRYPTION='y';
2805 CREATE TABLE db1.t2 (f1 int) ENCRYPTION='y';
2806 CREATE TABLE db1.t3 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2807 CREATE TABLE db1.t4 (f1 int) TABLESPACE=tsA ENCRYPTION='y';
2808 SET GLOBAL table_encryption_privilege_check=true;
2809 # Run ALTER TABLE and check for errors/warnings
2810 ALTER TABLE db1.t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2811 ALTER TABLE db1.t2 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2812 ALTER TABLE db1.t3 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2813 ALTER TABLE db1.t4 TABLESPACE=innodb_file_per_table ENCRYPTION='y';
2814 SET GLOBAL table_encryption_privilege_check=false;
2815 # Verify the ENCRYPTION clause value.
2816 SHOW CREATE TABLE db1.t1;
2817 Table Create Table
2818 t1 CREATE TABLE `t1` (
2819 `f1` int DEFAULT NULL
2820 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2821 SHOW CREATE TABLE db1.t2;
2822 Table Create Table
2823 t2 CREATE TABLE `t2` (
2824 `f1` int DEFAULT NULL
2825 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2826 SHOW CREATE TABLE db1.t3;
2827 Table Create Table
2828 t3 CREATE TABLE `t3` (
2829 `f1` int DEFAULT NULL
2830 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2831 SHOW CREATE TABLE db1.t4;
2832 Table Create Table
2833 t4 CREATE TABLE `t4` (
2834 `f1` int DEFAULT NULL
2835 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
2836 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
2837 WHERE TABLE_SCHEMA='db1';
2838 TABLE_NAME CREATE_OPTIONS
2839 t1 ENCRYPTION='y'
2840 t2 ENCRYPTION='y'
2841 t3 ENCRYPTION='y'
2842 t4 ENCRYPTION='y'
2843 # Cleanup
2844 DROP DATABASE db1;
2845 DROP TABLESPACE tsA;
2846 # Cleanup
2847 DROP USER u1@localhost;
2848 SET GLOBAL debug= '-d,skip_table_encryption_admin_check_for_set';