mariadb-alter-user

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

SKILL.md

GitHub

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() (or CURRENT_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 for mysql_native_password / mysql_old_password.
  • IDENTIFIED BY PASSWORD 'hash' — the value is stored verbatim as an already-computed hash (e.g. the output of PASSWORD('...')); no re-hashing occurs.
  • IDENTIFIED VIA|WITH plugin — sets/replaces the account's authentication method(s). Multiple methods can be chained with OR (since 10.4) to support a migration window (e.g. old + new plugin side by side). Re-running ALTER USER ... IDENTIFIED VIA drops 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/CIPHER all imply SSL. SSL/X509 cannot combine with other TLS options; ISSUER/SUBJECT/CIPHER can combine with each other.
  • WITH resource 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. 0 means 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_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');       -- 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 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

<sub>This page is: Copyright © 2026 MariaDB. All rights reserved.</sub>