Debian Codegraph @

mysql-test/suite/encryption/t/create_table.test

raw
1 --echo #
2 --echo # WL#12261 Control (enforce and disable) table encryption
3 --echo #
4 --source include/have_debug.inc
5
6 --echo # Pre-define user u1, which is used in different tests below.
7 CREATE USER u1@localhost;
8 GRANT ALL ON db1.* TO u1@localhost;
9 GRANT CREATE TABLESPACE, PROCESS, SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
10 SET GLOBAL debug= '+d,skip_table_encryption_admin_check_for_set';
11 connect (con1, localhost, u1);
12
13
14 --echo # This test run CREATE TABLE in different configurations,
15 --echo #
16 --echo # - Setting table_encryption_privilege_check to true/false.
17 --echo # - Setting default_table_encryption to true/false.
18 --echo # - With and without ENCRYPTION clause.
19 --echo # - With and without user holding TABLE_ENCRYPTION_ADMIN privilege.
20 --echo # - Test SHOW CREATE TABLE
21 --echo # - Test INFORMATION_SCHEMA.TABLES.CREATE_OPTIONS
22 --echo # - Check for warnings generated.
23 --echo #
24
25 # Initialization
26 --let caseno=0
27 --let use_tablespace=false
28 --let expected_error=0
29 --let expected_error2=0
30
31 --echo `````````````````````````````````````````````````````````
32 --echo # Test CREATE TABLE on DATABASE with ENCRYPTION 'y/n'
33 --echo # and with different values for system variable
34 --echo # 'table_encryption_privilege_check' and 'default_table_encryption'
35
36 --echo `````````````````````````````````````````````````````````
37 --echo # CREATE TABLE without ENCRYPTION clause
38
39 --let explicit_encryption_clause=false
40
41 --let privilege_check=false
42 --let database_encryption='n'
43 --source ./create_table.inc
44 --let database_encryption='y'
45 --source ./create_table.inc
46
47 --let privilege_check=true
48 --let database_encryption='n'
49 --source ./create_table.inc
50 --let database_encryption='y'
51 --source ./create_table.inc
52
53 --echo `````````````````````````````````````````````````````````
54 --echo # CREATE TABLE with ENCRYPTION clause
55
56 --let explicit_encryption_clause=true
57
58 --let privilege_check=false
59
60 --let database_encryption='n'
61 --let table_encryption='y'
62 --source ./create_table.inc
63 --let table_encryption='n'
64 --source ./create_table.inc
65
66 --let database_encryption='y'
67 --let table_encryption='y'
68 --source ./create_table.inc
69 --let table_encryption='n'
70 --source ./create_table.inc
71
72 --let privilege_check=true
73
74 --let database_encryption='n'
75 --let table_encryption='y'
76 --echo `````````````````````````````````````````````````````````
77 --echo # Fails as there is mismatch between table and database encryption.
78 --let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION
79 --source ./create_table.inc
80 --let expected_error=0
81 --let table_encryption='n'
82 --source ./create_table.inc
83
84 --let database_encryption='y'
85 --let table_encryption='y'
86 --source ./create_table.inc
87 --echo `````````````````````````````````````````````````````````
88 --echo # Fails as there is mismatch between table and database encryption.
89 --let table_encryption='n'
90 --let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION
91 --source ./create_table.inc
92
93 --echo `````````````````````````````````````````````````````````
94 --echo # Check with invalid ENCRYPTION value
95 --let privilege_check=false
96 --let database_encryption='n'
97 --let expected_error=ER_INVALID_ENCRYPTION_OPTION
98 --let table_encryption='k'
99 --source ./create_table.inc
100
101
102 --echo `````````````````````````````````````````````````````````
103 --echo # With innodb_file_per_table=off and
104 --echo # Test CREATE TABLE without ENCRYPTION clause.
105 SET GLOBAL innodb_file_per_table = OFF;
106 --let expected_error=0
107 --let use_tablespace=false
108 --let explicit_encryption_clause=false
109
110 --let privilege_check=false
111 --let database_encryption='n'
112 --source ./create_table.inc
113
114 --echo `````````````````````````````````````````````````````````
115 --echo # Fails because we cannot created a encrypted table in system tablespace.
116 --let database_encryption='y'
117 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
118 --source ./create_table.inc
119 --let expected_error=0
120 --let privilege_check=true
121 --let database_encryption='n'
122 --source ./create_table.inc
123
124 --echo `````````````````````````````````````````````````````````
125 --echo # Fails because we cannot created a encrypted table in system tablespace.
126 --let database_encryption='y'
127 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
128 --source ./create_table.inc
129 --let expected_error=0
130
131 --echo `````````````````````````````````````````````````````````
132 --echo # With explicit ENCRYPTION clause
133 --let explicit_encryption_clause=true
134
135 --let privilege_check=false
136
137 --echo `````````````````````````````````````````````````````````
138 --echo # Fails because we cannot created a encrypted table in system tablespace.
139 --let database_encryption='n'
140 --let table_encryption='y'
141 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
142
143 --source ./create_table.inc
144 --let expected_error=0
145 --let table_encryption='n'
146 --source ./create_table.inc
147
148 --echo `````````````````````````````````````````````````````````
149 --echo # Fails because we cannot created a encrypted table in system tablespace.
150 --let database_encryption='y'
151 --let table_encryption='y'
152 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
153
154 --source ./create_table.inc
155 --let expected_error=0
156 --let table_encryption='n'
157 --source ./create_table.inc
158
159 --let privilege_check=true
160
161 --echo `````````````````````````````````````````````````````````
162 --echo # Fails because we cannot created a encrypted table in system tablespace.
163 --let database_encryption='n'
164 --let table_encryption='y'
165 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
166 --source ./create_table.inc
167
168 --let expected_error=0
169 --let table_encryption='n'
170 --source ./create_table.inc
171
172 --echo `````````````````````````````````````````````````````````
173 --echo # Fails because we cannot created a encrypted table in system tablespace.
174 --let database_encryption='y'
175 --let table_encryption='y'
176 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
177 --source ./create_table.inc
178
179 --let expected_error=0
180 --let table_encryption='n'
181 --let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION
182 --source ./create_table.inc
183
184 --echo `````````````````````````````````````````````````````````
185 --echo # With innodb_file_per_table = ON.
186 --echo # Test CREATE TABLE with explicit TABLESPACE=innodb_file_per_table
187 SET GLOBAL innodb_file_per_table = ON;
188 --let use_tablespace=true
189 --let tablespace_name=innodb_file_per_table
190
191 --echo `````````````````````````````````````````````````````````
192 --echo # Without ENCRYPTION clause
193 --let explicit_encryption_clause=false
194 --let expected_error=0
195
196 --let privilege_check=false
197 --let database_encryption='n'
198 --source ./create_table.inc
199 --let database_encryption='y'
200 --source ./create_table.inc
201
202 --let privilege_check=true
203 --let database_encryption='n'
204 --source ./create_table.inc
205 --let database_encryption='y'
206 --source ./create_table.inc
207
208 --echo `````````````````````````````````````````````````````````
209 --echo # With ENCRYPTION clause
210 --let explicit_encryption_clause=true
211
212 --let privilege_check=false
213
214 --let database_encryption='n'
215 --let table_encryption='y'
216 --source ./create_table.inc
217 --let table_encryption='n'
218 --source ./create_table.inc
219
220 --let database_encryption='y'
221 --let table_encryption='y'
222 --source ./create_table.inc
223 --let table_encryption='n'
224 --source ./create_table.inc
225
226 --let privilege_check=true
227
228 --echo `````````````````````````````````````````````````````````
229 --echo # Fails as there is mismatch between table and database encryption.
230 --let database_encryption='n'
231 --let table_encryption='y'
232 --let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION
233 --source ./create_table.inc
234 --let expected_error=0
235 --let table_encryption='n'
236 --source ./create_table.inc
237
238 --let database_encryption='y'
239 --let table_encryption='y'
240 --source ./create_table.inc
241 --let table_encryption='n'
242 --echo `````````````````````````````````````````````````````````
243 --echo # Fails as there is mismatch between table and database encryption.
244 --let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION
245 --source ./create_table.inc
246
247
248 --echo `````````````````````````````````````````````````````````
249 --echo # Test CREATE TABLE with TABLESPACE=innodb_system
250 --let use_tablespace=true
251 --let tablespace_name=innodb_system
252
253 --echo `````````````````````````````````````````````````````````
254 --echo # Without ENCRYPTION clause
255 --let explicit_encryption_clause=false
256
257 --let expected_error=0
258
259 --let privilege_check=false
260 --let database_encryption='n'
261 --source ./create_table.inc
262
263 --echo `````````````````````````````````````````````````````````
264 --echo # Fails because we cannot created a encrypted table in system tablespace.
265 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
266 --let database_encryption='y'
267 --source ./create_table.inc
268
269 --let expected_error=0
270 --let privilege_check=true
271 --let database_encryption='n'
272 --source ./create_table.inc
273
274 --echo `````````````````````````````````````````````````````````
275 --echo # Fails because we cannot created a encrypted table in system tablespace.
276 --let database_encryption='y'
277 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
278 --source ./create_table.inc
279 --let expected_error=0
280
281 --echo `````````````````````````````````````````````````````````
282 --echo # With ENCRYPTION clause
283 --let explicit_encryption_clause=true
284
285 --let privilege_check=false
286
287 --echo `````````````````````````````````````````````````````````
288 --echo # Fails because we cannot created a encrypted table in system tablespace.
289 --let database_encryption='n'
290 --let table_encryption='y'
291 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
292 --source ./create_table.inc
293
294 --let expected_error=0
295 --let table_encryption='n'
296 --source ./create_table.inc
297
298 --echo `````````````````````````````````````````````````````````
299 --echo # Fails because we cannot created a encrypted table in system tablespace.
300 --let database_encryption='y'
301 --let table_encryption='y'
302 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
303 --source ./create_table.inc
304
305 --let expected_error=0
306 --let table_encryption='n'
307 --source ./create_table.inc
308
309 --let privilege_check=true
310
311 --echo `````````````````````````````````````````````````````````
312 --echo # Fails as there is mismatch between table and database encryption.
313 --echo # The statement would fail even with user owning TABLE_ENCRYPTION_ADMIN
314 --echo # with ER_ILLEGAL_HA_CREATE_OPTION because one cannot create
315 --echo # encrypted table in system tablespace.
316 --let database_encryption='n'
317 --let table_encryption='y'
318 --let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION
319 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
320 --source ./create_table.inc
321 --let expected_error=0
322 --let expected_error2=0
323 --let table_encryption='n'
324 --source ./create_table.inc
325
326 --echo `````````````````````````````````````````````````````````
327 --echo # Fails because we cannot created a encrypted table in system tablespace.
328 --let database_encryption='y'
329 --let table_encryption='y'
330 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
331 --source ./create_table.inc
332
333 --echo `````````````````````````````````````````````````````````
334 --echo # Fails as there is mismatch between table and database encryption.
335 --let expected_error=0
336 --let table_encryption='n'
337 --let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION
338 --source ./create_table.inc
339
340
341 --echo `````````````````````````````````````````````````````````
342 --echo # Test CREATE TABLE with general TABLESPACE
343
344 --echo `````````````````````````````````````````````````````````
345 --echo # With unencrypted general tablespace and without ENCRYPTION clause.
346 --let use_tablespace=true
347 --let tablespace_name=ts1
348 --let table_encryption='n'
349 --let explicit_encryption_clause=false
350 --let expected_error=0
351
352 --let privilege_check=false
353 --let database_encryption='n'
354 --source ./create_table.inc
355
356 --echo `````````````````````````````````````````````````````````
357 --echo # Fails as there is mismatch between table and database encryption.
358 --echo # The table ENCRYPTION clause is inherited from database and this
359 --echo # does not match tablespace encryption type.
360 --let database_encryption='y'
361 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
362 --source ./create_table.inc
363
364 --let expected_error=0
365 --let privilege_check=true
366 --let database_encryption='n'
367 --source ./create_table.inc
368
369 --echo `````````````````````````````````````````````````````````
370 --echo # Fails as there is mismatch between table and database encryption.
371 --echo # The table ENCRYPTION clause is inherited from database and this
372 --echo # does not match tablespace encryption type.
373 --let database_encryption='y'
374 --let expected_error=ER_INVALID_ENCRYPTION_REQUEST
375 --source ./create_table.inc
376 --let expected_error=0
377
378 --echo `````````````````````````````````````````````````````````
379 --echo # With ENCRYPTION clause
380 --let explicit_encryption_clause=true
381
382 --let privilege_check=false
383
384 --let database_encryption='n'
385 --let table_encryption='y'
386 --source ./create_table.inc
387 --let table_encryption='n'
388 --source ./create_table.inc
389
390 --let database_encryption='y'
391 --let table_encryption='y'
392 --source ./create_table.inc
393 --let table_encryption='n'
394 --source ./create_table.inc
395
396 --let privilege_check=true
397
398 --echo `````````````````````````````````````````````````````````
399 --echo # Fails as there is mismatch between table and database encryption.
400 --let database_encryption='n'
401 --let table_encryption='y'
402 --let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION
403 --source ./create_table.inc
404
405 --let expected_error=0
406 --let table_encryption='n'
407 --source ./create_table.inc
408
409 --let database_encryption='y'
410 --let table_encryption='y'
411 --source ./create_table.inc
412
413 --echo `````````````````````````````````````````````````````````
414 --echo # Fails as there is mismatch between table and database encryption.
415 --let table_encryption='n'
416 --let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION
417 --source ./create_table.inc
418
419
420 --echo # Cleanup
421 disconnect con1;
422 connection default;
423 DROP USER u1@localhost;
424 SET GLOBAL debug= '-d,skip_table_encryption_admin_check_for_set';