Debian Codegraph @

mysql-test/suite/auth_sec/t/atomic_rename_user.test

raw
1 --source include/have_log_bin.inc
2 --source include/save_binlog_position.inc
3
4 # Save the initial number of concurrent sessions
5 --source include/count_sessions.inc
6
7 --echo # -----------------------------------------------------------------------
8
9 --echo # Begin : Tests for RENAME USER
10
11 CREATE USER userX, userY, userZ;
12
13 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
14 --source include/save_binlog_position.inc
15
16 --echo # Modify multiple users
17 RENAME USER userX TO userA, userY TO userB, userZ TO userC;
18
19 --echo # Must show userA, userB, userC
20 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
21
22 --let $event= !Q(RENAME USER.*userX.TO.*userA.*,.*userY.*TO.*userB.*,.*userZ.*TO.*userC.*)
23 --source ../include/auth_sec_assert_binlog_events.inc
24 --source include/save_binlog_position.inc
25
26 --echo # Try renaming a non-existent user
27 --error ER_CANNOT_USER
28 RENAME USER userA TO userX, user_ne1 TO user_ne2, userC TO userZ;
29
30 --echo # Must show userA, userB, userC
31 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
32
33 --let $invert= 1
34 --let $event= !Q(RENAME USER.*userA.*TO.*userX.*,.*user_ne1.*TO.*user_ne2.*,.*userC.*TO.*userZ.*)
35 --source ../include/auth_sec_assert_binlog_events.inc
36 --let $invert= 0
37
38 --echo # Insert an entry in mysql.user table but do not execute FLUSH PRIVILEGES
39 CREATE TABLE mysql.tmp_user LIKE mysql.user;
40 INSERT INTO mysql.tmp_user SELECT * FROM mysql.user WHERE user LIKE 'userC';
41 UPDATE mysql.tmp_user SET user='userZ' WHERE user LIKE 'userC';
42 INSERT INTO mysql.user SELECT * FROM mysql.tmp_user;
43 DROP TABLE mysql.tmp_user;
44
45 --source include/save_binlog_position.inc
46
47 --echo # Though userX is not present in cache, RENAME should check in mysql.user
48 --echo # table for the presence of an entry which may create problem while
49 --echo # renaming users.
50
51 --error ER_CANNOT_USER
52 RENAME USER userA TO userX, userB TO userY, userC TO userZ;
53
54 --echo # Must show userA, userB, userC and userZ
55 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
56
57 --let $invert= 1
58 --let $event= !Q(RENAME USER.*userA.*TO.*userX.*,.*userB.*TO.*userY.*,.*userC.*TO.*userZ.*)
59 --source ../include/auth_sec_assert_binlog_events.inc
60 --let $invert= 0
61
62 FLUSH PRIVILEGES;
63 --source include/save_binlog_position.inc
64
65 --echo # Try renaming two different user and use same user as target
66 --error ER_CANNOT_USER
67 RENAME USER userA TO userX, userB TO userX;
68
69 --echo # Must show userA, userB, userC
70 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
71
72 --let $invert= 1
73 --let $event= !Q(RENAME USER.*userA.*TO.*userX.*,.*userB.*TO.*userX.*)
74 --source ../include/auth_sec_assert_binlog_events.inc
75 --let $invert= 0
76
77 --echo # This should succeed
78 RENAME USER userA TO userX, userB TO userA, userZ TO userB, userC TO userZ;
79
80 --echo # Must show userA, userB, userX and userZ
81 SELECT user, authentication_string FROM mysql.user WHERE user like 'user%';
82
83 --let $event= !Q(RENAME USER.*userA.*TO.*userX.*,.*userB.*TO.*userA.*,.*userZ.*TO.*userB.*,.*userC.*TO.*userZ.*)
84 --source ../include/auth_sec_assert_binlog_events.inc
85 --source include/save_binlog_position.inc
86
87 DROP USER userA, userB, userX, userZ;
88
89 --source include/save_binlog_position.inc
90
91 --echo ## Malformed table should be handled gracefully
92 --disable_query_log
93 call mtr.add_suppression('.*does not support system tables.*');
94 call mtr.add_suppression('.*Column count of mysql.user is wrong.*');
95 --enable_query_log
96
97 RENAME TABLE mysql.user TO mysql.user_bak;
98 CREATE TABLE mysql.user(dummy INT) ENGINE=MyISAM;
99 FLUSH PRIVILEGES;
100 SHOW WARNINGS;
101 DROP TABLE mysql.user;
102 RENAME TABLE mysql.user_bak TO mysql.user;
103 FLUSH PRIVILEGES;
104
105 --echo #
106 --echo # Bug #32068470: TO_HOST AND TO_USER NOT SET
107 --echo # Self-renaming isuses
108 --echo # Regular user
109 CREATE USER a;
110 GRANT CREATE USER ON *.* TO a;
111 --connect(con1, localhost, a)
112 SELECT user(), current_user();
113 RENAME USER a TO b;
114 SELECT user(), current_user();
115
116 --connection default
117 disconnect con1;
118 DROP USER b;
119
120 CREATE USER a;
121 GRANT CREATE USER ON *.* TO a;
122 --connect(con1, localhost, a)
123 SELECT user(), current_user();
124 RENAME USER a TO b, b TO c;
125 SELECT user(), current_user();
126
127 --connection default
128 disconnect con1;
129 DROP USER c;
130
131 CREATE USER a;
132 GRANT CREATE USER ON *.* TO a;
133 --connect(con1, localhost, a)
134 SELECT user(), current_user();
135 RENAME USER a TO b, b TO c, c TO a;
136 SELECT user(), current_user();
137
138 --connection default
139 disconnect con1;
140 DROP USER a;
141
142 CREATE USER a;
143 GRANT CREATE USER ON *.* TO a;
144 --connect(con1, localhost, a)
145 SELECT user(), current_user();
146 RENAME USER a TO a@1;
147 SELECT user(), current_user();
148
149 --connection default
150 disconnect con1;
151 DROP USER a@1;
152
153 CREATE USER a;
154 GRANT CREATE USER ON *.* TO a;
155 --connect(con1, localhost, a)
156 SELECT user(), current_user();
157 RENAME USER a TO b@1;
158 SELECT user(), current_user();
159
160 --connection default
161 disconnect con1;
162 DROP USER b@1;
163
164
165 --echo # Proxy user
166 CREATE USER a IDENTIFIED with 'mysql_native_password', b;
167 GRANT CREATE USER ON *.* TO b;
168 GRANT PROXY ON b TO a;
169 SET @@global.check_proxy_users = ON;
170 SET @@global.mysql_native_password_proxy_users = ON;
171 --connect(con1, localhost, a)
172 SELECT user(), current_user(), @@proxy_user;
173 RENAME USER a TO c;
174 SELECT user(), current_user(), @@proxy_user;
175
176 --connection default
177 disconnect con1;
178 DROP USER b, c;
179 SET @@global.check_proxy_users = OFF;
180 SET @@global.mysql_native_password_proxy_users = OFF;
181
182 --echo # Anonymous user
183 CREATE USER ''@'localhost';
184 GRANT CREATE USER ON *.* TO ''@'localhost';
185 --connect(con1, localhost)
186 --replace_column 1 CONNECTED_USER
187 SELECT user(), current_user();
188 RENAME USER ''@'localhost' TO a;
189 --replace_column 1 CONNECTED_USER
190 SELECT user(), current_user();
191
192 --connection default
193 disconnect con1;
194 DROP USER a;
195
196 --echo # End : Tests for RENAME USER
197
198 --echo # -----------------------------------------------------------------------
199
200 # Wait till we reached the initial number of concurrent sessions
201 --source include/wait_until_count_sessions.inc