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