Bug #79999 ALTER USER IDENTIFIED WITH ‘auth_pam’ disables user account

• Submitted: 15 Jan 2016 9:20
• Modified: 20 Jan 2016 16:46
• Reporter: Georgi Kodinov
• Status: Closed
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 5.7.11
• OS: Any
• CPU Architecture: Any

[15 Jan 2016 9:20] Georgi Kodinov

Description:
ALTER USER marks passwords as expired

How to repeat:
mysql> create user mytest@localhost;
--------------
create user mytest@localhost
--------------

Query OK, 0 rows affected (0,00 sec)

mysql> select user,host,plugin,password_expired from mysql.user where user='mytest';
--------------
select user,host,plugin,password_expired from mysql.user where user='mytest'
--------------

+--------+-----------+-----------------------+------------------+
| user   | host      | plugin                | password_expired |
+--------+-----------+-----------------------+------------------+
| mytest | localhost | mysql_native_password | N                |
+--------+-----------+-----------------------+------------------+
1 row in set (0,00 sec)

mysql> ALTER USER mytest@localhost IDENTIFIED WITH authentication_pam;
--------------
ALTER USER mytest@localhost IDENTIFIED WITH authentication_pam
--------------

Query OK, 0 rows affected (0,00 sec)

mysql> select user,host,plugin,password_expired from mysql.user where user='mytest';
--------------
select user,host,plugin,password_expired from mysql.user where user='mytest'
--------------

+--------+-----------+--------------------+------------------+
| user   | host      | plugin             | password_expired |
+--------+-----------+--------------------+------------------+
| mytest | localhost | authentication_pam | Y                |
+--------+-----------+--------------------+------------------+
1 row in set (0,00 sec)

Suggested fix:
Don't mark passwords as expired for plugins that don't use the local password store

[20 Jan 2016 16:46] Paul DuBois

Noted in 5.7.12, 5.8.0 changelogs.

ALTER USER IDENTIFIED WITH ... expired the account password, even if
the authentication plugin did not support password expiration.