WL#7131: Add timestamp in mysql.user on the last time the password was changed
Affects: Server-5.7 — Status: Complete
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.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.