[MDEV-21767] If default_password_lifetime > 0, then password_lifetime is not set for newly set passwords Created: 2020-02-19  Updated: 2020-02-19  Resolved: 2020-02-19

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.4.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-7597 Expiration of user passwords Closed
relates to MDEV-18716 Document password expiration Closed

 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?

 Comments   
Comment by Geoff Montee (Inactive) [ 2020-02-19 ]

It looks like this might actually be working as intended, and the server code handles the default case in the User_table_json:::get_password_lifetime() method. In that method, if the password_lifetime field isn't set, then it seems to return -1:

  longlong get_password_lifetime () const
 
  { return get_int_value("password_lifetime", -1); }

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

And -1 is the value that tells the server to use the default_password_lifetime value.

Comment by Geoff Montee (Inactive) [ 2020-02-19 ]

I performed a more thorough test of this.

First, I created a user account:

CREATE USER 'pw_expires_not_specified'@'localhost' IDENTIFIED BY 'password';
 
GRANT ALL PRIVILEGES ON *.* TO 'pw_expires_not_specified'@'localhost';

And then I manually altered the privilege table to make its password 10 days old, and I flushed the privileges:

UPDATE mysql.global_priv 
   SET Priv=JSON_SET(Priv, '$.password_last_changed', 
      (JSON_EXTRACT(Priv, '$.password_last_changed') - (60 * 60 * 24 * 10)))
 
   WHERE User = 'pw_expires_not_specified' 
      AND Host = 'localhost'\G
 
FLUSH PRIVILEGES;

And then I manually set default_password_lifetime to 5 days:

SET GLOBAL default_password_lifetime=5;

And then I tried logging in as the account:

$ mysql -u pw_expires_not_specified -ppassword

And then I tried querying a table:

SELECT JSON_DETAILED(Priv) FROM mysql.global_priv WHERE User = 'pw_expires_not_specified' AND Host = 'localhost'\G

I did see the following error at this point:

MariaDB [(none)]> SELECT FROM_UNIXTIME(JSON_EXTRACT(Priv, '$.password_last_changed')) FROM mysql.global_priv WHERE User = 'pw_expires_not_specified' AND Host = 'localhost'\G
 
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

So it does appear that a non-existent password_lifetime attribute is treated the same as -1, and everything seems to be working as intended.

Generated at Thu Feb 08 09:09:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.