Password policy, failed-login lockout and zero-downtime password rotation

Command

-- Password strength policy (component)
INSTALL COMPONENT 'file://component_validate_password';
SET PERSIST validate_password.policy = 'MEDIUM';
SET PERSIST validate_password.length = 12;
SELECT VALIDATE_PASSWORD_STRENGTH('Summer2024') AS score;

-- Failed-login lockout, password lifetime, history
ALTER USER 'ana'@'%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1
  PASSWORD EXPIRE INTERVAL 90 DAY PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY;

-- Zero-downtime rotation: dual password
ALTER USER 'svc_app'@'10.0.%' IDENTIFIED BY 'N3w#AppSecret2025' RETAIN CURRENT PASSWORD;
-- Once every application uses the new password
ALTER USER 'svc_app'@'10.0.%' DISCARD OLD PASSWORD;

What it does

Enables a policy that rejects weak passwords, temporarily locks accounts after consecutive failed logins, sets password lifetime and reuse rules; with dual passwords a service account password is changed without downtime while applications are updated one by one.

When to use it

For security audits / compliance (PCI, ISO 27001) and service account password rotation.

Caution

The policy applies only to new / changed passwords; it does not detect existing weak ones. On service accounts FAILED_LOGIN_ATTEMPTS lets a single misconfigured client lock the account and cause an outage; use it for human accounts. PASSWORD_LOCK_TIME is in days (UNBOUNDED: until unlocked manually); unlock with ALTER USER ... ACCOUNT UNLOCK. PASSWORD EXPIRE causes unexpected outages on service accounts; prefer PASSWORD EXPIRE NEVER plus dual password rotation there.