Configure password settings like expiration, reuse, dual, and failed login attempt in MySQL

We are using password management for better security purposes. It will be great to change the password at specific intervals. Configure the password with commands as follows:

Enable or Disable the Password Expiration in MySQL:

--Change the configure file my.cnf, set password expire after 6 month
[mysqld]
default_password_lifetime=180
Note: if its value set to Zero then password never expire. default_password_lifetime=0

-- Use SET PRESIST
Enable:
SET PERSIST default_password_lifetime = 180;
Disable:
SET PERSIST default_password_lifetime = 0;
Note: SET PERSIST sets the global variable runtime value, but also writes the variable setting to the mysqld-auto.cnf file

--CREATE or ALTER command is also use to set for particular user:
Enable:
CREATE USER 'hr'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'hr'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
Disable:
CREATE USER 'hr'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'hr'@'localhost' PASSWORD EXPIRE NEVER;

Password Reuse Policy is also configured in MySQL:

Set password reuse policy to prohibit the old password used in the last 10 times or within 365 days:

-- Change configuration file:
[mysqld]
password_history=10
password_reuse_interval=365

--You can also use PERSIST Command:
SET PERSIST password_history = 10;
SET PERSIST password_reuse_interval = 365;

--Use alter or Create command for same: ( not repeat 10 password or within 356 day)
CREATE USER 'hr'@'localhost' PASSWORD HISTORY 10 PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'hr'@'localhost' PASSWORD HISTORY 10
 PASSWORD REUSE INTERVAL 365 DAY;

Dual Password in MySQL:

It is used to retain the two passwords for the user, If you want to change the application password then you can use the old password until you change the application’s new password in all its modules or setting like background jobs or different application modules.

-- Add a new password to existing user:
ALTER USER 'app1'@'localhost' IDENTIFIED BY 'password_b'
 RETAIN CURRENT PASSWORD;

--Discard the old password:
ALTER USER 'app1'@'localhost'
 DISCARD OLD PASSWORD;

Failed login attempt which locks the account for specific times:

If user has specified failed login attempt then its user is locked for specific time:

-- Failed login attempts means user tried N times login attempt will lock the account.
--Password lock time means how long it will be in lock( n specify in days) or unbounded means until administrator unlock the account.

CREATE USER 'hr'@'localhost' IDENTIFIED BY 'password' FAILED_LOGIN_ATTEMPTS n PASSWORD_LOCK_TIME n;
ALTER USER 'hr'@'localhost' FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME UNBOUNDED;