Debian Codegraph @

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

raw
1 # InnoDB transparent encrypted tablespace
2 # This test case will test basic encryption support features.
3
4 --source include/no_valgrind_without_big.inc
5 --source include/have_innodb_max_16k.inc
6
7 --disable_query_log
8 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Encryption can't find master key, please check the keyring is loaded.");
9 call mtr.add_suppression("Can't generate new master key for tablespace encryption, please check the keyring is loaded.");
10 call mtr.add_suppression("The error means that another program is using InnoDB's files");
11 call mtr.add_suppression("Operating system error number .* in a file operation");
12 call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] Check keyring fail, please check the keyring is loaded.");
13 --enable_query_log
14
15 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
16
17 #------------------------------------------------------------------------------
18 # InnoDB transparent encrypted tablespace:
19 # 1) Try creating encrypt table without loading keying plugin
20 # 2) Run , ALTER INSTANCE ROTATE INNODB MASTER KEY , when plugin is not loaded
21 # 3) Try , UNINSTALL PLUGIN keyring_file , when plugin is not loaded
22 --echo # Create encrypt table before loading keyring plugin
23 SET GLOBAL innodb_file_per_table = 1;
24 SELECT @@innodb_file_per_table;
25 --disable_warnings
26 DROP TABLE IF EXISTS t_encrypt;
27 --enable_warnings
28 # Create encryp table
29 --error ER_CANNOT_FIND_KEY_IN_KEYRING
30 CREATE TABLE t_encrypt(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
31 # Uninstall keyring plugin whuch is not loaded yet
32 --error ER_SP_DOES_NOT_EXIST
33 UNINSTALL PLUGIN keyring_file;
34 --error ER_CANNOT_FIND_KEY_IN_KEYRING
35 ALTER INSTANCE ROTATE INNODB MASTER KEY;
36
37 --echo # Starting server with keyring plugin
38 # restart with keying
39 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
40 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
41 --replace_regex /\.dll/.so/
42 --source include/restart_mysqld.inc
43
44
45 #------------------------------------------------------------------------------
46 # InnoDB transparent encrypted tablespace:
47 # 1) With JSON , VIRTUAL/GENERATED column
48 # 2) With rtree ( spatial index)
49 # 3) Check "ALTER INSTANCE .." not conflict with DML/explicit locks
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 --enable_warnings
55
56 SET GLOBAL innodb_file_per_table = 1;
57 SELECT @@innodb_file_per_table;
58
59 CREATE TABLE tde_db.t_encrypt(c4 JSON ,
60 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
61 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
62 c7 POINT NOT NULL SRID 0,
63 spatial INDEX idx2 (c7)
64 ) ENCRYPTION="Y" ENGINE = InnoDB;
65
66 SHOW CREATE TABLE tde_db.t_encrypt;
67 INSERT INTO tde_db.t_encrypt(c4,c7) VALUES('{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
68 INSERT INTO tde_db.t_encrypt(c4,c7) select c4,c7 from tde_db.t_encrypt;
69
70 SELECT c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
71 SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
72 SHOW CREATE TABLE tde_db.t_encrypt;
73
74
75 # Lock table and try to rotate key (key rotation ,
76 # involves re-writting secure key)
77 --echo # In connection 1
78 connect (con1,localhost,root,,);
79 LOCK TABLES tde_db.t_encrypt WRITE;
80 --echo # In connection default
81 connection default;
82 #show open tables WHERE Table LIKE 'tde_db.t_encrypt' AND In_use > 0;
83 SHOW OPEN TABLES LIKE 't_encrypt';
84 --echo "ALTER INSTANCE.." do not conflict with "LOCK TABLE .." COMMAND
85 ALTER INSTANCE ROTATE INNODB MASTER KEY;
86 --echo # In connection 1
87 connection con1;
88 INSERT INTO tde_db.t_encrypt(c4,c7) VALUES('{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
89 SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
90 UNLOCK TABLES;
91 disconnect con1;
92 --echo # In connection default
93 connection default;
94 SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
95
96 # Restart to confirm the encryption info can be retrieved properly.
97 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
98 -- send_shutdown
99 -- source include/wait_until_disconnected.inc
100 --exec echo "restart:--early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
101 --enable_reconnect
102 --source include/wait_until_connected_again.inc
103 --disable_reconnect
104
105 SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
106
107 DROP DATABASE tde_db;
108
109
110 # Cleanup
111 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
112
113
114 #------------------------------------------------------------------------------
115 # InnoDB transparent encrypted tablespace:
116 # 1) with blob
117 # 2) check value read properly after restart
118 --disable_warnings
119 DROP DATABASE IF EXISTS tde_db;
120 CREATE DATABASE tde_db;
121 USE tde_db;
122 DROP TABLE IF EXISTS tde_db.t_encrypt;
123 DROP TABLE IF EXISTS tde_db.t_encrypt_1;
124
125 --enable_warnings
126
127 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
128 SET GLOBAL innodb_file_per_table = 1;
129 SELECT @@innodb_file_per_table;
130
131 # Create a table with encryption
132 CREATE TABLE tde_db.t_encrypt(c1 INT, c2 char(20), c3 BLOB) ENCRYPTION="Y" ENGINE = InnoDB;
133 --error ER_INVALID_ENCRYPTION_OPTION
134 CREATE TABLE tde_db.t_encrypt_1(c1 INT, c2 char(20)) ENCRYPTION="Yes" ENGINE = InnoDB;
135 CREATE TABLE tde_db.t_encrypt_1(c1 INT, c2 char(20)) ENCRYPTION="y" ENGINE = InnoDB;
136 DROP TABLE tde_db.t_encrypt_1;
137 CREATE TABLE tde_db.t_encrypt_1(c1 INT, c2 char(20),c3 BLOB) ENGINE = InnoDB;
138
139 SHOW CREATE TABLE tde_db.t_encrypt;
140 INSERT INTO tde_db.t_encrypt VALUES(0, "aaaaa",repeat('A', 20000));
141 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
142 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
143 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
144 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
145 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
146 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
147
148 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
149 --echo #Insert into non encrypted table
150 INSERT INTO tde_db.t_encrypt_1 SELECT * FROM tde_db.t_encrypt;
151 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt_1 LIMIT 10;
152 SELECT COUNT(*) FROM tde_db.t_encrypt_1;
153
154 # Restart to confirm the encryption info can be retrieved properly.
155 --echo # Starting server with keyring plugin restart with keying
156 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
157 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
158 --source include/restart_mysqld.inc
159 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
160 SELECT COUNT(*) FROM tde_db.t_encrypt;
161 --echo #check non encrypted table
162 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt_1 LIMIT 10;
163 SELECT COUNT(*) FROM tde_db.t_encrypt_1;
164
165 DROP TABLE tde_db.t_encrypt;
166 DROP TABLE tde_db.t_encrypt_1;
167
168 # Cleanup
169 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
170
171
172 #------------------------------------------------------------------------------
173 # InnoDB transparent encrypted tablespace:
174 # 1) With different row format
175 # 2) With partition table
176 # 3) Negative cases
177 # a) error when innodb_file_per_table=0
178 # b) error when shared tablespace is specified during create
179 # c) error with temp table
180 # d) ENCRYPTION=Y is ignored for myisam
181 # 4) Create table from procedure
182
183 --disable_warnings
184 DROP DATABASE IF EXISTS tde_db;
185 DROP TABLE IF EXISTS tde_db.t_encrypt;
186 CREATE DATABASE tde_db;
187 USE tde_db;
188 --enable_warnings
189
190 --echo # File per table is set 0. Encryption not possible.
191 SET GLOBAL innodb_file_per_table = 0;
192 SELECT @@innodb_file_per_table;
193 --error ER_INVALID_ENCRYPTION_REQUEST
194 CREATE TABLE tde_db.t_encrypt(c4 JSON ,
195 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
196 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
197 c7 POINT NOT NULL SRID 0,
198 spatial INDEX idx2 (c7)
199 ) ENCRYPTION="Y" ENGINE = InnoDB;
200 SHOW WARNINGS;
201
202 SET GLOBAL innodb_file_per_table = 1;
203 SELECT @@innodb_file_per_table;
204
205 CREATE TABLESPACE s_alt1 ADD DATAFILE 's_alt1.ibd';
206 --error ER_INVALID_ENCRYPTION_REQUEST
207 CREATE TABLE tde_db.t_encrypt (a int, b text) ENCRYPTION="Y" TABLESPACE=`s_alt1` ENGINE=InnoDB;
208 SHOW WARNINGS;
209 DROP TABLESPACE s_alt1;
210
211 --error ER_CANNOT_USE_ENCRYPTION_CLAUSE
212 CREATE TEMPORARY TABLE tde_db.t_encrypt (a int, b text) ENCRYPTION="Y" ENGINE=InnoDB;
213 SHOW WARNINGS;
214
215 # Now error/warning shown
216 --error ER_CHECK_NOT_IMPLEMENTED
217 CREATE TABLE tde_db.t_encrypt_myisam (a int, b text) ENCRYPTION="Y" ENGINE=MyISAM;
218
219 # Create a table with encryption and different row format
220 DELIMITER |;
221 CREATE PROCEDURE tde_db.row_format_t_encrypt(row_form VARCHAR(1000))
222 begin
223 declare i int default 1;
224 declare has_error int default 0;
225 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
226
227 DROP TABLE IF EXISTS tde_db.t_encrypt;
228
229 SET @saved_innodb_strict_mode=@@SESSION.innodb_strict_mode;
230 SET SESSION innodb_strict_mode=OFF;
231 SET @sql_text = CONCAT('CREATE TABLE tde_db.t_encrypt ('," c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,c3 VARCHAR(255), c4 JSON ,c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,c7 POINT NOT NULL SRID 0,spatial INDEX idx2 (c7) ) ", ' ENCRYPTION="Y" ', row_form ,' ENGINE=InnoDB');
232 PREPARE stmt FROM @sql_text;
233 EXECUTE stmt;
234 DEALLOCATE PREPARE stmt;
235 SET SESSION innodb_strict_mode=@saved_innodb_strict_mode;
236
237 SHOW CREATE TABLE tde_db.t_encrypt;
238
239 INSERT INTO tde_db.t_encrypt(c3,c4,c7) VALUES (REPEAT('a',200),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
240 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
241 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
242 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
243 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
244 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
245 SELECT COUNT(*) FROM tde_db.t_encrypt;
246 SELECT c2,c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
247 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
248 DELETE FROM tde_db.t_encrypt WHERE c2 > 10;
249 UPDATE tde_db.t_encrypt SET c2 = 100 WHERE c2=1;
250 SELECT c2,c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
251 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
252 SHOW CREATE TABLE tde_db.t_encrypt;
253 end|
254 DELIMITER ;|
255
256
257
258
259
260 call tde_db.row_format_t_encrypt(" ROW_FORMAT=DYNAMIC ");
261
262 --echo # restart with keying
263 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
264 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
265 --source include/restart_mysqld.inc
266 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
267 SELECT COUNT(*) FROM tde_db.t_encrypt;
268 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
269 SELECT COUNT(*) FROM tde_db.t_encrypt;
270
271 call tde_db.row_format_t_encrypt(" ROW_FORMAT=COMPACT ");
272 --echo # restart with keying
273 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
274 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
275 --source include/restart_mysqld.inc
276 --disable_warnings
277 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
278 --enable_warnings
279 SELECT COUNT(*) FROM tde_db.t_encrypt;
280 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
281 SELECT COUNT(*) FROM tde_db.t_encrypt;
282
283 call tde_db.row_format_t_encrypt(" ROW_FORMAT=REDUNDANT ");
284 --echo # restart with keying
285 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
286 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
287 --source include/restart_mysqld.inc
288 --disable_warnings
289 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
290 --enable_warnings
291 SELECT COUNT(*) FROM tde_db.t_encrypt;
292 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
293 SELECT COUNT(*) FROM tde_db.t_encrypt;
294
295 call tde_db.row_format_t_encrypt(" ROW_FORMAT=COMPRESSED " );
296 --echo # restart with keying
297 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
298 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
299 --source include/restart_mysqld.inc
300 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
301 SELECT COUNT(*) FROM tde_db.t_encrypt;
302 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
303 SELECT COUNT(*) FROM tde_db.t_encrypt;
304
305 call tde_db.row_format_t_encrypt(" ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 ");
306 --echo # restart with keying
307 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
308 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
309 --source include/restart_mysqld.inc
310 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
311 SELECT COUNT(*) FROM tde_db.t_encrypt;
312 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
313 SELECT COUNT(*) FROM tde_db.t_encrypt;
314
315 #call tde_db.row_format_t_encrypt(" ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 ");
316 #call tde_db.row_format_t_encrypt(" ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16 ");
317
318 --echo # Create partition table
319 DROP TABLE tde_db.t_encrypt;
320 CREATE TABLE tde_db.t_encrypt (c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,c3 VARCHAR(255), c4 JSON ,c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL ) ENCRYPTION="Y" ENGINE=InnoDB PARTITION BY RANGE (c2) (PARTITION p1 VALUES LESS THAN (4),PARTITION p2 VALUES LESS THAN (8),PARTITION p3 VALUES LESS THAN (1000)) ;
321 SHOW CREATE TABLE tde_db.t_encrypt;
322
323 INSERT INTO tde_db.t_encrypt(c3,c4) VALUES (REPEAT('a',200),'{ "key_a": 1, "key_b": 2, "key_c": 3 }');
324 INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
325 INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
326 INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
327 INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
328 INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
329 SELECT c2,c4,c5 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
330 SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
331 DELETE FROM tde_db.t_encrypt WHERE c2 > 10;
332 UPDATE tde_db.t_encrypt SET c2 = 100 WHERE c2=1;
333 SELECT c2,c4,c5 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
334 SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
335 ALTER TABLE tde_db.t_encrypt TRUNCATE PARTITION p2;
336 SELECT c2,c4,c5 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
337 SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
338 SHOW CREATE TABLE tde_db.t_encrypt;
339
340 --echo # restart with keying
341 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
342 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
343 --source include/restart_mysqld.inc
344 SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
345
346 DROP TABLE tde_db.t_encrypt;
347
348 DROP DATABASE tde_db;
349
350 # Cleanup
351 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
352
353 #------------------------------------------------------------------------------
354 # InnoDB transparent encrypted tablespace:
355 # With concurrent read/write/alter instance statements
356
357 --disable_warnings
358 DROP DATABASE IF EXISTS tde_db;
359 DROP TABLE IF EXISTS tde_db. t_encrypt;
360 CREATE DATABASE tde_db;
361 USE tde_db;
362 --enable_warnings
363
364 SET GLOBAL innodb_file_per_table = 1;
365 SELECT @@innodb_file_per_table;
366
367 # Create a table with encryption
368 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL PRIMARY KEY,
369 c3 CHAR(255) Default 'No text',
370 c4 JSON ,
371 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
372 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
373 c7 POINT NOT NULL SRID 0,
374 spatial INDEX idx2 (c7)
375 ) ENCRYPTION="Y" ENGINE = InnoDB;
376
377 DELIMITER |;
378 CREATE PROCEDURE tde_db.populate_t_encrypt()
379 begin
380 declare i int default 1;
381 declare has_error int default 0;
382 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
383 while (i <= 2000) DO
384 insert into tde_db.t_encrypt(c2,c3,c4,c7) VALUES(i,CONCAT(REPEAT('a',200),LPAD(CAST(i AS CHAR),4,'0')),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
385 set i = i + 1;
386 end while;
387 end|
388 CREATE PROCEDURE tde_db.populate_t_encrypt_small()
389 begin
390 declare i int default 1;
391 declare has_error int default 0;
392 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
393 while (i <= 500) DO
394 insert into tde_db.t_encrypt(c2,c3,c4,c7) VALUES(i,CONCAT(REPEAT('a',200),LPAD(CAST(i AS CHAR),4,'0')),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
395 set i = i + 1;
396 end while;
397 end|
398 CREATE PROCEDURE tde_db.read_t_encrypt()
399 begin
400 declare i int default 1;
401 while (i <= 30) DO
402 SELECT * FROM (SELECT * FROM tde_db.t_encrypt ORDER BY RAND() LIMIT 1) AS A WHERE A.c2 < 0 ;
403 set i = i + 1;
404 end while;
405 end|
406 CREATE PROCEDURE tde_db.rotate_master_key()
407 begin
408 declare i int default 1;
409 declare has_error int default 0;
410 while (i <= 500) DO
411 ALTER INSTANCE ROTATE INNODB MASTER KEY;
412 set i = i + 1;
413 end while;
414 end|
415 CREATE PROCEDURE tde_db.create_encrypt_table(encrypt VARCHAR(5))
416 begin
417 declare i int default 1;
418 declare has_error int default 0;
419 while (i <= 50) DO
420 SET @sql_text = CONCAT('CREATE TABLE ',CONCAT('tde_db.t_encrypt_',encrypt,'_',i),' (c1 INT) ENCRYPTION="',encrypt,'"' ,' ENGINE=InnoDB');
421 PREPARE stmt FROM @sql_text;
422 EXECUTE stmt;
423 DEALLOCATE PREPARE stmt;
424 set i = i + 1;
425 end while;
426 end|
427 DELIMITER ;|
428
429
430 SHOW CREATE TABLE tde_db.t_encrypt;
431 --echo # In connection con1 - Running insert
432 connect (con1,localhost,root,,);
433 send call tde_db.populate_t_encrypt();
434 --echo # In connection con2 - Running insert
435 connect (con2,localhost,root,,);
436 send call tde_db.populate_t_encrypt_small();
437 --echo # In connection con3 : Running select
438 connect (con3,localhost,root,,);
439 send call tde_db.read_t_encrypt();
440 --echo # In connection con4 : Running select
441 connect (con4,localhost,root,,);
442 send call tde_db.read_t_encrypt();
443 --echo # In connection con5 - Running "alter instance"
444 connect (con5,localhost,root,,);
445 send call tde_db.rotate_master_key();
446 --enable_query_log
447 --echo # In connection con6 - Running "create table"
448 connect (con6,localhost,root,,);
449 send call tde_db.create_encrypt_table("Y");
450 --enable_query_log
451 --echo # In connection con7 - Running "create table"
452 connect (con7,localhost,root,,);
453 send call tde_db.create_encrypt_table("N");
454 --enable_query_log
455
456 --echo # In connection con1
457 connection con1;
458 --disable_query_log
459 reap;
460 --enable_query_log
461 disconnect con1;
462 --echo # In connection con2
463 connection con2;
464 --disable_query_log
465 reap;
466 --enable_query_log
467 disconnect con2;
468 --echo # In connection con3
469 connection con3;
470 --disable_query_log
471 reap;
472 --enable_query_log
473 disconnect con3;
474 --echo # In connection con4
475 connection con4;
476 --disable_query_log
477 reap;
478 --enable_query_log
479 disconnect con4;
480 --echo # In connection con5
481 connection con5;
482 --disable_query_log
483 reap;
484 --enable_query_log
485 disconnect con5;
486 --echo # In connection con6
487 connection con6;
488 --disable_query_log
489 reap;
490 --enable_query_log
491 disconnect con6;
492 --echo # In connection con7
493 connection con7;
494 --disable_query_log
495 reap;
496 --enable_query_log
497 disconnect con7;
498
499
500 connection default;
501 USE tde_db;
502 SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
503 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
504 SELECT COUNT(*) FROM tde_db.t_encrypt;
505 SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
506 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
507
508 --echo # restart with keying
509 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
510 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
511 --source include/restart_mysqld.inc
512 SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
513 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
514 SELECT COUNT(*) FROM tde_db.t_encrypt;
515 SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
516 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
517
518
519 DROP DATABASE tde_db;
520
521
522 # Cleanup
523 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
524
525 #------------------------------------------------------------------------------
526 # InnoDB transparent encrypted tablespace, try run ""ALTER INSTANCE ..."
527 # 1) with non priv user
528 # 2) with priv user
529 --disable_warnings
530 DROP DATABASE IF EXISTS tde_db;
531 CREATE DATABASE tde_db;
532 USE tde_db;
533 DROP TABLE IF EXISTS tde_db.t_encrypt;
534 #
535 --enable_warnings
536 #
537 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
538 SET GLOBAL innodb_file_per_table = 1;
539 SELECT @@innodb_file_per_table;
540 #
541 # Create a table with encryption
542 CREATE TABLE tde_db.t_encrypt(c1 INT, c2 char(20), c3 BLOB) ENCRYPTION="Y" ENGINE = InnoDB;
543 SHOW CREATE TABLE tde_db.t_encrypt;
544 INSERT INTO tde_db.t_encrypt VALUES(0, "aaaaa",repeat('A', 20000));
545 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
546 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
547 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
548 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
549 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
550 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
551
552 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
553
554 CREATE USER encryptprivuser@localhost IDENTIFIED BY 'auth';
555 GRANT ALL PRIVILEGES ON *.* to encryptprivuser@localhost;
556 FLUSH PRIVILEGES;
557 CREATE USER encryptnonprivuser@localhost IDENTIFIED BY 'noauth';
558 GRANT SELECT ON *.* to encryptnonprivuser@localhost;
559 FLUSH PRIVILEGES;
560 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
561
562 --echo # In connection 1
563 connect (con1,localhost,encryptprivuser,'auth',);
564 SELECT CURRENT_USER();
565 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
566 ALTER INSTANCE ROTATE INNODB MASTER KEY;
567 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
568 disconnect con1;
569 --echo # In connection 2
570 connect (con2,localhost,encryptnonprivuser,'noauth',);
571 SELECT CURRENT_USER();
572 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
573 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
574 ALTER INSTANCE ROTATE INNODB MASTER KEY;
575 --error ER_TABLEACCESS_DENIED_ERROR
576 CREATE TABLE tde_db.t_encrypt_np(c1 INT, c2 char(20), c3 BLOB) ENCRYPTION="Y" ENGINE = InnoDB;
577 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
578 disconnect con2;
579 --echo # In connection default
580 connection default;
581
582 # Restart to confirm the encryption info can be retrieved properly.
583 --echo # Starting server with keyring plugin
584 # restart with keying
585 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
586 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
587 --source include/restart_mysqld.inc
588 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
589
590 DROP USER encryptnonprivuser@localhost;
591 DROP USER encryptprivuser@localhost;
592 DROP TABLE tde_db.t_encrypt;
593 #
594 ## Cleanup
595 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
596
597
598
599 #------------------------------------------------------------------------------
600 ## InnoDB transparent encrypted tablespace
601 # - with PK-FK table
602 # - view on encrypted table
603 # - trigger on encrypted table
604 # - fulltext index
605 # - alter table on encrypted table
606 --disable_warnings
607 DROP DATABASE IF EXISTS tde_db;
608 CREATE DATABASE tde_db;
609 USE tde_db;
610 DROP TABLE IF EXISTS tde_db.t_encrypt;
611 #
612 --enable_warnings
613 #
614 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
615 SET GLOBAL innodb_file_per_table = 1;
616 SELECT @@innodb_file_per_table;
617
618 # Create a table with encryption
619 CREATE TABLE tde_db.t_encrypt(c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 char(100), c3 BLOB , FULLTEXT INDEX `idx1` (c2)) ENCRYPTION="Y" ENGINE = InnoDB;
620 CREATE TABLE tde_db.t_encrypt1(c11 INT , c22 char(100), c33 BLOB , FULLTEXT INDEX `idx1` (c22)) ENCRYPTION="Y" ENGINE = InnoDB;
621 SHOW CREATE TABLE tde_db.t_encrypt;
622
623 CREATE TABLE tde_db.t_encrypt2 (f1 INT PRIMARY KEY, f2 CHAR(100),
624 FOREIGN KEY (f1) REFERENCES tde_db.t_encrypt(c1) ON UPDATE CASCADE) ENCRYPTION="Y" ENGINE=InnoDB;
625
626
627 DELIMITER |;
628 CREATE TRIGGER tde_db.trigger_encrypt_table AFTER INSERT ON tde_db.t_encrypt
629 FOR EACH ROW
630 begin
631 INSERT INTO tde_db.t_encrypt1 SET c11 = NEW.c1*-1, c22 = NEW.c2 , c33 = NEW.c3;
632 end|
633 DELIMITER ;|
634
635 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("transparanet tablespace encryption",repeat('A', 200));
636 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("general tablespace option",repeat('A', 200));
637 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("page level encryption",repeat('A', 200));
638
639
640 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(1,"transparanet tablespace encryption");
641 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");
642
643 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
644
645 # fulltext query
646 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace');
647 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace' IN BOOLEAN MODE);
648 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('+tablespace -encryption' IN BOOLEAN MODE);
649 ALTER TABLE tde_db.t_encrypt DROP INDEX idx1;
650 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
651 ALTER TABLE tde_db.t_encrypt ADD COLUMN c4 CHAR(20) DEFAULT 'text';
652 SELECT c1,c2,right(c3, 20),c4 FROM tde_db.t_encrypt LIMIT 10;
653
654 # check view
655 CREATE VIEW tde_db.t_encrypt_view AS SELECT c1,c2 FROM tde_db.t_encrypt;
656 SELECT c2 FROM tde_db.t_encrypt_view LIMIT 10;
657 SELECT A.c2,B.c2,right(B.c3,20) FROM tde_db.t_encrypt_view A , tde_db.t_encrypt B WHERE A.c2 = B.c2;
658 DROP VIEW tde_db.t_encrypt_view;
659
660 # Check triggr table
661 SELECT c11,c22,right(c33, 20) FROM tde_db.t_encrypt1 LIMIT 10;
662
663 # check PK-FK , ON UPDATE CASECADE
664 # Duplicate key
665 --ERROR ER_DUP_ENTRY
666 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");
667 # No entry in PK
668 --ERROR 1452
669 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(8,"general tablespace option");
670 SELECT f1,f2 FROM tde_db.t_encrypt2;
671 UPDATE tde_db.t_encrypt SET c1=10 WHERE c1=1;
672 SELECT f1,f2 FROM tde_db.t_encrypt2;
673
674 DROP DATABASE tde_db;
675 #
676 ## Cleanup
677 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
678
679
680 #------------------------------------------------------------------------------
681 # InnoDB transparent encrypted tablespace
682 # - transation
683 --disable_warnings
684 DROP DATABASE IF EXISTS tde_db;
685 CREATE DATABASE tde_db;
686 USE tde_db;
687 DROP TABLE IF EXISTS tde_db.t_encrypt;
688 #
689 --enable_warnings
690 #
691 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
692 SET GLOBAL innodb_file_per_table = 1;
693 SELECT @@innodb_file_per_table;
694 CREATE TABLE tde_db.t_encrypt (c2 INT NOT NULL AUTO_INCREMENT ,c3 VARCHAR(255), c4 JSON ,c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,c7 POINT NOT NULL SRID 0,spatial INDEX idx2 (c7) , PRIMARY KEY (c2,c3(100))) ENCRYPTION="Y" ENGINE=InnoDB;
695
696 DELIMITER |;
697 CREATE PROCEDURE tde_db.txn_t_encrypt()
698 BEGIN
699 declare i int default 0;
700 declare rowcnt int default 0;
701 START TRANSACTION;
702 WHILE (i <= 2000) DO
703
704 SET i = i + 1;
705 SET rowcnt = rowcnt + 1;
706
707 INSERT INTO tde_db.t_encrypt(c3,c4,c7) VALUES (CONCAT(REPEAT('a',10),REPEAT(i,10)),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
708
709
710 IF (rowcnt = 3) THEN
711 UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 21, "key_b": 22, "key_c": 23 }' WHERE c2 = i-1 ;
712 DELETE FROM tde_db.t_encrypt WHERE c2 = i;
713 SAVEPOINT A;
714 END IF;
715 IF (rowcnt = 5) THEN
716 UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 41, "key_b": 42, "key_c": 43 }' WHERE c2 = i-1 ;
717 DELETE FROM tde_db.t_encrypt WHERE c2 = i;
718 SAVEPOINT B;
719 END IF;
720 IF (rowcnt = 10) THEN
721 ROLLBACK TO SAVEPOINT A;
722 COMMIT;
723 SET rowcnt = 0;
724 START TRANSACTION;
725 END IF;
726
727
728 END WHILE;
729 COMMIT;
730 end|
731 DELIMITER ;|
732
733 call tde_db.txn_t_encrypt();
734 SELECT COUNT(*) FROM tde_db.t_encrypt;
735 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt LIMIT 10;
736 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt WHERE c2 > 500 AND c2 < 600;
737 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt ORDER BY c2 DESC LIMIT 10;
738
739
740 --echo # Starting server with keyring plugin
741 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
742 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
743 --source include/restart_mysqld.inc
744 SELECT COUNT(*) FROM tde_db.t_encrypt;
745 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt LIMIT 10;
746 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt WHERE c2 > 500 AND c2 < 600;
747 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt ORDER BY c2 DESC LIMIT 10;
748 DROP DATABASE tde_db;
749
750 --echo # Global privilege ENCRYPTION_KEY_ADMIN can replace super.
751 connection default;
752 USE test;
753 CREATE USER encryption_admin@localhost IDENTIFIED BY 'foo';
754 GRANT ENCRYPTION_KEY_ADMIN, CREATE ON *.* TO encryption_admin@localhost;
755 connect (con10,localhost,encryption_admin,foo,test);
756 # Create a table with encryption
757 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
758 ALTER INSTANCE ROTATE INNODB MASTER KEY;
759 connection default;
760 REVOKE ENCRYPTION_KEY_ADMIN ON *.* FROM encryption_admin@localhost;
761 connection con10;
762 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
763 ALTER INSTANCE ROTATE INNODB MASTER KEY;
764 connection default;
765 disconnect con10;
766 DROP USER encryption_admin@localhost;
767 DROP TABLE t1;
768
769 # Normal restart
770 --disable_query_log
771 let $restart_parameters = restart:;
772 --enable_query_log
773 --source include/restart_mysqld.inc
774 ## Cleanup
775 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
776 --remove_file $MYSQL_TMP_DIR/mysecret_keyring