Debian Codegraph @

mysql-test/suite/component_keyring_file/r/alter_table.result

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