WL#7131: Add timestamp in mysql.user on the last time the password was changed

We need to track when the password was last changed and implement password 
rotation.

Put a TIMESTAMP column inside mysql.user table and update it when the password
is updated.

Put another column in mysql.user, holding the number of DAYS after which the 
password must expire.

Password rotation policy will be provided on a site-wide basis that can be 
overridden for individual users. And that this policy will be down to a day 
resolution.

User Documentation
==================

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html
http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html
http://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html
http://dev.mysql.com/doc/refman/5.7/en/alter-user.html
http://dev.mysql.com/doc/refman/5.7/en/grant-table-structure.html

List of functional requirements:

F1:  The new password_last_changed column will be created as a TIMESTAMP(0) 
column which allows NULL values.

F2:  The password_last_changed column will report as NULL for all user accounts 
for which credentials are not maintained by MySQL Server (e.g., Windows, LDAP 
and Peer Socket Auth plugins).

F3:  The value will only be exposed through direct queries of mysql.user table.

F4:  The password_last_changed column will allow direct manipulation by users 
with appropriate privileges only.  Consequences of direct manipulation (e.g., 
conflicts with F2 caused by direct manipulation of mysql.user's auth_plugin 
column) will be considered undefined.

F5:  The password_last_changed column will report the CURRENT_TIMESTAMP() at the 
time credentials were last successfully created or modified via account 
management statements (e.g., CREATE USER, SET PASSWORD, GRANT which creates new 
account, etc.) for all MySQL Server-managed credentials (e.g., 
mysql_native_password, mysql_old_password, sha256_password).

F5.1: When a new user is directly inserted (using INSERT INTO) into the table 
and value is not provided for password_last_changed and password_lifetime 
column, then these two columns will have NULL (default) entries. Otherwise 
(cases like GRANT and CREATE user) the password_last_changed will have current 
timestamp and password_lifetime will hold NULL value.

F6:  Account management statements which meet F5 requirements, but do not result 
in altered account credentials (because the new credentials match the existing 
credentials, or because GRANT command only modifies permissions instead of 
creating/altering existing credentials) will not update the 
password_last_changed column value.

F7:  Account management statements which fail (e.g., due to password validation 
failures or incorrect password hash) will not update the password_last_changed 
column.

F8:  The second new column password_lifetime will be created as SMALL 
INT which allows NULL values. It will be NULL for all user accounts for which 
credentials are not maintained by MySQL Server

F9 : The password_lifetime will hold the number of days after which the 
password for this user will expire, 0 to signify that no expiration will take 
place and NULL to signify that the global server expiration policy will be taken 
instead.

*F9.1. : The global server-wide expiration policy will be governed by the 
"default_password_lifetime" system variable.
F9.1.1.: "default_password_lifetime" system variable is a numerical variable 
denoting the number of days before the server expires a password.
F9.1.2.: "default_password_lifetime" system variable can be 0, meaning no auto-
expiration will  take place
F9.1.3.: "default_password_lifetime" system variable will apply only for the 
user accounts that have their "password_lifetime" column value set to 
NULL
F9.1.4.: "default_password_lifetime" system variable will have a default of 360.
F9.1.5.: "default_password_lifetime" system variable can be specified in config 
file(s)
F9.1.6.: "default_password_lifetime" system variable can be changed as a global 
variable by a super user
F9.1.7.: Changes to "default_password_lifetime" system variable will have an 
effect on new sessions only. Existing sessions will not check for updates of 
both the column and the system variable.

*F9.2. : At authentication time the session will get a password expiration 
period as follows :
 - if there's a non-NULL value in the "password_lifetime" column and in 
the 
password_last_updated column these will be used to calculate a timestamp on when 
the password shall expire.
 - otherwise the value of the "default_password_lifetime" system variable will 
be added to the password_last_updated value to calculate the timestamp.
 - Then the calculated timestamp will be compared to the current timestamp. If 
