mariadb-corporation 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]
-- or, since 10.5.8/10.4.7:
[password_option] [lock_option] -- either order is accepted
user_specification:
username [IDENTIFIED BY 'password'
|IDENTIFIED BY PASSWORD 'hash'
|IDENTIFIED {VIA|WITH} plugin [USING 'string' | USING PASSWORD('cleartext')]
[OR plugin ...] ]
- Modifying the current session's own account: there is no implicit "no user given" form. Use
CURRENT_USER()(orCURRENT_USER) explicitly:ALTER USER CURRENT_USER() IDENTIFIED BY 'newpass'; IDENTIFIED BY 'password'— cleartext input, hashed by the account's authentication plugin before storage. Only meaningful formysql_native_password/mysql_old_password.IDENTIFIED BY PASSWORD 'hash'— the value is stored verbatim as an already-computed hash (e.g. the output ofPASSWORD('...')); no re-hashing occurs.IDENTIFIED VIA|WITH plugin— sets/replaces the account's authentication method(s). Multiple methods can be chained withOR(since 10.4) to support a migration window (e.g. old + new plugin side by side). Re-runningALTER USER ... IDENTIFIED VIAdrops any method not re-listed — this is a full replacement, not a merge.REQUIRE— TLS/X.509 constraints (SSL,X509,CIPHER,ISSUER,SUBJECT);X509/ISSUER/SUBJECT/CIPHERall implySSL.SSL/X509cannot combine with other TLS options;ISSUER/SUBJECT/CIPHERcan combine with each other.WITHresource limits —MAX_QUERIES_PER_HOUR,MAX_UPDATES_PER_HOUR,MAX_CONNECTIONS_PER_HOUR,MAX_USER_CONNECTIONS,MAX_STATEMENT_TIME. Tracked per'user'@'host'account, not per user name.0means unlimited for that resource.
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; -- expire immediately
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY; -- expire in N days
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE NEVER; -- opt out of auto-expiry
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE DEFAULT; -- fall back to default_password_lifetime
- Bare
PASSWORD EXPIRE(no modifier) expires the password immediately — the next login enters sandbox mode. default_password_lifetimeis the server-wide policy (days;0= disabled, which is the default); per-accountPASSWORD EXPIREoptions 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 PASSWORDis 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'); -- hash a plaintext input
SET PASSWORD [FOR user] = OLD_PASSWORD('cleartext'); -- pre-4.1 hash format; only for very old clients
SET PASSWORD [FOR user] = 'already-hashed-value'; -- stored as-is, NO re-hashing
- No
FORclause targets the current session's account. Any non-anonymous user can change their own password this way. - With
FOR user, the caller needs theUPDATEprivilege on themysqldatabase;usermust match theUser/Hostvalues exactly as recorded inmysql.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 withoutPASSWORD()) 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) rejectSET PASSWORDoutright 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 '...'andSET PASSWORD ... = PASSWORD('...')are functionally equivalent ways to change a password to a plaintext value;ALTER USERis the modern, more general statement and also acceptsIF 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 withALTER USERmariadb-grant— privilege grants, which also accept an inlineIDENTIFIED BYthat can create the account- Canonical references on
mariadb.com/docs(consult only for edge cases not covered here):- https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/alter-user
- https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/set-password
- https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/create-user
This page is: Copyright © 2026 MariaDB. All rights reserved.