| name | mariadb-alter-user |
| description | MariaDB-specific syntax and behavior for ALTER USER and SET PASSWORD — IF EXISTS warning-not-error semantics, IDENTIFIED BY vs IDENTIFIED BY PASSWORD vs IDENTIFIED VIA/WITH multi-plugin auth, PASSWORD EXPIRE variants and default_password_lifetime interaction, ACCOUNT LOCK/UNLOCK, resource limits, TLS/REQUIRE options, and SET PASSWORD's three literal forms (PASSWORD(), OLD_PASSWORD(), pre-hashed string — there is no cleartext-string form). Use when writing, generating, or reviewing statements that create/change account credentials, expire or lock accounts, or set passwords on MariaDB. |
ALTER USER and SET PASSWORD in MariaDB
Last updated: 2026-07-20
This skill covers the delta between what an LLM tends to write for account/password management and MariaDB's actual behavior. It assumes the agent already knows CREATE USER basics. Consolidates ALTER USER and SET PASSWORD into one lookup, since the two overlap heavily on password semantics.
Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.
What LLMs Often Miss
| If the agent writes / suggests… | …prefer the MariaDB form |
|---|
SET PASSWORD FOR 'u'@'h' = 'mynewpassword'; expecting a cleartext-password shortcut | There is no cleartext bare-string form. A bare string on the right of SET PASSWORD = is stored as-is as an already-hashed value (e.g. what PASSWORD() returns) — writing a plaintext string there sets the account's hash to that literal text, which will not match the real password on login. Use SET PASSWORD FOR 'u'@'h' = PASSWORD('mynewpassword'); for a plaintext input |
ALTER USER 'bob'@'%'; with no user, expecting "modify my own account" | ALTER USER always requires an explicit user (or CURRENT_USER()/CURRENT_USER) in the specification — there is no bare "no-user-means-me" shorthand. Write ALTER USER CURRENT_USER() IDENTIFIED BY '...' |
ALTER USER foo IDENTIFIED VIA ed25519 USING 'newhash' to add ed25519 alongside an existing plugin, expecting other auth methods to survive | ALTER USER ... IDENTIFIED VIA ... replaces the account's entire authentication method list. Any previously configured plugin not repeated in the new IDENTIFIED VIA ... OR ... clause is removed (since 10.4.13; earlier the removal was inconsistent — MDEV-21928) |
ALTER USER 'bob'@'%' PASSWORD EXPIRE INTERVAL 90; | Needs the DAY unit: PASSWORD EXPIRE INTERVAL 90 DAY — there is no bare-integer or other unit form |
| Assuming a locked account still finishes login and just gets restricted | ACCOUNT LOCK blocks the connection attempt itself with ER_ACCOUNT_HAS_BEEN_LOCKED ("Access denied, this account is locked") — it is checked before password validation, not a post-login restriction. Existing open connections are unaffected |
| Assuming an expired password blocks the connection outright | By default the client is allowed to connect (if it advertises CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS), but the session is put in "sandbox mode": every statement except SET PASSWORD fails with ER_MUST_CHANGE_PASSWORD ("You must SET PASSWORD before executing this statement") until the password is changed. Only if disconnect_on_expired_password = ON (and the client can't handle expired passwords) is the connection itself refused with ER_MUST_CHANGE_PASSWORD_LOGIN |
ALTER USER foo on a nonexistent user and expecting a hard failure to always block subsequent DDL | Without IF EXISTS, a nonexistent user is an error, but ALTER USER still applies to every account in the list that does exist and does not error — only one error is raised for the whole statement covering the accounts that failed. With IF EXISTS, a nonexistent user produces a warning, not an error, and the rest apply normally |
Assuming default_password_lifetime auto-expires passwords out of the box | Its default is 0 (disabled) — passwords never auto-expire unless an admin sets this global variable to a positive day count, or expiry is set per-account with PASSWORD EXPIRE INTERVAL n DAY |
ALTER USER — Option Surface
ALTER USER shares its option grammar with CREATE USER (see the mariadb-create-user skill for the full option catalog). The distinguishing pieces for ALTER USER:
ALTER USER [IF EXISTS] user_specification [, user_specification] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[lock_option] [password_option]
[password_option] [lock_option]
user_specification:
username [IDENTIFIED BY 'password'
|IDENTIFIED BY PASSWORD 'hash'
|IDENTIFIED {VIA|WITH} plugin [USING 'string' | USING PASSWORD('cleartext')]
[OR plugin ...] ]
IF EXISTS
ALTER USER IF EXISTS 'bob'@'%' PASSWORD EXPIRE;
Turns a "no such user" condition from a hard error into a warning for that account, letting the rest of a multi-user statement (or an idempotent provisioning script) proceed without erroring. Without IF EXISTS, MariaDB still applies the statement to every account that does exist — the error covers only the accounts that failed, not the whole batch — but the statement as a whole reports failure.
PASSWORD EXPIRE Options
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE;
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE DEFAULT;
- Bare
PASSWORD EXPIRE (no modifier) expires the password immediately — the next login enters sandbox mode.
default_password_lifetime is the server-wide policy (days; 0 = disabled, which is the default); per-account PASSWORD EXPIRE options override it for that account.- Sandbox-mode behavior on an expired password: see "What LLMs Often Miss" above — the account can usually still authenticate, but only
SET PASSWORD is permitted until the password is reset.
ACCOUNT LOCK / ACCOUNT UNLOCK
ALTER USER 'marijn'@'localhost' ACCOUNT LOCK;
ALTER USER 'marijn'@'localhost' ACCOUNT UNLOCK;
Blocks (or re-permits) new connections for that account. Locking does not kill sessions already open at the time of the lock. A locked account's login attempt fails with ER_ACCOUNT_HAS_BEEN_LOCKED, checked before credentials are evaluated.
SET PASSWORD
SET PASSWORD [FOR user] = PASSWORD('cleartext');
SET PASSWORD [FOR user] = OLD_PASSWORD('cleartext');
SET PASSWORD [FOR user] = 'already-hashed-value';
- No
FOR clause targets the current session's account. Any non-anonymous user can change their own password this way.
- With
FOR user, the caller needs the UPDATE privilege on the mysql database; user must match the User/Host values exactly as recorded in mysql.user/mysql.global_priv.
- The bare-string third form is not a cleartext shortcut — the exact text becomes the stored authentication hash. It exists so tooling can copy a hash from one account/server to another (e.g.
SELECT PASSWORD('x') output, or a dump), not so a human can type a plaintext password directly. Getting this wrong (typing a real plaintext password without PASSWORD()) silently creates an account nobody can log into with that password.
- Works for any plugin that stores its credential in
mysql.global_priv (mysql_native_password, mysql_old_password, ed25519). Plugins that authenticate by other means (unix_socket, named_pipe, gssapi, pam) reject SET PASSWORD outright with an explicit "ignored" error, since there's no password to store.
- Clearing a password:
SET PASSWORD FOR 'bob'@'localhost' = PASSWORD(''); — sets a blank password (the account can then connect with no password supplied). A blank password is not a wildcard that matches anything.
ALTER USER ... IDENTIFIED BY '...' and SET PASSWORD ... = PASSWORD('...') are functionally equivalent ways to change a password to a plaintext value; ALTER USER is the modern, more general statement and also accepts IF EXISTS, expiry, locking, and resource-limit clauses in the same statement.
See Also
mariadb-create-user — full option catalog (IDENTIFIED VIA/WITH, DEFAULT ROLE, resource limits, TLS) shared with ALTER USERmariadb-grant — privilege grants, which also accept an inline IDENTIFIED BY that can create the account- Canonical references on
mariadb.com/docs (consult only for edge cases not covered here):
