Debian Codegraph @

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

raw
1 #------------------------------------------------------------------------------
2 # InnoDB transparent tablespace data encryption for general tablespace
3 #
4 # This test create tables in encrypted/non-encrypted genral tablespace and
5 # try to access them after 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 --source include/no_valgrind_without_big.inc
15 # Waiting time when (re)starting the server
16 --let $explicit_default_wait_counter=10000
17
18 # Suppress warnings
19 -- disable_query_log
20 call mtr.add_suppression("\\[ERROR\\].*ibd can't be decrypted, please confirm that keyring is loaded");
21 call mtr.add_suppression("\\[ERROR\\].*Plugin keyring_file reported: 'keyring_file initialization failure.");
22 call mtr.add_suppression("\\[ERROR\\].*Plugin keyring_file reported: 'File .*keyring' not found .*");
23 call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Encryption can't find master key, please check the keyring is loaded.");
24 call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Check keyring fail, please check the keyring is loaded.");
25 -- enable_query_log
26
27 --echo #########################################################################
28 --echo # RESTART 1 : WITH KEYRING PLUGIN
29 --echo #########################################################################
30 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
31 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
32 --replace_regex /\.dll/.so/
33 --source include/restart_mysqld.inc
34
35 --echo #########
36 --echo # SETUP #
37 --echo #########
38
39 --disable_warnings
40 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION ='Y' ENGINE=InnoDB;
41 CREATE TABLESPACE encrypt_ts1 ADD DATAFILE 'encrypt_ts1.ibd' ENCRYPTION ='N' ENGINE=InnoDB;
42 DROP DATABASE IF EXISTS tde_db;
43 DROP TABLE IF EXISTS tde_db. t_encrypt;
44 CREATE DATABASE tde_db;
45 USE tde_db;
46 --enable_warnings
47
48 DELIMITER |;
49 CREATE PROCEDURE tde_db.init_setup()
50 begin
51 /* Create table in encrypted tablespace */
52 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
53 c3 CHAR(255) Default 'No text',
54 c4 JSON ,
55 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
56 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
57 c7 POINT NOT NULL SRID 0,
58 spatial INDEX idx2 (c7)
59 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
60
61 /* Create table in non-encrypted tablesapce */
62 CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
63 c3 CHAR(255) Default 'No text',
64 c4 JSON ,
65 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
66 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
67 c7 POINT NOT NULL SRID 0,
68 spatial INDEX idx2 (c7)
69 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
70
71 /* insert into encrypted table */
72 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)'));
73 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
74 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
75 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
76 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
77 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
78 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
79 SELECT '/* select tde_db.t_encrypt */';
80 SELECT COUNT(*) FROM tde_db.t_encrypt;
81 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
82
83 /* insert into non encrypted table */
84 INSERT INTO tde_db.t_non_encrypt(c2,c3,c4,c7) SELECT c2,c3,c4,c7 FROM tde_db.t_encrypt;
85 SELECT '/* select tde_db.t_non_encrypt */';
86 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
87 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
88
89 ALTER INSTANCE ROTATE INNODB MASTER KEY;
90
91 CREATE TABLE tde_db.t_encrypt_2(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
92 c3 CHAR(255) Default 'No text',
93 c4 JSON ,
94 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
95 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
96 c7 POINT NOT NULL SRID 0,
97 spatial INDEX idx2 (c7)
98 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
99 CREATE TABLE tde_db.t_non_encrypt_2(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
100 c3 CHAR(255) Default 'No text',
101 c4 JSON ,
102 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
103 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
104 c7 POINT NOT NULL SRID 0,
105 spatial INDEX idx2 (c7)
106 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
107 /* insert into encrypted table 2 */
108 INSERT INTO tde_db.t_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
109 SELECT '/* select tde_db.t_encrypt_2 */';
110 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
111 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
112 /* insert into NON encrypted table 2 */
113 INSERT INTO tde_db.t_non_encrypt_2(c2,c3,c4,c7) SELECT c2,c3,c4,c7 FROM tde_db.t_encrypt;
114 SELECT '/* select tde_db.t_non_encrypt_2 */';
115 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
116 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_2 LIMIT 10;
117 end|
118 DELIMITER ;|
119
120 --echo #-----------------------------------------------------------------------
121 --echo # Initialize tables
122 call tde_db.init_setup();
123 --echo # plugin already installed error
124 --replace_regex /\.dll/.so/
125 --error ER_UDF_EXISTS
126 eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN';
127 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
128 SELECT @@global.keyring_file_data;
129 --echo # Uninstall is possible when server started with --early-plugin-load
130 UNINSTALL PLUGIN keyring_file;
131 --echo # variable not accessible after uninstall
132 --error ER_UNKNOWN_SYSTEM_VARIABLE
133 SELECT @@global.keyring_file_data;
134
135 --echo # Select non encrypted tables : Pass
136 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
137 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
138 --echo # Select encrypted tables : No Error (after uninstall plugin -master key is cached)
139 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
140 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
141 SELECT COUNT(*) FROM tde_db.t_encrypt;
142 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
143
144 --echo # Error on "ALTER INSTANCE ..." after UNINSTALL PLUGIN
145 --error ER_CANNOT_FIND_KEY_IN_KEYRING
146 ALTER INSTANCE ROTATE INNODB MASTER KEY;
147
148
149 --echo # new table creation in encrypted tablespace is allowed after uninstall
150 CREATE TABLE tde_db.t_encrypt_3(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
151 c3 CHAR(255) Default 'No text',
152 c4 JSON ,
153 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
154 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
155 c7 POINT NOT NULL SRID 0,
156 spatial INDEX idx2 (c7)
157 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
158 --echo # new table in non-encrypted tablespace is allowed after uninstall
159 CREATE TABLE tde_db.t_non_encrypt_3(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
160 c3 CHAR(255) Default 'No text',
161 c4 JSON ,
162 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
163 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
164 c7 POINT NOT NULL SRID 0,
165 spatial INDEX idx2 (c7)
166 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
167
168 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 , tde_db.t_encrypt_3 ;
169 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 , tde_db.t_non_encrypt_3;
170 SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
171
172 --echo #########################################################################
173 --echo # Test 1 : Restart with same keyring plugin option
174 --echo # - all tables accessible
175 --echo #########################################################################
176 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
177 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
178 --source include/restart_mysqld.inc
179 SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
180 --echo # Initialize tables
181 call tde_db.init_setup();
182 --echo # restart with same --early-plugin-load and keyring_file_data option
183 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
184 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
185 --source include/restart_mysqld.inc
186 --echo # All tables must be accessible
187 SELECT COUNT(*) FROM tde_db.t_encrypt;
188 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
189 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
190 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
191 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
192 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
193 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
194 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_2 LIMIT 10;
195
196 --echo # Insert into old encrypted tables
197 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
198 INSERT INTO tde_db.t_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
199 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
200 --echo # Insert into old non encrypted tables
201 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
202 INSERT INTO tde_db.t_non_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
203 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
204 --echo # Update into old encrypted tables
205 UPDATE tde_db.t_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
206 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
207 --echo # Update into old non encrypted tables
208 UPDATE tde_db.t_non_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
209 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
210 --echo # Delete from old encrypted tables
211 DELETE FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
212 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
213 --echo # Delete from old non encrypted tables
214 DELETE FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
215 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
216
217 --echo # Create new tables
218 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
219 c3 CHAR(255) Default 'No text',
220 c4 JSON ,
221 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
222 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
223 c7 POINT NOT NULL SRID 0,
224 spatial INDEX idx2 (c7)
225 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
226 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
227 c3 CHAR(255) Default 'No text',
228 c4 JSON ,
229 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
230 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
231 c7 POINT NOT NULL SRID 0,
232 spatial INDEX idx2 (c7)
233 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
234
235
236 INSERT INTO tde_db.t_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
237 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
238 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
239 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_4 LIMIT 10;
240 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
241 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_4 LIMIT 10;
242
243 ALTER INSTANCE ROTATE INNODB MASTER KEY;
244
245 SELECT COUNT(*) FROM tde_db.t_encrypt;
246 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
247 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
248 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
249 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
250 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
251
252 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 , tde_db.t_encrypt_4;
253 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 , tde_db.t_non_encrypt_4;
254
255
256 --echo #########################################################################
257 --echo # Test 2 : Restart without keyring option
258 --echo # - old encrypted tables accessible
259 --echo # - unencryption tables are accessible
260 --echo #########################################################################
261 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
262 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
263 --source include/restart_mysqld.inc
264 SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
265 --echo # Initialize tables
266 call tde_db.init_setup();
267 --echo # restart without --early-plugin-load and keyring_file_data
268 let $restart_parameters = restart: ;
269 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
270 --source include/restart_mysqld.inc
271
272 --echo # Encrypted tables must not be accessible
273 --error ER_CANNOT_FIND_KEY_IN_KEYRING
274 SELECT COUNT(*) FROM tde_db.t_encrypt;
275 --error ER_CANNOT_FIND_KEY_IN_KEYRING
276 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
277 --echo # Unencrypted tables must be accessible
278 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
279 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
280
281 --echo # Creating table in encrypted tablespace must not be possible as tablespace
282 --echo # would be missing
283 --error ER_TABLESPACE_MISSING
284 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
285 c3 CHAR(255) Default 'No text',
286 c4 JSON ,
287 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
288 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
289 c7 POINT NOT NULL SRID 0,
290 spatial INDEX idx2 (c7)
291 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
292
293 --echo # Creating table in unencrypted tablespace must be possible
294 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
295 c3 CHAR(255) Default 'No text',
296 c4 JSON ,
297 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
298 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
299 c7 POINT NOT NULL SRID 0,
300 spatial INDEX idx2 (c7)
301 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
302
303 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_non_encrypt;
304 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
305
306 --error ER_CANNOT_FIND_KEY_IN_KEYRING
307 ALTER INSTANCE ROTATE INNODB MASTER KEY;
308
309 --echo # Encrypted tables must not be accessible
310 --error ER_CANNOT_FIND_KEY_IN_KEYRING
311 SELECT COUNT(*) FROM tde_db.t_encrypt;
312 --error ER_CANNOT_FIND_KEY_IN_KEYRING
313 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
314
315 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2;
316 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 ,tde_db.t_non_encrypt_4;
317
318 --echo #########################################################################
319 --echo # Test 3 : Restart, Uninstall and re-load plugin using command
320 --echo # - All tables must be accessible
321 --echo #########################################################################
322 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
323 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
324 --source include/restart_mysqld_no_echo.inc
325
326 SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
327
328 --echo # Initalize tables
329 call tde_db.init_setup();
330
331 --echo # restart without --early-plugin-load (still need to provide with
332 --echo # keyring_file_data otherwise it would point to default location which might be
333 --echo # non-writable to mtr). Instead explicitly install plugin to access old table
334 let $restart_parameters = restart: --loose-keyring_file_data=$MYSQL_TMP_DIR/mydummy_keyring;
335 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
336 --source include/restart_mysqld.inc
337
338 --echo # Install keyring plugin
339 --replace_regex /\.dll/.so/
340 --disable_warnings
341 eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN';
342 --enable_warnings
343 --echo # Set keyring_file_data as old file so as to access old tables
344 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
345 eval SET @@global.keyring_file_data='$MYSQL_TMP_DIR/mysecret_keyring';
346 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
347 SELECT @@global.keyring_file_data;
348
349 --echo # All tables must be accessible
350 SELECT COUNT(*) FROM tde_db.t_encrypt;
351 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
352 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
353 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
354 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
355 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
356 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
357 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_2 LIMIT 10;
358
359 --echo # Insert into old encrypted tables
360 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
361 INSERT INTO tde_db.t_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
362 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
363 --echo # Insert into old non encrypted tables
364 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
365 INSERT INTO tde_db.t_non_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
366 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
367 --echo # Update into old encrypted tables
368 UPDATE tde_db.t_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
369 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
370 --echo # Update into old non encrypted tables
371 UPDATE tde_db.t_non_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
372 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
373 --echo # Delete from old encrypted tables
374 DELETE FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
375 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
376 --echo # Delete from old non encrypted tables
377 DELETE FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
378 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
379
380 --echo # Create new tables
381 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
382 c3 CHAR(255) Default 'No text',
383 c4 JSON ,
384 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
385 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
386 c7 POINT NOT NULL SRID 0,
387 spatial INDEX idx2 (c7)
388 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
389 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
390 c3 CHAR(255) Default 'No text',
391 c4 JSON ,
392 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
393 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
394 c7 POINT NOT NULL SRID 0,
395 spatial INDEX idx2 (c7)
396 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
397
398
399 INSERT INTO tde_db.t_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
400 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
401 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
402 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_4 LIMIT 10;
403 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
404 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_4 LIMIT 10;
405
406 ALTER INSTANCE ROTATE INNODB MASTER KEY;
407
408 SELECT COUNT(*) FROM tde_db.t_encrypt;
409 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
410 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
411 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
412 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
413 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
414
415 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 , tde_db.t_encrypt_4;
416 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 , tde_db.t_non_encrypt_4;
417
418 UNINSTALL PLUGIN keyring_file;
419
420
421 #Once keyring plugin is uninstalled, we should still be able to use existing
422 #tables in encrypted tablespaces as their keys are cached. That's why, even after
423 #keyring plugin is uninstalled, we can do DMLs on encrypted tables and create
424 #tables in encrypted tablespaces.
425
426 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
427 c3 CHAR(255) Default 'No text',
428 c4 JSON ,
429 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
430 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
431 c7 POINT NOT NULL SRID 0,
432 spatial INDEX idx2 (c7)
433 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
434
435 --error ER_CANNOT_FIND_KEY_IN_KEYRING
436 ALTER INSTANCE ROTATE INNODB MASTER KEY;
437
438 DROP TABLE tde_db.t_encrypt_4;
439
440 --echo #########################################################################
441 --echo # Test 4 : Restart with new keyring_data_file
442 --echo # - Old encrypted tables not accessible
443 --echo # - Non encrypted tables accessible
444 --echo # - Creation of new tables in encrypted/unencrypted tablespace
445 --echo # is also posible
446 --echo #########################################################################
447 --echo # restart with --early-plugin-load to load initial data
448
449 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
450 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
451 --source include/restart_mysqld.inc
452 SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
453 --echo # init tables
454 call tde_db.init_setup();
455 --echo # restart with with different keyring_file_data file
456 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring_new $KEYRING_PLUGIN_OPT;
457 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
458 --source include/restart_mysqld.inc
459
460 --echo # old encrypted tables not accessible
461 --error ER_CANNOT_FIND_KEY_IN_KEYRING
462 SELECT COUNT(*) FROM tde_db.t_encrypt;
463 --error ER_CANNOT_FIND_KEY_IN_KEYRING
464 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
465 --echo # NON encrypted tables accessible
466 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
467 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
468
469 --echo # new encrypted tablespace possible
470 CREATE TABLESPACE encrypt_ts2 ADD DATAFILE 'encrypt_ts2.ibd' ENCRYPTION ='Y' ENGINE=InnoDB;
471
472 --echo # new table in new encrypted tablespace possible
473 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
474 c3 CHAR(255) Default 'No text',
475 c4 JSON ,
476 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
477 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
478 c7 POINT NOT NULL SRID 0,
479 spatial INDEX idx2 (c7)
480 ) TABLESPACE encrypt_ts2 ENCRYPTION='Y' ENGINE = InnoDB;
481
482 --echo # new table in non encrypted tablespace possible
483 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
484 c3 CHAR(255) Default 'No text',
485 c4 JSON ,
486 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
487 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
488 c7 POINT NOT NULL SRID 0,
489 spatial INDEX idx2 (c7)
490 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
491
492 INSERT INTO tde_db.t_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_non_encrypt;
493 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
494 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_non_encrypt;
495 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
496
497 ALTER INSTANCE ROTATE INNODB MASTER KEY;
498
499 --echo # Old encrypted tables in encrypted tablespace must not be accessible
500 --error ER_CANNOT_FIND_KEY_IN_KEYRING
501 SELECT COUNT(*) FROM tde_db.t_encrypt;
502 --error ER_CANNOT_FIND_KEY_IN_KEYRING
503 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
504 --echo # Old unencrypted table must be accessible
505 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
506 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
507 --echo # New encrypted tables must be accessible
508 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
509 --echo # New unencrypted tables must be accessible
510 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
511
512 --echo ###########
513 --echo # CLEANUP #
514 --echo ###########
515
516 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 ,tde_db.t_encrypt_4;
517 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 ,tde_db.t_non_encrypt_4;
518 DROP DATABASE tde_db;
519 DROP TABLESPACE encrypt_ts;
520 DROP TABLESPACE encrypt_ts1;
521 DROP TABLESPACE encrypt_ts2;
522 remove_file $MYSQL_TMP_DIR/mydummy_keyring;
523 remove_file $MYSQL_TMP_DIR/mysecret_keyring;
524 remove_file $MYSQL_TMP_DIR/mysecret_keyring_new;
525
526 --echo # Restarting server without keyring to restore server state
527 let $restart_parameters = restart: ;
528 --source include/restart_mysqld.inc
529