# # WL#6409: CREATE/ALTER USER # # CREATE USER CREATE USER u1@localhost; SELECT User,plugin FROM mysql.user WHERE USER='u1'; User u1 plugin CREATE USER u2@localhost IDENTIFIED BY 'auth_string'; SELECT User,plugin FROM mysql.user WHERE USER='u2'; User u2 plugin CREATE USER u3@localhost IDENTIFIED WITH 'sha256_password'; SELECT User,plugin FROM mysql.user WHERE USER='u3'; User u3 plugin sha256_password CREATE USER u4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'; SELECT User,plugin FROM mysql.user WHERE USER='u4'; User u4 plugin sha256_password CREATE USER u5@localhost REQUIRE SSL; SELECT User,plugin,ssl_type FROM mysql.user WHERE USER='u5'; User u5 plugin ssl_type ANY CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509; SELECT User,plugin,ssl_type FROM mysql.user WHERE USER='u6'; User u6 plugin ssl_type X509 CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password' REQUIRE CIPHER "DHE-RSA-AES256-SHA" PASSWORD EXPIRE NEVER; SELECT User,plugin,ssl_type, ssl_cipher,x509_issuer,x509_subject,password_expired,password_lifetime FROM mysql.user WHERE USER='u7'; User u7 plugin sha256_password ssl_type SPECIFIED ssl_cipher DHE-RSA-AES256-SHA x509_issuer x509_subject password_expired N password_lifetime 0 CREATE USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE ISSUER 'issuer'; SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject FROM mysql.user WHERE USER='u8'; User u8 plugin sha256_password ssl_type SPECIFIED ssl_cipher x509_issuer issuer x509_subject CREATE USER u9@localhost REQUIRE SUBJECT 'sub'; SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject FROM mysql.user WHERE USER='u9'; User u9 plugin ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject sub CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB" ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB"; SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject FROM mysql.user WHERE USER='u10'; User u10 plugin sha256_password ssl_type SPECIFIED ssl_cipher DHE-RSA-AES256-SHA x509_issuer /C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB x509_subject /C=SE/ST=Uppsala/O=MySQL AB CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2; SELECT User,plugin,max_questions FROM mysql.user WHERE USER='u11'; User u11 plugin max_questions 2 CREATE USER u12@localhost IDENTIFIED BY 'auth_string' WITH MAX_QUERIES_PER_HOUR 2; SELECT User,plugin,max_questions FROM mysql.user WHERE USER='u12'; User u12 plugin max_questions 2 CREATE USER u13@localhost IDENTIFIED WITH 'sha256_password' WITH MAX_CONNECTIONS_PER_HOUR 2; SELECT User,plugin,max_connections FROM mysql.user WHERE USER='u13'; User u13 plugin sha256_password max_connections 2 CREATE USER u14@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE INTERVAL 6 DAY; SELECT User,plugin,max_user_connections, password_expired,password_lifetime FROM mysql.user WHERE USER='u14'; User u14 plugin sha256_password max_user_connections 2 password_expired N password_lifetime 6 CREATE USER u15@localhost, u16@localhost IDENTIFIED BY 'auth_string', u17@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' PASSWORD EXPIRE; SELECT User,plugin,password_expired,password_lifetime FROM mysql.user WHERE USER BETWEEN 'u15' AND 'u17' ORDER BY User; User u15 plugin password_expired Y password_lifetime NULL User u16 plugin password_expired Y password_lifetime NULL User u17 plugin password_expired Y password_lifetime NULL CREATE USER u18@localhost, u19@localhost IDENTIFIED BY 'auth_string', u20@localhost IDENTIFIED WITH 'sha256_password', u21@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER; SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject, max_questions,max_user_connections,password_expired,password_lifetime FROM mysql.user WHERE USER BETWEEN 'u18' AND 'u21' ORDER BY User; User u18 plugin ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB max_questions 2 max_user_connections 2 password_expired N password_lifetime 0 User u19 plugin ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB max_questions 2 max_user_connections 2 password_expired N password_lifetime 0 User u2 plugin ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_user_connections 0 password_expired N password_lifetime NULL User u20 plugin ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB max_questions 2 max_user_connections 2 password_expired N password_lifetime 0 User u21 plugin ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB max_questions 2 max_user_connections 2 password_expired N password_lifetime 0 drop user u1@localhost, u2@localhost, u3@localhost, u4@localhost, u5@localhost, u6@localhost, u7@localhost, u8@localhost, u9@localhost, u10@localhost, u11@localhost, u12@localhost, u13@localhost, u14@localhost, u15@localhost, u16@localhost, u17@localhost, u18@localhost, u19@localhost, u20@localhost, u21@localhost; # ALTER USER CREATE USER u1@localhost; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u1'; User u1 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL its a no op ALTER USER u1@localhost; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u1'; User u1 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL CREATE USER u2@localhost IDENTIFIED BY 'auth_string'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u2'; User u2 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL ALTER USER u2@localhost IDENTIFIED BY 'new_auth_string'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u2'; User u2 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL CREATE USER u3@localhost IDENTIFIED WITH 'sha256_password'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u3'; User u3 ssl_type ssl_cipher x509_issuer x509_subject plugin sha256_password password_expired N password_lifetime NULL ALTER USER u3@localhost IDENTIFIED WITH 'mysql_native_password'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u3'; User u3 ssl_type ssl_cipher x509_issuer x509_subject plugin mysql_native_password password_expired Y password_lifetime NULL CREATE USER u4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u4'; User u4 ssl_type ssl_cipher x509_issuer x509_subject plugin sha256_password password_expired N password_lifetime NULL ALTER USER u4@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u4'; User u4 ssl_type ssl_cipher x509_issuer x509_subject plugin mysql_native_password password_expired N password_lifetime NULL CREATE USER u5@localhost REQUIRE SSL; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u5'; User u5 ssl_type ANY ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL ALTER USER u5@localhost IDENTIFIED WITH 'sha256_password'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u5'; User u5 ssl_type ANY ssl_cipher x509_issuer x509_subject plugin sha256_password password_expired Y password_lifetime NULL CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u6'; User u6 ssl_type X509 ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL ALTER USER u6@localhost IDENTIFIED BY 'new_auth_string' REQUIRE SSL; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u6'; User u6 ssl_type ANY ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE CIPHER 'cipher'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u7'; User u7 ssl_type SPECIFIED ssl_cipher cipher x509_issuer x509_subject plugin sha256_password password_expired N password_lifetime NULL ALTER USER u7@localhost IDENTIFIED WITH 'mysql_native_password' REQUIRE ISSUER 'issuer'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u7'; User u7 ssl_type SPECIFIED ssl_cipher x509_issuer issuer x509_subject plugin mysql_native_password password_expired Y password_lifetime NULL CREATE USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE ISSUER 'issuer'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u8'; User u8 ssl_type SPECIFIED ssl_cipher x509_issuer issuer x509_subject plugin sha256_password password_expired N password_lifetime NULL ALTER USER u8@localhost IDENTIFIED WITH 'mysql_native_password' REQUIRE CIPHER "DHE-RSA-AES256-SHA"; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u8'; User u8 ssl_type SPECIFIED ssl_cipher DHE-RSA-AES256-SHA x509_issuer x509_subject plugin mysql_native_password password_expired Y password_lifetime NULL CREATE USER u9@localhost REQUIRE SUBJECT 'sub'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u9'; User u9 ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject sub plugin password_expired N password_lifetime NULL ALTER USER u9@localhost REQUIRE ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB"; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u9'; User u9 ssl_type SPECIFIED ssl_cipher x509_issuer /C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB x509_subject plugin password_expired N password_lifetime NULL CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB" ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB"; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u10'; User u10 ssl_type SPECIFIED ssl_cipher DHE-RSA-AES256-SHA x509_issuer /C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB x509_subject /C=SE/ST=Uppsala/O=MySQL AB plugin sha256_password password_expired N password_lifetime NULL ALTER USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE SSL; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u10'; User u10 ssl_type ANY ssl_cipher x509_issuer x509_subject plugin sha256_password password_expired N password_lifetime NULL CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2; SELECT User,max_questions FROM mysql.user WHERE USER='u11'; User u11 max_questions 2 ALTER USER u11@localhost WITH MAX_QUERIES_PER_HOUR 6; SELECT User,max_questions FROM mysql.user WHERE USER='u11'; User u11 max_questions 6 CREATE USER u12@localhost IDENTIFIED BY 'auth_string' WITH MAX_QUERIES_PER_HOUR 2; SELECT User,max_questions FROM mysql.user WHERE USER='u12'; User u12 max_questions 2 ALTER USER u12@localhost IDENTIFIED WITH 'sha256_password' WITH MAX_QUERIES_PER_HOUR 8; SELECT User,max_questions FROM mysql.user WHERE USER='u12'; User u12 max_questions 8 CREATE USER u13@localhost IDENTIFIED WITH 'sha256_password' WITH MAX_CONNECTIONS_PER_HOUR 2; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u13'; User u13 ssl_type ssl_cipher x509_issuer x509_subject plugin sha256_password password_expired N password_lifetime NULL ALTER USER u13@localhost PASSWORD EXPIRE; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER='u13'; User u13 ssl_type ssl_cipher x509_issuer x509_subject plugin sha256_password password_expired Y password_lifetime NULL CREATE USER u14@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' WITH MAX_USER_CONNECTIONS 2; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired,max_user_connections, password_lifetime FROM mysql.user WHERE USER='u14'; User u14 ssl_type ssl_cipher x509_issuer x509_subject plugin sha256_password password_expired N max_user_connections 2 password_lifetime NULL ALTER USER u14@localhost WITH MAX_USER_CONNECTIONS 12 PASSWORD EXPIRE INTERVAL 365 DAY; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired,max_user_connections, password_lifetime FROM mysql.user WHERE USER='u14'; User u14 ssl_type ssl_cipher x509_issuer x509_subject plugin sha256_password password_expired N max_user_connections 12 password_lifetime 365 CREATE USER u15@localhost, u16@localhost IDENTIFIED WITH 'sha256_password', u17@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER BETWEEN 'u15' AND 'u17' order by 1; User u15 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL User u16 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL User u17 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL ALTER USER u15@localhost IDENTIFIED WITH 'sha256_password', u16@localhost, u17@localhost IDENTIFIED BY 'new_auth_string' PASSWORD EXPIRE DEFAULT; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired, password_lifetime FROM mysql.user WHERE USER BETWEEN 'u15' AND 'u17' order by 1; User u15 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired Y password_lifetime NULL User u16 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL User u17 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired N password_lifetime NULL CREATE USER u18@localhost, u19@localhost IDENTIFIED BY 'auth_string', u20@localhost IDENTIFIED WITH 'sha256_password', u21@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired,max_user_connections, max_questions,password_lifetime FROM mysql.user WHERE USER BETWEEN 'u18' AND 'u21' order by 1; User u18 ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB plugin password_expired N max_user_connections 2 max_questions 2 password_lifetime NULL User u19 ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB plugin password_expired N max_user_connections 2 max_questions 2 password_lifetime NULL User u2 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired N max_user_connections 0 max_questions 0 password_lifetime NULL User u20 ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB plugin password_expired N max_user_connections 2 max_questions 2 password_lifetime NULL User u21 ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB plugin password_expired N max_user_connections 2 max_questions 2 password_lifetime NULL ALTER USER u18@localhost, u19@localhost, u20@localhost, u21@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER; SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject, plugin,password_expired,max_user_connections, max_questions,password_lifetime FROM mysql.user WHERE USER BETWEEN 'u18' AND 'u21' order by 1; User u18 ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB plugin password_expired N max_user_connections 2 max_questions 2 password_lifetime 0 User u19 ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB plugin password_expired N max_user_connections 2 max_questions 2 password_lifetime 0 User u2 ssl_type ssl_cipher x509_issuer x509_subject plugin password_expired N max_user_connections 0 max_questions 0 password_lifetime NULL User u20 ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB plugin password_expired N max_user_connections 2 max_questions 2 password_lifetime 0 User u21 ssl_type SPECIFIED ssl_cipher x509_issuer x509_subject /C=SE/ST=Uppsala/O=MySQL AB plugin password_expired N max_user_connections 2 max_questions 2 password_lifetime 0 drop user u1@localhost, u2@localhost, u3@localhost, u4@localhost, u5@localhost, u6@localhost, u7@localhost, u8@localhost, u9@localhost, u10@localhost, u11@localhost, u12@localhost, u13@localhost, u14@localhost, u15@localhost, u16@localhost, u17@localhost, u18@localhost, u19@localhost, u20@localhost, u21@localhost; # CREATE USER with password expire attributes CREATE USER u1@localhost PASSWORD EXPIRE NEVER; # This should report 0 SELECT password_lifetime FROM mysql.user where user='u1'; password_lifetime 0 DROP USER u1@localhost; CREATE USER u1@localhost PASSWORD EXPIRE DEFAULT; # This should report NULL SELECT password_expired,password_lifetime FROM mysql.user where user='u1'; password_expired password_lifetime N NULL DROP USER u1@localhost; CREATE USER u1@localhost PASSWORD EXPIRE INTERVAL 4 DAY; # Should report 4 SELECT password_lifetime FROM mysql.user where user='u1'; password_lifetime 4 DROP USER u1@localhost; CREATE USER u1@localhost PASSWORD EXPIRE; # This should report Y SELECT password_expired FROM mysql.user where user='u1'; password_expired Y Please use --connect-expired-password option or invoke mysql in interactive mode. DROP USER u1@localhost; # CREATE USER with password expire attributes for anonymous user CREATE USER '' PASSWORD EXPIRE; ERROR HY000: Operation CREATE USER failed for anonymous user CREATE USER '' PASSWORD EXPIRE NEVER; ERROR HY000: Operation CREATE USER failed for anonymous user CREATE USER '' PASSWORD EXPIRE INTERVAL 4 DAY; ERROR HY000: Operation CREATE USER failed for anonymous user # ALTER USER with user() CREATE USER u1@localhost IDENTIFIED BY 'abc'; SELECT USER(); USER() u1@localhost ALTER USER u1@localhost PASSWORD EXPIRE; SELECT USER(); ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. SET PASSWORD = 'def'; SELECT USER(); USER() u1@localhost ALTER USER u1@localhost PASSWORD EXPIRE; SELECT USER(); ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. ALTER USER user() IDENTIFIED BY 'abc'; SELECT USER(); USER() u1@localhost ALTER USER u1@localhost PASSWORD EXPIRE; SELECT USER(); ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. ALTER USER u1@localhost IDENTIFIED BY 'def'; SELECT USER(); USER() u1@localhost DROP USER u1@localhost; # ALTER USER with current user is allowed to set only credential information CREATE USER u1@localhost, u2@localhost IDENTIFIED BY 'abc'; GRANT ALL ON *.* TO u2@localhost; ALTER USER USER() IDENTIFIED WITH 'sha256_password'; ERROR 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 'WITH 'sha256_password'' at line 1 ALTER USER USER() IDENTIFIED BY 'def', u2@localhost PASSWORD EXPIRE; ERROR 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 ', u2@localhost PASSWORD EXPIRE' at line 1 ALTER USER USER() IDENTIFIED BY 'def' PASSWORD EXPIRE; ERROR 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' at line 1 ALTER USER ; ERROR 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 DROP USER u1@localhost, u2@localhost; # SHOW CREATE USER CREATE USER u1@localhost; SHOW CREATE USER u1@localhost; CREATE USER for u1@localhost CREATE USER 'u1'@'localhost' IDENTIFIED WITH '' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u1@localhost IDENTIFIED BY 'auth_string'; SHOW CREATE USER u1@localhost; CREATE USER for u1@localhost CREATE USER 'u1'@'localhost' IDENTIFIED WITH '' AS '' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u2@localhost IDENTIFIED BY 'auth_string'; SHOW CREATE USER u2@localhost; CREATE USER for u2@localhost CREATE USER 'u2'@'localhost' IDENTIFIED WITH '' AS '' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u2@localhost IDENTIFIED WITH 'sha256_password'; SHOW CREATE USER u2@localhost; CREATE USER for u2@localhost CREATE USER 'u2'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u3@localhost IDENTIFIED WITH 'sha256_password'; SHOW CREATE USER u3@localhost; CREATE USER for u3@localhost CREATE USER 'u3'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u3@localhost PASSWORD EXPIRE NEVER; SHOW CREATE USER u3@localhost; CREATE USER for u3@localhost CREATE USER 'u3'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'; SHOW CREATE USER u4@localhost; CREATE USER for u4@localhost CREATE USER 'u4'@'localhost' IDENTIFIED WITH 'sha256_password' AS '' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u4@localhost PASSWORD EXPIRE INTERVAL 365 DAY; SHOW CREATE USER u4@localhost; CREATE USER for u4@localhost CREATE USER 'u4'@'localhost' IDENTIFIED WITH 'sha256_password' AS '' REQUIRE NONE PASSWORD EXPIRE INTERVAL 365 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u5@localhost REQUIRE SSL; SHOW CREATE USER u5@localhost; CREATE USER for u5@localhost CREATE USER 'u5'@'localhost' IDENTIFIED WITH '' REQUIRE SSL PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u5@localhost REQUIRE CIPHER "DHE-RSA-AES256-SHA"; SHOW CREATE USER u5@localhost; CREATE USER for u5@localhost CREATE USER 'u5'@'localhost' IDENTIFIED WITH '' REQUIRE CIPHER 'DHE-RSA-AES256-SHA' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509; SHOW CREATE USER u6@localhost; CREATE USER for u6@localhost CREATE USER 'u6'@'localhost' IDENTIFIED WITH '' AS '' REQUIRE X509 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u6@localhost REQUIRE CIPHER "DHE-RSA-AES256-SHA" WITH MAX_QUERIES_PER_HOUR 2; SHOW CREATE USER u6@localhost; CREATE USER for u6@localhost CREATE USER 'u6'@'localhost' IDENTIFIED WITH '' AS '' REQUIRE CIPHER 'DHE-RSA-AES256-SHA' WITH MAX_QUERIES_PER_HOUR 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password' REQUIRE CIPHER 'DHE-RSA-AES256-SHA'; SHOW CREATE USER u7@localhost; CREATE USER for u7@localhost CREATE USER 'u7'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE CIPHER 'DHE-RSA-AES256-SHA' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u7@localhost REQUIRE NONE WITH MAX_USER_CONNECTIONS 12; SHOW CREATE USER u7@localhost; CREATE USER for u7@localhost CREATE USER 'u7'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE WITH MAX_USER_CONNECTIONS 12 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE ISSUER 'issuer'; SHOW CREATE USER u8@localhost; CREATE USER for u8@localhost CREATE USER 'u8'@'localhost' IDENTIFIED WITH 'sha256_password' AS '' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'; SHOW CREATE USER u8@localhost; CREATE USER for u8@localhost CREATE USER 'u8'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' REQUIRE ISSUER 'issuer' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u9@localhost REQUIRE SUBJECT 'sub'; SHOW CREATE USER u9@localhost; CREATE USER for u9@localhost CREATE USER 'u9'@'localhost' IDENTIFIED WITH '' REQUIRE SUBJECT 'sub' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u9@localhost; SHOW CREATE USER u9@localhost; CREATE USER for u9@localhost CREATE USER 'u9'@'localhost' IDENTIFIED WITH '' REQUIRE SUBJECT 'sub' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB" ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB"; SHOW CREATE USER u10@localhost; CREATE USER for u10@localhost CREATE USER 'u10'@'localhost' IDENTIFIED WITH 'sha256_password' AS '' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u10@localhost PASSWORD EXPIRE NEVER; SHOW CREATE USER u10@localhost; CREATE USER for u10@localhost CREATE USER 'u10'@'localhost' IDENTIFIED WITH 'sha256_password' AS '' PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2; SHOW CREATE USER u11@localhost; CREATE USER for u11@localhost CREATE USER 'u11'@'localhost' IDENTIFIED WITH '' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u11@localhost WITH MAX_QUERIES_PER_HOUR 10; SHOW CREATE USER u11@localhost; CREATE USER for u11@localhost CREATE USER 'u11'@'localhost' IDENTIFIED WITH '' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 10 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u12@localhost IDENTIFIED BY 'auth_string' WITH MAX_QUERIES_PER_HOUR 2; SHOW CREATE USER u12@localhost; CREATE USER for u12@localhost CREATE USER 'u12'@'localhost' IDENTIFIED WITH '' AS '' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u12@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10; SHOW CREATE USER u12@localhost; CREATE USER for u12@localhost CREATE USER 'u12'@'localhost' IDENTIFIED WITH '' AS '' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u13@localhost IDENTIFIED WITH 'sha256_password' WITH MAX_CONNECTIONS_PER_HOUR 2; SHOW CREATE USER u13@localhost; CREATE USER for u13@localhost CREATE USER 'u13'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE WITH MAX_CONNECTIONS_PER_HOUR 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u13@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10; SHOW CREATE USER u13@localhost; CREATE USER for u13@localhost CREATE USER 'u13'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u14@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' WITH MAX_USER_CONNECTIONS 2; SHOW CREATE USER u14@localhost; CREATE USER for u14@localhost CREATE USER 'u14'@'localhost' IDENTIFIED WITH 'sha256_password' AS '' REQUIRE NONE WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u14@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10 PASSWORD EXPIRE; SHOW CREATE USER u14@localhost; CREATE USER for u14@localhost CREATE USER 'u14'@'localhost' IDENTIFIED WITH 'sha256_password' AS '' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u15@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB" CIPHER 'DHE-RSA-AES256-SHA' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2; SHOW CREATE USER u15@localhost; CREATE USER for u15@localhost CREATE USER 'u15'@'localhost' IDENTIFIED WITH 'sha256_password' AS '' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' ISSUER '/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB' CIPHER 'DHE-RSA-AES256-SHA' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u15@localhost REQUIRE X509 PASSWORD EXPIRE INTERVAL 365 DAY; SHOW CREATE USER u15@localhost; CREATE USER for u15@localhost CREATE USER 'u15'@'localhost' IDENTIFIED WITH 'sha256_password' AS '' REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE INTERVAL 365 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u16@localhost IDENTIFIED BY 'auth_string' PASSWORD EXPIRE; SHOW CREATE USER u16@localhost; CREATE USER for u16@localhost CREATE USER 'u16'@'localhost' IDENTIFIED WITH '' AS '' REQUIRE NONE PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u16@localhost REQUIRE X509 PASSWORD EXPIRE INTERVAL 365 DAY; SHOW CREATE USER u16@localhost; CREATE USER for u16@localhost CREATE USER 'u16'@'localhost' IDENTIFIED WITH '' AS '' REQUIRE X509 PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u17@localhost WITH MAX_QUERIES_PER_HOUR 200 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER; SHOW CREATE USER u17@localhost; CREATE USER for u17@localhost CREATE USER 'u17'@'localhost' IDENTIFIED WITH '' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 200 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u17@localhost REQUIRE X509 PASSWORD EXPIRE INTERVAL 365 DAY; SHOW CREATE USER u17@localhost; CREATE USER for u17@localhost CREATE USER 'u17'@'localhost' IDENTIFIED WITH '' REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 200 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE INTERVAL 365 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u18@localhost IDENTIFIED WITH 'sha256_password' PASSWORD EXPIRE INTERVAL 365 DAY; SHOW CREATE USER u18@localhost; CREATE USER for u18@localhost CREATE USER 'u18'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE PASSWORD EXPIRE INTERVAL 365 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u18@localhost PASSWORD EXPIRE NEVER; SHOW CREATE USER u18@localhost; CREATE USER for u18@localhost CREATE USER 'u18'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT CREATE USER u19@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB" PASSWORD EXPIRE DEFAULT; SHOW CREATE USER u19@localhost; CREATE USER for u19@localhost CREATE USER 'u19'@'localhost' IDENTIFIED WITH '' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' ISSUER '/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ALTER USER u19@localhost WITH MAX_QUERIES_PER_HOUR 200 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER; SHOW CREATE USER u19@localhost; CREATE USER for u19@localhost CREATE USER 'u19'@'localhost' IDENTIFIED WITH '' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' ISSUER '/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 200 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT drop user u1@localhost, u2@localhost, u3@localhost, u4@localhost, u5@localhost, u6@localhost, u7@localhost, u8@localhost, u9@localhost, u10@localhost, u11@localhost, u12@localhost, u13@localhost, u14@localhost, u15@localhost, u16@localhost, u17@localhost, u18@localhost, u19@localhost; # # Bug #20553132 USER WITH EXPIRED PASSWORD ABLE TO EXECUTE # ALTER USER .. PASSWORD EXPIRE COMMAND # CREATE USER 20553132_u1@localhost; CREATE USER 20553132_u2@localhost; CREATE USER '20553132_u3'@'%'; GRANT ALL ON *.* TO 20553132_u1@localhost; ALTER USER 20553132_u1@localhost PASSWORD EXPIRE; ALTER USER '20553132_u3'@'%' PASSWORD EXPIRE; ALTER USER 20553132_u1@localhost PASSWORD EXPIRE NEVER; ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. ALTER USER 20553132_u1@localhost PASSWORD EXPIRE DEFAULT; ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. ALTER USER 20553132_u1@localhost, 20553132_u2@localhost IDENTIFIED BY 'abcd' PASSWORD EXPIRE NEVER; ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. ALTER USER 20553132_u2@localhost IDENTIFIED BY 'abcd', 20553132_u1@localhost IDENTIFIED BY 'defg' PASSWORD EXPIRE NEVER; ALTER USER 20553132_u1@localhost PASSWORD EXPIRE; ALTER USER 20553132_u2@localhost IDENTIFIED BY 'abcd', 20553132_u1@localhost IDENTIFIED WITH 'mysql_native_password' BY 'hijk' PASSWORD EXPIRE DEFAULT; SELECT USER(); USER() 20553132_u1@localhost ALTER USER CURRENT_USER() IDENTIFIED BY 'abcd'; SELECT CURRENT_USER(); CURRENT_USER() 20553132_u3@% ALTER USER '20553132_u3'@'%' PASSWORD EXPIRE; ALTER USER '20553132_u3'@'%' IDENTIFIED BY 'abcd'; SELECT CURRENT_USER(); CURRENT_USER() 20553132_u3@% DROP USER 20553132_u1@localhost; DROP USER 20553132_u2@localhost; DROP USER '20553132_u3'@'%'; End of 5.7 tests! Bug #20600865: IDENTIFIED BY PASSWORD IS NOT DEPRECATED FOR ALTER USER BUT DOESN\'T WORK CREATE USER u1; ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'; ERROR 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 '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'' at line 1 ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' PASSWORD EXPIRE; ERROR 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 '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' PASSWORD EXPIRE' at line 1 ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2; ERROR 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 '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' WITH MAX_QUERIES_PER_' at line 1 ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"; ERROR 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 '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' REQUIRE CIPHER "DHE-R' at line 1 ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' PASSWORD EXPIRE DEFAULT; ERROR 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 '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' PASSWORD EXPIRE DEFAU' at line 1 DROP USER u1; Bug #20634154 GRANT/ALTER USER CLEARS PASSWORD EXPIRE. CREATE USER bug20634154@localhost IDENTIFIED BY 'abc'; SELECT CURRENT_USER(); CURRENT_USER() bug20634154@localhost ALTER USER bug20634154@localhost PASSWORD EXPIRE; SELECT CURRENT_USER(); ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. GRANT USAGE ON *.* TO bug20634154@localhost; SELECT CURRENT_USER(); ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. ALTER USER bug20634154@localhost; SELECT CURRENT_USER(); ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. ALTER USER bug20634154@localhost IDENTIFIED BY 'def'; SELECT CURRENT_USER(); CURRENT_USER() bug20634154@localhost ALTER USER bug20634154@localhost IDENTIFIED BY 'abc' PASSWORD EXPIRE; SELECT CURRENT_USER(); ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. ALTER USER bug20634154@localhost IDENTIFIED BY 'def' PASSWORD EXPIRE INTERVAL 10 DAY; SELECT CURRENT_USER(); CURRENT_USER() bug20634154@localhost DROP USER bug20634154@localhost; Bug #22205360 ALTER USER/SET PASSWORD DO NOT WORK FOR --INIT-FILE EXECUTION CREATE USER bug22205360@localhost; # shutdown the server # Restart server with init-file option SELECT 1; 1 1 # shutdown the server # Restart server with init-file option SELECT 1; 1 1 DROP USER bug22205360@localhost;