Debian Codegraph @

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