Debian Codegraph @

mysql-test/suite/auth_sec/r/atomic_rename_user.result

raw
1 include/save_binlog_position.inc
2 # -----------------------------------------------------------------------
3 # Begin : Tests for RENAME USER
4 CREATE USER userX, userY, userZ;
5 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
6 user authentication_string
7 userX
8 userY
9 userZ
10 include/save_binlog_position.inc
11 # Modify multiple users
12 RENAME USER userX TO userA, userY TO userB, userZ TO userC;
13 # Must show userA, userB, userC
14 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
15 user authentication_string
16 userA
17 userB
18 userC
19 # This event sequence pattern MUST be present in binlog: !Q(RENAME USER.*userX.TO.*userA.*,.*userY.*TO.*userB.*,.*userZ.*TO.*userC.*)
20 include/assert_binlog_events.inc
21 include/save_binlog_position.inc
22 # Try renaming a non-existent user
23 RENAME USER userA TO userX, user_ne1 TO user_ne2, userC TO userZ;
24 ERROR HY000: Operation RENAME USER failed for 'user_ne1'@'%'
25 # Must show userA, userB, userC
26 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
27 user authentication_string
28 userA
29 userB
30 userC
31 # This event sequence pattern MUST NOT be present in binlog: !Q(RENAME USER.*userA.*TO.*userX.*,.*user_ne1.*TO.*user_ne2.*,.*userC.*TO.*userZ.*)
32 include/assert_binlog_events.inc
33 # Insert an entry in mysql.user table but do not execute FLUSH PRIVILEGES
34 CREATE TABLE mysql.tmp_user LIKE mysql.user;
35 INSERT INTO mysql.tmp_user SELECT * FROM mysql.user WHERE user LIKE 'userC';
36 UPDATE mysql.tmp_user SET user='userZ' WHERE user LIKE 'userC';
37 INSERT INTO mysql.user SELECT * FROM mysql.tmp_user;
38 DROP TABLE mysql.tmp_user;
39 include/save_binlog_position.inc
40 # Though userX is not present in cache, RENAME should check in mysql.user
41 # table for the presence of an entry which may create problem while
42 # renaming users.
43 RENAME USER userA TO userX, userB TO userY, userC TO userZ;
44 ERROR HY000: Operation RENAME USER failed for 'userC'@'%'
45 # Must show userA, userB, userC and userZ
46 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
47 user authentication_string
48 userA
49 userB
50 userC
51 userZ
52 # This event sequence pattern MUST NOT be present in binlog: !Q(RENAME USER.*userA.*TO.*userX.*,.*userB.*TO.*userY.*,.*userC.*TO.*userZ.*)
53 include/assert_binlog_events.inc
54 FLUSH PRIVILEGES;
55 include/save_binlog_position.inc
56 # Try renaming two different user and use same user as target
57 RENAME USER userA TO userX, userB TO userX;
58 ERROR HY000: Operation RENAME USER failed for 'userB'@'%'
59 # Must show userA, userB, userC
60 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
61 user authentication_string
62 userA
63 userB
64 userC
65 userZ
66 # This event sequence pattern MUST NOT be present in binlog: !Q(RENAME USER.*userA.*TO.*userX.*,.*userB.*TO.*userX.*)
67 include/assert_binlog_events.inc
68 # This should succeed
69 RENAME USER userA TO userX, userB TO userA, userZ TO userB, userC TO userZ;
70 # Must show userA, userB, userX and userZ
71 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
72 user authentication_string
73 userA
74 userB
75 userX
76 userZ
77 # This event sequence pattern MUST be present in binlog: !Q(RENAME USER.*userA.*TO.*userX.*,.*userB.*TO.*userA.*,.*userZ.*TO.*userB.*,.*userC.*TO.*userZ.*)
78 include/assert_binlog_events.inc
79 include/save_binlog_position.inc
80 DROP USER userA, userB, userX, userZ;
81 include/save_binlog_position.inc
82 ## Malformed table should be handled gracefully
83 RENAME TABLE mysql.user TO mysql.user_bak;
84 CREATE TABLE mysql.user(dummy INT) ENGINE=MyISAM;
85 Warnings:
86 Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.user]
87 FLUSH PRIVILEGES;
88 Warnings:
89 Warning 1805 Column count of mysql.user is wrong. Expected 51, found 1. The table is probably corrupted
90 SHOW WARNINGS;
91 Level Code Message
92 Warning 1805 Column count of mysql.user is wrong. Expected 51, found 1. The table is probably corrupted
93 DROP TABLE mysql.user;
94 RENAME TABLE mysql.user_bak TO mysql.user;
95 FLUSH PRIVILEGES;
96 #
97 # Bug #32068470: TO_HOST AND TO_USER NOT SET
98 # Self-renaming isuses
99 # Regular user
100 CREATE USER a;
101 GRANT CREATE USER ON *.* TO a;
102 SELECT user(), current_user();
103 user() current_user()
104 a@localhost a@%
105 RENAME USER a TO b;
106 SELECT user(), current_user();
107 user() current_user()
108 a@localhost b@%
109 DROP USER b;
110 CREATE USER a;
111 GRANT CREATE USER ON *.* TO a;
112 SELECT user(), current_user();
113 user() current_user()
114 a@localhost a@%
115 RENAME USER a TO b, b TO c;
116 SELECT user(), current_user();
117 user() current_user()
118 a@localhost c@%
119 DROP USER c;
120 CREATE USER a;
121 GRANT CREATE USER ON *.* TO a;
122 SELECT user(), current_user();
123 user() current_user()
124 a@localhost a@%
125 RENAME USER a TO b, b TO c, c TO a;
126 SELECT user(), current_user();
127 user() current_user()
128 a@localhost a@%
129 DROP USER a;
130 CREATE USER a;
131 GRANT CREATE USER ON *.* TO a;
132 SELECT user(), current_user();
133 user() current_user()
134 a@localhost a@%
135 RENAME USER a TO a@1;
136 SELECT user(), current_user();
137 user() current_user()
138 a@localhost a@1
139 DROP USER a@1;
140 CREATE USER a;
141 GRANT CREATE USER ON *.* TO a;
142 SELECT user(), current_user();
143 user() current_user()
144 a@localhost a@%
145 RENAME USER a TO b@1;
146 SELECT user(), current_user();
147 user() current_user()
148 a@localhost b@1
149 DROP USER b@1;
150 # Proxy user
151 CREATE USER a IDENTIFIED with 'mysql_native_password', b;
152 GRANT CREATE USER ON *.* TO b;
153 GRANT PROXY ON b TO a;
154 SET @@global.check_proxy_users = ON;
155 SET @@global.mysql_native_password_proxy_users = ON;
156 SELECT user(), current_user(), @@proxy_user;
157 user() current_user() @@proxy_user
158 a@localhost b@% 'a'@'%'
159 RENAME USER a TO c;
160 SELECT user(), current_user(), @@proxy_user;
161 user() current_user() @@proxy_user
162 a@localhost b@% 'c'@'%'
163 DROP USER b, c;
164 SET @@global.check_proxy_users = OFF;
165 SET @@global.mysql_native_password_proxy_users = OFF;
166 # Anonymous user
167 CREATE USER ''@'localhost';
168 GRANT CREATE USER ON *.* TO ''@'localhost';
169 SELECT user(), current_user();
170 user() current_user()
171 CONNECTED_USER @localhost
172 RENAME USER ''@'localhost' TO a;
173 SELECT user(), current_user();
174 user() current_user()
175 CONNECTED_USER a@%
176 DROP USER a;
177 # End : Tests for RENAME USER
178 # -----------------------------------------------------------------------