Debian Codegraph @

mysql-test/suite/auth_sec/t/key_rotation_qa.test

raw
1 # Wl8821 Testing
2 # This test will will check parser support for
3 # ALTER INSTANCE ROTATE <key_type> MASTER KEY statement
4 # and key rotation testing.
5
6 --source include/no_valgrind_without_big.inc
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("\\[ERROR\\] .*MY-\\d+.* Plugin keyring_file reported: 'keyring_file initialization failure.");
10 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Function 'keyring_file' already exists");
11 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Couldn't load plugin named 'keyring_file' with soname 'keyring_file.so'.");
12 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Couldn't load plugin named 'keyring_file' with soname 'keyring_file.dll'.");
13 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Can't generate new master key, please check the keyring is loaded.");
14 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Encryption information in datafile");
15 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Operating system error number 2 in a file operation.");
16 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* The error means the system cannot find the path specified.");
17 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Could not find a valid tablespace file for");
18 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.");
19 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Can't generate new master key for tablespace encryption, please check the keyring is loaded.");
20 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Ignoring tablespace `test/.*` because it could not be opened.");
21 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Failed to find tablespace for table");
22 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Can't read encryption key from file");
23 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Table .* in the InnoDB data dictionary has tablespace id .*, but tablespace with that id or name does not exist");
24 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Please refer to .* for how to resolve the issue");
25 call mtr.add_suppression("Could not flush keys to keyring's backup");
26 call mtr.add_suppression("\\[ERROR\\].* Tablespace [0-9]+, name 'test.t[0-9]+', unable to open file '.*test.t[0-9]+.ibd' - Data structure corruption");
27 call mtr.add_suppression("Plugin mysqlx reported: 'Failed at SSL configuration");
28 --enable_query_log
29
30 # Invalid syntax
31 --error ER_PARSE_ERROR
32 ALTER INSTANCE ROTATE MYISAM MASTER KEY;
33 --error ER_PARSE_ERROR
34 ALTER INSTANCE ROTATE INNODB;
35 --error ER_PARSE_ERROR
36 ALTER INSTANCE STORE INNDB;
37 --error ER_PARSE_ERROR
38 ALTER INSTANCE ROTATE INNODB SLAVE KEY;
39
40 # When keyring_file plugin not loaded.
41 --error ER_CANNOT_FIND_KEY_IN_KEYRING
42 ALTER INSTANCE ROTATE INNODB MASTER KEY;
43
44 --error 0,1
45 --remove_file $MYSQL_TMP_DIR/keyring
46
47 --echo
48 # Re-starting mysql server with keyring_file plugin.
49 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
50 -- send_shutdown
51 -- source include/wait_until_disconnected.inc
52 --exec echo "restart:--early-plugin-load="keyring_file=$KEYRING_PLUGIN" --keyring_file_data=$MYSQL_TMP_DIR/keyring/keyring" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
53 --enable_reconnect
54 --source include/wait_until_connected_again.inc
55 --disable_reconnect
56
57 --error ER_UDF_EXISTS
58 INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';
59
60 --echo
61 SHOW GLOBAL variables LIKE 'early-plugin-load';
62 # Command-Line option.
63 --error ER_PARSE_ERROR
64 SET @@global.early-plugin-load="keyring_file=keyring_file.so";
65
66 --echo
67 # Check keyring_file plugin.
68 query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS
69 FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
70 --file_exists $MYSQL_TMP_DIR/keyring/keyring
71 --echo
72
73 # Roating master key when keyring file is empty.
74 # It should genereate new master key.
75 ALTER INSTANCE ROTATE INNODB MASTER KEY;
76
77 # Creating table with encryption.
78 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
79 INSERT INTO t1 VALUES(0, "aaaaa");
80 INSERT INTO t1 VALUES(1, "bbbbb");
81 INSERT INTO t1 VALUES(2, "ccccc");
82 SELECT * FROM t1;
83
84 ALTER INSTANCE ROTATE INNODB MASTER KEY;
85
86 CREATE TABLE t2(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
87 INSERT INTO t2 SELECT * FROM t1;
88 SELECT COUNT(*) FROM t2;
89
90 --echo # Remove keyring file
91 # If we remove the keyring file and the server
92 # is still running the keyring will stay in the memory
93 # and the encrypted table/s will be accessible.
94 --error 0,1
95 --remove_file $MYSQL_TMP_DIR/keyring/keyring_tmp
96 --copy_file $MYSQL_TMP_DIR/keyring/keyring $MYSQL_TMP_DIR/keyring/keyring_tmp
97 --remove_file $MYSQL_TMP_DIR/keyring/keyring
98 --echo
99 --echo # Check for keyring file.
100 --list_files $MYSQL_TMP_DIR/keyring/
101 --echo
102 SELECT COUNT(*) FROM t2;
103 CREATE TABLE t3(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
104 INSERT INTO t3 VALUES(2, "ccccc");
105
106 --echo # Check for keyring file.
107 --list_files $MYSQL_TMP_DIR/keyring/
108 --echo
109
110 # ALTER INSTANCE ROTATE cmd will fail if keyring file
111 # does not exist
112 --ERROR 3185
113 ALTER INSTANCE ROTATE INNODB MASTER KEY;
114
115 --echo # Check for keyring file.
116 --list_files $MYSQL_TMP_DIR/keyring/
117 --error 1
118 --file_exists $MYSQL_TMP_DIR/keyring/keyring
119 --echo
120 SELECT * FROM t1;
121
122 # Uninstalling keyring_file plugin.
123 UNINSTALL PLUGIN keyring_file;
124 query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS
125 FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
126
127 --echo # Try to access encrypted table.
128 # Cached the tablespace key in memory.
129 SELECT COUNT(*) FROM t1;
130 SELECT * FROM t2;
131
132 CREATE TABLE t10(c1 INT, c2 char(20)) ENGINE = InnoDB;
133
134 # Recreate the keyring file
135 --copy_file $MYSQL_TMP_DIR/keyring/keyring_tmp $MYSQL_TMP_DIR/keyring/keyring
136 --remove_file $MYSQL_TMP_DIR/keyring/keyring_tmp
137
138 # After restart encrypted table/s should be accessible
139 # when using same keyring file.
140 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --keyring_file_data=$MYSQL_TMP_DIR/keyring/keyring;
141 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN keyring_file.so
142 --source include/restart_mysqld.inc
143 --file_exists $MYSQL_TMP_DIR/keyring/keyring
144 SELECT * FROM t1;
145 SELECT * FROM t3;
146 --remove_file $MYSQL_TMP_DIR/keyring/keyring
147
148 # After restart old encrypted tables will not be not accesiable,
149 # if keyring file missing or keyring_file_data location changed.
150 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --keyring_file_data=$MYSQL_TMP_DIR/keyring/keyring_new;
151 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN keyring_file.so
152 --source include/restart_mysqld.inc
153 --error ER_CANNOT_FIND_KEY_IN_KEYRING
154 SELECT * FROM t1;
155
156 CREATE TABLE t12(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
157 INSERT INTO t12 VALUES(2, "ccccchfc");
158
159 ALTER INSTANCE ROTATE INNODB MASTER KEY;
160
161 SELECT * FROM t12;
162 --remove_file $MYSQL_TMP_DIR/keyring/keyring_new
163
164 # Test keyring_file with early-plugin-load and plugin-load.
165 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
166 -- send_shutdown
167 -- source include/wait_until_disconnected.inc
168 --exec echo "restart:--early-plugin-load="keyring_file=$KEYRING_PLUGIN" --plugin-load="keyring_file=$KEYRING_PLUGIN" --keyring_file_data=$MYSQL_TMP_DIR/keyring/keyring" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
169 --enable_reconnect
170 --source include/wait_until_connected_again.inc
171 --disable_reconnect
172
173 query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS
174 FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
175
176 --error ER_CANNOT_FIND_KEY_IN_KEYRING
177 SELECT * FROM t2;
178
179 CREATE TABLE t11(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
180
181 # Table will not be accesiable,since the original key file is removed,
182 # Innodb will mark is as data file missing.
183 --error ER_CANNOT_FIND_KEY_IN_KEYRING
184 SELECT * FROM t2;
185
186 # Uninstalling keyring_file plugin.
187 UNINSTALL PLUGIN keyring_file;
188 query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS
189 FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
190
191 SELECT * FROM t11;
192 # Try to access encrypted table, Which are created before restart.
193 --error ER_CANNOT_FIND_KEY_IN_KEYRING
194 SELECT COUNT(*) FROM t1;
195
196 # Creating table after uninstalling keyring_file plugin.
197 --error ER_CANNOT_FIND_KEY_IN_KEYRING
198 CREATE TABLE t4(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
199
200 # ALTER INSTANCE ROTATE <key_type> MASTER KEY statement,after UNINSTALL.
201 --error ER_CANNOT_FIND_KEY_IN_KEYRING
202 ALTER INSTANCE ROTATE INNODB MASTER KEY;
203
204 # Creating table without encryption.
205 CREATE TABLE t5(c1 INT, c2 char(20)) ENGINE = InnoDB;
206 INSERT INTO t5 VALUES(100, "without_enc");
207
208 # Uninstall will not remove keyring file.
209 --list_files $MYSQL_TMP_DIR/keyring/
210 --file_exists $MYSQL_TMP_DIR/keyring/keyring
211
212 --remove_file $MYSQL_TMP_DIR/keyring/keyring
213
214 # Encrypted table DROP when keyring_file plugin missing/removed.
215 DROP TABLE t11;
216
217 --replace_regex /\.dll/.so/
218 eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN';
219 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
220 eval SET @@global.keyring_file_data='$MYSQL_TMP_DIR/keyring/keyring';
221 query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS
222 FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
223
224 --error ER_CANNOT_FIND_KEY_IN_KEYRING
225 CREATE TABLE t6(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
226
227 --error ER_CANNOT_FIND_KEY_IN_KEYRING
228 ALTER INSTANCE ROTATE INNODB MASTER KEY;
229
230 --error ER_CANNOT_FIND_KEY_IN_KEYRING
231 SELECT * FROM t3;
232 --echo
233 ## Try to access non-encrypted table.
234 SELECT * FROM t5;
235
236 # Cleanup
237 DROP TABLE t1,t2,t3,t5,t12,t10;
238
239 --source include/force_restart.inc
240 --echo #End:
241