WL#2392: "Change Password at next login" (initial default for root)

Affects: Server-5.6   —   Status: Complete   —   Priority: Medium

Customers have asked to be able to flag an account "change password at next
login" - a feature common to many OS authentication schemes. An additional perk
would be to have this be default for root with a new install. 
Add one new column for mysql.user:
password_expired enum('Y','N') default 'N' not null

Initially the value of password_expired is 'N'.
Installation-related scripts (mysql_install_db,
mysql_fix_privilege_tables, etc.) will not set it
to 'Y'.
Anyone with the privilege to update mysql.user or with ADD USER grant
can update it to 'Y'.

If it is 'Y', then the user will not be able
to perform any DDL or DML statements. The
error message will be:
"You must SET PASSWORD before executing this statement"
The only significant statement that the user
will be able to do is SET PASSWORD.

Instead of just updating mysql.user, allow specific syntax:
ALTER USER user_name PASSWORD EXPIRE, user_name PASSWORD EXPIRE, ...; /* Oracle 
style */

The following commands will re-set the must_change column back to N:

Rejected alternatives

Instead of must_change, add an expiry_date column,
and require SET PASSWORD if current_date > expiry_date.
We won't do that.

Instead of just updating mysql.user, allow specific syntax:
ALTER LOGIN ... MUST_CHANGE; /* SQL Server 2005 style */
SET PASSWORD [FOR user] = MUST_CHANGE; /* instead of PASSWORD('x') */
We won't do that.

Add a new clause for CREATE USER, e.g. "CREATE USER j MUST_CHANGE".
We won't do that.

Functional specification
1. Users that can update mysql.user or have the ADD USER privilege can set 
mysql.user.password_expired to 'Y'.
2. Changes to password_expired take effect for all susequently started sessions. 
The current open sessions other from the one updating the flag remain intact.
3. Command to set password_expired to 'Y' is: ALTER USER user PASSWORD EXPIRE
4. password_expired can be manipulated directly through updating mysql.user, but 
one will need to issue FLUSH PRIVILEGES so that changes become effective.
5. ALTER USER must take an arbibtrary number of user specifications and apply 
only the ones that can be applied and return an error detailing the ones that 
can't be applied.
6. When the password_expired mode is active for the current session all commands 
except SET PASSWORD = ... and SET PASSWORD FOR CURRRENT_USER() = ... will cause 
an error to be thrown.
7. SET PASSWORD = ... and SET PASSWORD FOR CURRRENT_USER() = ... reset the 
password_expired flag.


Oracle ALTER USER description:

SQL Server 2005 ALTER LOGIN description: