default_password_lifetime

Overview

In 11.4 ES, 10.6 ES, 10.6 CS, 10.5 ES, 10.5 CS, 10.4 ES, 10.4 CS:

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.

In 11.4 ES, 10.6 ES, 10.5 ES, 10.4 ES:

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.

In 10.3 ES, 10.3 CS, 10.2 ES, 10.2 CS:

Not present

See also: System Variables for MariaDB Enterprise Server 11.4, in 10.6 ES, in 10.5 ES, in 10.4 ES, in 10.3 ES, in 10.2 ES, in 10.6 CS, in 10.5 CS, in 10.4 CS, in 10.3 CS, and in 10.2 CS

See also: System Variables for MariaDB Enterprise Server 11.4, in 10.6 ES, in 10.5 ES, and in 10.4 ES

USAGE

The default_password_lifetime system variable can have its default value configured via SkySQL Configuration Manager.

The default_password_lifetime system variable can be set in a configuration file:

[mariadb]
default_password_lifetime=180

The default_password_lifetime system variable can also be set dynamically at runtime by executing SET GLOBAL:

SET GLOBAL default_password_lifetime=180;

When the default_password_lifetime system variable is set dynamically at runtime, its value will be reset the next time the server restarts. To make the value persist on restart, set it in a configuration file too.

DETAILS

The default_password_lifetime system variable defines the number of days that are required between password changes by default.

  • If the default_password_lifetime system variable is set to 0, then passwords do not expire by default.

  • The default_password_lifetime system variable will be used for a given user account if the user account was created or altered with no PASSWORD EXPIRE ... clause.

  • The default_password_lifetime system variable will also be used for a given user account if the user account was created or altered with the PASSWORD EXPIRE DEFAULT clause.

  • The default_password_lifetime system variable will be ignored for a given user account if the user account was created or altered with any PASSWORD EXPIRE ... clause other than the PASSWORD EXPIRE DEFAULT clause.

SYNONYMS

SCHEMA

PARAMETERS

Command-line

--default_password_lifetime=#

Configuration file

Supported

Dynamic

Yes

Scope

Global

Data Type

INT UNSIGNED

Minimum Value

0

Maximum Value

4294967295

Product Default Value

0

SKYSQL

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

Multi-Node Analytics

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

Single Node Analytics

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

Replicated Transactions

10.4

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.5

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

Single Node Transactions

10.4

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.5

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

GCP

Multi-Node Analytics

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

Single Node Analytics

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

Replicated Transactions

10.4

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.5

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

Single Node Transactions

10.4

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.5

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

ColumnStore Data Warehouse

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

Enterprise Server With Replica(s)

10.4

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.5

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

Enterprise Server Single Node

10.4

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.5

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

GCP

ColumnStore Data Warehouse

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

Enterprise Server With Replica(s)

10.4

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.5

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

Enterprise Server Single Node

10.4

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.5

0 (choices: 0, 7, 14, 30, 90, 180, 365)

10.6

0 (choices: 0, 7, 14, 30, 90, 180, 365)

PRIVILEGES

The default_password_lifetime system variable requires the SUPER privilege to dynamically set its value at runtime.

EXAMPLES

Default Behavior

If a user account is created or altered with no PASSWORD EXPIRE ... clause, the password_lifetime field for the user account will not be set, and the user account will use the default_password_lifetime system variable to determine its password expiration policy:

SET GLOBAL default_password_lifetime=180;

CREATE USER 'pw_expires_no_clause'@'localhost'
   IDENTIFIED BY 'mariadb';

SELECT JSON_DETAILED(Priv)
FROM mysql.global_priv
WHERE User = 'pw_expires_no_clause'
   AND Host = 'localhost';

Example output:

*************************** 1. row ***************************
JSON_DETAILED(Priv): {
 "access": 0,
 "plugin": "mysql_native_password",
 "authentication_string": "*54958E764CE10E50764C2EECBB71D01F08549980",
 "password_last_changed": 1582074510
}

Using the Password Expire Default Clause

If a user account is created or altered with the PASSWORD EXPIRE DEFAULT clause, the password_lifetime field for the user account is set to -1, and the user account will use the default_password_lifetime system variable to determine its password expiration policy:

SET GLOBAL default_password_lifetime=180;

CREATE USER 'pw_expires_default_clause'@'localhost'
   IDENTIFIED BY 'mariadb'
   PASSWORD EXPIRE DEFAULT;

SELECT JSON_DETAILED(Priv)
FROM mysql.global_priv
WHERE User = 'pw_expires_default_clause'
   AND Host = 'localhost';

Example output:

*************************** 1. row ***************************
JSON_DETAILED(Priv): {
 "access": 0,
 "plugin": "mysql_native_password",
 "authentication_string": "*54958E764CE10E50764C2EECBB71D01F08549980",
 "password_last_changed": 1582074440,
 "password_lifetime": -1
}

Querying Password Expiration Dates

Database administrators can use default_password_lifetime in the following query to check when all current user account passwords will expire:

WITH password_expiration_info AS (
   SELECT User, Host,
      IF(
         IFNULL(JSON_EXTRACT(Priv, '$.password_lifetime'), -1) = -1,
         @@global.default_password_lifetime,
         JSON_EXTRACT(Priv, '$.password_lifetime')
      ) AS password_lifetime,
      JSON_EXTRACT(Priv, '$.password_last_changed') AS password_last_changed
   FROM mysql.global_priv
)
SELECT pei.User, pei.Host,
   pei.password_lifetime,
   FROM_UNIXTIME(pei.password_last_changed) AS password_last_changed_datetime,
   FROM_UNIXTIME(
      pei.password_last_changed +
      (pei.password_lifetime * 60 * 60 * 24)
   ) AS password_expiration_datetime
