File: multiple_passwords_ddl.result

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