Debian Codegraph @

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

raw
1 # InnoDB transparent encryption on redo log.
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 #Suppress following messages from myslqd log
8 --disable_query_log
9 call mtr.add_suppression("keyring_file initialization failure. Please check if the keyring_file_data points to readable keyring file or keyring file can be created in the specified location. The keyring_file will stay unusable until correct path to the keyring file gets provided");
10 call mtr.add_suppression("Error while loading keyring content. The keyring might be malformed");
11 call mtr.add_suppression("ibd can't be decrypted, please confirm that keyring is loaded.");
12 call mtr.add_suppression("\\[Warning\\] .* Plugin mysqlx reported.*");
13 call mtr.add_suppression("\\[Warning\\] .* Ignoring tablespace .* because it could not be opened");
14 call mtr.add_suppression("\\[ERROR\\] .* Encryption can't find master key, please check the keyring is loaded.");
15 call mtr.add_suppression("\\[ERROR\\] .* Failed to find tablespace for table `\.\.*`\.`\.\.*` in the cache.");
16 call mtr.add_suppression("\\[ERROR\\] .* Can't set redo log files to be encrypted.");
17 --enable_query_log
18
19 let $old_innodb_redo_log_encrypt = `SELECT @@innodb_redo_log_encrypt`;
20
21 CREATE DATABASE tde_db;
22 USE tde_db;
23
24 # Explicitly run , INSTALL PLUGIN keyring_file SONAME 'keyring_file.so'
25 # Expected : plugin already installed
26
27 SELECT @@global.innodb_redo_log_encrypt ;
28 --replace_regex /\.dll/.so/
29 --error ER_UDF_EXISTS
30 eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN';
31 UNINSTALL PLUGIN keyring_file;
32
33 # Flush logs
34 # (closes and reopens any flushable logs for installed storage engines.
35 # This causes InnoDB to flush its logs to disk. )
36 --let $restart_parameters = restart:
37 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
38 --replace_regex /\.dll/.so/
39 --source include/restart_mysqld.inc
40
41 SELECT @@global.innodb_redo_log_encrypt ;
42 SET GLOBAL innodb_redo_log_encrypt = 1;
43 SELECT @@global.innodb_redo_log_encrypt ;
44 CREATE TABLE tde_db.t1 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
45 INSERT INTO t1 (a, b) VALUES (1, REPEAT('a', 6*512*512));
46 SELECT a,LEFT(b,10) FROM tde_db.t1;
47 CREATE TABLE tde_db.t2 (a BIGINT PRIMARY KEY, b LONGBLOB)
48 ENCRYPTION='Y' ENGINE=InnoDB;
49 INSERT INTO t2 (a, b) VALUES (1, REPEAT('a', 6*512*512));
50 SELECT a,LEFT(b,10) FROM tde_db.t2;
51 SET GLOBAL innodb_redo_log_encrypt = 0;
52 SELECT @@global.innodb_redo_log_encrypt ;
53 CREATE TABLE tde_db.t3 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
54 INSERT INTO t3 (a, b) VALUES (1, REPEAT('a', 6*512*512));
55 SELECT a,LEFT(b,10) FROM tde_db.t3;
56 CREATE TABLE tde_db.t4 (a BIGINT PRIMARY KEY, b LONGBLOB)
57 ENCRYPTION='Y' ENGINE=InnoDB;
58 INSERT INTO t4 (a, b) VALUES (1, REPEAT('a', 6*512*512));
59 SELECT a,LEFT(b,10) FROM tde_db.t4;
60 FLUSH LOGS;
61 --let $restart_parameters = restart:
62 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
63 --replace_regex /\.dll/.so/
64 --source include/restart_mysqld.inc
65
66
67 SELECT
68 PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE
69 FROM INFORMATION_SCHEMA.PLUGINS
70 WHERE PLUGIN_NAME LIKE '%keyring_file%' ;
71 SELECT a,LEFT(b,10) FROM tde_db.t1;
72 SELECT a,LEFT(b,10) FROM tde_db.t2;
73 SELECT a,LEFT(b,10) FROM tde_db.t3;
74 SELECT a,LEFT(b,10) FROM tde_db.t4;
75 #UNINSTALL PLUGIN keyring_file;
76 DROP TABLE tde_db.t1,tde_db.t2,tde_db.t3,tde_db.t4;
77
78 # innodb_redo_log_encrypt=ON/OFF with transaction
79 SELECT @@global.innodb_redo_log_encrypt ;
80 CREATE TABLE tde_db.t1 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
81 CREATE TABLE tde_db.t2 (a BIGINT PRIMARY KEY, b LONGBLOB)
82 ENCRYPTION='Y' ENGINE=InnoDB;
83 START TRANSACTION;
84 SET GLOBAL innodb_redo_log_encrypt = 1;
85 INSERT INTO t1 (a, b) VALUES (1, REPEAT('a', 6*512*512));
86 INSERT INTO t2 (a, b) VALUES (1, REPEAT('a', 6*512*512));
87 SELECT a,LEFT(b,10) FROM tde_db.t1;
88 SELECT a,LEFT(b,10) FROM tde_db.t2;
89 ROLLBACK;
90 START TRANSACTION;
91 INSERT INTO t1 (a, b) VALUES (2, REPEAT('a', 6*512*512));
92 INSERT INTO t2 (a, b) VALUES (2, REPEAT('a', 6*512*512));
93 SELECT a,LEFT(b,10) FROM tde_db.t1;
94 SELECT a,LEFT(b,10) FROM tde_db.t2;
95 COMMIT;
96 CREATE TABLE tde_db.t3 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
97 CREATE TABLE tde_db.t4 (a BIGINT PRIMARY KEY, b LONGBLOB)
98 ENCRYPTION='Y' ENGINE=InnoDB;
99 START TRANSACTION;
100 SET GLOBAL innodb_redo_log_encrypt = 0;
101 SELECT @@global.innodb_redo_log_encrypt ;
102 INSERT INTO t3 (a, b) VALUES (1, REPEAT('a', 6*512*512));
103 INSERT INTO t4 (a, b) VALUES (1, REPEAT('a', 6*512*512));
104 SELECT a,LEFT(b,10) FROM tde_db.t3;
105 SELECT a,LEFT(b,10) FROM tde_db.t4;
106 ROLLBACK;
107 START TRANSACTION;
108 INSERT INTO t3 (a, b) VALUES (2, REPEAT('a', 6*512*512));
109 INSERT INTO t4 (a, b) VALUES (2, REPEAT('a', 6*512*512));
110 SELECT a,LEFT(b,10) FROM tde_db.t3;
111 SELECT a,LEFT(b,10) FROM tde_db.t4;
112 COMMIT;
113 --let $restart_parameters = restart:
114 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
115 --replace_regex /\.dll/.so/
116 --source include/restart_mysqld.inc
117
118
119 SELECT
120 PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE
121 FROM INFORMATION_SCHEMA.PLUGINS
122 WHERE PLUGIN_NAME LIKE '%keyring_file%' ;
123 SELECT a,LEFT(b,10) FROM tde_db.t1;
124 SELECT a,LEFT(b,10) FROM tde_db.t2;
125 SELECT a,LEFT(b,10) FROM tde_db.t3;
126 SELECT a,LEFT(b,10) FROM tde_db.t4;
127
128 # Rotate master key when innodb_redo_log_encrypt=ON/OFF
129 SET GLOBAL innodb_redo_log_encrypt = 0;
130 SELECT @@global.innodb_redo_log_encrypt ;
131 ALTER INSTANCE ROTATE INNODB MASTER KEY;
132 SELECT a,LEFT(b,10) FROM tde_db.t1;
133 SELECT a,LEFT(b,10) FROM tde_db.t2;
134 SELECT a,LEFT(b,10) FROM tde_db.t3;
135 SELECT a,LEFT(b,10) FROM tde_db.t4;
136 SET GLOBAL innodb_redo_log_encrypt = 1;
137 SELECT @@global.innodb_redo_log_encrypt ;
138 ALTER INSTANCE ROTATE INNODB MASTER KEY;
139 SELECT a,LEFT(b,10) FROM tde_db.t1;
140 SELECT a,LEFT(b,10) FROM tde_db.t2;
141 SELECT a,LEFT(b,10) FROM tde_db.t3;
142 SELECT a,LEFT(b,10) FROM tde_db.t4;
143
144 # Non priviledge user can not set innodb_redo_log_encrypt variable.
145 CREATE USER encryptnonprivuser@localhost IDENTIFIED BY 'noauth';
146 GRANT SELECT ON *.* to encryptnonprivuser@localhost;
147 FLUSH PRIVILEGES;
148 --echo # In connection 1 - with encryptnonprivuser
149 connect (con1,localhost,encryptnonprivuser,'noauth',);
150 SELECT @@global.innodb_redo_log_encrypt ;
151 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
152 SET GLOBAL innodb_redo_log_encrypt = 0;
153 SELECT @@global.innodb_redo_log_encrypt ;
154 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
155 SET GLOBAL innodb_undo_log_encrypt = 0;
156 SELECT @@global.innodb_undo_log_encrypt ;
157 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
158 SET GLOBAL innodb_redo_log_encrypt = 1;
159 SELECT @@global.innodb_redo_log_encrypt ;
160 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
161 SET GLOBAL innodb_undo_log_encrypt = 1;
162 SELECT @@global.innodb_undo_log_encrypt ;
163 disconnect con1;
164 --echo # In connection default
165 connection default;
166
167 DROP TABLE tde_db.t1,tde_db.t2,tde_db.t3,tde_db.t4;
168
169 # Cleanup
170 --disable_query_log
171 eval SET GLOBAL innodb_redo_log_encrypt=$old_innodb_redo_log_encrypt;
172 DROP USER encryptnonprivuser@localhost;
173 DROP DATABASE tde_db;
174 --enable_query_log