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.
7CREATE USER u1@localhost;
8GRANT ALL ON db1.* TO u1@localhost;
9GRANT CREATE TABLESPACE, PROCESS, SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
10SET GLOBAL debug= '+d,skip_table_encryption_admin_check_for_set';
11connect (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.
105SET 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
187SET 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
421disconnect con1;
422connection default;
423DROP USER u1@localhost;
424SET GLOBAL debug= '-d,skip_table_encryption_admin_check_for_set';
425