the calculated timestamp is earlier than the current the session will be marked 
as password expired.

*F9.3.: when a password expires the password expiration flag column will not be 
flipped to 'Y'.

*F9.4.: When SET PASSWORD is called the password expiration flag will be reset 
back no matter how it was raised. 

F11:  Consequences of direct manipulation of mysql.user table behavior (e.g., 
doing an UPDATE on auth_plugin column resulting in data state which violates F2, 
or an UPDATE on password column directly) is left undefined, but must be 
consistently implemented, documented and tested.

F12:  mysql_upgrade will create the 2 new columns password_last_changed and 
password_lifetime when it does not already exist in mysql.user, 
and will set the values to CURRENT_TIMESTAMP() and NULL respectively
for all rows having MySQL Server-managed creentials.

List of non-functional requirements:

NF1: Changed behavior: After the upgrade the passwords for existing user 
accounts will auto-expire after one year.

NF2: All other existing features will behave as usual. 

Problem Description:
====================
Currently, there is no mechanism to track when a user has last changed his 
password and no policy to enforce password rotation.
This WL will track when a user has last changed his password (for MySQL server 
managed credentials) and also enforce password change/rotation policy.

** Server-side changes **
=========================

New columns in mysql.user table
===============================
Two new columns will be added to the mysql.user table.
1) password_last_changed (TIMESTAMP(0)) will keep a track of when the password 
was last changed for this account. Default: NULL.
2) password_lifetime (SMALL INT) will store the number of DAYS after 
which this user's password will expire.Range: (0, SMALLINT_MAX) (The 
password_expired column will NOT be updated to 'Y'). Default: NULL.

Backward/Cross Compatibility
============================
Backward/Cross compatibility is guaranteed by default.

For instance :
1) Old database <-> New server 
   Server will not store any features implemented in this WL.
   Auto expiration according to the global variable's default will take place in 
this case.

New global system variable
==========================
A new global system variable will be introduced.
Name: default_password_lifetime
Default: 360
Type: uint
Range: (0, UINT16_MAX)

New mysql Query
=================
A couple of new mysql queries will be introduced which is an extension of the 
existing query - ALTER USER foo PASSWORD EXPIRE.

The new queries will be,

ALTER USER foo PASSWORD EXPIRE INTERVAL  DAY;
Sets the local column value (password_lifetime) to .

ALTER USER foo PASSWORD EXPIRE NEVER;
Sets the local column value (password_lifetime) to 0, so that the 
password 
will never expire, unless changed again.

ALTER USER foo PASSWORD EXPIRE DEFAULT;
Sets the local column value (password_lifetime) to NULL, so that the 
default 
can kick in.

Note: In all the three above cases, the password_expired column in mysql.user 
table is left untouched.

Implementation w.r.t this WL:
=============================
With this WL we will keep a track of when a user had last changed his password. 
The global variable (default_password_lifetime) will be used as a global policy 
that applies to all the user accounts that don't specify otherwise.
- If this is 0 then no auto-expiration takes place.
- If the user has otherwise supplied a value using the new query introduced
  (explained above) then from then on the individual expiration policy will
  apply for that account.
- If it is non-zero all the user accounts with NULL in 
password_lifetime 
column (the default) will behave as if the global variable value was explicitly 
set for them.
- This default_password_lifetime variable will be settable only on global level 
and in the config file

* If for some user account you specify a non-NULL non-zero 
password_lifetime 
column value (through ALTER USER  PASSWORD EXPIRE INTERVAL  DAYS or 
through a direct table update/FLUSH PRIVILEGES) then from then on the individual 
expiration policy will apply for that account.
* If for some user account you specify a zero value for the 
password_lifetime column (through ALTER USER  PASSWORD EXPIRE 
INTERVAL 
0 DAYS or through a direct table update/FLUSH PRIVILEGES) no expiration will 
take place for this account no matter what the global says.