MariaDB user password expiration

Hello colleagues.

In today's article, I want to tell you about such a feature as user password expiration in MariaDB. Password expiration allows administrators to set user password expiration manually or automatically. We will consider both options.

Article content:

1. Checking the password expiration of all MariaDB database users.
2. Default_password_lifetime system variable.
3. Set the password expiration date for the user.
4. Remove password expiration restrictions for the user.
5. Password expiration time by default.

1. Checking the password expiration of all MariaDB database users.

First, let's check the password expiration date for all users, for this we will use the query.

mariadb> 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;

As you can see in the screenshot, the password_lifetime field has a value of 0, which means that by default, the password life for all users in the MariaDB database is not set.

2. Default_password_lifetime system variable.

The MariaDB database has a default_password_lifetime system variable, by default it has a value of 0, which means that the password does not expire for users. To check the value of a variable, use the query:

mariadb> SHOW VARIABLES LIKE 'default_password_lifetime';

Let's change the value of the default_password_lifetime variable, add a password for users to expire after 1 day.

mariadb> set global default_password_lifetime = 1;

As soon as we changed the values of the default_password_lifetime variable by one day, the values of the password_expiration_datetime field also changed. The value in this field indicates at what time the user's password will expire.

3. Set the password expiration date for the user.

When creating a new user, you can explicitly specify how long the user's password will expire. Let's create a new user and explicitly state that their password will expire in two days.

mariadb> CREATE USER 'potomka'@'localhost' IDENTIFIED BY 'Qwertysdf3SDasd3%' PASSWORD EXPIRE INTERVAL 2 DAY;

The PASSWORD EXPIRE INTERVAL 2 DAY parameter just specifies how many days the password will expire.

After creating the user, we see that the password_lifetime field for the user has a value of 2 days.

4. Remove password expiration restrictions for the user.

Let's say potomka is a technology account for us, and setting a password expiration date for it is a very bad practice, because after the password expires, your application will stop working because the MariaDB database will not miss the account in which the password must already be changed. To solve this problem, you can explicitly specify the account so that its password is eternal and does not have a statute of limitations.

mariadb> ALTER USER 'potomka'@'localhost' PASSWORD EXPIRE NEVER;

The PASSWORD EXPIRE NEVER option explicitly specifies that the password does not expire and is forever.

As you can see, for the potomka account, the value of the password_lifetime field became 0 days and the password_expiration_datetime field also became 0 days.

5. Password expiration time by default.

Let's imagine that you create accounts very often and constantly remembering how many days you need to set the password expiration time is not very convenient. This is exactly what the default_password_lifetime variable exists for. We have already set its values ​​above so that all passwords expire after one day, and now when we create a new user, instead of the PASSWORD EXPIRE INTERVAL 1 DAY parameter, we will specify PASSWORD EXPIRE DEFAULT, this means the default password expiration time, and it is equal to the value of the default_password_lifetime variable, and the value of the default_password_lifetime variable is set to one day.

mariadb> ALTER USER 'potomka'@'localhost' PASSWORD EXPIRE DEFAULT;

Thank you all, I hope my article was of some help to you.