Debian Codegraph @

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

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