Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/rpl_do_grant.test

raw
1 # Works in statement-based and row-based binlogging.
2 # Test that GRANT and other user management commands are replicated to the slave
3
4 --source include/not_group_replication_plugin.inc
5 --source include/no_ps_protocol.inc
6 --source include/set_privilege_checks_user_as_system_user.inc
7 --let $rpl_privilege_checks_user_grant_option = 1
8 --source include/master-slave.inc
9
10 # do not be influenced by other tests.
11 connection master;
12 call mtr.add_suppression("REVOKE/GRANT failed while storing routine level "
13 "grants in the privilege tables. An incident event "
14 "has been written to the binary log which will "
15 "stop the slaves.");
16 delete from mysql.user where user=_binary'rpl_do_grant';
17 delete from mysql.db where user=_binary'rpl_do_grant';
18 flush privileges;
19 sync_slave_with_master;
20 # if these DELETE did nothing on the master, we need to do them manually on the
21 # slave.
22 delete from mysql.user where user=_binary'rpl_ignore_grant';
23 delete from mysql.db where user=_binary'rpl_ignore_grant';
24 flush privileges;
25
26 # test replication of GRANT
27 connection master;
28 create user rpl_do_grant@localhost;
29 grant select on *.* to rpl_do_grant@localhost;
30 grant drop on test.* to rpl_do_grant@localhost;
31 sync_slave_with_master;
32 show grants for rpl_do_grant@localhost;
33
34 # test replication of SET PASSWORD
35 connection master;
36 set password for rpl_do_grant@localhost="does it work?";
37 sync_slave_with_master;
38 select authentication_string<>_binary'' from mysql.user where user=_binary'rpl_do_grant';
39
40 #
41 # Bug#24158 SET PASSWORD in binary log fails under ANSI_QUOTES
42 #
43 connection master;
44 update mysql.user set authentication_string='' where user='rpl_do_grant';
45 flush privileges;
46 select authentication_string<>'' from mysql.user where user='rpl_do_grant';
47 set sql_mode='ANSI_QUOTES';
48 set password for rpl_do_grant@localhost='does it work?';
49 set sql_mode='';
50 sync_slave_with_master;
51 select authentication_string<>'' from mysql.user where user='rpl_do_grant';
52
53
54 # clear what we have done, to not influence other tests.
55 connection master;
56 delete from mysql.user where user=_binary'rpl_do_grant';
57 delete from mysql.db where user=_binary'rpl_do_grant';
58 flush privileges;
59 sync_slave_with_master;
60 # The mysql database is not replicated, so we have to do the deletes
61 # manually on the slave as well.
62 delete from mysql.user where user=_binary'rpl_do_grant';
63 delete from mysql.db where user=_binary'rpl_do_grant';
64 flush privileges;
65
66 # End of 4.1 tests
67
68 connection master;
69 --error 1141
70 show grants for rpl_do_grant@localhost;
71 connection slave;
72 --error 1141
73 show grants for rpl_do_grant@localhost;
74
75 connection master;
76 create user rpl_do_grant@localhost;
77 show grants for rpl_do_grant@localhost;
78 --error 1141
79 show grants for rpl_do_grant2@localhost;
80 sync_slave_with_master;
81 show grants for rpl_do_grant@localhost;
82 --error 1141
83 show grants for rpl_do_grant2@localhost;
84
85 connection master;
86 rename user rpl_do_grant@localhost to rpl_do_grant2@localhost;
87 show grants for rpl_do_grant2@localhost;
88 sync_slave_with_master;
89 show grants for rpl_do_grant2@localhost;
90
91 connection master;
92 grant DELETE,INSERT on mysqltest1.* to rpl_do_grant2@localhost;
93 show grants for rpl_do_grant2@localhost;
94 sync_slave_with_master;
95 show grants for rpl_do_grant2@localhost;
96
97 connection master;
98 revoke DELETE on mysqltest1.* from rpl_do_grant2@localhost;
99 show grants for rpl_do_grant2@localhost;
100 sync_slave_with_master;
101 show grants for rpl_do_grant2@localhost;
102
103 connection master;
104 revoke all privileges, grant option from rpl_do_grant2@localhost;
105 show grants for rpl_do_grant2@localhost;
106 sync_slave_with_master;
107 show grants for rpl_do_grant2@localhost;
108
109 connection master;
110 drop user rpl_do_grant2@localhost;
111 --error 1141
112 show grants for rpl_do_grant2@localhost;
113 sync_slave_with_master;
114 --error 1141
115 show grants for rpl_do_grant2@localhost;
116
117 #####################################################
118 # Purpose
119 # Test whether mysql.procs_priv get replicated
120 # Related bugs:
121 # BUG42217 mysql.procs_priv does not get replicated
122 #####################################################
123 connection master;
124 call mtr.add_suppression("Replica: Operation DROP USER failed for 'create_rout_db'@'localhost' Error_code: 1396");
125 sync_slave_with_master;
126 connection master;
127
128 --disable_warnings
129 DROP DATABASE IF EXISTS bug42217_db;
130 --enable_warnings
131 CREATE DATABASE bug42217_db;
132
133 CREATE USER 'create_rout_db'@'localhost' IDENTIFIED BY 'create_rout_db';
134 GRANT CREATE ROUTINE ON bug42217_db.* TO 'create_rout_db'@'localhost'
135 WITH GRANT OPTION;
136
137 -- sync_slave_with_master
138 -- connection master
139
140 connect (create_rout_db_master, localhost, create_rout_db, create_rout_db, bug42217_db,$MASTER_MYPORT,);
141 connect (create_rout_db_slave, localhost, create_rout_db, create_rout_db, bug42217_db, $SLAVE_MYPORT,);
142
143 connection create_rout_db_master;
144
145
146 USE bug42217_db;
147
148 DELIMITER //;
149 CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30)
150 BEGIN
151 RETURN "INSIDE upgrade_del_func()";
152 END//
153
154 DELIMITER ;//
155
156 connection master;
157
158 USE bug42217_db;
159 --replace_column 8 #
160 SELECT * FROM mysql.procs_priv;
161 SELECT upgrade_del_func();
162
163 sync_slave_with_master;
164 FLUSH PRIVILEGES;
165 FLUSH TABLES;
166 --let $wait_condition= SELECT COUNT(*)=1 FROM mysql.procs_priv WHERE host='localhost';
167 --source include/wait_condition.inc
168 --replace_column 8 #
169 SELECT * FROM mysql.procs_priv;
170 SHOW GRANTS FOR 'create_rout_db'@'localhost';
171
172 USE bug42217_db;
173 SHOW CREATE FUNCTION upgrade_del_func;
174 SELECT upgrade_del_func();
175
176 --echo "Check whether the definer user will be able to execute the replicated routine on slave"
177 connection create_rout_db_slave;
178 USE bug42217_db;
179 SHOW CREATE FUNCTION upgrade_del_func;
180 SELECT upgrade_del_func();
181
182 connection slave;
183 DELETE FROM mysql.procs_priv;
184 FLUSH PRIVILEGES;
185 USE bug42217_db;
186 --echo "Can't execute the replicated routine on slave like before after procs privilege is deleted "
187 --error 1370
188 SELECT upgrade_del_func();
189
190 --echo "Test the user who creates a function on master doesn't exist on slave."
191 --echo "Hence SQL thread ACL_GLOBAL privilege jumps in and no mysql.procs_priv is inserted"
192 DROP USER 'create_rout_db'@'localhost';
193
194 connection create_rout_db_master;
195 DELIMITER //;
196 CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(30)
197 BEGIN
198 RETURN "INSIDE upgrade_alter_func()";
199 END//
200 DELIMITER ;//
201
202 connection master;
203 SELECT upgrade_alter_func();
204
205 sync_slave_with_master;
206 SHOW CREATE FUNCTION upgrade_alter_func;
207 --echo "Should no privilege record for upgrade_alter_func in mysql.procs_priv"
208 --replace_column 8 #
209 SELECT * FROM mysql.procs_priv;
210 --error 1449
211 SELECT upgrade_alter_func();
212
213 ###### CLEAN UP SECTION ##############
214 disconnect create_rout_db_master;
215 disconnect create_rout_db_slave;
216 connection master;
217 USE bug42217_db;
218 DROP FUNCTION upgrade_del_func;
219 DROP FUNCTION upgrade_alter_func;
220 DROP DATABASE bug42217_db;
221 -- sync_slave_with_master
222 -- connection master
223
224 # user was already dropped in the slave before
225 # so we should not replicate this statement.
226 SET SQL_LOG_BIN= 0;
227 DROP USER 'create_rout_db'@'localhost';
228 SET SQL_LOG_BIN= 1;
229
230 # finish entire clean up (remove binlogs)
231 # so that we leave a pristine environment for the
232 # following tests
233 --let $rpl_only_running_threads= 1
234 -- source include/rpl_reset.inc
235 USE test;
236
237 # BUG#49119: Master crashes when executing 'REVOKE ... ON
238 # {PROCEDURE|FUNCTION} FROM ...'
239 #
240 # The tests are divided into two test cases:
241 #
242 # i) a test case that mimics the one in the bug report.
243 #
244 # - We show that, despite the fact, that a revoke command fails
245 # when binlogging is active, the master will not hit an
246 # assertion.
247 #
248 # ii) a test case that partially succeeds on the master will also
249 # partially succeed on the slave.
250 #
251 # - The revoke statement that partially succeeds tries to revoke
252 # an EXECUTE grant for two users, and only one of the user has
253 # the specific grant. This will cause mysql to drop one of the
254 # grants and report error for the statement. The slave should
255 # also drop the grants that the master succeed and the SQL
256 # thread should not stop on statement failure.
257
258 -- echo ######## BUG#49119 #######
259 -- echo ### i) test case from the 'how to repeat section'
260
261 -- connection master
262
263 CREATE TABLE t1(c1 INT);
264 DELIMITER |;
265 CREATE PROCEDURE p1() SELECT * FROM t1 |
266 DELIMITER ;|
267 -- error ER_NONEXISTING_PROC_GRANT
268 REVOKE EXECUTE ON PROCEDURE p1 FROM 'root'@'localhost';
269 -- sync_slave_with_master
270
271 -- connection master
272 DROP TABLE t1;
273 DROP PROCEDURE p1;
274
275 -- sync_slave_with_master
276
277 -- echo ### ii) Test case in which REVOKE partially succeeds
278
279 -- connection master
280 -- source include/rpl_reset.inc
281 -- connection master
282
283 CREATE TABLE t1(c1 INT);
284 DELIMITER |;
285 CREATE PROCEDURE p1() SELECT * FROM t1 |
286 DELIMITER ;|
287
288 CREATE USER 'user49119'@'localhost';
289 GRANT EXECUTE ON PROCEDURE p1 TO 'user49119'@'localhost';
290
291 -- echo ##############################################################
292 -- echo ### Showing grants for both users: root and user49119 (master)
293 SHOW GRANTS FOR 'user49119'@'localhost';
294 SHOW GRANTS FOR CURRENT_USER;
295 -- echo ##############################################################
296
297 -- sync_slave_with_master
298
299 -- echo ##############################################################
300 -- echo ### Showing grants for both users: root and user49119 (master)
301 SHOW GRANTS FOR 'user49119'@'localhost';
302 SHOW GRANTS FOR CURRENT_USER;
303 -- echo ##############################################################
304
305 -- connection master
306
307 -- echo ## This statement will make the revoke fail because root has no
308 -- echo ## execute grant. However, it will still revoke the grant for
309 -- echo ## user49119.
310 -- error ER_NONEXISTING_PROC_GRANT
311 REVOKE EXECUTE ON PROCEDURE p1 FROM 'user49119'@'localhost', 'root'@'localhost';
312
313 -- echo ##############################################################
314 -- echo ### Showing grants for both users: root and user49119 (master)
315 -- echo ### after revoke statement failure
316 SHOW GRANTS FOR 'user49119'@'localhost';
317 SHOW GRANTS FOR CURRENT_USER;
318 -- echo ##############################################################
319
320 -- connection master
321 -- sync_slave_with_master
322
323 -- echo #############################################################
324 -- echo ### Showing grants for both users: root and user49119 (slave)
325 -- echo ### after revoke statement failure (should match
326 SHOW GRANTS FOR 'user49119'@'localhost';
327 SHOW GRANTS FOR CURRENT_USER;
328 -- echo ##############################################################
329
330 -- connection master
331 DROP TABLE t1;
332 DROP PROCEDURE p1;
333 DROP USER 'user49119'@'localhost';
334
335 -- sync_slave_with_master
336
337 #
338 # Bug #51987 revoke privileges logs wrong error code
339 #
340
341 -- source include/rpl_reset.inc
342 -- connection master
343
344 create user foo@"1.2.3.4";
345 grant all on *.* to foo@"1.2.3.4";
346 -- error ER_REVOKE_GRANTS
347 revoke all privileges, grant option from "foo";
348
349 ## assertion: revoke is logged
350 -- source include/show_binlog_events.inc
351 -- sync_slave_with_master
352
353 -- connection master
354 DROP USER foo@"1.2.3.4";
355 -- sync_slave_with_master
356
357 --echo
358 --echo # Bug#27606 GRANT statement should be replicated with DEFINER information
359 --source include/rpl_reset.inc
360 --connection master
361 CREATE USER user_bug27606@localhost;
362 GRANT SELECT, INSERT ON mysql.user TO user_bug27606@localhost;
363
364 SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
365 sync_slave_with_master;
366 SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
367
368 --connection master
369 REVOKE SELECT ON mysql.user FROM user_bug27606@localhost;
370 SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
371 sync_slave_with_master;
372 SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
373
374 --connection master
375 DROP USER user_bug27606@localhost;
376
377 --source include/rpl_end.inc