CREATE and ALTER USER

CREATE USER, ALTER USER, and Password Policy

CREATE USER makes an account that can log in and do nothing else; ALTER USER takes the same clauses later. The validate_password component (Securing a Fresh Installation) rejects weak passwords server-wide, and per-account clauses set expiry, reuse history, and a block after failed logins:

A password policy, an application account and a human accountSQL

INSTALL COMPONENT 'file://component_validate_password';
CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'shopapp2026';
CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'App#Shop-2026'
  PASSWORD EXPIRE NEVER FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
CREATE USER 'ana'@'localhost' IDENTIFIED BY 'Ana#Pass-2026'
  PASSWORD EXPIRE INTERVAL 90 DAY PASSWORD HISTORY 5 PASSWORD REQUIRE CURRENT
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;

Output

ERROR 1819 (HY000) at line 2: Your password does not satisfy the current policy requirements

IDENTIFIED BY RANDOM PASSWORD has the server generate one, and REQUIRE SSL demands TLS. An expired application password takes the site down, so shop_app never expires; rotate it with dual passwords instead. RETAIN CURRENT PASSWORD keeps the old password valid beside the new while you deploy it, and DISCARD OLD PASSWORD ends the overlap:

Rotating an application password without downtimeSQL

try() { mysql -ushop_app -p"$1" -Nse "SELECT CURRENT_USER()" 2>&1 | grep -v Warning; }
sudo mysql -e "ALTER USER 'shop_app'@'localhost' IDENTIFIED BY 'App#Shop-2027'
  RETAIN CURRENT PASSWORD"
try 'App#Shop-2026'; try 'App#Shop-2027'
sudo mysql -e "ALTER USER 'shop_app'@'localhost' DISCARD OLD PASSWORD"
try 'App#Shop-2026'

Output

shop_app@localhost
shop_app@localhost
ERROR 1045 (28000): Access denied for user 'shop_app'@'localhost' (using password: YES)

try hides the client's warning about passwords on the command line. Retaining one's own password needs APPLICATION_PASSWORD_ADMIN. ana changes hers with ALTER USER USER() IDENTIFIED BY '...' REPLACE 'Ana#Pass-2026', since PASSWORD REQUIRE CURRENT asks for the old one.

MENU

CREATE and ALTER USER