Debian Codegraph @

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