Users

MySQL accounts are managed with five statements: CREATE USER, SET PASSWORD, ALTER USER, RENAME USER, and DROP USER. Every account is identified by a user name and a host name ('user'@'host'); the host part can be omitted, in which case it defaults to the wildcard host '%'. Privileges are attached to accounts separately with GRANT, and accounts can be grouped under roles.

5.1.1. CREATE USER

Creates one or more new accounts, optionally in the same statement assigning authentication credentials, default roles, TLS requirements, resource limits, a password policy, and the account's initial lock state.

CREATE USER [IF NOT EXISTS] user [auth_option] [, user [auth_option]] ...
  [DEFAULT ROLE role [, role] ...]
  [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
  [WITH resource_option [resource_option] ...]
  [password_option | lock_option] ...
  [COMMENT 'comment_string' | ATTRIBUTE 'json_object'];

Creates each listed user. If a user value omits the host name, the wildcard host '%' is used.

auth_option controls how the account authenticates, and MySQL 8 lets it chain up to three factors for multi-factor authentication:

IDENTIFIED BY 'auth_string' — sets a password using the account's default authentication plugin.
IDENTIFIED BY RANDOM PASSWORD — the server generates and returns a random password.
IDENTIFIED WITH auth_plugin — selects an authentication plugin (for example caching_sha2_password) without assigning a password.
IDENTIFIED WITH auth_plugin BY 'auth_string' / BY RANDOM PASSWORD / AS 'auth_string' — selects a plugin and supplies credentials for it; AS passes a pre-hashed string instead of a plain-text password.
AND 2fa_auth_option / AND 3fa_auth_option — append a second and third authentication factor, each using the same set of clauses, to enable multi-factor authentication (MFA).

If IDENTIFIED WITH is used alone, the server does not assign a password. If IDENTIFIED BY is used, the server relies on the account's implicit, default authentication plugin, controlled by the default_authentication_plugin system variable. 'auth_string' is a quoted string passed to the plugin; use PASSWORD instead of BY to supply an already-hashed value rather than plain text.

tls_option (following REQUIRE) restricts how the account may connect: SSL, X509, CIPHER 'cipher', ISSUER 'issuer', and SUBJECT 'subject', optionally combined with AND.

resource_option (following WITH) caps server resource usage per account: MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, MAX_CONNECTIONS_PER_HOUR count, and MAX_USER_CONNECTIONS count.

password_option sets the password policy for the account:

PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY] — controls when the password expires.
PASSWORD HISTORY {DEFAULT | N} — how many previous passwords are remembered to prevent reuse.
PASSWORD REUSE INTERVAL {DEFAULT | N DAY} — minimum age before a password can be reused.
PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL] — whether changing the password requires supplying the current one.
FAILED_LOGIN_ATTEMPTS N and PASSWORD_LOCK_TIME {N | UNBOUNDED} — lock the account temporarily after N consecutive failed logins.

lock_option is ACCOUNT LOCK or ACCOUNT UNLOCK; see Account Locking below.

User comments and attributes are stored together internally as a single JSON object, with the comment text stored under the comment key. Both can be read back from the ATTRIBUTE column of the Information Schema USER_ATTRIBUTES table:

CREATE USER 'mary'@'localhost' COMMENT 'This is Mary Smith''s account';
ALTER USER 'mary'@'localhost' ATTRIBUTE '{"fname":"Mary", "lname":"Smith"}';
ALTER USER 'mary'@'localhost' ATTRIBUTE '{"email":"mary.smith@example.com"}';
SELECT USER, HOST, ATTRIBUTE->>"$.fname" AS 'First Name',
  ATTRIBUTE->>"$.lname" AS 'Last Name',
  ATTRIBUTE->>"$.email" AS 'Email',
  ATTRIBUTE->>"$.comment" AS 'Comment'
FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
WHERE USER='mary' AND HOST='localhost';

Attaches a comment and two custom attributes to the account, then reads all of them back with JSON path expressions.

CREATE USER 'mary'@'localhost' COMMENT 'This is Mary Smith''s account';
ALTER USER 'mary'@'localhost' ATTRIBUTE '{"fname":"Mary", "lname":"Smith"}';
ALTER USER 'mary'@'localhost' ATTRIBUTE '{"email":"mary.smith@example.com"}';

