MySQL Bugs: #67568: Non-interactive clients fail when password is expired

Bug #67568	Non-interactive clients fail when password is expired
Submitted:	12 Nov 2012 20:37	Modified:	1 Feb 2013 4:16
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.6.8-rc	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
ALTER USER ... PASSWORD EXPIRE causes the affected user account to be restricted to statements needed to change password, but does not prohibit connection.  This behavior assumes an interactive client or driver that can capture the errors produced by attempting execution of other statements and can change the account password.  This is not the case for most application accounts, and is particularly problematic for deployments using connection pools.  Because the connection isn't flagged as unusable when the connection is first established, the driver (and the connection pool library) will be unaware that the connection it gives to the application cannot be used in any meaningful way.

How to repeat:
See above

Suggested fix:
Refuse connections when the authenticated user account's password is expired unless a client capabilities flag is set indicating the client/driver can handle expired password process.

Noted in 5.6.10, 5.7.1 changelogs.

The following changes were made to the sandbox mode that the server
uses to handle client connections for accounts with expired
passwords: 

* There is a new disconnect_on_expired_passwords system variable
  (default: enabled). This controls how the server treats
  expired-password accounts.

* Two flags were added to the C API client library:
  MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS for mysql_options() and
  CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS for mysql_real_connect(). Each
  flag enables a client program to indicate whether it can handle
  sandbox mode for accounts with expired passwords.

* MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS is enabled for mysqltest
  unconditionally, for mysql in interactive mode, and for mysqladmin if
  the first command is password.

For more information about how the client-side flags interact with
disconnect_on_expired_passwords, see
http://dev.mysql.com/doc/refman/5.6/en/password-expiration.html