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