include/save_binlog_position.inc # ----------------------------------------------------------------------- # Begin : Tests for RENAME USER CREATE USER userX, userY, userZ; SELECT user, authentication_string FROM mysql.user WHERE user like 'user%'; user authentication_string userX userY userZ include/save_binlog_position.inc # Modify multiple users RENAME USER userX TO userA, userY TO userB, userZ TO userC; # Must show userA, userB, userC SELECT user, authentication_string FROM mysql.user WHERE user like 'user%'; user authentication_string userA userB userC # This event sequence pattern MUST be present in binlog: !Q(RENAME USER.*userX.TO.*userA.*,.*userY.*TO.*userB.*,.*userZ.*TO.*userC.*) include/assert_binlog_events.inc include/save_binlog_position.inc # Try renaming a non-existent user RENAME USER userA TO userX, user_ne1 TO user_ne2, userC TO userZ; ERROR HY000: Operation RENAME USER failed for 'user_ne1'@'%' # Must show userA, userB, userC SELECT user, authentication_string FROM mysql.user WHERE user like 'user%'; user authentication_string userA userB userC # This event sequence pattern MUST NOT be present in binlog: !Q(RENAME USER.*userA.*TO.*userX.*,.*user_ne1.*TO.*user_ne2.*,.*userC.*TO.*userZ.*) include/assert_binlog_events.inc # Insert an entry in mysql.user table but do not execute FLUSH PRIVILEGES CREATE TABLE mysql.tmp_user LIKE mysql.user; INSERT INTO mysql.tmp_user SELECT * FROM mysql.user WHERE user LIKE 'userC'; UPDATE mysql.tmp_user SET user='userZ' WHERE user LIKE 'userC'; INSERT INTO mysql.user SELECT * FROM mysql.tmp_user; DROP TABLE mysql.tmp_user; include/save_binlog_position.inc # Though userX is not present in cache, RENAME should check in mysql.user # table for the presence of an entry which may create problem while # renaming users. RENAME USER userA TO userX, userB TO userY, userC TO userZ; ERROR HY000: Operation RENAME USER failed for 'userC'@'%' # Must show userA, userB, userC and userZ SELECT user, authentication_string FROM mysql.user WHERE user like 'user%'; user authentication_string userA userB userC userZ # This event sequence pattern MUST NOT be present in binlog: !Q(RENAME USER.*userA.*TO.*userX.*,.*userB.*TO.*userY.*,.*userC.*TO.*userZ.*) include/assert_binlog_events.inc FLUSH PRIVILEGES; include/save_binlog_position.inc # Try renaming two different user and use same user as target RENAME USER userA TO userX, userB TO userX; ERROR HY000: Operation RENAME USER failed for 'userB'@'%' # Must show userA, userB, userC SELECT user, authentication_string FROM mysql.user WHERE user like 'user%'; user authentication_string userA userB userC userZ # This event sequence pattern MUST NOT be present in binlog: !Q(RENAME USER.*userA.*TO.*userX.*,.*userB.*TO.*userX.*) include/assert_binlog_events.inc # This should succeed RENAME USER userA TO userX, userB TO userA, userZ TO userB, userC TO userZ; # Must show userA, userB, userX and userZ SELECT user, authentication_string FROM mysql.user WHERE user like 'user%'; user authentication_string userA userB userX userZ # This event sequence pattern MUST be present in binlog: !Q(RENAME USER.*userA.*TO.*userX.*,.*userB.*TO.*userA.*,.*userZ.*TO.*userB.*,.*userC.*TO.*userZ.*) include/assert_binlog_events.inc include/save_binlog_position.inc DROP USER userA, userB, userX, userZ; include/save_binlog_position.inc ## Malformed table should be handled gracefully RENAME TABLE mysql.user TO mysql.user_bak; CREATE TABLE mysql.user(dummy INT) ENGINE=MyISAM; Warnings: Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.user] FLUSH PRIVILEGES; Warnings: Warning 1805 Column count of mysql.user is wrong. Expected 51, found 1. The table is probably corrupted SHOW WARNINGS; Level Code Message Warning 1805 Column count of mysql.user is wrong. Expected 51, found 1. The table is probably corrupted DROP TABLE mysql.user; RENAME TABLE mysql.user_bak TO mysql.user; FLUSH PRIVILEGES; # # Bug #32068470: TO_HOST AND TO_USER NOT SET # Self-renaming isuses # Regular user CREATE USER a; GRANT CREATE USER ON *.* TO a; SELECT user(), current_user(); user() current_user() a@localhost a@% RENAME USER a TO b; SELECT user(), current_user(); user() current_user() a@localhost b@% DROP USER b; CREATE USER a; GRANT CREATE USER ON *.* TO a; SELECT user(), current_user(); user() current_user() a@localhost a@% RENAME USER a TO b, b TO c; SELECT user(), current_user(); user() current_user() a@localhost c@% DROP USER c; CREATE USER a; GRANT CREATE USER ON *.* TO a; SELECT user(), current_user(); user() current_user() a@localhost a@% RENAME USER a TO b, b TO c, c TO a; SELECT user(), current_user(); user() current_user() a@localhost a@% DROP USER a; CREATE USER a; GRANT CREATE USER ON *.* TO a; SELECT user(), current_user(); user() current_user() a@localhost a@% RENAME USER a TO a@1; SELECT user(), current_user(); user() current_user() a@localhost a@1 DROP USER a@1; CREATE USER a; GRANT CREATE USER ON *.* TO a; SELECT user(), current_user(); user() current_user() a@localhost a@% RENAME USER a TO b@1; SELECT user(), current_user(); user() current_user() a@localhost b@1 DROP USER b@1; # Proxy user CREATE USER a IDENTIFIED with 'mysql_native_password', b; GRANT CREATE USER ON *.* TO b; GRANT PROXY ON b TO a; SET @@global.check_proxy_users = ON; SET @@global.mysql_native_password_proxy_users = ON; SELECT user(), current_user(), @@proxy_user; user() current_user() @@proxy_user a@localhost b@% 'a'@'%' RENAME USER a TO c; SELECT user(), current_user(), @@proxy_user; user() current_user() @@proxy_user a@localhost b@% 'c'@'%' DROP USER b, c; SET @@global.check_proxy_users = OFF; SET @@global.mysql_native_password_proxy_users = OFF; # Anonymous user CREATE USER ''@'localhost'; GRANT CREATE USER ON *.* TO ''@'localhost'; SELECT user(), current_user(); user() current_user() CONNECTED_USER @localhost RENAME USER ''@'localhost' TO a; SELECT user(), current_user(); user() current_user() CONNECTED_USER a@% DROP USER a; # End : Tests for RENAME USER # -----------------------------------------------------------------------