EFAULT;

SHOW CREATE USER 'monty'@'localhost';
+------------------------------------------------------------------+
| CREATE USER for monty@localhost                                  |
+------------------------------------------------------------------+
| CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY |
+------------------------------------------------------------------+

SHOW CREATE USER 'konstantin'@'localhost';
+------------------------------------------------------------+
| CREATE USER for konstantin@localhost                       |
+------------------------------------------------------------+
| CREATE USER 'konstantin'@'localhost' PASSWORD EXPIRE NEVER |
+------------------------------------------------------------+

SHOW CREATE USER 'amse'@'localhost';
+--------------------------------+
| CREATE USER for amse@localhost |
+--------------------------------+
| CREATE USER 'amse'@'localhost' |
+--------------------------------+

Checking When Passwords Expire
------------------------------

The following query can be used to check when the current passwords expire for
all users:

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;

--connect-expired-password Client Option
----------------------------------------

The mariadb client --connect-expired-password option notifies the server that
the client is prepared to handle expired password sandbox mode (even if the
--batch option was specified).

URL: https://mariadb.com/kb/en/user-password-expiry/https://mariadb.com/kb/en/user-password-expiry/