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: SET PASSWORD = ... SET PASSWORD FOR CURRENT_USER() = ... 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. References ---------- Oracle ALTER USER description: http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_4 003.htm#sthref4149 SQL Server 2005 ALTER LOGIN description: http://msdn2.microsoft.com/en-us/library/ms189828.aspx