FROM password_expiration_info pei
WHERE pei.password_lifetime != 0
   AND pei.password_last_changed IS NOT NULL
UNION
SELECT pei.User, pei.Host,
   pei.password_lifetime,
   FROM_UNIXTIME(pei.password_last_changed) AS password_last_changed_datetime,
   0 AS password_expiration_datetime
FROM password_expiration_info pei
WHERE pei.password_lifetime = 0
   OR pei.password_last_changed IS NULL;

Example output:

*************************** 1. row ***************************
                    User: pw_expires_no_clause
                    Host: localhost
          password_lifetime: 180
password_last_changed_datetime: 2020-02-20 22:39:54.000000
  password_expiration_datetime: 2020-08-18 22:39:54
*************************** 2. row ***************************
                    User: pw_expires_default_clause
                    Host: localhost
          password_lifetime: 180
password_last_changed_datetime: 2020-02-20 22:40:55.000000
  password_expiration_datetime: 2020-08-18 22:40:55
*************************** 3. row ***************************
                    User: pw_expires_never_clause
                    Host: localhost
          password_lifetime: 0
password_last_changed_datetime: 2020-02-21 01:43:44.000000
  password_expiration_datetime: 0
*************************** 4. row ***************************
                    User: root
                    Host: localhost
          password_lifetime: 180
password_last_changed_datetime: NULL
  password_expiration_datetime: 0

The output can be interpreted in the following way:

  • The password_last_changed_datetime column is the date and time when the password was last changed.

  • If the password_last_changed_datetime column is NULL, then the password has never been changed. The user account may be using an authentication plugin that does not store a password, such as unix_socket.

  • If the password_lifetime column is 0, then the password will never expire.

  • If the password_lifetime column is greater than 0, then the column represents the total number of days that the password is valid starting from the value of the password_last_changed_datetime column.

  • The password_expiration_datetime column is the date and time when the current password expires.

  • If the password_expiration_datetime column is 0, then the password will never expire.

Dynamically Resetting Global Value to Default

The global value of the default_password_lifetime system variable can be dynamically reset to the default value by setting it to DEFAULT:

SET GLOBAL default_password_lifetime=180;

SHOW GLOBAL VARIABLES LIKE 'default_password_lifetime';

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 180   |
+---------------------------+-------+

SET GLOBAL default_password_lifetime=DEFAULT;

SHOW GLOBAL VARIABLES LIKE 'default_password_lifetime';

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+

Invalid Value for Variable

If a user tries to set the default_password_lifetime system variable to an invalid value, then the specific behavior depends on the value of the sql_mode system variable.

If the sql_mode system variable does not contain STRICT_ALL_TABLES and a user tries to set the default_password_lifetime system variable to an invalid value, then the operation will appear to succeed, but a warning will be raised with the ER_TRUNCATED_WRONG_VALUE error code. The warning will point out that the value was truncated, which means that the value was automatically set to the closest invalid value:

SET GLOBAL default_password_lifetime=-1;
SHOW WARNINGS;

+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect default_password_lifetime value: '-1' |
+---------+------+-----------------------------------------------------------+

SHOW GLOBAL VARIABLES LIKE 'default_password_lifetime';

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+

SET GLOBAL default_password_lifetime=4294967296;
SHOW WARNINGS;

+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect default_password_lifetime value: '4294967296' |
+---------+------+-------------------------------------------------------------------+

SHOW GLOBAL VARIABLES LIKE 'default_password_lifetime';

+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| default_password_lifetime | 4294967295 |
+---------------------------+------------+

If the sql_mode system variable does contain STRICT_ALL_TABLES and a user tries to set the default_password_lifetime system variable to an invalid value, then the operation will fail with the ER_WRONG_VALUE_FOR_VAR error code:

SHOW SESSION VARIABLES LIKE 'sql_mode';

+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+

SET SESSION sql_mode=CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES');

SET GLOBAL default_password_lifetime=-1;

ERROR 1231 (42000): Variable 'default_password_lifetime' can't be set to the value of '-1'

Invalid Type for Variable

If a user tries to set the default_password_lifetime system variable to an invalid argument, then the operation will fail with the ER_WRONG_TYPE_FOR_VAR error code:

SET GLOBAL default_password_lifetime='';

ERROR 1232 (42000): Incorrect argument type to variable 'default_password_lifetime'

SET GLOBAL default_password_lifetime='infinity';

ERROR 1232 (42000): Incorrect argument type to variable 'default_password_lifetime'

Handling Expired Passwords

When a user account's password expires, then the specific behavior depends on whether the client can handle sandbox mode. In sandbox mode, the user account is only allowed to execute the SET PASSWORD statement. All other statements will fail with the ER_MUST_CHANGE_PASSWORD error code:

SELECT 1;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

If a client can handle sandbox mode, then it should send the CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS capability flag to the server. When using MariaDB Connector/C, this can be enabled by setting the MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS option for the connection.

If a client cannot handle sandbox mode, then the specific behavior depends on the value of the disconnect_on_expired_password system variable. If the disconnect_on_expired_password system variable is set to OFF, then the server will allow a user account with an expired password to connect to the server in sandbox mode anyway. If the disconnect_on_expired_password system variable is set to ON, then the login will fail with the ER_MUST_CHANGE_PASSWORD_LOGIN error code:

$ mysql --batch --user=mariadb --password
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it
using a client that supports expired passwords

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Added in MariaDB Community Server 10.4.3.

10.3 Enterprise

  • Not present.

10.3 Community

  • Not present.

10.2 Enterprise

  • Not present.

10.2 Community

  • Not present.

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES