WL#6595: Password rotation policy

MySQL at present offers password expiration policy which enforces password change based on time. MySQL also has, through plugin, ability to control what can and can not be used as password. In order to have a password rotation policy, we need a final piece of the puzzle - ability to restrict password reuse and thus enforce user to supply new, strong password with each password change.

This worklog aims at introducing restriction on password reuse. Such restrictions can be configured at global level as well as individual user level. Care will be taken in maintain password history in very secure manner because it may give clues about habits/patterns used by individual users when they change their password.

• FR1: It should be possible to enforce minimum number of password changes required before a password can be reused for a given user account.
  • FR1.1: A new global only system variable will be added called password_history (default to 0) to hold the global default value for minimum number of password changes required before a password can be reused.
    • FR1.1.1: The password_history system variable will be settable at runtime
    • FR1.1.2: The new value of password_history will have immediate effect on all user accounts with a default value for password_history
  • FR1.2: A PASSWORD HISTORY N|DEFAULT clause will be added to CREATE/ALTER USER that will set the password history for a particular user account either to a specific value (in which case it becomes independent from the global password_history value) or to DEFAULT (in which case the user account's policy follows the global password_history value).
  • NF1.3: a new column mysql.user.password_reuse_history will be added to hold the value of PASSWORD HISTORY for the account.
• FR2: It should be possible to enforce minimum amount of days required before a password can be reused for a given user account.
  • FR2.1. A new global only system variable will be added called password_reuse_interval (default 0) to hold the minimum number of days before a password can be reused.
    • FR2.1.1: The password_reuse_interval system variable will be settable at runtime
    • FR2.1.2: The new value of password_reuse_interval will have immediate effect on all user accounts with a default value for password_history
  • FR2.2: A PASSWORD REUSE INTERVAL [N DAYS]|DEFAULT clause will be added to CREATE/ALTER USER that will set the password reuse interval for a particular user account either to a specific value (in which case it becomes independent from the global password_reuse_interval value) or to DEFAULT (in which case the user account's policy follows the global password_history value).
  • NF2.3: a new column mysql.user.password_reuse_time will be added to hold the value of PASSWORD REUSE INTERVAL for the account (NULL for the default value).
• NF3: A new table mysql.password_history will be added to hold the password history for all accounts.
  • NF3.1. The primary key for mysql.password_history is user,host and timestamp.
  • NF3.2. The password history will contain enough rows per user to allow full check of both the password reuse interval and the password history length.
  • FR3.3. As a new history is added the excess entries that sit outside range needed to enforce password history and password reuse interval for the current values of the 2 will be auto-pruned.
  • FR3.4. When a user account is deleted all of the history entries for it will be deleted.
  • FR3.5. When the authentication method for an account is changed (the plugin) all of the password history entries will be pruned
  • FR3.6. The password history will contain a hash of the password as returned by the authentication plugin in a format identical to the one used for mysql.user.authentication_string
  • FR3.7. When the user account is renamed via RENAME USER the entries in the password_history table for that account will be updated to contain the new account/host name.
  • FR3.8. No in-memory hash of the password history will be maintained. Changes to the table data will be immediately active.
  • FR3.9. When the mysql.password_history table is absent it will be treated as empty.
  • FR3.9. When the mysql.password_history table is absent updates to it (i.e. any change involving storing password history) will fail with an error.
  • FR3.10 Updates done to mysql.password_history will not affect the atomicity of the ACL statements.
• FR4. The new clauses to CREATE/ALTER USER will not be a per-user option, but will apply to all users in CREATE/ALTER USER, similarly to the other password_options (see e.g. https://dev.mysql.com/doc/refman/8.0/en/create-user.html).
  • FR4.1. Multiple PASSWORD HISTORY/PASSWORD REUSE INTERVAL can be supplied. The last one of each kind will be effective.
• FR5. The new clauses to CREATE/ALTER USER will be replicated into the ACL statement text if specified in the original statement.
• FR6: the new clauses to CREATE/ALTER USER will always be visible in SHOW CREATE USER.
• FR7: Empty passwords are not verified against nor stored into password history.

• D1: A user account is represented as <user_identifier>@<host_identifier>
• D2: A password hash is transformation done on plaintext credential + SALT by performing SHA2 hash multiple times.

I1: Updated semantics for CREATE USER [IF NOT EXISTS]

• CREATE USER [IF NOT EXISTS] will support additional clauses to specify credential reuse restriction

 CREATE USER [IF NOT EXISTS]
   user_specification [, user_specification] ...
   [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
   [WITH resource_option [resource_option] ...]
   [password_option | lock_option] ...
 
 password_option: {
   PASSWORD EXPIRE
 | PASSWORD EXPIRE DEFAULT
 | PASSWORD EXPIRE NEVER
 | PASSWORD EXPIRE INTERVAL N DAY
 | PASSWORD HISTORY N | PASSWORD HISTORY DEFAULT
 | PASSWORD REUSE INTERVAL N DAY | PASSWORD REUSE INTERVAL DEFAULT
 }

• Value accepted by clauses PASSWORD HISTORY and PASSWORD REUSE INTERNAL will be in the range of 0 to INT32_MAX
• For both options, a value of 0 indicates that there are no restriction of a given option.
• If restriction based on history is not specified, it will be considered equivalent to PASSWORD HISTORY DEFAULT.
• If restriction based on reuse interval is not specified, it will be considered equivalent to PASSWORD REUSE INTERVAL DEFAULT.

I2: Updated semantics for ALTER USER [IF EXISTS]

• ALTER USER [IF EXISTS] will support additional clauses to specify credential reuse restriction

 ALTER USER [IF EXISTS]
   user_specification [, user_specification] ...
   [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
   [WITH resource_option [resource_option] ...]
   [password_option | lock_option] ...
 
 password_option: {
   PASSWORD EXPIRE
 | PASSWORD EXPIRE DEFAULT
 | PASSWORD EXPIRE NEVER
 | PASSWORD EXPIRE INTERVAL N DAY
 | PASSWORD HISTORY N | PASSWORD HISTORY DEFAULT 
 | PASSWORD REUSE INTERVAL N DAY | PASSWORD REUSE INTERVAL DEFAULT
 }

• Value accepted by clauses PASSWORD HISTORY and PASSWORD REUSE INTERNAL will be in the range of 0 to INT32_MAX.
• For both options, a value of 0 indicates that there are no restriction of a given option.
• history will be logged for both hashes and clear text passwords
• history check will be performed only when a clear text password is supplied.

I3: New system variable : password_history

• This variable will serve as a default policy for user accounts for which password history based restriction is not specified explicitly or is specified as PASSWORD HISTORY DEFAULT.
  • Min value : 0 (No restriction)
  • Max value : INT32_MAX
  • Default : 0 (No restriction)
  • Type of variable : Global, Dynamic
• Privilege requirement : As imposed by MySQL server for setting global system variable

I4: New system variable : password_reuse_interval

• This variable will serve as a default policy for user accounts for which reuse interval based restriction is not specified explicitly or is specified as PASSWORD REUSE INTERVAL DEFAULT
  • Min value : 0 (No restriction)
  • Max value : INT32_MAX
  • Default : 0 (No restriction)
  • Unit of the value : days
  • Type of variable : Global, Dynamic
• Privilege requirement : As imposed by MySQL server for setting global system variable

I5: Updated structure of system table : mysql.user

• mysql.user table will be updated to contain two new columns for storing restriction on credential reuse.

ALTER TABLE user ADD password_reuse_history smallint unsigned NULL DEFAULT NULL AFTER Drop_role_priv;
ALTER TABLE user ADD password_reuse_time smallint unsigned NULL DEFAULT NULL AFTER password_reuse_history;

I6: New system table : mysql.password_history

• A new table will be introduced in mysql database. This table will contain password history information for various users.

 CREATE TABLE IF NOT EXISTS password_history
 (
 HOST CHAR(60) BINARY DEFAULT  NOT NULL, USER CHAR(32) BINARY DEFAULT  NOT NULL, CREDENTIAL TEXT,
 CREDENTIAL_TIMESTAMP TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(HOST, USER, TIMESTAMP DESC)
 ) engine=InnoDB STATS_PERSISTENT=0 CHARACTER SET utf8 COLLATE utf8_bin comment='Password history for users';

I7: How credential history checks will be performed

• A new credential can be submitted through one of the following ways:
  • ALTER USER
  • SET PASSWORD
  • CREATE USER
• When A new credential is to be checked, control will be passed to function responsible for history check with following information
  • user name
  • host name
  • existing restriction on reuse based on history
  • existing restriction on reuse based on interval
  • auth plugin reference
  • the clear text password, if available
  • The password hash produced by the authentication plugin
  • Handle to mysql.password_history table
• The function will:
  • start a counter
  • iterate over all rows matching the partial PK (host, user) in PK order (descending timestamp order)
  • for each row where the counter is less than or equal to the limit or the difference in days between the event and today is less than the days limit
    • if clear text password is supplied call the auth plugin's compare_password_with_hash() to check the hash stored in the table against the cleartext password. If a match is found an error is returned.
    • if row number is greater than or equal with the password reuse limit AND if the day difference between the event and today is greater than the password reuse days limit, delete the row (since it will never be needed for further checks with the same parameters).
  • add a new row with the hash supplied by the caller and current timestamp.

In this way if you e.g. have 5 rows in password history, you reset the password backlog count to 2 and you set a new password it will: 1. check the first 2 rows for matches 2. delete the 4 oldest rows 3. Add a new row with the password being set

It is possible that we maintain more rows than required by history based reuse limit because they don't violate time based reuse limit. Vice versa is also true.

Note that if no cleartext password is supplied it will just add the new row (and delete rows as needed). This is basically to support updating the slave's password history table while just operating on the password hash.

Note that password history check is plugin dependent. I.e. when a different plugin is set the history will not be usable for checks.

I8: Displaying history based/time based constraints on credentials

• SHOW CREATE USER will be enhanced to display history based and time based constraints on credentials.

I9: Restrictions

• Restrictions related to password reuse will be enforced only for those authentication plugins which sets AUTH_FLAG_USES_INTERNAL_STORAGE.
• Restrictions related to password reuse will be enforced from the first credential change after policy change.
• Restrictions related to password reuse can not be specified through GRANT statement because modifying user account properties through GRANT is deprecated since MySQL 5.7.

I10: Implementation notes

• In memory caches of mysql.password_history will not be maintained. Entries of mysql.password_history will be processed as part of ALTER USER/SET PASSWORD and discarded fro memory at the end of the query.
  • Thread's memroot will be used to cache required entries for the duration of the query.

I11: Effects on replication

• The CREATE ALTER USER will be extended to replicate the new clauses too.
• The password will still be replicated as a hash (as it is now).
• The changes to the password_history table will not be replicated.

This will ensure all of the data for proper validation will be present on any of the slaves.

I12: Extension to the authentication API

A new function will be added at the end of the auth API, as follows:

  /**
    Plugin API to compare a clear text password with a stored hash
  
    @arg hash              pointer to the hashed data
    @arg hash_length       length of the hashed data
    @arg cleartext         pointer to the clear text password
    @arg cleartext_length  length of the cleat text password
    @arg[out] is_error     non-zero in case of error extracting the salt
    @retval 0              the hash was created with that password
    @retval non-zero       the hash was created with a different password
  */
  typedef int
  (*compare_password_with_hash_t)(const char *hash, unsigned long hash_length,
                                  const char *cleartext,
                                  unsigned long cleartext_length,
                                  int *is_error);

Auth API version will be bumped to:

1. define MYSQL_AUTHENTICATION_INTERFACE_VERSION 0x0102