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