SELECT USER, HOST, ATTRIBUTE->>"$.fname" AS 'First Name',
  ATTRIBUTE->>"$.lname" AS 'Last Name',
  ATTRIBUTE->>"$.email" AS 'Email',
  ATTRIBUTE->>"$.comment" AS 'Comment'
FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
WHERE USER='mary' AND HOST='localhost';

CREATE USER 'mary'@'localhost' COMMENT 'This is Mary Smith''s account';
ALTER USER 'mary'@'localhost' ATTRIBUTE '{"fname":"Mary", "lname":"Smith"}';
ALTER USER 'mary'@'localhost' ATTRIBUTE '{"email":"mary.smith@example.com"}';

SELECT USER, HOST, ATTRIBUTE->>"$.fname" AS 'First Name',
  ATTRIBUTE->>"$.lname" AS 'Last Name',
  ATTRIBUTE->>"$.email" AS 'Email',
  ATTRIBUTE->>"$.comment" AS 'Comment'
FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
WHERE USER='mary' AND HOST='localhost';

USER	HOST	First Name	Last Name	Email	Comment
mary	localhost	Mary	Smith	mary.smith@example.com	This is Mary Smith's account

Multi-factor authentication chains additional IDENTIFIED WITH ... AND IDENTIFIED WITH ... clauses, for example combining a password, an LDAP identity, and a FIDO device. MySQL 9.1 adds the authentication_webauthn plugin as another concrete choice for these clauses: it authenticates against a FIDO2/WebAuthn security key or a platform authenticator such as Windows Hello, and can be chained as an additional factor the same way as authentication_fido above.

CREATE USER 'u2'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'sha2_password'
AND IDENTIFIED WITH authentication_webauthn;

u2 authenticates with a password as the first factor and a WebAuthn security key or platform authenticator as the second. As with other factor-based plugins that require it, the WebAuthn credential itself is registered separately using the INITIATE REGISTRATION / FINISH REGISTRATION handshake described under ALTER USER below.

Resource limits and password expiration can be set in the same statement:

CREATE USER 'jeffrey'@'localhost' REQUIRE SSL;
CREATE USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY 6;
CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;

REQUIRE SSL forces encrypted connections; WITH caps hourly query/update volume; the PASSWORD clauses control expiration, history depth, and whether changing the password requires the current one.

5.1.2. SET PASSWORD

Sets the password for an account, or for the current session's user if none is named.

SET PASSWORD [FOR user] {= 'auth_string' | TO RANDOM}
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD];

If FOR user is omitted, the current user's password is set. REPLACE supplies the current password when one is required to change it; RETAIN CURRENT PASSWORD keeps the old password valid alongside the new one during a rollover window.

SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string';
SET PASSWORD = 'auth_string';

The first form sets jeffrey's password explicitly; the second changes the password of the currently connected user.

A password hash can also be assigned directly at account-creation time, bypassing plain text, using the legacy IDENTIFIED BY PASSWORD form with a value already produced by PASSWORD():

CREATE USER 'ali'@'localhost' IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';

Creates the account with a pre-computed password hash instead of a plain-text string.

5.1.3. ALTER USER

Modifies an existing account's authentication, TLS requirements, resource limits, password policy, lock state, default roles, or multi-factor authentication factors.

