Debian Codegraph @

mysql-test/suite/innodb/t/table_encrypt_5.test

raw
1 #------------------------------------------------------------------------------
2 # InnoDB transparent tablespace data encryption
3 #
4 # This test create encrypt , non encrypt tables and try to access them after
5 # restarting with different combinitions such as
6 # - restart with same server option (acccess all tables)
7 # - restart without keyring options(encrypt table not accessible , rest are)
8 # - restart without keyring option but explicilty load plugin (access all)
9 # - restart with keyring option but using new key_file_data
10 # (old encrypt table not accessible , rest are. New encrypt
11 # table creation possible)
12 #
13 #------------------------------------------------------------------------------
14 # InnoDB transparent tablespace data encryption
15
16 --source include/no_valgrind_without_big.inc
17
18 # Suppress warnings
19 -- disable_query_log
20 call mtr.add_suppression("Can't generate new master key for tablespace encryption, please check the keyring is loaded.");
21 call mtr.add_suppression("Operating system error number");
22 call mtr.add_suppression("The error means the system cannot find the path specified");
23 call mtr.add_suppression("If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them");
24 call mtr.add_suppression("Could not find a valid tablespace file");
25 call mtr.add_suppression("Ignoring tablespace");
26 call mtr.add_suppression("Failed to find tablespace for table");
27 call mtr.add_suppression("Error while loading keyring content. The keyring might be malformed");
28 call mtr.add_suppression("\\[Warning\\] .* Please refer to .* for how to resolve the issue");
29 call mtr.add_suppression("\\[ERROR\\] .* Encryption can't find master key, please check the keyring is loaded");
30 call mtr.add_suppression("\\[ERROR\\] .* Encryption information in datafile: .* can't be decrypted, please confirm that keyring is loaded");
31 call mtr.add_suppression("\\[ERROR\\] .* Table tde_db/t_encrypt.* in the InnoDB data dictionary has tablespace id .*, but tablespace with that id or name does not exist");
32 call mtr.add_suppression("\\[ERROR\\] .* Plugin keyring_file reported: 'Could not create keyring directory");
33 call mtr.add_suppression("\\[ERROR\\] .* Plugin keyring_file reported: 'keyring_file initialization failure.");
34 call mtr.add_suppression("\\[ERROR\\] .* Plugin keyring_file reported: 'File .*keyring' not found .*");
35 call mtr.add_suppression("\\[ERROR\\] .* Tablespace [0-9]+, name 'tde_db.*t_encrypt.*', unable to open file '.*tde_db.*t_encrypt.*.ibd' - Data structure corruption");
36 call mtr.add_suppression("\\[ERROR\\] .* Check keyring fail, please check the keyring is loaded.");
37 -- enable_query_log
38
39 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
40
41
42 --echo # Starting server with keyring plugin
43 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
44 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
45 --replace_regex /\.dll/.so/
46 --source include/restart_mysqld.inc
47
48 #------------------------------------------------------------------------------
49 # Initial setup for test which create encrypt and non encrypt tables
50 --disable_warnings
51 DROP DATABASE IF EXISTS tde_db;
52 DROP TABLE IF EXISTS tde_db. t_encrypt;
53 CREATE DATABASE tde_db;
54 USE tde_db;
55 --enable_warnings
56 SET GLOBAL innodb_file_per_table = 1;
57 SELECT @@innodb_file_per_table;
58
59 DELIMITER |;
60 CREATE PROCEDURE tde_db.init_setup()
61 begin
62 /* Create encrypt table with encryption */
63 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
64 c3 CHAR(255) Default 'No text',
65 c4 JSON ,
66 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
67 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
68 c7 POINT NOT NULL SRID 0,
69 spatial INDEX idx2 (c7)
70 ) ENCRYPTION="Y" ENGINE = InnoDB;
71
72 /* Create NON encrypt table with encryption */
73 CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
74 c3 CHAR(255) Default 'No text',
75 c4 JSON ,
76 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
77 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
78 c7 POINT NOT NULL SRID 0,
79 spatial INDEX idx2 (c7)
80 ) ENGINE = InnoDB;
81
82 /* insert into encrypt table */
83 INSERT INTO tde_db.t_encrypt(c3,c4,c7) VALUES(CONCAT(REPEAT('a',200),LPAD(CAST(1 AS CHAR),4,'0')),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
84 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
85 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
86 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
87 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
88 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
89 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
90 SELECT '/* select tde_db.t_encrypt */';
91 SELECT COUNT(*) FROM tde_db.t_encrypt;
92 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
93
94 /* insert into non encrypt table */
95 INSERT INTO tde_db.t_non_encrypt(c2,c3,c4,c7) SELECT c2,c3,c4,c7 FROM tde_db.t_encrypt;
96 SELECT '/* select tde_db.t_non_encrypt */';
97 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
98 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
99
100 ALTER INSTANCE ROTATE INNODB MASTER KEY;
101
102 CREATE TABLE tde_db.t_encrypt_2(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
103 c3 CHAR(255) Default 'No text',
104 c4 JSON ,
105 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
106 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
107 c7 POINT NOT NULL SRID 0,
108 spatial INDEX idx2 (c7)
109 ) ENCRYPTION="Y" ENGINE = InnoDB;
110 CREATE TABLE tde_db.t_non_encrypt_2(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
111 c3 CHAR(255) Default 'No text',
112 c4 JSON ,
113 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
114 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
115 c7 POINT NOT NULL SRID 0,
116 spatial INDEX idx2 (c7)
117 ) ENGINE = InnoDB;
118 /* insert into encrypt table 2 */
119 INSERT INTO tde_db.t_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
120 SELECT '/* select tde_db.t_encrypt_2 */';
121 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
122 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
123 /* insert into NON encrypt table 2 */
124 INSERT INTO tde_db.t_non_encrypt_2(c2,c3,c4,c7) SELECT c2,c3,c4,c7 FROM tde_db.t_encrypt;
125 SELECT '/* select tde_db.t_non_encrypt_2 */';
126 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
127 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_2 LIMIT 10;
128 end|
129 DELIMITER ;|
130
131 --echo #-----------------------------------------------------------------------
132 --echo # init tables
133 call tde_db.init_setup();
134 --echo # plugin already installed error
135 --replace_regex /\.dll/.so/
136 --error 1125
137 eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN';
138 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
139 SELECT @@global.keyring_file_data;
140 --echo # Uninstall is possible when server started with --early-plugin-load
141 UNINSTALL PLUGIN keyring_file;
142 --echo # variable not accessible after uninstall
143 --error 1193
144 SELECT @@global.keyring_file_data;
145
146 --echo # Select non encrypt table : Pass
147 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
148 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
149 --echo # Select encrypt table : No Error (after uninstall plugin -master key is cached)
150 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
151 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
152 SELECT COUNT(*) FROM tde_db.t_encrypt;
153 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
154
155 --echo # Error on "ALTER INSTANCE ..." after UNINSTALL PLUGIN
156 --error ER_CANNOT_FIND_KEY_IN_KEYRING
157 ALTER INSTANCE ROTATE INNODB MASTER KEY;
158
159
160 --echo # new encrypt table creation is blocked after uninstall
161 --error ER_CANNOT_FIND_KEY_IN_KEYRING
162 CREATE TABLE tde_db.t_encrypt_3(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
163 c3 CHAR(255) Default 'No text',
164 c4 JSON ,
165 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
166 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
167 c7 POINT NOT NULL SRID 0,
168 spatial INDEX idx2 (c7)
169 ) ENCRYPTION="Y" ENGINE = InnoDB;
170 --echo # new non encrypt table
171 CREATE TABLE tde_db.t_non_encrypt_3(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
172 c3 CHAR(255) Default 'No text',
173 c4 JSON ,
174 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
175 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
176 c7 POINT NOT NULL SRID 0,
177 spatial INDEX idx2 (c7)
178 ) ENGINE = InnoDB;
179
180 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 ;
181 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 , tde_db.t_non_encrypt_3;
182 SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
183
184 --echo #-----------------------------------------------------------------------
185 --echo # Test 1 : Restart with same keyring option , all tables accesible
186 --echo # restart with --early-plugin-load
187 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
188 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
189 --source include/restart_mysqld.inc
190 SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
191 --echo # init tables
192 call tde_db.init_setup();
193 --echo # restart with same --early-plugin-load and keyring_file_data option
194 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
195 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
196 --source include/restart_mysqld.inc
197 # All tables accessible
198 SELECT COUNT(*) FROM tde_db.t_encrypt;
199 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
200 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
201 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
202 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
203 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
204 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
205 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_2 LIMIT 10;
206
207 --echo # insert into old encrypt tables
208 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
209 INSERT INTO tde_db.t_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
210 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
211 --echo # insert into old non encrypt tables
212 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
213 INSERT INTO tde_db.t_non_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
214 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
215 --echo # update into old encrypt tables
216 UPDATE tde_db.t_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
217 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
218 --echo # update into old non encrypt tables
219 UPDATE tde_db.t_non_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
220 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
221 --echo # delete into old encrypt tables
222 DELETE FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
223 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
224 --echo # delete into old non encrypt tables
225 DELETE FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
226 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
227
228 --echo # new table
229 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
230 c3 CHAR(255) Default 'No text',
231 c4 JSON ,
232 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
233 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
234 c7 POINT NOT NULL SRID 0,
235 spatial INDEX idx2 (c7)
236 ) ENCRYPTION="Y" ENGINE = InnoDB;
237 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
238 c3 CHAR(255) Default 'No text',
239 c4 JSON ,
240 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
241 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
242 c7 POINT NOT NULL SRID 0,
243 spatial INDEX idx2 (c7)
244 ) ENGINE = InnoDB;
245
246
247 INSERT INTO tde_db.t_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
248 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
249 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
250 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_4 LIMIT 10;
251 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
252 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_4 LIMIT 10;
253
254 ALTER INSTANCE ROTATE INNODB MASTER KEY;
255
256 SELECT COUNT(*) FROM tde_db.t_encrypt;
257 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
258 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
259 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
260 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
261 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
262
263 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 , tde_db.t_encrypt_4;
264 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 , tde_db.t_non_encrypt_4;
265
266
267 --echo #-----------------------------------------------------------------------
268 --echo # Test 2 : Restart without keyring option - old encrypt table not
269 --echo # accessible but rest are.
270 --echo # restart with --early-plugin-load to load initial data
271 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
272 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
273 --source include/restart_mysqld.inc
274 SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
275 --echo # init tables
276 call tde_db.init_setup();
277 --echo # restart without --early-plugin-load and keyring_file_data
278 let $restart_parameters = restart: ;
279 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
280 --source include/restart_mysqld.inc
281
282 --echo # encrypt table not accessible
283 --error ER_CANNOT_FIND_KEY_IN_KEYRING
284 SELECT COUNT(*) FROM tde_db.t_encrypt;
285 --error ER_CANNOT_FIND_KEY_IN_KEYRING
286 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
287 --echo # NON encrypt table are accessible
288 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
289 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
290
291 --echo # encrypt table not possible
292 --error ER_CANNOT_FIND_KEY_IN_KEYRING
293 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
294 c3 CHAR(255) Default 'No text',
295 c4 JSON ,
296 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
297 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
298 c7 POINT NOT NULL SRID 0,
299 spatial INDEX idx2 (c7)
300 ) ENCRYPTION="Y" ENGINE = InnoDB;
301
302 --echo # non encrypt table possible
303 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
304 c3 CHAR(255) Default 'No text',
305 c4 JSON ,
306 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
307 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
308 c7 POINT NOT NULL SRID 0,
309 spatial INDEX idx2 (c7)
310 ) ENGINE = InnoDB;
311
312 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_non_encrypt;
313 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
314
315 --error ER_CANNOT_FIND_KEY_IN_KEYRING
316 ALTER INSTANCE ROTATE INNODB MASTER KEY;
317
318 --echo # encrypt table not accessible
319 --error ER_CANNOT_FIND_KEY_IN_KEYRING
320 SELECT COUNT(*) FROM tde_db.t_encrypt;
321 --error ER_CANNOT_FIND_KEY_IN_KEYRING
322 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
323
324 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2;
325 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 ,tde_db.t_non_encrypt_4;
326
327 DROP DATABASE tde_db;
328
329
330
331 #------------------------------------------------------------------------------
332 --echo # Initial setup
333 --echo # Starting server with keyring plugin
334 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
335 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
336 --replace_regex /\.dll/.so/
337 --source include/restart_mysqld.inc
338
339 --disable_warnings
340 DROP DATABASE IF EXISTS tde_db;
341 CREATE DATABASE tde_db;
342 USE tde_db;
343 --enable_warnings
344 SET GLOBAL innodb_file_per_table = 1;
345 SELECT @@innodb_file_per_table;
346
347 DELIMITER |;
348 CREATE PROCEDURE tde_db.init_setup()
349 begin
350 /* Create encrypt table with encryption */
351 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
352 c3 CHAR(255) Default 'No text',
353 c4 JSON ,
354 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
355 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
356 c7 POINT NOT NULL SRID 0,
357 spatial INDEX idx2 (c7)
358 ) ENCRYPTION="Y" ENGINE = InnoDB;
359
360 /* Create NON encrypt table with encryption */
361 CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
362 c3 CHAR(255) Default 'No text',
363 c4 JSON ,
364 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
365 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
366 c7 POINT NOT NULL SRID 0,
367 spatial INDEX idx2 (c7)
368 ) ENGINE = InnoDB;
369
370 /* insert into encrypt table */
371 INSERT INTO tde_db.t_encrypt(c3,c4,c7) VALUES(CONCAT(REPEAT('a',200),LPAD(CAST(1 AS CHAR),4,'0')),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
372 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
373 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
374 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
375 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
376 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
377 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
378 SELECT '/* select tde_db.t_encrypt */';
379 SELECT COUNT(*) FROM tde_db.t_encrypt;
380 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
381
382 /* insert into non encrypt table */
383 INSERT INTO tde_db.t_non_encrypt(c2,c3,c4,c7) SELECT c2,c3,c4,c7 FROM tde_db.t_encrypt;
384 SELECT '/* select tde_db.t_non_encrypt */';
385 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
386 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
387
388 ALTER INSTANCE ROTATE INNODB MASTER KEY;
389
390 CREATE TABLE tde_db.t_encrypt_2(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
391 c3 CHAR(255) Default 'No text',
392 c4 JSON ,
393 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
394 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
395 c7 POINT NOT NULL SRID 0,
396 spatial INDEX idx2 (c7)
397 ) ENCRYPTION="Y" ENGINE = InnoDB;
398 CREATE TABLE tde_db.t_non_encrypt_2(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
399 c3 CHAR(255) Default 'No text',
400 c4 JSON ,
401 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
402 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
403 c7 POINT NOT NULL SRID 0,
404 spatial INDEX idx2 (c7)
405 ) ENGINE = InnoDB;
406 /* insert into encrypt table 2 */
407 INSERT INTO tde_db.t_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
408 SELECT '/* select tde_db.t_encrypt_2 */';
409 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
410 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
411 /* insert into NON encrypt table 2 */
412 INSERT INTO tde_db.t_non_encrypt_2(c2,c3,c4,c7) SELECT c2,c3,c4,c7 FROM tde_db.t_encrypt;
413 SELECT '/* select tde_db.t_non_encrypt_2 */';
414 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
415 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_2 LIMIT 10;
416 end|
417 DELIMITER ;|
418
419 #------------------------------------------------------------------------------
420 --echo # Test 3 : Restart without keyring option but load plugin using command
421 --echo # Access all tables
422 --echo #-----------------------------------------------------------------------
423 SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
424 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
425 SELECT @@global.keyring_file_data;
426 --echo # init tables
427 call tde_db.init_setup();
428 --echo # restart without --early-plugin-load (still need to provide with
429 --echo # keyring_file_data otherwise it would point to default location which might be
430 --echo # non-writable to mtr). Instead explicitly install plugin to access old table
431 let $restart_parameters = restart: --loose-keyring_file_data=$MYSQL_TMP_DIR/mydummy_keyring;
432 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
433 --source include/restart_mysqld.inc
434
435 --echo # Install keyring plugin
436 --replace_regex /\.dll/.so/
437 --disable_warnings
438 eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN';
439 --enable_warnings
440 --echo # Set keyring_file_data as old file so as to access old tables
441 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
442 eval SET @@global.keyring_file_data='$MYSQL_TMP_DIR/mysecret_keyring';
443 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
444 SELECT @@global.keyring_file_data;
445
446 # All tables accessible
447 SELECT COUNT(*) FROM tde_db.t_encrypt;
448 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
449 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
450 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
451 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
452 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
453 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
454 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_2 LIMIT 10;
455
456 --echo # insert into old encrypt tables
457 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
458 INSERT INTO tde_db.t_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
459 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
460 --echo # insert into old non encrypt tables
461 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
462 INSERT INTO tde_db.t_non_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
463 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
464 --echo # update into old encrypt tables
465 UPDATE tde_db.t_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
466 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
467 --echo # update into old non encrypt tables
468 UPDATE tde_db.t_non_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
469 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
470 --echo # delete into old encrypt tables
471 DELETE FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
472 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
473 --echo # delete into old non encrypt tables
474 DELETE FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
475 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
476
477 --echo # new table
478 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
479 c3 CHAR(255) Default 'No text',
480 c4 JSON ,
481 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
482 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
483 c7 POINT NOT NULL SRID 0,
484 spatial INDEX idx2 (c7)
485 ) ENCRYPTION="Y" ENGINE = InnoDB;
486 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
487 c3 CHAR(255) Default 'No text',
488 c4 JSON ,
489 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
490 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
491 c7 POINT NOT NULL SRID 0,
492 spatial INDEX idx2 (c7)
493 ) ENGINE = InnoDB;
494
495
496 INSERT INTO tde_db.t_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
497 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
498 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
499 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_4 LIMIT 10;
500 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
501 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_4 LIMIT 10;
502
503 ALTER INSTANCE ROTATE INNODB MASTER KEY;
504
505 SELECT COUNT(*) FROM tde_db.t_encrypt;
506 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
507 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
508 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
509 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
510 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
511
512 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 , tde_db.t_encrypt_4;
513 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 , tde_db.t_non_encrypt_4;
514
515 UNINSTALL PLUGIN keyring_file;
516
517
518 --error ER_CANNOT_FIND_KEY_IN_KEYRING
519 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
520 c3 CHAR(255) Default 'No text',
521 c4 JSON ,
522 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
523 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
524 c7 POINT NOT NULL SRID 0,
525 spatial INDEX idx2 (c7)
526 ) ENCRYPTION="Y" ENGINE = InnoDB;
527
528 --error ER_CANNOT_FIND_KEY_IN_KEYRING
529 ALTER INSTANCE ROTATE INNODB MASTER KEY;
530
531 --echo #-----------------------------------------------------------------------
532 --echo # Test 4 : Restart with new keyring_data_file
533 --echo # Old encrypt table not accessible , non encrypt tables accessible
534 --echo # And creation of new encrypt,non encrypt table is also posible
535 #------------------------------------------------------------------------------
536 --echo # restart with --early-plugin-load to load initial data
537 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
538 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
539 --source include/restart_mysqld.inc
540 SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
541 --echo # init tables
542 call tde_db.init_setup();
543 --echo # restart with with different keyring_file_data file
544 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring_new $KEYRING_PLUGIN_OPT;
545 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
546 --source include/restart_mysqld.inc
547
548 --echo # encrypt table not accessible
549 --error ER_CANNOT_FIND_KEY_IN_KEYRING
550 SELECT COUNT(*) FROM tde_db.t_encrypt;
551 --error ER_CANNOT_FIND_KEY_IN_KEYRING
552 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
553 --echo # NON encrypt table are accessible
554 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
555 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
556
557 --echo # new encrypt table is possible
558 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
559 c3 CHAR(255) Default 'No text',
560 c4 JSON ,
561 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
562 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
563 c7 POINT NOT NULL SRID 0,
564 spatial INDEX idx2 (c7)
565 ) ENCRYPTION="Y" ENGINE = InnoDB;
566
567 --echo # non encrypt table possible
568 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
569 c3 CHAR(255) Default 'No text',
570 c4 JSON ,
571 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
572 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
573 c7 POINT NOT NULL SRID 0,
574 spatial INDEX idx2 (c7)
575 ) ENGINE = InnoDB;
576
577 INSERT INTO tde_db.t_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_non_encrypt;
578 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
579 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_non_encrypt;
580 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
581
582 ALTER INSTANCE ROTATE INNODB MASTER KEY;
583
584 --echo # old encrypt table not accessible
585 --error ER_CANNOT_FIND_KEY_IN_KEYRING, ER_NO_SUCH_TABLE
586 SELECT COUNT(*) FROM tde_db.t_encrypt;
587 --error ER_CANNOT_FIND_KEY_IN_KEYRING, ER_NO_SUCH_TABLE
588 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
589 --echo # NON encrypt old table are accessible
590 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
591 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
592 --echo # new encrypt table accessible
593 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
594 --echo # new NON encrypt table accessible
595 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
596
597
598
599 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 ,tde_db.t_encrypt_4;
600 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 ,tde_db.t_non_encrypt_4;
601
602 DROP DATABASE tde_db;
603
604 --echo # Starting server without keyring
605 let $restart_parameters = restart: ;
606 --source include/restart_mysqld.inc
607
608 # Cleanup
609 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
610 --remove_file $MYSQL_TMP_DIR/mydummy_keyring
611 --remove_file $MYSQL_TMP_DIR/mysecret_keyring
612 --remove_file $MYSQL_TMP_DIR/mysecret_keyring.backup
613 --remove_file $MYSQL_TMP_DIR/mysecret_keyring_new