1# Default server variable value must be OFF 2SELECT @@global.password_require_current; 3@@global.password_require_current 40 5 6######################################################################## 7# 1. Create user without any PASSWPRD REQUIRE CURRENT clause which 8# must be equivalent to DEFAULT clause 9######################################################################## 10CREATE USER non_priv_usr IDENTIFIED BY 'pwd1', 11priv_usr IDENTIFIED BY 'pwd2'; 12 13# Show how the users were created 14 15SHOW CREATE USER non_priv_usr; 16CREATE USER for non_priv_usr@% 17CREATE USER 'non_priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT 18SHOW CREATE USER priv_usr; 19CREATE USER for priv_usr@% 20CREATE USER 'priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT 21SELECT password_require_current from mysql.user 22where user like '%priv_usr' OR user like '%non_priv_usr'; 23password_require_current 24NULL 25NULL 26 27# Grant the update privileges to the user 28GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION; 29 30#------------------------------------------------------------------------ 31# Priv user changing his own password 32#------------------------------------------------------------------------ 33 34# Without REPLACE clause 35SET PASSWORD='pwd1'; 36SET PASSWORD for priv_usr='pwd2'; 37ALTER USER user() IDENTIFIED BY 'pwd1'; 38ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 39 40# With invalid current password in the REPLACE clause 41SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 42ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 43SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 44ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 45ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 46ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 47ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 48ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 49 50# With valid current password in the REPLACE clause 51SET PASSWORD='pwd1' REPLACE 'pwd2'; 52SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1'; 53ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 54ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 55 56#------------------------------------------------------------------------ 57# Priv user changing the password of non_priv_usr 58#------------------------------------------------------------------------ 59 60SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1'; 61ERROR HY000: Do not specify the current password while changing it for other users. 62ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE ''; 63ERROR HY000: Do not specify the current password while changing it for other users. 64 65SET PASSWORD for non_priv_usr='pwd2'; 66ALTER USER non_priv_usr IDENTIFIED BY 'pwd2'; 67 68#------------------------------------------------------------------------ 69# One priv user(root) changing password for other priv user (priv_usr) 70#------------------------------------------------------------------------ 71 72SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 73ERROR HY000: Do not specify the current password while changing it for other users. 74ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 75ERROR HY000: Do not specify the current password while changing it for other users. 76 77ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 78SET PASSWORD for priv_usr='pwd1'; 79 80# Now test with Global server variable ON 81SET @@global.password_require_current=ON; 82 83#------------------------------------------------------------------------ 84# Priv user changing his own password 85#------------------------------------------------------------------------ 86 87# Can change without REPLACE clause 88SET PASSWORD='pwd2'; 89SET PASSWORD for priv_usr='pwd1'; 90ALTER USER user() IDENTIFIED BY 'pwd2'; 91ALTER USER priv_usr IDENTIFIED BY 'pwd1'; 92 93# Incorrect password specified in the REPLACE clause 94SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 95ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 96SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 97ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 98ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 99ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 100ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 101ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 102 103# Correct password specified in the REPLACE clause 104SET PASSWORD='pwd2' REPLACE 'pwd1'; 105SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 106ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 107ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2' 108 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT; 109 110#------------------------------------------------------------------------ 111# Priv user changing the password of non_priv_usr 112#------------------------------------------------------------------------ 113 114SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 115ERROR HY000: Do not specify the current password while changing it for other users. 116ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 117ERROR HY000: Do not specify the current password while changing it for other users. 118 119SET PASSWORD for non_priv_usr='pwd2' ; 120ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 121 122#------------------------------------------------------------------------ 123# One priv user(root) changing password for other priv user (priv_usr) 124#------------------------------------------------------------------------ 125 126SET PASSWORD for priv_usr='pwd1' REPLACE ''; 127ERROR HY000: Do not specify the current password while changing it for other users. 128ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 129ERROR HY000: Do not specify the current password while changing it for other users. 130SET PASSWORD for priv_usr='pwd1'; 131ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 132 133# Reset the privileges and global variable 134 135REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%'; 136SET @@global.password_require_current=OFF; 137 138#------------------------------------------------------------------------ 139# Non priv user changing his own password 140#------------------------------------------------------------------------ 141 142# REPLACE clause is not specified 143SET PASSWORD='pwd2'; 144SET PASSWORD for non_priv_usr='pwd2'; 145ALTER USER user() IDENTIFIED BY 'pwd1'; 146ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 147 148# Incorrect password specified in the REPLACE clause 149SET PASSWORD='pwd2' REPLACE '' ; 150ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 151SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 152ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 153ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 154ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 155ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 156ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 157 158# Correct password specified in the REPLACE clause 159SET PASSWORD='pwd2' REPLACE 'pwd1'; 160SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 161ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 162ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 163 164# Now test with Global server variable ON 165SET @@global.password_require_current=ON; 166 167#------------------------------------------------------------------------ 168# Non priv user changing his own password 169#------------------------------------------------------------------------ 170 171# REPLACE clause is not specified 172SET PASSWORD='pwd2'; 173ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 174SET PASSWORD for non_priv_usr='pwd2'; 175ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 176ALTER USER user() IDENTIFIED BY 'pwd1'; 177ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 178ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 179ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 180 181# Incorrect password specified in the REPLACE clause 182SET PASSWORD='pwd2' REPLACE '' ; 183ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 184SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 185ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 186ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 187ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 188ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 189ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 190 191# Correct password specified in the REPLACE clause 192SET PASSWORD='pwd2' REPLACE 'pwd1'; 193SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 194ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 195ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 196 197# Reset the Global server variable to OFF 198SET @@global.password_require_current=OFF; 199 200DROP USER non_priv_usr, priv_usr; 201 202######################################################################## 203# 2. Create user with PASSWPRD REQUIRE CURRENT DEFAULT clause 204######################################################################## 205CREATE USER non_priv_usr IDENTIFIED BY 'pwd1', 206priv_usr IDENTIFIED BY 'pwd2' 207 PASSWORD REQUIRE CURRENT DEFAULT; 208 209# Show how the users were created 210 211SHOW CREATE USER non_priv_usr; 212CREATE USER for non_priv_usr@% 213CREATE USER 'non_priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT 214SHOW CREATE USER priv_usr; 215CREATE USER for priv_usr@% 216CREATE USER 'priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT 217SELECT password_require_current from mysql.user 218where user like '%priv_usr' OR user like '%non_priv_usr'; 219password_require_current 220NULL 221NULL 222 223# Grant the update privileges to the user 224GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION; 225 226#------------------------------------------------------------------------ 227# Priv user changing his own password 228#------------------------------------------------------------------------ 229 230# Without REPLACE clause 231SET PASSWORD='pwd1'; 232SET PASSWORD for priv_usr='pwd2'; 233ALTER USER user() IDENTIFIED BY 'pwd1'; 234ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 235 236# With invalid current password in the REPLACE clause 237SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 238ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 239SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 240ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 241ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 242ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 243ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 244ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 245 246# With valid current password in the REPLACE clause 247SET PASSWORD='pwd1' REPLACE 'pwd2'; 248SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1'; 249ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 250ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 251 252#------------------------------------------------------------------------ 253# Priv user changing the password of non_priv_usr 254#------------------------------------------------------------------------ 255 256SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1'; 257ERROR HY000: Do not specify the current password while changing it for other users. 258ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE ''; 259ERROR HY000: Do not specify the current password while changing it for other users. 260 261SET PASSWORD for non_priv_usr='pwd2'; 262ALTER USER non_priv_usr IDENTIFIED BY 'pwd2'; 263 264#------------------------------------------------------------------------ 265# One priv user(root) changing password for other priv user (priv_usr) 266#------------------------------------------------------------------------ 267 268SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 269ERROR HY000: Do not specify the current password while changing it for other users. 270ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 271ERROR HY000: Do not specify the current password while changing it for other users. 272 273ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 274SET PASSWORD for priv_usr='pwd1'; 275 276# Now test with Global server variable ON 277SET @@global.password_require_current=ON; 278 279#------------------------------------------------------------------------ 280# Priv user changing his own password 281#------------------------------------------------------------------------ 282 283# Can change without REPLACE clause 284SET PASSWORD='pwd2'; 285SET PASSWORD for priv_usr='pwd1'; 286ALTER USER user() IDENTIFIED BY 'pwd2'; 287ALTER USER priv_usr IDENTIFIED BY 'pwd1'; 288 289# Incorrect password specified in the REPLACE clause 290SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 291ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 292SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 293ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 294ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 295ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 296ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 297ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 298 299# Correct password specified in the REPLACE clause 300SET PASSWORD='pwd2' REPLACE 'pwd1'; 301SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 302ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 303ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2' 304 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT; 305 306#------------------------------------------------------------------------ 307# Priv user changing the password of non_priv_usr 308#------------------------------------------------------------------------ 309 310SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 311ERROR HY000: Do not specify the current password while changing it for other users. 312ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 313ERROR HY000: Do not specify the current password while changing it for other users. 314 315SET PASSWORD for non_priv_usr='pwd2' ; 316ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 317 318#------------------------------------------------------------------------ 319# One priv user(root) changing password for other priv user (priv_usr) 320#------------------------------------------------------------------------ 321 322SET PASSWORD for priv_usr='pwd1' REPLACE ''; 323ERROR HY000: Do not specify the current password while changing it for other users. 324ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 325ERROR HY000: Do not specify the current password while changing it for other users. 326SET PASSWORD for priv_usr='pwd1'; 327ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 328 329# Reset the privileges and global variable 330 331REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%'; 332SET @@global.password_require_current=OFF; 333 334#------------------------------------------------------------------------ 335# Non priv user changing his own password 336#------------------------------------------------------------------------ 337 338# REPLACE clause is not specified 339SET PASSWORD='pwd2'; 340SET PASSWORD for non_priv_usr='pwd2'; 341ALTER USER user() IDENTIFIED BY 'pwd1'; 342ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 343 344# Incorrect password specified in the REPLACE clause 345SET PASSWORD='pwd2' REPLACE '' ; 346ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 347SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 348ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 349ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 350ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 351ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 352ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 353 354# Correct password specified in the REPLACE clause 355SET PASSWORD='pwd2' REPLACE 'pwd1'; 356SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 357ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 358ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 359 360# Now test with Global server variable ON 361SET @@global.password_require_current=ON; 362 363#------------------------------------------------------------------------ 364# Non priv user changing his own password 365#------------------------------------------------------------------------ 366 367# REPLACE clause is not specified 368SET PASSWORD='pwd2'; 369ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 370SET PASSWORD for non_priv_usr='pwd2'; 371ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 372ALTER USER user() IDENTIFIED BY 'pwd1'; 373ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 374ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 375ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 376 377# Incorrect password specified in the REPLACE clause 378SET PASSWORD='pwd2' REPLACE '' ; 379ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 380SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 381ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 382ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 383ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 384ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 385ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 386 387# Correct password specified in the REPLACE clause 388SET PASSWORD='pwd2' REPLACE 'pwd1'; 389SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 390ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 391ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 392 393# Reset the Global server variable to OFF 394SET @@global.password_require_current=OFF; 395 396DROP USER non_priv_usr, priv_usr; 397 398######################################################################## 399# 3. Create user with CURRENT clause 400######################################################################## 401CREATE USER IF NOT EXISTS non_priv_usr IDENTIFIED BY 'pwd1', 402priv_usr IDENTIFIED BY 'pwd2' PASSWORD REQUIRE CURRENT; 403 404# Show how the users were created 405 406SHOW CREATE USER non_priv_usr; 407CREATE USER for non_priv_usr@% 408CREATE USER 'non_priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT 409SHOW CREATE USER priv_usr; 410CREATE USER for priv_usr@% 411CREATE USER 'priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT 412SELECT password_require_current from mysql.user 413where user like '%priv_usr' OR user like '%non_priv_usr'; 414password_require_current 415Y 416Y 417 418# Grant the update privileges to the user 419GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION; 420 421#------------------------------------------------------------------------ 422# Priv user changing his own password 423#------------------------------------------------------------------------ 424 425# Without REPLACE clause 426SET PASSWORD='pwd1'; 427SET PASSWORD for priv_usr='pwd2'; 428ALTER USER user() IDENTIFIED BY 'pwd1'; 429ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 430 431# With invalid current password in the REPLACE clause 432SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 433ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 434SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 435ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 436ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 437ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 438ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 439ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 440 441# With valid current password in the REPLACE clause 442SET PASSWORD='pwd1' REPLACE 'pwd2'; 443SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1'; 444ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 445ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 446 447#------------------------------------------------------------------------ 448# Priv user changing the password of non_priv_usr 449#------------------------------------------------------------------------ 450 451SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1'; 452ERROR HY000: Do not specify the current password while changing it for other users. 453ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE ''; 454ERROR HY000: Do not specify the current password while changing it for other users. 455 456SET PASSWORD for non_priv_usr='pwd2'; 457ALTER USER non_priv_usr IDENTIFIED BY 'pwd2'; 458 459#------------------------------------------------------------------------ 460# One priv user(root) changing password for other priv user (priv_usr) 461#------------------------------------------------------------------------ 462 463SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 464ERROR HY000: Do not specify the current password while changing it for other users. 465ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 466ERROR HY000: Do not specify the current password while changing it for other users. 467 468ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 469SET PASSWORD for priv_usr='pwd1'; 470 471# Now test with Global server variable ON 472SET @@global.password_require_current=ON; 473 474#------------------------------------------------------------------------ 475# Priv user changing his own password 476#------------------------------------------------------------------------ 477 478# Can change without REPLACE clause 479SET PASSWORD='pwd2'; 480SET PASSWORD for priv_usr='pwd1'; 481ALTER USER user() IDENTIFIED BY 'pwd2'; 482ALTER USER priv_usr IDENTIFIED BY 'pwd1'; 483 484# Incorrect password specified in the REPLACE clause 485SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 486ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 487SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 488ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 489ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 490ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 491ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 492ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 493 494# Correct password specified in the REPLACE clause 495SET PASSWORD='pwd2' REPLACE 'pwd1'; 496SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 497ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 498ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2' 499 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT; 500 501#------------------------------------------------------------------------ 502# Priv user changing the password of non_priv_usr 503#------------------------------------------------------------------------ 504 505SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 506ERROR HY000: Do not specify the current password while changing it for other users. 507ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 508ERROR HY000: Do not specify the current password while changing it for other users. 509 510SET PASSWORD for non_priv_usr='pwd2' ; 511ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 512 513#------------------------------------------------------------------------ 514# One priv user(root) changing password for other priv user (priv_usr) 515#------------------------------------------------------------------------ 516 517SET PASSWORD for priv_usr='pwd1' REPLACE ''; 518ERROR HY000: Do not specify the current password while changing it for other users. 519ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 520ERROR HY000: Do not specify the current password while changing it for other users. 521SET PASSWORD for priv_usr='pwd1'; 522ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 523 524# Reset the privileges and global variable 525 526REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%'; 527SET @@global.password_require_current=OFF; 528 529#------------------------------------------------------------------------ 530# Non priv user changing his own password 531#------------------------------------------------------------------------ 532 533# REPLACE clause is not specified 534SET PASSWORD='pwd2'; 535ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 536SET PASSWORD for non_priv_usr='pwd2'; 537ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 538ALTER USER user() IDENTIFIED BY 'pwd1'; 539ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 540ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 541ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 542 543# Incorrect password specified in the REPLACE clause 544SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 545ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 546SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 547ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 548ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 549ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 550ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 551ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 552 553# Correct password specified in the REPLACE clause 554SET PASSWORD='pwd2' REPLACE 'pwd1'; 555SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 556ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 557ALTER USER IF EXISTS non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 558 559# Now test with Global server variable ON 560SET @@global.password_require_current=ON; 561 562#------------------------------------------------------------------------ 563# Non priv user changing his own password 564#------------------------------------------------------------------------ 565 566# REPLACE clause is not specified 567SET PASSWORD='pwd2'; 568ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 569SET PASSWORD for non_priv_usr='pwd2'; 570ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 571ALTER USER user() IDENTIFIED BY 'pwd2'; 572ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 573ALTER USER non_priv_usr IDENTIFIED BY 'pwd2'; 574ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 575 576# Incorrect password specified in the REPLACE clause 577SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 578ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 579SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 580ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 581ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 582ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 583ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 584ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 585 586# Correct password specified in the REPLACE clause 587SET PASSWORD='pwd2' REPLACE 'pwd1'; 588SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 589ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 590ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 591 592# Reset the Global server variable to OFF 593SET @@global.password_require_current=OFF; 594 595DROP USER non_priv_usr, priv_usr; 596 597######################################################################## 598# 4. Create user with OPTIONAL clause 599######################################################################## 600CREATE USER non_priv_usr IDENTIFIED BY 'pwd1', 601priv_usr IDENTIFIED BY 'pwd2' 602 PASSWORD REQUIRE CURRENT OPTIONAL; 603 604# Show how the users were created 605 606SHOW CREATE USER non_priv_usr; 607CREATE USER for non_priv_usr@% 608CREATE USER 'non_priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT OPTIONAL 609SHOW CREATE USER priv_usr; 610CREATE USER for priv_usr@% 611CREATE USER 'priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT OPTIONAL 612SELECT password_require_current from mysql.user 613where user like '%priv_usr' OR user like '%non_priv_usr'; 614password_require_current 615N 616N 617 618# Grant the update privileges to the user 619GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION; 620 621#------------------------------------------------------------------------ 622# Priv user changing his own password 623#------------------------------------------------------------------------ 624 625# Without REPLACE clause 626SET PASSWORD='pwd1'; 627SET PASSWORD for priv_usr='pwd2'; 628ALTER USER user() IDENTIFIED BY 'pwd1'; 629ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 630 631# With invalid current password in the REPLACE clause 632SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 633ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 634SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 635ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 636ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 637ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 638ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 639ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 640 641# With valid current password in the REPLACE clause 642SET PASSWORD='pwd1' REPLACE 'pwd2'; 643SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1'; 644ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 645ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 646 647#------------------------------------------------------------------------ 648# Priv user changing the password of non_priv_usr 649#------------------------------------------------------------------------ 650 651SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1'; 652ERROR HY000: Do not specify the current password while changing it for other users. 653ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE ''; 654ERROR HY000: Do not specify the current password while changing it for other users. 655 656SET PASSWORD for non_priv_usr='pwd2'; 657ALTER USER non_priv_usr IDENTIFIED BY 'pwd2'; 658 659#------------------------------------------------------------------------ 660# One priv user(root) changing password for other priv user (priv_usr) 661#------------------------------------------------------------------------ 662 663SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 664ERROR HY000: Do not specify the current password while changing it for other users. 665ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 666ERROR HY000: Do not specify the current password while changing it for other users. 667 668ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 669SET PASSWORD for priv_usr='pwd1'; 670 671# Now test with Global server variable ON 672SET @@global.password_require_current=ON; 673 674#------------------------------------------------------------------------ 675# Priv user changing his own password 676#------------------------------------------------------------------------ 677 678# Can change without REPLACE clause 679SET PASSWORD='pwd2'; 680SET PASSWORD for priv_usr='pwd1'; 681ALTER USER user() IDENTIFIED BY 'pwd2'; 682ALTER USER priv_usr IDENTIFIED BY 'pwd1'; 683 684# Incorrect password specified in the REPLACE clause 685SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 686ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 687SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 688ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 689ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 690ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 691ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 692ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 693 694# Correct password specified in the REPLACE clause 695SET PASSWORD='pwd2' REPLACE 'pwd1'; 696SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 697ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 698ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2' 699 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT; 700 701#------------------------------------------------------------------------ 702# Priv user changing the password of non_priv_usr 703#------------------------------------------------------------------------ 704 705SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 706ERROR HY000: Do not specify the current password while changing it for other users. 707ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 708ERROR HY000: Do not specify the current password while changing it for other users. 709 710SET PASSWORD for non_priv_usr='pwd2' ; 711ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 712 713#------------------------------------------------------------------------ 714# One priv user(root) changing password for other priv user (priv_usr) 715#------------------------------------------------------------------------ 716 717SET PASSWORD for priv_usr='pwd1' REPLACE ''; 718ERROR HY000: Do not specify the current password while changing it for other users. 719ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 720ERROR HY000: Do not specify the current password while changing it for other users. 721SET PASSWORD for priv_usr='pwd1'; 722ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 723 724# Reset the privileges and global variable 725 726REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%'; 727SET @@global.password_require_current=OFF; 728 729#------------------------------------------------------------------------ 730# Non priv user changing his own password 731#------------------------------------------------------------------------ 732 733# REPLACE clause is not specified 734SET PASSWORD='pwd2'; 735SET PASSWORD for non_priv_usr='pwd2'; 736ALTER USER user() IDENTIFIED BY 'pwd1'; 737ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 738 739# Incorrect password specified in the REPLACE clause 740SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 741ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 742SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 743ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 744ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 745ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 746ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 747ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 748 749# Correct password specified in the REPLACE clause 750SET PASSWORD='pwd2' REPLACE 'pwd1'; 751SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 752ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 753ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 754 755# Now test with Global server variable ON 756SET @@global.password_require_current=ON; 757 758#------------------------------------------------------------------------ 759# Non priv user changing his own password 760#------------------------------------------------------------------------ 761 762# REPLACE clause is not specified 763SET PASSWORD='pwd2'; 764SET PASSWORD for non_priv_usr='pwd1'; 765ALTER USER user() IDENTIFIED BY 'pwd2'; 766ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 767 768# Incorrect password specified in the REPLACE clause 769SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 770ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 771SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 772ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 773ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 774ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 775ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 776ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 777 778# Correct password specified in the REPLACE clause 779SET PASSWORD='pwd2' REPLACE 'pwd1'; 780SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 781ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 782ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 783 784# Reset the Global server variable to OFF 785SET @@global.password_require_current=OFF; 786 787DROP USER non_priv_usr, priv_usr; 788 789######################################################################## 790# 5. Verify the ALTER USER scenarios 791######################################################################## 792CREATE USER non_priv_usr IDENTIFIED BY 'pwd1', 793priv_usr IDENTIFIED BY 'pwd2' 794 PASSWORD REQUIRE CURRENT; 795# 796# Change password_require_current --> password_require_current_optional 797# 798ALTER USER non_priv_usr IDENTIFIED BY 'pwd1', 799priv_usr IDENTIFIED BY 'pwd2' 800 WITH MAX_USER_CONNECTIONS 2 801PASSWORD REQUIRE CURRENT OPTIONAL 802PASSWORD EXPIRE NEVER; 803 804# Show how the users were created 805 806SHOW CREATE USER non_priv_usr; 807CREATE USER for non_priv_usr@% 808CREATE USER 'non_priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT OPTIONAL 809SHOW CREATE USER priv_usr; 810CREATE USER for priv_usr@% 811CREATE USER 'priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT OPTIONAL 812SELECT password_require_current from mysql.user 813where user like '%priv_usr' OR user like '%non_priv_usr'; 814password_require_current 815N 816N 817 818# Grant the update privileges to the user 819GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION; 820 821#------------------------------------------------------------------------ 822# Priv user changing his own password 823#------------------------------------------------------------------------ 824 825# Without REPLACE clause 826SET PASSWORD='pwd1'; 827SET PASSWORD for priv_usr='pwd2'; 828ALTER USER user() IDENTIFIED BY 'pwd1'; 829ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 830 831# With invalid current password in the REPLACE clause 832SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 833ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 834SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 835ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 836ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 837ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 838ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 839ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 840 841# With valid current password in the REPLACE clause 842SET PASSWORD='pwd1' REPLACE 'pwd2'; 843SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1'; 844ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 845ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 846 847#------------------------------------------------------------------------ 848# Priv user changing the password of non_priv_usr 849#------------------------------------------------------------------------ 850 851SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1'; 852ERROR HY000: Do not specify the current password while changing it for other users. 853ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE ''; 854ERROR HY000: Do not specify the current password while changing it for other users. 855 856SET PASSWORD for non_priv_usr='pwd2'; 857ALTER USER non_priv_usr IDENTIFIED BY 'pwd2'; 858 859#------------------------------------------------------------------------ 860# One priv user(root) changing password for other priv user (priv_usr) 861#------------------------------------------------------------------------ 862 863SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 864ERROR HY000: Do not specify the current password while changing it for other users. 865ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 866ERROR HY000: Do not specify the current password while changing it for other users. 867 868ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 869SET PASSWORD for priv_usr='pwd1'; 870 871# Now test with Global server variable ON 872SET @@global.password_require_current=ON; 873 874#------------------------------------------------------------------------ 875# Priv user changing his own password 876#------------------------------------------------------------------------ 877 878# Can change without REPLACE clause 879SET PASSWORD='pwd2'; 880SET PASSWORD for priv_usr='pwd1'; 881ALTER USER user() IDENTIFIED BY 'pwd2'; 882ALTER USER priv_usr IDENTIFIED BY 'pwd1'; 883 884# Incorrect password specified in the REPLACE clause 885SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 886ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 887SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 888ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 889ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 890ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 891ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 892ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 893 894# Correct password specified in the REPLACE clause 895SET PASSWORD='pwd2' REPLACE 'pwd1'; 896SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 897ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 898ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2' 899 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT; 900 901#------------------------------------------------------------------------ 902# Priv user changing the password of non_priv_usr 903#------------------------------------------------------------------------ 904 905SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 906ERROR HY000: Do not specify the current password while changing it for other users. 907ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 908ERROR HY000: Do not specify the current password while changing it for other users. 909 910SET PASSWORD for non_priv_usr='pwd2' ; 911ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 912 913#------------------------------------------------------------------------ 914# One priv user(root) changing password for other priv user (priv_usr) 915#------------------------------------------------------------------------ 916 917SET PASSWORD for priv_usr='pwd1' REPLACE ''; 918ERROR HY000: Do not specify the current password while changing it for other users. 919ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 920ERROR HY000: Do not specify the current password while changing it for other users. 921SET PASSWORD for priv_usr='pwd1'; 922ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 923 924# Reset the privileges and global variable 925 926REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%'; 927SET @@global.password_require_current=OFF; 928 929#------------------------------------------------------------------------ 930# Non priv user changing his own password 931#------------------------------------------------------------------------ 932 933# REPLACE clause is not specified 934SET PASSWORD='pwd2'; 935SET PASSWORD for non_priv_usr='pwd2'; 936ALTER USER user() IDENTIFIED BY 'pwd1'; 937ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 938 939# Incorrect password specified in the REPLACE clause 940SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 941ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 942SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 943ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 944ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 945ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 946ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 947ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 948 949# Correct password specified in the REPLACE clause 950SET PASSWORD='pwd2' REPLACE 'pwd1'; 951SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 952ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 953ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 954 955# Now test with Global server variable ON 956SET @@global.password_require_current=ON; 957 958#------------------------------------------------------------------------ 959# Non priv user changing his own password 960#------------------------------------------------------------------------ 961 962# REPLACE clause is not specified 963SET PASSWORD='pwd2'; 964SET PASSWORD for non_priv_usr='pwd1'; 965ALTER USER user() IDENTIFIED BY 'pwd2'; 966ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 967 968# Incorrect password specified in the REPLACE clause 969SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 970ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 971SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 972ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 973ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 974ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 975ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 976ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 977 978# Correct password specified in the REPLACE clause 979SET PASSWORD='pwd2' REPLACE 'pwd1'; 980SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 981ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 982ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 983 984# Reset the Global server variable to OFF 985SET @@global.password_require_current=OFF; 986 987# 988# Change password_require_optional --> password_require_current_default 989# 990ALTER USER non_priv_usr IDENTIFIED BY 'pwd1', 991priv_usr IDENTIFIED BY 'pwd2' 992 PASSWORD EXPIRE DEFAULT 993PASSWORD REUSE INTERVAL 0 DAY 994PASSWORD REQUIRE CURRENT DEFAULT; 995 996# Show how the users were created 997 998SHOW CREATE USER non_priv_usr; 999CREATE USER for non_priv_usr@% 1000CREATE USER 'non_priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL 0 DAY PASSWORD REQUIRE CURRENT DEFAULT 1001SHOW CREATE USER priv_usr; 1002CREATE USER for priv_usr@% 1003CREATE USER 'priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 100 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL 0 DAY PASSWORD REQUIRE CURRENT DEFAULT 1004SELECT password_require_current from mysql.user 1005where user like '%priv_usr' OR user like '%non_priv_usr'; 1006password_require_current 1007NULL 1008NULL 1009 1010# Grant the update privileges to the user 1011GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION; 1012 1013#------------------------------------------------------------------------ 1014# Priv user changing his own password 1015#------------------------------------------------------------------------ 1016 1017# Without REPLACE clause 1018SET PASSWORD='pwd1'; 1019SET PASSWORD for priv_usr='pwd2'; 1020ALTER USER user() IDENTIFIED BY 'pwd1'; 1021ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 1022 1023# With invalid current password in the REPLACE clause 1024SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 1025ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1026SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 1027ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1028ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1029ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1030ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1031ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1032 1033# With valid current password in the REPLACE clause 1034SET PASSWORD='pwd1' REPLACE 'pwd2'; 1035SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1'; 1036ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1037ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1038 1039#------------------------------------------------------------------------ 1040# Priv user changing the password of non_priv_usr 1041#------------------------------------------------------------------------ 1042 1043SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1'; 1044ERROR HY000: Do not specify the current password while changing it for other users. 1045ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE ''; 1046ERROR HY000: Do not specify the current password while changing it for other users. 1047 1048SET PASSWORD for non_priv_usr='pwd2'; 1049ALTER USER non_priv_usr IDENTIFIED BY 'pwd2'; 1050 1051#------------------------------------------------------------------------ 1052# One priv user(root) changing password for other priv user (priv_usr) 1053#------------------------------------------------------------------------ 1054 1055SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 1056ERROR HY000: Do not specify the current password while changing it for other users. 1057ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1058ERROR HY000: Do not specify the current password while changing it for other users. 1059 1060ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 1061SET PASSWORD for priv_usr='pwd1'; 1062 1063# Now test with Global server variable ON 1064SET @@global.password_require_current=ON; 1065 1066#------------------------------------------------------------------------ 1067# Priv user changing his own password 1068#------------------------------------------------------------------------ 1069 1070# Can change without REPLACE clause 1071SET PASSWORD='pwd2'; 1072SET PASSWORD for priv_usr='pwd1'; 1073ALTER USER user() IDENTIFIED BY 'pwd2'; 1074ALTER USER priv_usr IDENTIFIED BY 'pwd1'; 1075 1076# Incorrect password specified in the REPLACE clause 1077SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 1078ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1079SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 1080ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1081ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1082ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1083ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1084ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1085 1086# Correct password specified in the REPLACE clause 1087SET PASSWORD='pwd2' REPLACE 'pwd1'; 1088SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 1089ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1090ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2' 1091 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT; 1092 1093#------------------------------------------------------------------------ 1094# Priv user changing the password of non_priv_usr 1095#------------------------------------------------------------------------ 1096 1097SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 1098ERROR HY000: Do not specify the current password while changing it for other users. 1099ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1100ERROR HY000: Do not specify the current password while changing it for other users. 1101 1102SET PASSWORD for non_priv_usr='pwd2' ; 1103ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 1104 1105#------------------------------------------------------------------------ 1106# One priv user(root) changing password for other priv user (priv_usr) 1107#------------------------------------------------------------------------ 1108 1109SET PASSWORD for priv_usr='pwd1' REPLACE ''; 1110ERROR HY000: Do not specify the current password while changing it for other users. 1111ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1112ERROR HY000: Do not specify the current password while changing it for other users. 1113SET PASSWORD for priv_usr='pwd1'; 1114ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 1115 1116# Reset the privileges and global variable 1117 1118REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%'; 1119SET @@global.password_require_current=OFF; 1120 1121#------------------------------------------------------------------------ 1122# Non priv user changing his own password 1123#------------------------------------------------------------------------ 1124 1125# REPLACE clause is not specified 1126SET PASSWORD='pwd2'; 1127SET PASSWORD for non_priv_usr='pwd2'; 1128ALTER USER user() IDENTIFIED BY 'pwd1'; 1129ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 1130 1131# Incorrect password specified in the REPLACE clause 1132SET PASSWORD='pwd2' REPLACE '' ; 1133ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1134SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 1135ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1136ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1137ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1138ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1139ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1140 1141# Correct password specified in the REPLACE clause 1142SET PASSWORD='pwd2' REPLACE 'pwd1'; 1143SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 1144ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1145ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1146 1147# Now test with Global server variable ON 1148SET @@global.password_require_current=ON; 1149 1150#------------------------------------------------------------------------ 1151# Non priv user changing his own password 1152#------------------------------------------------------------------------ 1153 1154# REPLACE clause is not specified 1155SET PASSWORD='pwd2'; 1156ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1157SET PASSWORD for non_priv_usr='pwd2'; 1158ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1159ALTER USER user() IDENTIFIED BY 'pwd1'; 1160ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1161ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 1162ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1163 1164# Incorrect password specified in the REPLACE clause 1165SET PASSWORD='pwd2' REPLACE '' ; 1166ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1167SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 1168ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1169ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1170ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1171ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1172ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1173 1174# Correct password specified in the REPLACE clause 1175SET PASSWORD='pwd2' REPLACE 'pwd1'; 1176SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 1177ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1178ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1179 1180# Reset the Global server variable to OFF 1181SET @@global.password_require_current=OFF; 1182 1183# 1184# Change password_require_default --> password_require_current 1185# 1186ALTER USER non_priv_usr IDENTIFIED BY 'pwd1', 1187priv_usr IDENTIFIED BY 'pwd2' 1188 WITH MAX_QUERIES_PER_HOUR 100 1189PASSWORD HISTORY 0 1190PASSWORD REQUIRE CURRENT; 1191 1192# Show how the users were created 1193 1194SHOW CREATE USER non_priv_usr; 1195CREATE USER for non_priv_usr@% 1196CREATE USER 'non_priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 100 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY PASSWORD REQUIRE CURRENT 1197SHOW CREATE USER priv_usr; 1198CREATE USER for priv_usr@% 1199CREATE USER 'priv_usr'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 100 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY PASSWORD REQUIRE CURRENT 1200SELECT password_require_current from mysql.user 1201where user like '%priv_usr' OR user like '%non_priv_usr'; 1202password_require_current 1203Y 1204Y 1205 1206# Grant the update privileges to the user 1207GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION; 1208 1209#------------------------------------------------------------------------ 1210# Priv user changing his own password 1211#------------------------------------------------------------------------ 1212 1213# Without REPLACE clause 1214SET PASSWORD='pwd1'; 1215SET PASSWORD for priv_usr='pwd2'; 1216ALTER USER user() IDENTIFIED BY 'pwd1'; 1217ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 1218 1219# With invalid current password in the REPLACE clause 1220SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 1221ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1222SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 1223ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1224ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1225ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1226ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1227ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1228 1229# With valid current password in the REPLACE clause 1230SET PASSWORD='pwd1' REPLACE 'pwd2'; 1231SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1'; 1232ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1233ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1234 1235#------------------------------------------------------------------------ 1236# Priv user changing the password of non_priv_usr 1237#------------------------------------------------------------------------ 1238 1239SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1'; 1240ERROR HY000: Do not specify the current password while changing it for other users. 1241ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE ''; 1242ERROR HY000: Do not specify the current password while changing it for other users. 1243 1244SET PASSWORD for non_priv_usr='pwd2'; 1245ALTER USER non_priv_usr IDENTIFIED BY 'pwd2'; 1246 1247#------------------------------------------------------------------------ 1248# One priv user(root) changing password for other priv user (priv_usr) 1249#------------------------------------------------------------------------ 1250 1251SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 1252ERROR HY000: Do not specify the current password while changing it for other users. 1253ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1254ERROR HY000: Do not specify the current password while changing it for other users. 1255 1256ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 1257SET PASSWORD for priv_usr='pwd1'; 1258 1259# Now test with Global server variable ON 1260SET @@global.password_require_current=ON; 1261 1262#------------------------------------------------------------------------ 1263# Priv user changing his own password 1264#------------------------------------------------------------------------ 1265 1266# Can change without REPLACE clause 1267SET PASSWORD='pwd2'; 1268SET PASSWORD for priv_usr='pwd1'; 1269ALTER USER user() IDENTIFIED BY 'pwd2'; 1270ALTER USER priv_usr IDENTIFIED BY 'pwd1'; 1271 1272# Incorrect password specified in the REPLACE clause 1273SET PASSWORD='pwd2' REPLACE 'P&(*^_)D'; 1274ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1275SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D'; 1276ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1277ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1278ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1279ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1280ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1281 1282# Correct password specified in the REPLACE clause 1283SET PASSWORD='pwd2' REPLACE 'pwd1'; 1284SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2'; 1285ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1286ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2' 1287 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT; 1288 1289#------------------------------------------------------------------------ 1290# Priv user changing the password of non_priv_usr 1291#------------------------------------------------------------------------ 1292 1293SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 1294ERROR HY000: Do not specify the current password while changing it for other users. 1295ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1296ERROR HY000: Do not specify the current password while changing it for other users. 1297 1298SET PASSWORD for non_priv_usr='pwd2' ; 1299ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 1300 1301#------------------------------------------------------------------------ 1302# One priv user(root) changing password for other priv user (priv_usr) 1303#------------------------------------------------------------------------ 1304 1305SET PASSWORD for priv_usr='pwd1' REPLACE ''; 1306ERROR HY000: Do not specify the current password while changing it for other users. 1307ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1308ERROR HY000: Do not specify the current password while changing it for other users. 1309SET PASSWORD for priv_usr='pwd1'; 1310ALTER USER priv_usr IDENTIFIED BY 'pwd2'; 1311 1312# Reset the privileges and global variable 1313 1314REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%'; 1315SET @@global.password_require_current=OFF; 1316 1317#------------------------------------------------------------------------ 1318# Non priv user changing his own password 1319#------------------------------------------------------------------------ 1320 1321# REPLACE clause is not specified 1322SET PASSWORD='pwd2'; 1323ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1324SET PASSWORD for non_priv_usr='pwd2'; 1325ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1326ALTER USER user() IDENTIFIED BY 'pwd1'; 1327ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1328ALTER USER non_priv_usr IDENTIFIED BY 'pwd1'; 1329ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1330 1331# Incorrect password specified in the REPLACE clause 1332SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 1333ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1334SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 1335ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1336ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1337ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1338ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1339ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1340 1341# Correct password specified in the REPLACE clause 1342SET PASSWORD='pwd2' REPLACE 'pwd1'; 1343SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 1344ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1345ALTER USER IF EXISTS non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1346 1347# Now test with Global server variable ON 1348SET @@global.password_require_current=ON; 1349 1350#------------------------------------------------------------------------ 1351# Non priv user changing his own password 1352#------------------------------------------------------------------------ 1353 1354# REPLACE clause is not specified 1355SET PASSWORD='pwd2'; 1356ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1357SET PASSWORD for non_priv_usr='pwd2'; 1358ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1359ALTER USER user() IDENTIFIED BY 'pwd2'; 1360ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1361ALTER USER non_priv_usr IDENTIFIED BY 'pwd2'; 1362ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1363 1364# Incorrect password specified in the REPLACE clause 1365SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 1366ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1367SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D'; 1368ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1369ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1370ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1371ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1372ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1373 1374# Correct password specified in the REPLACE clause 1375SET PASSWORD='pwd2' REPLACE 'pwd1'; 1376SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2'; 1377ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1378ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1379 1380# Reset the Global server variable to OFF 1381SET @@global.password_require_current=OFF; 1382 1383DROP USER non_priv_usr, priv_usr; 1384 1385######################################################################## 1386# 6. Change password after privileges are granted/revoked from the user 1387######################################################################## 1388CREATE USER usr1 IDENTIFIED BY 'pwd1' PASSWORD REQUIRE CURRENT; 1389 1390#------------------------------------------------------------------------ 1391# Non priv user changing his own password 1392#------------------------------------------------------------------------ 1393 1394# REPLACE clause is not specified 1395SET PASSWORD='pwd2'; 1396ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1397SET PASSWORD for usr1='pwd2'; 1398ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1399ALTER USER user() IDENTIFIED BY 'pwd1'; 1400ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1401ALTER USER usr1 IDENTIFIED BY 'pwd1'; 1402ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1403 1404# Incorrect password specified in the REPLACE clause 1405SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 1406ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1407SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D'; 1408ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1409ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1410ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1411ALTER USER usr1 IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1412ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1413 1414# Correct password specified in the REPLACE clause 1415SET PASSWORD='pwd2' REPLACE 'pwd1'; 1416SET PASSWORD for usr1='pwd1' REPLACE 'pwd2'; 1417ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1418ALTER USER IF EXISTS usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1419 1420# Now test with Global server variable ON 1421SET @@global.password_require_current=ON; 1422 1423#------------------------------------------------------------------------ 1424# Non priv user changing his own password 1425#------------------------------------------------------------------------ 1426 1427# REPLACE clause is not specified 1428SET PASSWORD='pwd2'; 1429ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1430SET PASSWORD for usr1='pwd2'; 1431ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1432ALTER USER user() IDENTIFIED BY 'pwd2'; 1433ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1434ALTER USER usr1 IDENTIFIED BY 'pwd2'; 1435ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1436 1437# Incorrect password specified in the REPLACE clause 1438SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 1439ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1440SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D'; 1441ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1442ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1443ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1444ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1445ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1446 1447# Correct password specified in the REPLACE clause 1448SET PASSWORD='pwd2' REPLACE 'pwd1'; 1449SET PASSWORD for usr1='pwd1' REPLACE 'pwd2'; 1450ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1451ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1452 1453# Reset the Global server variable to OFF 1454SET @@global.password_require_current=OFF; 1455 1456CREATE USER usr2 IDENTIFIED BY 'pwd1' PASSWORD REQUIRE CURRENT; 1457 1458# Show how the users were created 1459 1460SHOW CREATE USER usr2; 1461CREATE USER for usr2@% 1462CREATE USER 'usr2'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT 1463SHOW CREATE USER usr1; 1464CREATE USER for usr1@% 1465CREATE USER 'usr1'@'%' IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT 1466SELECT password_require_current from mysql.user 1467where user like '%usr1' OR user like '%usr2'; 1468password_require_current 1469Y 1470Y 1471 1472# Grant the update privileges to the user 1473GRANT UPDATE ON mysql.* to 'usr1'@'%' WITH GRANT OPTION; 1474 1475#------------------------------------------------------------------------ 1476# Priv user changing his own password 1477#------------------------------------------------------------------------ 1478 1479# Without REPLACE clause 1480SET PASSWORD='pwd1'; 1481SET PASSWORD for usr1='pwd1'; 1482ALTER USER user() IDENTIFIED BY 'pwd1'; 1483ALTER USER usr1 IDENTIFIED BY 'pwd1'; 1484 1485# With invalid current password in the REPLACE clause 1486SET PASSWORD='pwd1' REPLACE 'P&(*^_)D'; 1487ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1488SET PASSWORD for usr1='pwd1' REPLACE 'P&(*^_)D'; 1489ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1490ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1491ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1492ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1493ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1494 1495# With valid current password in the REPLACE clause 1496SET PASSWORD='pwd1' REPLACE 'pwd1'; 1497SET PASSWORD for usr1='pwd1' REPLACE 'pwd1'; 1498ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd1'; 1499ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd1'; 1500 1501#------------------------------------------------------------------------ 1502# Priv user changing the password of usr2 1503#------------------------------------------------------------------------ 1504 1505SET PASSWORD for usr2='pwd1' REPLACE 'pwd1'; 1506ERROR HY000: Do not specify the current password while changing it for other users. 1507ALTER USER usr2 IDENTIFIED BY 'pwd1' REPLACE ''; 1508ERROR HY000: Do not specify the current password while changing it for other users. 1509 1510SET PASSWORD for usr2='pwd1'; 1511ALTER USER usr2 IDENTIFIED BY 'pwd1'; 1512 1513#------------------------------------------------------------------------ 1514# One priv user(root) changing password for other priv user (usr1) 1515#------------------------------------------------------------------------ 1516 1517SET PASSWORD for usr1='pwd1' REPLACE 'pwd1'; 1518ERROR HY000: Do not specify the current password while changing it for other users. 1519ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1520ERROR HY000: Do not specify the current password while changing it for other users. 1521 1522ALTER USER usr1 IDENTIFIED BY 'pwd1'; 1523SET PASSWORD for usr1='pwd1'; 1524 1525# Now test with Global server variable ON 1526SET @@global.password_require_current=ON; 1527 1528#------------------------------------------------------------------------ 1529# Priv user changing his own password 1530#------------------------------------------------------------------------ 1531 1532# Can change without REPLACE clause 1533SET PASSWORD='pwd1'; 1534SET PASSWORD for usr1='pwd1'; 1535ALTER USER user() IDENTIFIED BY 'pwd1'; 1536ALTER USER usr1 IDENTIFIED BY 'pwd1'; 1537 1538# Incorrect password specified in the REPLACE clause 1539SET PASSWORD='pwd1' REPLACE 'P&(*^_)D'; 1540ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1541SET PASSWORD for usr1='pwd1' REPLACE 'P&(*^_)D'; 1542ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1543ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1544ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1545ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1546ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1547 1548# Correct password specified in the REPLACE clause 1549SET PASSWORD='pwd1' REPLACE 'pwd1'; 1550SET PASSWORD for usr1='pwd1' REPLACE 'pwd1'; 1551ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd1'; 1552ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd1' 1553 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT; 1554 1555#------------------------------------------------------------------------ 1556# Priv user changing the password of usr2 1557#------------------------------------------------------------------------ 1558 1559SET PASSWORD for usr2='pwd1' REPLACE 'P&(*^_)D'; 1560ERROR HY000: Do not specify the current password while changing it for other users. 1561ALTER USER usr2 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1562ERROR HY000: Do not specify the current password while changing it for other users. 1563 1564SET PASSWORD for usr2='pwd1' ; 1565ALTER USER usr2 IDENTIFIED BY 'pwd1'; 1566 1567#------------------------------------------------------------------------ 1568# One priv user(root) changing password for other priv user (usr1) 1569#------------------------------------------------------------------------ 1570 1571SET PASSWORD for usr1='pwd1' REPLACE ''; 1572ERROR HY000: Do not specify the current password while changing it for other users. 1573ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd1'; 1574ERROR HY000: Do not specify the current password while changing it for other users. 1575SET PASSWORD for usr1='pwd1'; 1576ALTER USER usr1 IDENTIFIED BY 'pwd1'; 1577 1578# Reset the privileges and global variable 1579 1580REVOKE UPDATE ON mysql.* FROM 'usr1'@'%'; 1581SET @@global.password_require_current=OFF; 1582DROP USER usr2; 1583 1584#------------------------------------------------------------------------ 1585# Non priv user changing his own password 1586#------------------------------------------------------------------------ 1587 1588# REPLACE clause is not specified 1589SET PASSWORD='pwd2'; 1590ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1591SET PASSWORD for usr1='pwd2'; 1592ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1593ALTER USER user() IDENTIFIED BY 'pwd1'; 1594ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1595ALTER USER usr1 IDENTIFIED BY 'pwd1'; 1596ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1597 1598# Incorrect password specified in the REPLACE clause 1599SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 1600ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1601SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D'; 1602ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1603ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1604ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1605ALTER USER usr1 IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1606ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1607 1608# Correct password specified in the REPLACE clause 1609SET PASSWORD='pwd2' REPLACE 'pwd1'; 1610SET PASSWORD for usr1='pwd1' REPLACE 'pwd2'; 1611ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1612ALTER USER IF EXISTS usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1613 1614# Now test with Global server variable ON 1615SET @@global.password_require_current=ON; 1616 1617#------------------------------------------------------------------------ 1618# Non priv user changing his own password 1619#------------------------------------------------------------------------ 1620 1621# REPLACE clause is not specified 1622SET PASSWORD='pwd2'; 1623ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1624SET PASSWORD for usr1='pwd2'; 1625ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1626ALTER USER user() IDENTIFIED BY 'pwd2'; 1627ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1628ALTER USER usr1 IDENTIFIED BY 'pwd2'; 1629ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1630 1631# Incorrect password specified in the REPLACE clause 1632SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 1633ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1634SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D'; 1635ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1636ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1637ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1638ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D'; 1639ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1640 1641# Correct password specified in the REPLACE clause 1642SET PASSWORD='pwd2' REPLACE 'pwd1'; 1643SET PASSWORD for usr1='pwd1' REPLACE 'pwd2'; 1644ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1'; 1645ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd2'; 1646 1647# Reset the Global server variable to OFF 1648SET @@global.password_require_current=OFF; 1649 1650DROP USER usr1; 1651 1652######################################################################## 1653# 7. Change password of an user using old auth plugin with empty password 1654######################################################################## 1655CREATE USER usr1 IDENTIFIED WITH 'mysql_native_password' 1656 PASSWORD REQUIRE CURRENT; 1657 1658#------------------------------------------------------------------------ 1659# Non priv user changing his own password 1660#------------------------------------------------------------------------ 1661 1662# REPLACE clause is not specified 1663SET PASSWORD='pwd2'; 1664ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1665SET PASSWORD for usr1='pwd2'; 1666ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1667ALTER USER user() IDENTIFIED BY ''; 1668ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1669ALTER USER usr1 IDENTIFIED BY ''; 1670ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1671 1672# Incorrect password specified in the REPLACE clause 1673SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 1674ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1675SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D'; 1676ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1677ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1678ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1679ALTER USER usr1 IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1680ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1681 1682# Correct password specified in the REPLACE clause 1683SET PASSWORD='pwd2' REPLACE ''; 1684SET PASSWORD for usr1='' REPLACE 'pwd2'; 1685ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE ''; 1686ALTER USER IF EXISTS usr1 IDENTIFIED BY '' REPLACE 'pwd2'; 1687 1688# Now test with Global server variable ON 1689SET @@global.password_require_current=ON; 1690 1691#------------------------------------------------------------------------ 1692# Non priv user changing his own password 1693#------------------------------------------------------------------------ 1694 1695# REPLACE clause is not specified 1696SET PASSWORD='pwd2'; 1697ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1698SET PASSWORD for usr1='pwd2'; 1699ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1700ALTER USER user() IDENTIFIED BY 'pwd2'; 1701ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1702ALTER USER usr1 IDENTIFIED BY 'pwd2'; 1703ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1704 1705# Incorrect password specified in the REPLACE clause 1706SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 1707ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1708SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D'; 1709ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1710ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1711ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1712ALTER USER usr1 IDENTIFIED BY '' REPLACE 'P&(*^_)D'; 1713ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1714 1715# Correct password specified in the REPLACE clause 1716SET PASSWORD='pwd2' REPLACE ''; 1717SET PASSWORD for usr1='' REPLACE 'pwd2'; 1718ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE ''; 1719ALTER USER usr1 IDENTIFIED BY '' REPLACE 'pwd2'; 1720 1721# Reset the Global server variable to OFF 1722SET @@global.password_require_current=OFF; 1723 1724DROP USER usr1; 1725CREATE USER usr1 IDENTIFIED WITH 'sha256_password' 1726 PASSWORD REQUIRE CURRENT; 1727 1728#------------------------------------------------------------------------ 1729# Non priv user changing his own password 1730#------------------------------------------------------------------------ 1731 1732# REPLACE clause is not specified 1733SET PASSWORD='pwd2'; 1734ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1735SET PASSWORD for usr1='pwd2'; 1736ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1737ALTER USER user() IDENTIFIED BY ''; 1738ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1739ALTER USER usr1 IDENTIFIED BY ''; 1740ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1741 1742# Incorrect password specified in the REPLACE clause 1743SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 1744ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1745SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D'; 1746ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1747ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1748ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1749ALTER USER usr1 IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1750ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1751 1752# Correct password specified in the REPLACE clause 1753SET PASSWORD='pwd2' REPLACE ''; 1754SET PASSWORD for usr1='' REPLACE 'pwd2'; 1755ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE ''; 1756ALTER USER IF EXISTS usr1 IDENTIFIED BY '' REPLACE 'pwd2'; 1757 1758# Now test with Global server variable ON 1759SET @@global.password_require_current=ON; 1760 1761#------------------------------------------------------------------------ 1762# Non priv user changing his own password 1763#------------------------------------------------------------------------ 1764 1765# REPLACE clause is not specified 1766SET PASSWORD='pwd2'; 1767ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1768SET PASSWORD for usr1='pwd2'; 1769ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1770ALTER USER user() IDENTIFIED BY 'pwd2'; 1771ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1772ALTER USER usr1 IDENTIFIED BY 'pwd2'; 1773ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it. 1774 1775# Incorrect password specified in the REPLACE clause 1776SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ; 1777ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1778SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D'; 1779ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1780ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D'; 1781ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1782ALTER USER usr1 IDENTIFIED BY '' REPLACE 'P&(*^_)D'; 1783ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced. 1784 1785# Correct password specified in the REPLACE clause 1786SET PASSWORD='pwd2' REPLACE ''; 1787SET PASSWORD for usr1='' REPLACE 'pwd2'; 1788ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE ''; 1789ALTER USER usr1 IDENTIFIED BY '' REPLACE 'pwd2'; 1790 1791# Reset the Global server variable to OFF 1792SET @@global.password_require_current=OFF; 1793 1794DROP USER usr1; 1795 1796######################################################################## 1797# 8. Change the password of multiple users at once. 1798######################################################################## 1799CREATE USER u1, u2, u3; 1800GRANT SELECT, CREATE USER ON *.* to 'u1' WITH GRANT OPTION; 1801SELECT user, password_require_current FROM mysql.user WHERE USER LIKE 'u%'; 1802user password_require_current 1803u1 NULL 1804u2 NULL 1805u3 NULL 1806ALTER USER u1 IDENTIFIED BY '' REPLACE '', u2 IDENTIFIED BY '', 1807u3 IDENTIFIED BY '' PASSWORD REQUIRE CURRENT OPTIONAL; 1808SELECT user, password_require_current FROM mysql.user WHERE USER LIKE 'u%'; 1809user password_require_current 1810u1 N 1811u2 N 1812u3 N 1813ALTER USER u2 IDENTIFIED BY '', u1 IDENTIFIED BY '' REPLACE '', 1814u3 IDENTIFIED BY ''; 1815ALTER USER u3 IDENTIFIED BY '' , u2 IDENTIFIED BY '', 1816u1 IDENTIFIED BY '' REPLACE ''; 1817ALTER USER u1 IDENTIFIED BY '' REPLACE '', u2 IDENTIFIED BY '' REPLACE ''; 1818ERROR HY000: Do not specify the current password while changing it for other users. 1819ALTER USER u2 IDENTIFIED BY '' REPLACE '', u1 IDENTIFIED BY ''; 1820ERROR HY000: Do not specify the current password while changing it for other users. 1821ALTER USER u1 IDENTIFIED BY '123', 1822u2 IDENTIFIED BY '123' REPLACE '', 1823u3 IDENTIFIED BY ''; 1824ERROR HY000: Do not specify the current password while changing it for other users. 1825DROP USER u1, u2, u3; 1826 1827######################################################################## 1828# 9. Change the password as well as password require current clause 1829# of an user 1830######################################################################## 1831CREATE USER u1, u2; 1832SELECT user, password_require_current FROM mysql.user WHERE USER LIKE 'u%'; 1833user password_require_current 1834u1 NULL 1835u2 NULL 1836ALTER USER u1 IDENTIFIED BY '' REPLACE '' PASSWORD REQUIRE CURRENT; 1837ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation 1838GRANT SELECT, CREATE USER ON *.* to 'u1' WITH GRANT OPTION; 1839ALTER USER u1 IDENTIFIED BY '' REPLACE '' PASSWORD REQUIRE CURRENT; 1840SELECT user, password_require_current FROM mysql.user WHERE USER LIKE 'u%'; 1841user password_require_current 1842u1 Y 1843u2 NULL 1844ALTER USER u1 IDENTIFIED BY '' PASSWORD REQUIRE CURRENT OPTIONAL; 1845SELECT user, password_require_current FROM mysql.user WHERE USER LIKE 'u%'; 1846user password_require_current 1847u1 N 1848u2 NULL 1849DROP USER u1, u2; 1850 1851######################################################################## 1852# 10. Change the password of proxy user and proxied user 1853######################################################################## 1854CREATE USER 'u1' IDENTIFIED with 'mysql_native_password'; 1855CREATE USER 'u2' IDENTIFIED with 'mysql_native_password'; 1856GRANT PROXY ON 'u1' TO 'u2'; 1857SET @@global.check_proxy_users = ON; 1858SET @@global.mysql_native_password_proxy_users = ON; 1859SET @@global.password_require_current = ON; 1860SET PASSWORD='abc' REPLACE ''; 1861ALTER USER u1 IDENTIFIED BY '' REPLACE 'abc'; 1862ALTER USER u2 IDENTIFIED BY '' REPLACE ''; 1863ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation 1864SET PASSWORD='abc' REPLACE ''; 1865ALTER USER USER() IDENTIFIED BY '' REPLACE 'abc'; 1866ALTER USER u1 IDENTIFIED BY 'abc' REPLACE ''; 1867ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation 1868GRANT CREATE USER ON *.* to 'u1' WITH GRANT OPTION; 1869SELECT USER(); 1870USER() 1871u1@localhost 1872SELECT CURRENT_USER(); 1873CURRENT_USER() 1874u1@% 1875SET PASSWORD='abc' REPLACE ''; 1876ALTER USER u2 IDENTIFIED BY 'abc' REPLACE ''; 1877ERROR HY000: Do not specify the current password while changing it for other users. 1878SELECT USER(); 1879USER() 1880u2@localhost 1881SELECT CURRENT_USER(); 1882CURRENT_USER() 1883u1@% 1884SET PASSWORD='abc' REPLACE ''; 1885ALTER USER u1 IDENTIFIED BY '' REPLACE 'abc'; 1886ERROR HY000: Do not specify the current password while changing it for other users. 1887DROP USER u1, u2; 1888SET @@global.check_proxy_users = OFF; 1889SET @@global.mysql_native_password_proxy_users = OFF; 1890SET @@global.password_require_current = OFF; 1891 1892######################################################################## 1893# 11. Negative Tests 1894######################################################################## 1895CREATE USER non_priv_usr PASSWORD REQUIRE; 1896ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 1897CREATE USER non_priv_usr PASSWORD REQUIRE PASSWORD EXPIRE DEFAULT; 1898ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD EXPIRE DEFAULT' at line 1 1899CREATE USER non_priv_usr IDENTIFIED BY '123' REPLACE '133'; 1900ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPLACE '133'' at line 1 1901ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE; 1902ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 1903ALTER USER root IDENTIFIED BY 'pwd1' REPLACE; 1904ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 1905SET PASSWORD='' REPLACE; 1906ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 1907SET PASSWORD FOR root='' REPLACE; 1908ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 1909ALTER USER user() IDENTIFIED AS '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED' replace '' ; 1910ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED' repl' at line 1 1911