Debian Codegraph @

mysql-test/suite/rpl/t/rpl_alter_user.test

raw
1 #
2 # === Purpose ===
3 # This test script verifies that in a replication environment,
4 # the slave correctly parses the ALTER USER or SET PASSWORD query.
5 #
6 # === Bug#20228478: ON REPLICATION SLAVE, ALTER USER FAILING FOR USER
7 # WITH SHA256_PASSWORD PLUGIN
8 # It verifies that the slave doesn't encounter an error in parsing a hash
9 # string which contains a single quote. The fix ensures escaping the hash
10 # string correctly depending on the server mode. Since this issue is sporadic,
11 # we have used a debug flag to deterministically generate a hash string which
12 # contains a single quote.
13 #
14 # === WL#11544: Current password required for SET PASSWORD
15 # It verifies that binlog gets the "PASSWORD REQUIRE CURRENT" only if they are
16 # explicitly specified in the ALTER USER statement. It also verifies that
17 # REPLACE clause is never replicated to the binlog
18
19 --source include/not_have_privilege_checks_user.inc
20 --source include/master-slave.inc
21 --source include/have_debug.inc
22
23 #Scenario 1:
24 # When NO_BACKSLASH_ESCAPES mode is not set on both master and the slave.
25 SET @old_sql_mode_master= @@session.sql_mode;
26 SET @@session.sql_mode= (select replace(@@session.sql_mode,'NO_BACKSLASH_ESCAPES',''));
27
28 --connection slave
29 SET @old_sql_mode_slave= @@session.sql_mode;
30 SET @@session.sql_mode= (select replace(@@session.sql_mode,'NO_BACKSLASH_ESCAPES',''));
31
32 --connection master
33 SET GLOBAL DEBUG= '+d,force_hash_string_with_quote';
34 CREATE USER 'user1'@'localhost' IDENTIFIED WITH sha256_password BY 'auth_string1';
35 # Save master position
36 --let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
37 SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret';
38 # Check the binlog contents on the master
39 --let $binlog_start= $saved_master_pos
40 --let $binlog_limit= 2
41 if (`SELECT @@session.binlog_transaction_compression = TRUE`)
42 {
43 --let $binlog_limit= 3
44 }
45 --source include/show_binlog_events.inc
46 # Check the binlog contents on the slave
47 --source include/sync_slave_sql_with_master.inc
48 # slave's GTID log events are 7 bytes larger than the same events on master
49 --let $binlog_start= `SELECT $saved_master_pos + 7`
50 --source include/show_binlog_events.inc
51 # Reset the flag
52 --connection master
53 SET GLOBAL DEBUG= '-d,force_hash_string_with_quote';
54
55 # Scenario 2:
56 # Make sure that we are successfully able to login to the slave from a
57 # user whose password is changed and NO_BACKSLASH_ESCAPES mode is not
58 # set on both master and slave. The hash string generated here may or
59 # may not contain a single quote within it.
60 --connection master
61 SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret1';
62 --source include/sync_slave_sql_with_master.inc
63 --connect(con1, localhost, user1,'auth_secret1',,,,SSL)
64 SELECT USER();
65 disconnect con1;
66
67 # Scenario 3:
68 # When NO_BACKSLASH_ESCAPES mode is set on both master and the slave.
69 --connection master
70 SET GLOBAL DEBUG= '+d,force_hash_string_with_quote';
71 SET @@session.sql_mode= 'NO_BACKSLASH_ESCAPES';
72 --connection slave
73 SET @@session.sql_mode= 'NO_BACKSLASH_ESCAPES';
74 --connection master
75 # Save master position
76 --let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
77 SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret';
78 # Check the binlog contents on the master
79 --let $binlog_start= $saved_master_pos
80 --let $binlog_limit= 2
81 if (`SELECT @@session.binlog_transaction_compression = TRUE`)
82 {
83 --let $binlog_limit= 3
84 }
85 --source include/show_binlog_events.inc
86 # Check the binlog contents on the slave
87 --source include/sync_slave_sql_with_master.inc
88 # slave's GTID log events are 7 bytes larger than the same events on master
89 --let $binlog_start= `SELECT $saved_master_pos + 21`
90 --source include/show_binlog_events.inc
91 # Reset the flag
92 --connection master
93 SET GLOBAL DEBUG= '-d,force_hash_string_with_quote';
94
95 # Scenario 4:
96 # Make sure that we are successfully able to login to the slave
97 # after the password is changed for a user and NO_BACKSLASH_ESCAPES
98 # mode is set on both master and slave. This scenario will generate
99 # the hash string which may or may not contain a single quote.
100 SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret2';
101 --source include/sync_slave_sql_with_master.inc
102 --connect(con1, localhost, user1,'auth_secret2',,,,SSL)
103 SELECT USER();
104 disconnect con1;
105
106 --echo #
107 --echo # WL#11544: Current password required for SET PASSWORD
108 --echo # Create users with current password require clauses.
109 --echo #
110
111 --connection slave
112 --let $saved_master_relay_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
113
114 --connection master
115 --echo [connection master]
116 # Save master position
117 --let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
118 CREATE USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;
119 ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;
120 ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT;
121 SET PASSWORD FOR '11544_u1'@'localhost'='haha';
122 ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'hehe';
123
124 connect(11544_u1_con_master, localhost, 11544_u1, hehe,,,,SSL);
125 connection 11544_u1_con_master;
126 --echo #
127 --echo # To check that failed statements are not replicated to binlog
128 --echo #
129 --error ER_MISSING_CURRENT_PASSWORD
130 SET PASSWORD ='ahaha';
131 --error ER_MISSING_CURRENT_PASSWORD
132 SET PASSWORD FOR '11544_u1'@'localhost'='ahaha';
133 --error ER_MISSING_CURRENT_PASSWORD
134 ALTER USER user() IDENTIFIED BY 'ahaha';
135 --error ER_MISSING_CURRENT_PASSWORD
136 ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'ahaha';
137 --error ER_INCORRECT_CURRENT_PASSWORD
138 SET PASSWORD='ahaha' REPLACE 'xyz';
139 --error ER_INCORRECT_CURRENT_PASSWORD
140 SET PASSWORD FOR '11544_u1'@'localhost'='ahaha' REPLACE 'xyz';
141 --error ER_INCORRECT_CURRENT_PASSWORD
142 ALTER USER user() IDENTIFIED BY 'ahaha' REPLACE 'xyz';
143 --error ER_INCORRECT_CURRENT_PASSWORD
144 ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'ahaha' REPLACE 'xyz';
145 --echo #
146 --echo # To check that REPLACE clause is not reflected in the binlog
147 --echo #
148 SET PASSWORD='ahaha' REPLACE 'hehe';
149 SET PASSWORD FOR '11544_u1'@'localhost'='hehe' REPLACE 'ahaha';
150 ALTER USER user() IDENTIFIED BY 'ahaha' REPLACE 'hehe';
151 ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'hehe' REPLACE 'ahaha';
152 --connection master
153 disconnect 11544_u1_con_master;
154
155 --echo # Check the binlog contents on the master
156 --let $binlog_start= $saved_master_pos
157 --let $binlog_limit= 100
158 --let $mask_user_password_events=1
159 --source include/show_binlog_events.inc
160 --let $mask_user_password_events=0
161
162 --echo # Check the binlog contents on the slave
163 --source include/sync_slave_sql_with_master.inc
164 --echo [connection slave]
165 --let $binlog_start= $saved_master_relay_pos
166 --let $binlog_limit= 100
167 --let $mask_user_password_events=1
168 --source include/show_binlog_events.inc
169 --let $mask_user_password_events=0
170
171 --echo # Check that we are successfully able to login to the slave from
172 --echo # the user whose password is changed on master
173 connect(11544_u1_con_slave, localhost, 11544_u1, hehe,,,,SSL);
174 SELECT USER();
175 --connection master
176 --echo [connection master]
177 DROP USER '11544_u1'@'localhost';
178
179
180 #
181 # WL11772 CREATE USER WITH RANDOM PASSWORD
182 #
183 #
184 --let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
185
186 --connection master
187 --echo * Create a new user with random password
188 --let $sql= "CREATE USER u1@localhost IDENTIFIED BY RANDOM PASSWORD"
189 --let $passwd= query_get_value( $sql, "generated password", 1)
190 --source include/sync_slave_sql_with_master.inc
191 --connect(slave_con1, localhost, u1, $passwd, test, $SLAVE_MYPORT,,SSL)
192 --echo [connection slave]
193 SELECT CURRENT_USER();
194 --connection master
195 --echo [connection master]
196 --disconnect slave_con1
197 --echo * Set random password using SET PASSWORD
198 --let $sql= "SET PASSWORD FOR u1@localhost TO RANDOM"
199 --let $passwd= query_get_value($sql, "generated password", 1)
200 --source include/sync_slave_sql_with_master.inc
201 --connect(slave_con1, localhost, u1, $passwd, test, $SLAVE_MYPORT,,SSL)
202 --echo [connection slave]
203 SELECT CURRENT_USER();
204 --connection master
205 --echo [connection master]
206 --disconnect slave_con1
207 --echo * Set random password using ALTER USER
208 --let $sql= "ALTER USER u1@localhost IDENTIFIED BY RANDOM PASSWORD"
209 --let $passwd= query_get_value($sql, "generated password", 1)
210 --source include/sync_slave_sql_with_master.inc
211 --connect(slave_con1, localhost, u1, $passwd, test, $SLAVE_MYPORT,, SSL)
212 --echo [connection slave]
213 SELECT CURRENT_USER();
214 --connection master
215 --echo [connection master]
216 --disconnect slave_con1
217 DROP USER u1@localhost;
218
219 --echo * Check the binlog contents on the master
220 --let $binlog_start= $saved_master_pos
221 --let $binlog_limit= 100
222 --let $mask_user_password_events=1
223 --source include/show_binlog_events.inc
224 --let $mask_user_password_events=0
225
226
227 #
228 # WL13562 ARBITRARY JSON ATTRIBUTES
229 #
230 --let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
231 --connection master
232 --echo * Create a new user with an arbitrary attribute
233 CREATE USER u1@localhost IDENTIFIED BY 'foo' ATTRIBUTE '{"trackingId": "12345"}';
234 CREATE USER u2@localhost COMMENT 'This is account is used by my private LAMP project';
235 --source include/sync_slave_sql_with_master.inc
236 --connect(slave_con1, localhost, root,, test, $SLAVE_MYPORT,,SSL)
237 --echo [connection slave]
238 SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u1' OR USER = 'u2';
239 --connection master
240 --echo [connection master]
241 --disconnect slave_con1
242 ALTER USER u1@localhost COMMENT 'TODO: Delete this user';
243 --source include/sync_slave_sql_with_master.inc
244 --connect(slave_con1, localhost, root,, test, $SLAVE_MYPORT,,SSL)
245 --echo [connection slave]
246 SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u1';
247 --let $comment = `SELECT JSON_EXTRACT(ATTRIBUTE, "$.comment") FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER= 'u1'`
248 --let $assert_text = User JSON comment updated successfully
249 --let $assert_cond = $comment = "TODO: Delete this user"
250 --source include/assert.inc
251 --let $comment = `SELECT JSON_EXTRACT(ATTRIBUTE, "$.trackingId") FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER= 'u1'`
252 --let $assert_text = User JSON attribute updated successfully
253 --let $assert_cond = $comment = "12345"
254 --source include/assert.inc
255
256 --connection master
257 --echo [connection master]
258 --disconnect slave_con1
259 ALTER USER u2@localhost ATTRIBUTE '{ "code": "red" }';
260 ALTER USER u2@localhost COMMENT 'Change of mind';
261 --source include/sync_slave_sql_with_master.inc
262 --connect(slave_con1, localhost, root,, test, $SLAVE_MYPORT,, SSL)
263 --echo [connection slave]
264 SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u2';
265 --let $comment = `SELECT JSON_EXTRACT(ATTRIBUTE, "$.comment") FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER= 'u2'`
266 --let $assert_text = User JSON comment updated successfully
267 --let $assert_cond = $comment = "Change of mind"
268 --source include/assert.inc
269
270 --let $comment = `SELECT JSON_EXTRACT(ATTRIBUTE, "$.code") FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER= 'u2'`
271 --let $assert_text = User JSON attribute updated successfully
272 --let $assert_cond = $comment = "red"
273 --source include/assert.inc
274
275 --connection master
276 --echo [connection master]
277 --disconnect slave_con1
278 DROP USER u1@localhost;
279 DROP USER u2@localhost;
280
281 --echo * Check the binlog contents on the master
282 --let $binlog_start= $saved_master_pos
283 --let $binlog_limit= 100
284 --let $mask_user_password_events=1
285 --source include/show_binlog_events.inc
286 --let $mask_user_password_events=0
287
288 #Restore the old sql_mode at the master and the slave
289 --connection slave
290 SET @@session.sql_mode= @old_sql_mode_slave;
291 --connection master
292 SET @@session.sql_mode= @old_sql_mode_master;
293
294 #cleanup
295 DROP USER 'user1'@'localhost';
296 #--let $rpl_debug= 1
297 --source include/rpl_end.inc