Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21767

If default_password_lifetime > 0, then password_lifetime is not set for newly set passwords

    XMLWordPrintable

Details

    Description

      The description of the default_password_lifetime system variable says the following:

      This defines the global password expiration policy. 0 means automatic password expiration is disabled. If the value is a positive integer N, the passwords must be changed every N days. This behavior can be overridden using the password expiration options in ALTER USER.

      I would interpret this as meaning: if this system variable's value is non-zero, then password expiration will be enabled by default. However, as far as I can tell, password expiration is never enabled by default.

      The value provided by the default_password_lifetime system variable only seems to apply if the PASSWORD EXPIRE DEFAULT clause is provided to the CREATE USER or ALTER USER statements.

      We can test this by creating some users, and then querying mysql.global_priv, and then looking at the value of the password_lifetime attribute.

      For example, if the PASSWORD EXPIRE DEFAULT clause is provided to the CREATE USER or ALTER USER statement, then the password_lifetime attribute is set, so password expiration is definitely enabled for this user account:

      MariaDB [(none)]> SET GLOBAL default_password_lifetime=10;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [(none)]> CREATE USER 'pw_expires_default_clause'@'localhost' PASSWORD EXPIRE DEFAULT;
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [(none)]> SELECT JSON_DETAILED(Priv) FROM mysql.global_priv WHERE User = 'pw_expires_default_clause' AND Host = 'localhost';
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | JSON_DETAILED(Priv)                                                                                                                                               |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | {
          "access": 0,
          "plugin": "mysql_native_password",
          "authentication_string": "",
          "password_last_changed": 1582074440,
          "password_lifetime": -1
      } |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      But if no PASSWORD EXPIRE ... clause is provided to the CREATE USER or ALTER USER statement, then the password_lifetime attribute is not set, so it seems that password expiration is not enabled for this user account:

      MariaDB [(none)]> SET GLOBAL default_password_lifetime=10;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [(none)]> CREATE USER 'pw_expires_no_clause'@'localhost';
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [(none)]> SELECT JSON_DETAILED(Priv) FROM mysql.global_priv WHERE User = 'pw_expires_no_clause' AND Host = 'localhost';
      +--------------------------------------------------------------------------------------------------------------------------------------+
      | JSON_DETAILED(Priv)                                                                                                                  |
      +--------------------------------------------------------------------------------------------------------------------------------------+
      | {
          "access": 0,
          "plugin": "mysql_native_password",
          "authentication_string": "",
          "password_last_changed": 1582074510
      } |
      +--------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      According to the source code, if the password_lifetime attribute is not set for a given user account, then password expiration is not enabled:

        /* the password should never expire */
        if (!acl_user.password_lifetime)
          return false;
      

      https://github.com/MariaDB/server/blob/mariadb-10.4.12/sql/sql_acl.cc#L13610

      I would think that if the user sets default_password_lifetime to a non-zero value, then they probably want password expiration to be enabled by default. If so, then I think the password_lifetime attribute should be set to -1 if default_password_lifetime is set to a non-zero value and no PASSWORD EXPIRE ... clause is provided.

      Is this a bug, or is it working as intended?

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.