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;
#-----------------------------------------------------------------------
|