Debian Codegraph @

mysql-test/suite/rpl_gtid/t/rpl_current_user.test

raw
1 ##############################################################################
2 # BUG#48321 CURRENT_USER() incorrectly replicated for DROP/RENAME USER,
3 # REVOKE, GRANT, ALTER EVENT
4 #
5 # Calling CURRENT_USER() results into inconsistency between slave and master,
6 # as the slave SQL thread has different user with common users.
7 #
8 # After the patch for bug#48321, session's user will be written into query log
9 # event if CURRENT_USER() is called in 'DROP/RENAME USER', 'REVOKE', 'GRANT',
10 # 'ALTER EVENT'.
11 #
12 ##############################################################################
13
14 source include/have_binlog_format_statement.inc;
15 --source include/not_have_privilege_checks_user.inc
16
17 --let $rpl_topology= 1->2->3
18 --source include/rpl_init.inc
19
20 --let $rpl_connection_name= master
21 --let $rpl_server_number= 1
22 --source include/rpl_connect.inc
23
24 --let $rpl_connection_name= slave
25 --let $rpl_server_number= 2
26 --source include/rpl_connect.inc
27
28 --disable_query_log
29 --connection server_1
30 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*");
31 --connection server_2
32 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*");
33 --connection server_3
34 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*");
35 --enable_query_log
36
37 --connection master
38
39 CREATE TABLE t1(c1 char(100));
40 CREATE VIEW test.v_user AS SELECT * FROM mysql.user WHERE User LIKE 'bug48321%';
41 CREATE VIEW test.v_tables_priv AS SELECT * FROM mysql.tables_priv WHERE User LIKE 'bug48321%';
42 CREATE VIEW test.v_procs_priv AS SELECT * FROM mysql.procs_priv WHERE User LIKE 'bug48321%';
43 CREATE VIEW test.v_event AS SELECT definer FROM information_schema.events WHERE event_name = 'e1';
44 CREATE PROCEDURE p1() SELECT 1;
45 --echo # bug48321_1-01234 has the max length(16) of user.
46 CREATE USER 'bug48321_1-01234'@'localhost';
47 GRANT ALL PRIVILEGES ON *.* TO 'bug48321_1-01234'@'localhost' WITH GRANT OPTION;
48
49 --echo
50 --echo # Make sure the max lengths of user and host
51 --echo # the user name is too lengh
52 --error 1470
53 GRANT CREATE USER ON *.* TO '012345678901234567890123456789012'@'fakehost';
54 --echo # the host name is too lengh
55 --error 1470
56 GRANT CREATE USER ON *.* TO 'fakename'@'host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890X';
57
58 --echo
59 --echo # User 'bug48321_1-01234' connects to master by conn1
60 connect (conn1, 127.0.0.1, 'bug48321_1-01234'@'localhost',,);
61 connection conn1;
62 --echo # [On conn1]
63 --echo # Verify 'REVOKE ALL' statement
64 REVOKE ALL PRIVILEGES, GRANT OPTION FROM CURRENT_USER();
65 --source include/rpl_sync.inc
66 let $diff_tables= server_1:v_user, server_2:v_user, server_3:v_user;
67 source include/diff_tables.inc;
68
69 --echo
70 --echo # Verify 'GRANT ... ON TABLE ...' statement
71 GRANT CREATE, INSERT, SELECT ON TABLE test.t1 TO CURRENT_USER();
72 --source include/rpl_sync.inc
73 let $diff_tables= server_1:v_tables_priv, server_2:v_tables_priv, server_3:v_tables_priv;
74 source include/diff_tables.inc;
75
76 --echo
77 --echo # Verify 'GRANT ... ON PROCEDURE...' statement
78 GRANT ALTER ROUTINE, EXECUTE ON PROCEDURE p1 TO CURRENT_USER();
79 --source include/rpl_sync.inc
80 let $diff_tables= server_1:v_procs_priv, server_2:v_procs_priv, server_3:v_procs_priv;
81 source include/diff_tables.inc;
82
83 --echo
84 --echo # Verify 'GRANT ... ON *.* ...' statement
85 GRANT ALL PRIVILEGES ON *.* TO CURRENT_USER() WITH GRANT OPTION;
86 --source include/rpl_sync.inc
87 let $diff_tables= server_1:v_procs_priv, server_2:v_procs_priv, server_3:v_procs_priv;
88 source include/diff_tables.inc;
89
90 --echo
91 --echo # Verify 'REVOKE ... ON TABLE ...' statement
92 REVOKE CREATE, INSERT, SELECT ON TABLE t1 FROM CURRENT_USER();
93 --source include/rpl_sync.inc
94 let $diff_tables= server_1:v_tables_priv, server_2:v_tables_priv, server_3:v_tables_priv;
95 source include/diff_tables.inc;
96
97 --echo
98 --echo # Verify 'REVOKE ... ON PROCEDURE...' statement
99 REVOKE ALTER ROUTINE, EXECUTE ON PROCEDURE p1 FROM CURRENT_USER();
100 --source include/rpl_sync.inc
101 let $diff_tables= server_1:v_procs_priv, server_2:v_procs_priv, server_3:v_procs_priv;
102 source include/diff_tables.inc;
103
104 --echo
105 --echo # Verify 'REVOKE ... ON *.* ...' statement
106 REVOKE ALL PRIVILEGES ON *.* FROM CURRENT_USER();
107 --source include/rpl_sync.inc
108 let $diff_tables= server_1:v_user, server_2:v_user, server_3:v_user;
109 source include/diff_tables.inc;
110
111 --echo
112 --echo # Verify 'GRANT ...' statement in the procedure
113 CREATE PROCEDURE my_grant()
114 GRANT CREATE, INSERT, SELECT ON TABLE test.t1 TO CURRENT_USER();
115 call my_grant;
116 --source include/rpl_sync.inc
117 let $diff_tables= server_1:v_tables_priv, server_2:v_tables_priv, server_3:v_tables_priv;
118 source include/diff_tables.inc;
119
120 --echo
121 --echo # Verify 'REVOKE ... ON TABLE ...' statement in the procedure
122 CREATE PROCEDURE my_revoke()
123 REVOKE CREATE, INSERT, SELECT ON TABLE t1 FROM CURRENT_USER();
124 call my_revoke;
125 --source include/rpl_sync.inc
126 let $diff_tables= server_1:v_tables_priv, server_2:v_tables_priv, server_3:v_tables_priv;
127 source include/diff_tables.inc;
128
129 --echo
130 --echo # Verify 'RENAME USER ...' statement
131 RENAME USER CURRENT_USER TO 'bug48321_2'@'localhost';
132 --source include/rpl_sync.inc
133 let $diff_tables= server_1:v_user, server_2:v_user, server_3:v_user;
134 source include/diff_tables.inc;
135
136 disconnect conn1;
137
138 --echo
139 --echo # Verify 'DROP USER ...' statement
140 connection master;
141 GRANT CREATE USER ON *.* TO 'bug48321_2'@'localhost';
142 connect (conn1, 127.0.0.1, 'bug48321_2'@'localhost',,);
143 connection conn1;
144 DROP USER CURRENT_USER();
145 --source include/rpl_sync.inc
146 let $diff_tables= server_1:v_user, server_2:v_user, server_3:v_user;
147 source include/diff_tables.inc;
148
149 --echo
150 --echo # Verify 'ALTER EVENT...' statement
151 connection master;
152 CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT * FROM t1;
153
154 --echo # Explicitly assign CURRENT_USER() to definer
155 ALTER DEFINER=CURRENT_USER() EVENT e1 ENABLE;
156 --source include/rpl_sync.inc
157 let $diff_tables= server_1:v_event, server_2:v_event, server_3:v_event;
158 source include/diff_tables.inc;
159
160 --echo
161 --echo # Session user will be set as definer, if the statement does not assign
162 --echo # a definer
163 ALTER EVENT e1 ENABLE;
164 --source include/rpl_sync.inc
165 let $diff_tables= server_1:v_event, server_2:v_event, server_3:v_event;
166 source include/diff_tables.inc;
167
168 --echo
169 --echo # Verify that this patch does not affect the calling of CURRENT_USER()
170 --echo # in the other statements
171 connection master;
172 --echo # [On master]
173 INSERT INTO t1 VALUES(CURRENT_USER()), ('1234');
174 SELECT * FROM t1;
175 --source include/sync_slave_sql_with_master.inc
176 --connection slave
177 --echo # [On slave]
178 SELECT * FROM t1;
179 --echo # [On server_3]
180 --let $sync_slave_connection= server_3
181 --source include/sync_slave_sql_with_master.inc
182 SELECT * FROM t1;
183
184 connection master;
185 --echo # [On master]
186 UPDATE t1 SET c1=CURRENT_USER() WHERE c1='1234';
187 SELECT * FROM t1;
188 --source include/sync_slave_sql_with_master.inc
189 --connection slave
190 --echo # [On slave]
191 SELECT * FROM t1;
192 --let $sync_slave_connection= server_3
193 --source include/sync_slave_sql_with_master.inc
194 --echo # [On server_3]
195 SELECT * FROM t1;
196
197 connection master;
198 --echo # [On master]
199 DELETE FROM t1 WHERE c1=CURRENT_USER();
200 SELECT * FROM t1;
201 --source include/sync_slave_sql_with_master.inc
202 --connection slave
203 --echo # [On slave]
204 SELECT * FROM t1;
205 --let $sync_slave_connection= server_3
206 --source include/sync_slave_sql_with_master.inc
207 --echo # [On server_3]
208 SELECT * FROM t1;
209
210 connection master;
211 --echo # [On master]
212 CREATE TABLE t2(c1 char(100));
213
214 DELIMITER |;
215 CREATE FUNCTION my_user() RETURNS VARCHAR(64)
216 SQL SECURITY INVOKER
217 BEGIN
218 INSERT INTO t2 VALUES(CURRENT_USER());
219 RETURN CURRENT_USER();
220 END |
221 DELIMITER ;|
222
223 INSERT INTO t1 VALUES(my_user());
224 SELECT * FROM t1;
225 SELECT * FROM t2;
226 --source include/sync_slave_sql_with_master.inc
227 --connection slave
228 --echo # [On slave]
229 SELECT * FROM t1;
230 SELECT * FROM t2;
231 --let $sync_slave_connection= server_3
232 --source include/sync_slave_sql_with_master.inc
233 --echo # [On server_3]
234 SELECT * FROM t1;
235 SELECT * FROM t2;
236
237 --echo
238 --echo # END
239 connection master;
240 DROP TABLE t1, t2;
241 DROP VIEW v_user, v_tables_priv, v_procs_priv, v_event;
242 DROP PROCEDURE p1;
243 DROP PROCEDURE my_grant;
244 DROP PROCEDURE my_revoke;
245 DROP FUNCTION my_user;
246 DROP EVENT e1;
247 --source include/rpl_end.inc
248