ALTER USER [IF EXISTS] user [auth_option] [, user [auth_option]] ...
  [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
  [WITH resource_option [resource_option] ...]
  [password_option | lock_option] ...
  [COMMENT 'comment_string' | ATTRIBUTE 'json_object'];

ALTER USER [IF EXISTS] USER() user_func_auth_option;

ALTER USER [IF EXISTS] user [registration_option];

ALTER USER [IF EXISTS] user DEFAULT ROLE {NONE | ALL | role [, role] ...};

The USER() form changes the currently connected account's own password. DEFAULT ROLE changes which granted roles are active automatically at login; see Roles.

Beyond the auth_option variants shared with CREATE USER, ALTER USER adds clauses for managing additional authentication factors:

DISCARD OLD PASSWORD — drops the password retained by RETAIN CURRENT PASSWORD.
ADD factor factor_auth_option / MODIFY factor factor_auth_option / DROP factor — add, change, or remove the 2nd or 3rd authentication factor (factor is 2 FACTOR or 3 FACTOR).
factor INITIATE REGISTRATION, factor FINISH REGISTRATION SET CHALLENGE_RESPONSE AS 'auth_string', and factor UNREGISTER — drive the registration handshake for factor-based authentication methods that require it (such as FIDO devices).

Password expiration is controlled the same way as in CREATE USER:

PASSWORD EXPIRE expires the password immediately.
PASSWORD EXPIRE DEFAULT expires it after the number of days set by the default_password_lifetime system variable (default: 360); a value of 0 disables automatic expiration.
PASSWORD EXPIRE NEVER disables automatic expiration for the account.
PASSWORD EXPIRE INTERVAL N DAY expires the password after N days.

Once a password has expired, the account is restricted from most operations until it is changed.

5.1.4. RENAME USER

Renames one or more existing accounts.

RENAME USER old_user TO new_user [, old_user TO new_user] ...;

Changes an account's user name, host name, or both.

RENAME USER 'jane'@'localhost' TO 'jean'@'localhost';

Renames jane to jean while keeping the same host.

5.1.5. DROP USER

Removes one or more accounts and revokes their privileges.

DROP USER user [, user] ...;

Deletes each listed account.

5.1.6. Account Locking

MySQL supports locking and unlocking accounts with the ACCOUNT LOCK and ACCOUNT UNLOCK clauses, available on both CREATE USER and ALTER USER:

On CREATE USER, these clauses set the account's initial lock state. If neither is given, the account is created unlocked. If the validate_password component is enabled, an account cannot be created without a password even while locked.
On ALTER USER, these clauses change the lock state of an existing account. ALTER USER ... UNLOCK also clears a temporary lock caused by too many failed login attempts (see FAILED_LOGIN_ATTEMPTS / PASSWORD_LOCK_TIME above).

The lock state is recorded in the account_locked column of the mysql.user system table, and SHOW CREATE USER reports whether an account is locked. A connection attempt against a locked account fails: the server increments the Locked_connects status variable, returns an ER_ACCOUNT_HAS_BEEN_LOCKED error, and writes an entry to the error log. Locking an account does not prevent it from being used through a proxy user, nor does it affect stored routines or views whose DEFINER names the locked account — only direct connection attempts are blocked.

-- Multi-factor authentication: password + LDAP identity + FIDO device
CREATE USER 'u1'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'sha2_password'
  AND IDENTIFIED WITH authentication_ldap_sasl
      AS 'uid=u1_ldap,ou=People,dc=example,dc=com'
  AND IDENTIFIED WITH authentication_fido;

-- Multi-factor authentication: password + WebAuthn security key (MySQL 9.1+)
CREATE USER 'u2'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'sha2_password'
  AND IDENTIFIED WITH authentication_webauthn;

-- TLS requirement and resource limits
CREATE USER 'jeffrey'@'localhost' REQUIRE SSL;
CREATE USER 'jeffrey'@'localhost'
  WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;

-- Password policy
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD HISTORY 6;
ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;

-- Change a password
SET PASSWORD FOR 'jeffrey'@'localhost' = 'new_auth_string';
SET PASSWORD = 'new_auth_string';

-- Assign a password hash directly instead of plain text
CREATE USER 'ali'@'localhost' IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';

-- Lock and unlock an account
ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK;
ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;

-- Rename and finally remove an account
RENAME USER 'jane'@'localhost' TO 'jean'@'localhost';
DROP USER 'jean'@'localhost';

-- Multi-factor authentication: password + LDAP identity + FIDO device
CREATE USER 'u1'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'sha2_password'
  AND IDENTIFIED WITH authentication_ldap_sasl
      AS 'uid=u1_ldap,ou=People,dc=example,dc=com'
  AND IDENTIFIED WITH authentication_fido;

-- Multi-factor authentication: password + WebAuthn security key (MySQL 9.1+)
CREATE USER 'u2'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'sha2_password'
  AND IDENTIFIED WITH authentication_webauthn;

-- TLS requirement and resource limits
CREATE USER 'jeffrey'@'localhost' REQUIRE SSL;
CREATE USER 'jeffrey'@'localhost'
  WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;

-- Password policy
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD HISTORY 6;
ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;

-- Change a password
SET PASSWORD FOR 'jeffrey'@'localhost' = 'new_auth_string';
SET PASSWORD = 'new_auth_string';

-- Assign a password hash directly instead of plain text
CREATE USER 'ali'@'localhost' IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';

-- Lock and unlock an account
ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK;
ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;

-- Rename and finally remove an account
RENAME USER 'jane'@'localhost' TO 'jean'@'localhost';
DROP USER 'jean'@'localhost';

Data Control Language	<<<	Data Control Language	>>>	Privileges
last update: 17 Aug 2026

MENU