Details

      Description

      MariaDB should have support for expiring user passwords manually
      via the PASSWORD EXPIRE option for the CREATE USER
      and ALTER USER statements. We should also implement global and
      per-account policies for automatic password expiration.

      Given MySQL 5.7 already has this feature, we should preserve
      compatibility in terms of both API and datadir migration.

      We should support the following use cases:

          CREATE USER user@localhost PASSWORD EXPIRE [option];
      

          ALTER USER user@localhost PASSWORD EXPIRE [option];
      

      • If no option is specified, the password should be expired with immediate effect.
      • If option is DEFAULT, the password is expired every N days since last changed,
        where N is set in a system var such as default_password_lifetime.
      • If option is NEVER, the password is never expired for user@localhost.
      • Option can also be INTERVAL N DAY, this way the password is expired
        every N days.

      The effect of an expired password should be controlled via a new system var
      such as disconnect_on_expired_password. When this var is true, new client
      connections for the expired account should be refused with the error code ER_MUST_CHANGE_PASSWORD_LOGIN.
      If false, new client connections are restricted to use only statements for changing
      the password (e.g. ALTER USER, SET PASSWORD). The execution of any other
      statement should return ER_MUST_CHANGE_PASSWORD.

      Clients should be able to specify whether they can handle a disconnect with an
      option for the mysql binary such as --connect-expired-password or by passing
      the MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS flag to mysql_options() for the C
      library.

      Implementation details:

      • The password expiration state of an account should be kept in the JSON Priv column of
        mysql.global_priv. The User_table_json class will be enriched with accessors
        for reading/writing from/to this JSON field.

            MariaDB [(none)]> select user, host, Priv from mysql.global_priv where user='user';
            +-------+-----------+-------------------------------------------------------------------------------------------------------+
            | user  | host      | Priv                                                                                                  |
            +-------+-----------+-------------------------------------------------------------------------------------------------------+
            | user  | localhost | {..., "password_expired":true, "password_last_changed":"2019-02-20, 00:00:00", "password_lifetime":5} |
            +-------+-----------+-------------------------------------------------------------------------------------------------------+
            1 row in set (0.001 sec)
        

      • To preserve the drop-in replacement property for MySQL 5.7 datadirs, we have to add
        similar accessors with the ones above to the User_table_tabular class which
        will read/write from/to the password expiration columns in the mysql.user table.

      References:
      http://dev.mysql.com/doc/refman/5.6/en/password-expiration-sandbox-mode.html

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                robertbindar Robert Bindar
                Reporter:
                ratzpo Rasmus Johansson
              • Votes:
                15 Vote for this issue
                Watchers:
                22 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: