MySQL User Password Expiration and Validation Policies

2020-03-21 · mysql, security, users

Auditors ask about password policy, DBAs quietly ignore it, and then an application dies over the weekend because a service account's password expired. I've been on the receiving end of that call. MySQL has had the tools since 5.6.6, and 5.7 and 8.0 expanded them: strength validation, global or per-account expiration, and on 8.0 password history, reuse intervals, dual passwords and failed-login locking. Left at defaults they do nothing or surprise you. Here are the settings I actually care about, for 5.7 and 8.0.

Strength validation

On 5.7 validation is a plugin; on 8.0 it is a component. Check what is loaded:

-- 5.7
SHOW PLUGINS;                       -- look for validate_password
-- 8.0
SELECT * FROM mysql.component;      -- look for component://mysql/component_validate_password

The 5.7 installer on CentOS loads the plugin automatically; on Ubuntu it is off. To install:

-- 5.7
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
-- 8.0
INSTALL COMPONENT 'file://component_validate_password';

Then set the policy. Variable names on 5.7 use underscores (validate_password_policy); on 8.0 they use a dot (validate_password.policy):

SET PERSIST validate_password.policy = MEDIUM;         -- LOW: length only; MEDIUM: + mixed case, digit, special; STRONG: + dictionary
SET PERSIST validate_password.length = 12;
SET PERSIST validate_password.check_user_name = ON;    -- reject passwords containing the user name

On 5.7 use SET GLOBAL and mirror the settings in /etc/my.cnf. VALIDATE_PASSWORD_STRENGTH('candidate') returns 0–100 and is handy for testing a policy before enabling it.

Expiration

default_password_lifetime is the number of days after which every account with no explicit setting must change its password. It is 0 (disabled) by default since 5.7.11 and on all 8.0 releases. Enabling it globally affects service accounts too, so if you set it, exempt them explicitly:

SET PERSIST default_password_lifetime = 90;
ALTER USER 'shopapp'@'10.0.1.%' PASSWORD EXPIRE NEVER;
ALTER USER 'backup'@'localhost'  PASSWORD EXPIRE NEVER;

Per-account settings override the global one:

ALTER USER 'jane'@'%' PASSWORD EXPIRE INTERVAL 60 DAY;
ALTER USER 'jane'@'%' PASSWORD EXPIRE DEFAULT;    -- back to the global value
ALTER USER 'temp_consultant'@'%' PASSWORD EXPIRE; -- must change at next login

An expired account can still log in but gets ERROR 1820 on every statement except ALTER USER ... IDENTIFIED BY (and SET PASSWORD). Whether the client is even allowed to connect in that state depends on disconnect_on_expired_password (default ON, meaning clients that do not declare expired-password support are disconnected).

History and reuse (8.0 only)

To stop users cycling between two passwords:

SET PERSIST password_history = 5;             -- cannot reuse any of the last 5
SET PERSIST password_reuse_interval = 365;    -- nor any used within the last year
ALTER USER 'jane'@'%' PASSWORD HISTORY 10 PASSWORD REUSE INTERVAL 730 DAY;

Hashes of previous passwords are kept in mysql.password_history. Related, password_require_current (8.0.13+) forces a user changing their own password to supply the current one:

ALTER USER 'jane'@'%' PASSWORD REQUIRE CURRENT;
-- user then must use:
ALTER USER USER() IDENTIFIED BY 'new-secret' REPLACE 'old-secret';

Dual passwords for rotation without an outage (8.0.14+)

Rotating a service account password used by many application servers used to mean a brief outage. With dual passwords the old and new one are both valid until you retire the old:

ALTER USER 'shopapp'@'10.0.1.%' IDENTIFIED BY 'new-secret' RETAIN CURRENT PASSWORD;
-- roll out 'new-secret' to all application servers, then:
ALTER USER 'shopapp'@'10.0.1.%' DISCARD OLD PASSWORD;

Locking after failed logins (8.0.19+)

ALTER USER 'jane'@'%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;     -- lock for 1 day after 5 failures
ALTER USER 'jane'@'%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME UNBOUNDED;
ALTER USER 'jane'@'%' ACCOUNT UNLOCK;                                    -- manual reset

Both attributes must be non-zero for locking to take effect. Do not enable this on accounts used by applications with retry loops; a misconfigured deploy can lock the account and turn a bad password into a full outage.

Checking what's in force

SELECT user, host, password_expired, password_last_changed, password_lifetime, account_locked
FROM mysql.user ORDER BY user, host;

SELECT VALIDATE_PASSWORD_STRENGTH('Tr0ub4dor&3');
SELECT * FROM performance_schema.persisted_variables WHERE variable_name LIKE 'validate_password%' OR variable_name LIKE 'password_%';

On 8.0 the mysql.user row also has password_reuse_history, password_reuse_time and password_require_current columns for the per-account values (NULL means "use the global setting"). Test an expiring account with mysql -u jane -p and confirm the expected ERROR 1820 appears before the interval is reached on production.

Errors you'll see

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. from an application that was working yesterday – A password expired, almost always through default_password_lifetime. Set a new password and mark service accounts PASSWORD EXPIRE NEVER.
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements – The validation policy rejected it. SHOW VARIABLES LIKE 'validate_password%' tells you the exact rules in force.
ERROR 3638 (HY000): Cannot use these credentials for 'jane@%' because they contradict the password history policy – Reuse blocked by password_history or password_reuse_interval.
ERROR 3955 (HY000): Access denied for user 'jane'@'localhost'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 5 consecutive failed logins. – The failed-login lock from above; unlock with ALTER USER ... ACCOUNT UNLOCK after confirming it was not an attack.
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords. – The client is too old to handle the sandbox mode; connect with the 5.7 or 8.0 command-line client to change it.

What I do: MEDIUM validation with 12+ characters everywhere, 90-day expiry for humans only, PASSWORD EXPIRE NEVER on every service account, dual passwords for rotation on 8.0.