Debian Codegraph @

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

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