Security - MySQL - page 794

23.5. Expiring Passwords
Problem
You want users to pick a new MySQL password.
Solution
The ALTER USER statement expires passwords.
Discussion
MySQL 5.6.7 and up provides an ALTER USER statement that enables an administrator
to expire an account's password:
ALTER USER 'cbuser' @ 'localhost' PASSWORD EXPIRE ;
Here are some uses for password expiration:
• You can implement a policy that new users must select a new password when first
connecting: immediately expire the password for each new account you create.
• If you impose a stricter policy on acceptable passwords (see Recipe 23.3 ), you can
expire all existing passwords to require each user to choose a new one that meets
the more stringent requirements.
ALTER USER affects a single account. It works by setting the password_expired column
to Y for the appropriate mysql.user row. To “cheat” and expire passwords for all non‐
anonymous accounts at once, do this (anonymous users cannot reset their password,
so expiring those would be unfriendly):
UPDATE mysql . user SET password_expired = 'Y' WHERE User <> '' ;
FLUSH PRIVILEGES ;
Alternatively, to affect all accounts but avoid modifying the grant tables directly, use a
stored procedure that loops through all accounts and executes ALTER USER for each:
CREATE PROCEDURE expire_all_passwords ()
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE ;
DECLARE account TEXT ;
DECLARE cur CURSOR FOR
SELECT CONCAT ( QUOTE ( User ), '@' , QUOTE ( Host )) AS account
FROM mysql . user WHERE User <> '' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;
OPEN cur ;
expire_loop : LOOP
FETCH cur INTO account ;
IF done THEN