1
##############################################################################
2
# BUG#48321 CURRENT_USER() incorrectly replicated for DROP/RENAME USER,
3
# REVOKE, GRANT, ALTER EVENT
5
# Calling CURRENT_USER() results into inconsistency between slave and master,
6
# as the slave SQL thread has different user with common users.
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',
12
##############################################################################
14
source include/have_binlog_format_statement.inc;
16
--let $rpl_topology= 1->2->3
17
--source include/rpl_init.inc
19
--let $rpl_connection_name= master
20
--let $rpl_server_number= 1
21
--source include/rpl_connect.inc
23
--let $rpl_connection_name= slave
24
--let $rpl_server_number= 2
25
--source include/rpl_connect.inc
29
CREATE TABLE t1(c1 char(100));
30
CREATE VIEW test.v_user AS SELECT * FROM mysql.user WHERE User LIKE 'bug48321%';
31
CREATE VIEW test.v_tables_priv AS SELECT * FROM mysql.tables_priv WHERE User LIKE 'bug48321%';
32
CREATE VIEW test.v_procs_priv AS SELECT * FROM mysql.procs_priv WHERE User LIKE 'bug48321%';
33
CREATE VIEW test.v_event AS SELECT definer FROM mysql.event WHERE name = 'e1';
34
CREATE PROCEDURE p1() SELECT 1;
35
--echo # bug48321_1-01234 has the max length(16) of user.
36
GRANT ALL PRIVILEGES ON *.* TO 'bug48321_1-01234'@'localhost' WITH GRANT OPTION;
39
--echo # Make sure the max lengths of user and host
40
--echo # the user name is too lengh
42
GRANT CREATE USER ON *.* TO '01234567890123456'@'fakehost';
43
--echo # the host name is too lengh
45
GRANT CREATE USER ON *.* TO 'fakename'@'0123456789012345678901234567890123456789012345678901234567890';
48
--echo # User 'bug48321_1-01234' connects to master by conn1
49
connect (conn1, 127.0.0.1, 'bug48321_1-01234'@'localhost',,);
52
--echo # Verify 'REVOKE ALL' statement
53
REVOKE ALL PRIVILEGES, GRANT OPTION FROM CURRENT_USER();
54
--source include/rpl_sync.inc
55
let $diff_tables= server_1:v_user, server_2:v_user, server_3:v_user;
56
source include/diff_tables.inc;
59
--echo # Verify 'GRANT ... ON TABLE ...' statement
60
GRANT CREATE, INSERT, SELECT ON TABLE test.t1 TO CURRENT_USER();
61
--source include/rpl_sync.inc
62
let $diff_tables= server_1:v_tables_priv, server_2:v_tables_priv, server_3:v_tables_priv;
63
source include/diff_tables.inc;
66
--echo # Verify 'GRANT ... ON PROCEDURE...' statement
67
GRANT ALTER ROUTINE, EXECUTE ON PROCEDURE p1 TO CURRENT_USER();
68
--source include/rpl_sync.inc
69
let $diff_tables= server_1:v_procs_priv, server_2:v_procs_priv, server_3:v_procs_priv;
70
source include/diff_tables.inc;
73
--echo # Verify 'GRANT ... ON *.* ...' statement
74
GRANT ALL PRIVILEGES ON *.* TO CURRENT_USER() WITH GRANT OPTION;
75
--source include/rpl_sync.inc
76
let $diff_tables= server_1:v_procs_priv, server_2:v_procs_priv, server_3:v_procs_priv;
77
source include/diff_tables.inc;
80
--echo # Verify 'REVOKE ... ON TABLE ...' statement
81
REVOKE CREATE, INSERT, SELECT ON TABLE t1 FROM CURRENT_USER();
82
--source include/rpl_sync.inc
83
let $diff_tables= server_1:v_tables_priv, server_2:v_tables_priv, server_3:v_tables_priv;
84
source include/diff_tables.inc;
87
--echo # Verify 'REVOKE ... ON PROCEDURE...' statement
88
REVOKE ALTER ROUTINE, EXECUTE ON PROCEDURE p1 FROM CURRENT_USER();
89
--source include/rpl_sync.inc
90
let $diff_tables= server_1:v_procs_priv, server_2:v_procs_priv, server_3:v_procs_priv;
91
source include/diff_tables.inc;
94
--echo # Verify 'REVOKE ... ON *.* ...' statement
95
REVOKE ALL PRIVILEGES ON *.* FROM CURRENT_USER();
96
--source include/rpl_sync.inc
97
let $diff_tables= server_1:v_user, server_2:v_user, server_3:v_user;
98
source include/diff_tables.inc;
101
--echo # Verify 'GRANT ...' statement in the procedure
102
CREATE PROCEDURE my_grant()
103
GRANT CREATE, INSERT, SELECT ON TABLE test.t1 TO CURRENT_USER();
105
--source include/rpl_sync.inc
106
let $diff_tables= server_1:v_tables_priv, server_2:v_tables_priv, server_3:v_tables_priv;
107
source include/diff_tables.inc;
110
--echo # Verify 'REVOKE ... ON TABLE ...' statement in the procedure
111
CREATE PROCEDURE my_revoke()
112
REVOKE CREATE, INSERT, SELECT ON TABLE t1 FROM CURRENT_USER();
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;
119
--echo # Verify 'RENAME USER ...' statement
120
RENAME USER CURRENT_USER TO 'bug48321_2'@'localhost';
121
--source include/rpl_sync.inc
122
let $diff_tables= server_1:v_user, server_2:v_user, server_3:v_user;
123
source include/diff_tables.inc;
128
--echo # Verify 'DROP USER ...' statement
130
GRANT CREATE USER ON *.* TO 'bug48321_2'@'localhost';
131
connect (conn1, 127.0.0.1, 'bug48321_2'@'localhost',,);
133
DROP USER CURRENT_USER();
134
--source include/rpl_sync.inc
135
let $diff_tables= server_1:v_user, server_2:v_user, server_3:v_user;
136
source include/diff_tables.inc;
139
--echo # Verify 'ALTER EVENT...' statement
141
CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT * FROM t1;
143
--echo # Explicitly assign CURRENT_USER() to definer
144
ALTER DEFINER=CURRENT_USER() EVENT e1 ENABLE;
145
--source include/rpl_sync.inc
146
let $diff_tables= server_1:v_event, server_2:v_event, server_3:v_event;
147
source include/diff_tables.inc;
150
--echo # Session user will be set as definer, if the statement does not assign
152
ALTER EVENT e1 ENABLE;
153
--source include/rpl_sync.inc
154
let $diff_tables= server_1:v_event, server_2:v_event, server_3:v_event;
155
source include/diff_tables.inc;
158
--echo # Verify that this patch does not affect the calling of CURRENT_USER()
159
--echo # in the other statements
162
INSERT INTO t1 VALUES(CURRENT_USER()), ('1234');
164
sync_slave_with_master;
167
--echo # [On server_3]
168
sync_slave_with_master server_3;
173
UPDATE t1 SET c1=CURRENT_USER() WHERE c1='1234';
175
sync_slave_with_master;
178
sync_slave_with_master server_3;
179
--echo # [On server_3]
184
DELETE FROM t1 WHERE c1=CURRENT_USER();
186
sync_slave_with_master;
189
sync_slave_with_master server_3;
190
--echo # [On server_3]
195
CREATE TABLE t2(c1 char(100));
198
CREATE FUNCTION my_user() RETURNS VARCHAR(64)
201
INSERT INTO t2 VALUES(CURRENT_USER());
202
RETURN CURRENT_USER();
206
INSERT INTO t1 VALUES(my_user());
209
sync_slave_with_master;
213
sync_slave_with_master server_3;
214
--echo # [On server_3]
222
DROP VIEW v_user, v_tables_priv, v_procs_priv, v_event;
224
DROP PROCEDURE my_grant;
225
DROP PROCEDURE my_revoke;
226
DROP FUNCTION my_user;
228
--source include/rpl_end.inc