Debian Codegraph @

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

raw
1 #-----------------------------------------------------------------------
2 # Setup : Create users, grant privileges, create connections
3 CREATE USER arthurdent@localhost IDENTIFIED BY 'abcd';
4 CREATE USER marvintheparanoidandroid@localhost IDENTIFIED BY 'efghi';
5 CREATE USER zaphodbeeblebrox@localhost IDENTIFIED BY 'xyz';
6 GRANT CREATE USER ON *.* TO arthurdent@localhost;
7 GRANT UPDATE ON mysql.* TO zaphodbeeblebrox@localhost;
8 GRANT APPLICATION_PASSWORD_ADMIN ON *.* TO marvintheparanoidandroid@localhost;
9 #-----------------------------------------------------------------------
10 # Test 1: Use RETAIN CURRENT PASSWORD with ALTER USER
11 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
12 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
13 WHERE user LIKE 'arthurdent';
14 ADDITIONAL_PASSWORD_FOR_ARTHURDENT
15 0
16 ALTER USER arthurdent@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD;
17 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
18 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
19 WHERE user LIKE 'arthurdent';
20 ADDITIONAL_PASSWORD_FOR_ARTHURDENT
21 1
22 SET @additional_password_1=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
23 '$.additional_password') FROM mysql.user WHERE user LIKE 'arthurdent');
24 ALTER USER arthurdent@localhost IDENTIFIED by 'xyz' RETAIN CURRENT PASSWORD;
25 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
26 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
27 WHERE user LIKE 'arthurdent';
28 ADDITIONAL_PASSWORD_FOR_ARTHURDENT
29 1
30 SET @additional_password_2=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
31 '$.additional_password') FROM mysql.user WHERE user LIKE 'arthurdent');
32 SELECT @additional_password_1 <> @additional_password_2 AS
33 ADDITIONAL_PASSWORD_CHANGED;
34 ADDITIONAL_PASSWORD_CHANGED
35 1
36 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
37 SELECT COUNT(*) AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
38 WHERE user LIKE 'arthurdent';
39 ADDITIONAL_PASSWORD_FOR_ARTHURDENT
40 1
41 SET @additional_password_3=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
42 '$.additional_password') FROM mysql.user WHERE user LIKE 'arthurdent');
43 SELECT @additional_password_2 = @additional_password_3 AS
44 ADDITIONAL_PASSWORD_NOT_CHANGED;
45 ADDITIONAL_PASSWORD_NOT_CHANGED
46 1
47 #-----------------------------------------------------------------------
48 # Test 2: Use DISCARD OLD PASSWORD with ALTER USER
49 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
50 AS ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID FROM
51 mysql.user WHERE user LIKE 'marvintheparanoidandroid';
52 ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID
53 0
54 ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'abcd'
55 RETAIN CURRENT PASSWORD;
56 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
57 AS ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID FROM
58 mysql.user WHERE user LIKE 'marvintheparanoidandroid';
59 ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID
60 1
61 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
62 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
63 AS ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID FROM
64 mysql.user WHERE user LIKE 'marvintheparanoidandroid';
65 ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID
66 0
67 #-----------------------------------------------------------------------
68 # Test 3: Use RETAIN CURRENT PASSWORD with SET PASSWORD
69 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
70 AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
71 mysql.user WHERE user LIKE 'zaphodbeeblebrox';
72 ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX
73 0
74 SET PASSWORD FOR zaphodbeeblebrox@localhost = 'abcd' RETAIN CURRENT PASSWORD;
75 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
76 AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
77 mysql.user WHERE user LIKE 'zaphodbeeblebrox';
78 ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX
79 1
80 SET @additional_password_1=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
81 '$.additional_password') FROM mysql.user WHERE user LIKE 'zaphodbeeblebrox');
82 SET PASSWORD FOR zaphodbeeblebrox@localhost = 'efghi' RETAIN CURRENT PASSWORD;
83 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
84 AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
85 mysql.user WHERE user LIKE 'zaphodbeeblebrox';
86 ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX
87 1
88 SET @additional_password_2=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
89 '$.additional_password') FROM mysql.user WHERE user LIKE 'zaphodbeeblebrox');
90 SELECT @additional_password_1 <> @additional_password_2 AS
91 ADDITIONAL_PASSWORD_CHANGED;
92 ADDITIONAL_PASSWORD_CHANGED
93 1
94 SET PASSWORD FOR zaphodbeeblebrox@localhost='xyz';
95 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
96 AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
97 mysql.user WHERE user LIKE 'zaphodbeeblebrox';
98 ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX
99 1
100 SET @additional_password_3=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
101 '$.additional_password') FROM mysql.user WHERE user LIKE 'zaphodbeeblebrox');
102 SELECT @additional_password_2 = @additional_password_3 AS
103 ADDITIONAL_PASSWORD_NOT_CHANGED;
104 ADDITIONAL_PASSWORD_NOT_CHANGED
105 1
106 #-----------------------------------------------------------------------
107 # Test 4: RENAME USER
108 RENAME USER arthurdent@localhost TO arthurdent1@localhost;
109 SELECT COUNT(JSON_KEYS(mysql.user.user_attributes)) AS
110 ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
111 WHERE user LIKE 'arthurdent';
112 ADDITIONAL_PASSWORD_FOR_ARTHURDENT
113 0
114 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
115 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT1 FROM mysql.user
116 WHERE user LIKE 'arthurdent1';
117 ADDITIONAL_PASSWORD_FOR_ARTHURDENT1
118 1
119 RENAME USER arthurdent1@localhost TO arthurdent@localhost;
120 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
121 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
122 WHERE user LIKE 'arthurdent';
123 ADDITIONAL_PASSWORD_FOR_ARTHURDENT
124 1
125 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
126 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT1 FROM mysql.user
127 WHERE user LIKE 'arthurdent1';
128 ADDITIONAL_PASSWORD_FOR_ARTHURDENT1
129 0
130 #-----------------------------------------------------------------------
131 # Test 5: DROP USER
132 CREATE USER u1@localhost IDENTIFIED BY 'abcd';
133 CREATE USER u2@localhost IDENTIFIED BY 'abcd';
134 ALTER USER u1@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD,
135 u2@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD;
136 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
137 AS ADDITIONAL_PASSWORD_FOR_U1 FROM mysql.user WHERE user LIKE 'u1';
138 ADDITIONAL_PASSWORD_FOR_U1
139 1
140 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
141 AS ADDITIONAL_PASSWORD_FOR_U2 FROM mysql.user WHERE user LIKE 'u2';
142 ADDITIONAL_PASSWORD_FOR_U2
143 1
144 DROP USER u1@localhost, u2@localhost;
145 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
146 AS ADDITIONAL_PASSWORD_FOR_U1 FROM mysql.user WHERE user LIKE 'u1';
147 ADDITIONAL_PASSWORD_FOR_U1
148 0
149 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
150 AS ADDITIONAL_PASSWORD_FOR_U2 FROM mysql.user WHERE user LIKE 'u2';
151 ADDITIONAL_PASSWORD_FOR_U2
152 0
153 #-----------------------------------------------------------------------
154 # Test 6: Syntax
155 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
156 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
157 ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
158 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
159 AS NO_ADDITIONAL_PASSWORDS FROM mysql.user;
160 NO_ADDITIONAL_PASSWORDS
161 0
162 ALTER USER arthurdent@localhost IDENTIFIED by '1234' RETAIN CURRENT PASSWORD,
163 marvintheparanoidandroid@localhost IDENTIFIED BY '5678',
164 zaphodbeeblebrox@localhost IDENTIFIED BY '9012' RETAIN CURRENT PASSWORD;
165 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
166 AS TWO_ADDITIONAL_PASSWORDS FROM mysql.user;
167 TWO_ADDITIONAL_PASSWORDS
168 2
169 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD,
170 marvintheparanoidandroid@localhost IDENTIFIED BY '5678'
171 RETAIN CURRENT PASSWORD,
172 zaphodbeeblebrox@localhost IDENTIFIED BY '9012';
173 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
174 AS TWO_ADDITIONAL_PASSWORDS FROM mysql.user;
175 TWO_ADDITIONAL_PASSWORDS
176 2
177 ALTER USER arthurdent@localhost IDENTIFIED BY '5678',
178 marvintheparanoidandroid@localhost DISCARD OLD PASSWORD,
179 zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
180 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
181 AS NO_ADDITIONAL_PASSWORDS FROM mysql.user;
182 NO_ADDITIONAL_PASSWORDS
183 0
184 #-----------------------------------------------------------------------
185 # Test 7: Permission
186 SET PASSWORD FOR arthurdent@localhost = 'abcd' RETAIN CURRENT PASSWORD;
187 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
188 ALTER USER arthurdent@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD;
189 SET PASSWORD = 'abcd' RETAIN CURRENT PASSWORD;
190 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD,
191 zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
192 SET PASSWORD = 'xyz' RETAIN CURRENT PASSWORD;
193 ALTER USER marvintheparanoidandroid@localhost
194 IDENTIFIED BY 'abcd' RETAIN CURRENT PASSWORD;
195 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
196 ALTER USER marvintheparanoidandroid@localhost
197 IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD,
198 arthurdent@localhost DISCARD OLD PASSWORD;
199 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
200 #-----------------------------------------------------------------------
201 # Test 8: Non built-in plugin
202 CREATE USER plug IDENTIFIED WITH test_plugin_server BY '123';
203 ALTER USER plug IDENTIFIED BY '123' RETAIN CURRENT PASSWORD;
204 Warnings:
205 Warning 13294 RETAIN CURRENT PASSWORD ignored for user 'plug'@'%' as its authentication plugin test_plugin_server does not support multiple passwords.
206 ALTER USER plug DISCARD OLD PASSWORD;
207 Warnings:
208 Warning 13295 DISCARD OLD PASSWORD ignored for user 'plug'@'%' as its authentication plugin test_plugin_server does not support multiple passwords.
209 DROP USER plug;
210 #-----------------------------------------------------------------------
211 # Test 9: Plugin change
212 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
213 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
214 ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
215 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
216 AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
217 ZERO_ADDITIONAL_PASSWORDS
218 0
219 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' RETAIN CURRENT PASSWORD;
220 ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'efgh'
221 RETAIN CURRENT PASSWORD;
222 ALTER USER zaphodbeeblebrox@localhost IDENTIFIED BY 'ijkl'
223 RETAIN CURRENT PASSWORD;
224 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
225 AS THREE_ADDITIONAL_PASSWORDS FROM mysql.user;
226 THREE_ADDITIONAL_PASSWORDS
227 3
228 ALTER USER arthurdent@localhost IDENTIFIED WITH 'mysql_native_password'
229 BY 'abcd';
230 ALTER USER marvintheparanoidandroid@localhost IDENTIFIED WITH 'sha256_password'
231 BY 'efgh';
232 ALTER USER zaphodbeeblebrox@localhost IDENTIFIED WITH 'mysql_native_password'
233 BY 'ijkl';
234 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
235 AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
236 ZERO_ADDITIONAL_PASSWORDS
237 0
238 #-----------------------------------------------------------------------
239 # Test 10: Empty first password
240 SET PASSWORD FOR arthurdent@localhost = '';
241 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' RETAIN CURRENT PASSWORD;
242 ERROR HY000: Empty password can not be retained as second password for user 'arthurdent'@'localhost'.
243 #-----------------------------------------------------------------------
244 # Test 11: Binary log
245 CREATE USER userX IDENTIFIED BY 'abcd', userY IDENTIFIED BY 'efgh';
246 include/save_binlog_position.inc
247 ALTER USER userX IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD,
248 userY IDENTIFIED BY 'ijkl' RETAIN CURRENT PASSWORD;
249 # This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userX.*RETAIN CURRENT PASSWORD.*userY.*RETAIN CURRENT PASSWORD.*)
250 include/assert_binlog_events.inc
251 include/save_binlog_position.inc
252 ALTER USER userX DISCARD OLD PASSWORD,
253 userY IDENTIFIED BY 'mnop' RETAIN CURRENT PASSWORD;
254 # This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userX.*DISCARD OLD PASSWORD.*userY.*RETAIN CURRENT PASSWORD.*)
255 include/assert_binlog_events.inc
256 include/save_binlog_position.inc
257 ALTER USER userX IDENTIFIED BY 'qrst' RETAIN CURRENT PASSWORD,
258 userY DISCARD OLD PASSWORD PASSWORD REQUIRE CURRENT;
259 # This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userX.*RETAIN CURRENT PASSWORD.*userY.*DISCARD OLD PASSWORD.*PASSWORD REQUIRE CURRENT.*)
260 include/assert_binlog_events.inc
261 include/save_binlog_position.inc
262 DROP USER userX;
263 #-----------------------------------------------------------------------
264 # Test 12: With REPLACE clause
265 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
266 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' PASSWORD REQUIRE CURRENT;
267 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
268 ALTER USER arthurdent@localhost IDENTIFIED BY 'ijkl' REPLACE 'efgh';
269 SET PASSWORD = 'mnop' REPLACE 'ijkl';
270 SET PASSWORD = 'qrst' REPLACE 'mnop' RETAIN CURRENT PASSWORD;
271 ALTER USER arthurdent@localhost IDENTIFIED BY 'uvwx' REPLACE 'qrst' RETAIN CURRENT PASSWORD;
272 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' REPLACE 'qrst';
273 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
274 SET PASSWORD = 'efgh' REPLACE 'qrst';
275 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
276 #-----------------------------------------------------------------------
277 # Test 13: ALTER USER with USER() function
278 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
279 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
280 ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
281 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
282 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
283 ALTER USER USER() IDENTIFIED BY 'ijkl' RETAIN CURRENT PASSWORD;
284 ALTER USER USER() DISCARD OLD PASSWORD;
285 #-----------------------------------------------------------------------
286 # Test 14: Plugin change and retained password
287 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
288 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
289 ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
290 ALTER USER arthurdent@localhost IDENTIFIED WITH 'caching_sha2_password' BY 'abcd';
291 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
292 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
293 AS ONE_ADDITIONAL_PASSWORDS FROM mysql.user;
294 ONE_ADDITIONAL_PASSWORDS
295 1
296 ALTER USER arthurdent@localhost IDENTIFIED WITH caching_sha2_password BY 'ijkl';
297 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
298 AS ONE_ADDITIONAL_PASSWORDS FROM mysql.user;
299 ONE_ADDITIONAL_PASSWORDS
300 1
301 ALTER USER arthurdent@localhost IDENTIFIED WITH 'mysql_native_password' BY 'mnop';
302 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
303 AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
304 ZERO_ADDITIONAL_PASSWORDS
305 0
306 ALTER USER arthurdent@localhost IDENTIFIED WITH 'sha256_password' BY 'qrst' RETAIN CURRENT PASSWORD;
307 ERROR HY000: Current password can not be retained for user 'arthurdent'@'localhost' because authentication plugin is being changed.
308 #-----------------------------------------------------------------------
309 # Test 15: Setting empty password
310 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
311 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
312 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
313 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
314 ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'abcd';
315 ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
316 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
317 AS TWO_ADDITIONAL_PASSWORDS FROM mysql.user;
318 TWO_ADDITIONAL_PASSWORDS
319 2
320 ALTER USER arthurdent@localhost IDENTIFIED BY '';
321 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
322 AS ONE_ADDITIONAL_PASSWORDS FROM mysql.user;
323 ONE_ADDITIONAL_PASSWORDS
324 1
325 SET PASSWORD FOR marvintheparanoidandroid@localhost = '';
326 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
327 AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
328 ZERO_ADDITIONAL_PASSWORDS
329 0
330 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
331 ALTER USER arthurdent@localhost IDENTIFIED BY '' RETAIN CURRENT PASSWORD;
332 ERROR HY000: Current password can not be retained for user 'arthurdent'@'localhost' because new password is empty.
333 SET PASSWORD FOR arthurdent@localhost = '' RETAIN CURRENT PASSWORD;
334 ERROR HY000: Current password can not be retained for user 'arthurdent'@'localhost' because new password is empty.
335 CREATE USER userX;
336 SET PASSWORD FOR userX = 'abcd' RETAIN CURRENT PASSWORD;
337 ERROR HY000: Empty password can not be retained as second password for user 'userX'@'%'.
338 DROP USER userX;
339 #-----------------------------------------------------------------------
340 # Test 16: General log
341 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
342 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
343 TRUNCATE TABLE mysql.general_log;
344 SET @old_log_output = @@global.log_output;
345 SET @old_general_log = @@global.general_log;
346 SET @old_general_log_file = @@global.general_log_file;
347 SET GLOBAL general_log_file = 'MYSQLTEST_VARDIR/log/multiple_passwords_general.log';
348 SET GLOBAL log_output = 'FILE,TABLE';
349 SET GLOBAL general_log= 'ON';
350 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
351 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
352 SET PASSWORD FOR arthurdent@localhost = 'abcd' RETAIN CURRENT PASSWORD;
353 SET PASSWORD = 'efgh' REPLACE 'abcd' RETAIN CURRENT PASSWORD;
354 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD,
355 marvintheparanoidandroid@localhost DISCARD OLD PASSWORD,
356 zaphodbeeblebrox@localhost IDENTIFIED BY 'abcd'
357 REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 22 PASSWORD EXPIRE DEFAULT
358 PASSWORD HISTORY 10 PASSWORD REUSE INTERVAL 10 DAY
359 PASSWORD REQUIRE CURRENT OPTIONAL ACCOUNT UNLOCK;
360 Show what is logged:
361 ------ rewrite ------
362 SELECT argument FROM mysql.general_log WHERE argument LIKE 'ALTER USER %' AND
363 command_type NOT LIKE 'Prepare';
364 argument
365 ALTER USER 'arthurdent'@'localhost' IDENTIFIED BY <secret> RETAIN CURRENT PASSWORD
366 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD
367 ALTER USER 'arthurdent'@'localhost' IDENTIFIED BY <secret> RETAIN CURRENT PASSWORD,'marvintheparanoidandroid'@'localhost' DISCARD OLD PASSWORD,'zaphodbeeblebrox'@'localhost' IDENTIFIED BY <secret> REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 22 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 10 PASSWORD REUSE INTERVAL 10 DAY PASSWORD REQUIRE CURRENT OPTIONAL
368 SELECT argument FROM mysql.general_log WHERE argument LIKE 'SET PASSWORD %';
369 argument
370 SET PASSWORD FOR `arthurdent`@`localhost`=<secret> RETAIN CURRENT PASSWORD
371 SET PASSWORD FOR `arthurdent`@`localhost`=<secret> REPLACE <secret> RETAIN CURRENT PASSWORD
372 ------ done ------
373 #-----------------------------------------------------------------------
374 # Cleanup : Destroy connections, Drop users
375 DROP USER arthurdent@localhost;
376 DROP USER marvintheparanoidandroid@localhost;
377 DROP USER zaphodbeeblebrox@localhost;
378 #-----------------------------------------------------------------------