top of page
CerebroSQL

MySQL: 

ALTER USER

Syntax:
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 DEFAULT ROLE
{NONE | ALL | role [, role ] ...}

user:
(see )

auth_option: {
IDENTIFIED BY 'auth_string'
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
| IDENTIFIED BY RANDOM PASSWORD
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
| IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
| IDENTIFIED WITH auth_plugin AS 'auth_string'
| DISCARD OLD PASSWORD
}

user_func_auth_option: {
IDENTIFIED BY 'auth_string'
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
| DISCARD OLD PASSWORD
}

tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}

resource_option: {
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}

password_option: {
PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
| PASSWORD HISTORY {DEFAULT | N}
| PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
| PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
| FAILED_LOGIN_ATTEMPTS N
| PASSWORD_LOCK_TIME {N | UNBOUNDED}
}

lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}

The ALTER USER statement modifies MySQL accounts. It enables
authentication, role, SSL/TLS, resource-limit, and password-management
properties to be modified for existing accounts. It can also be used to
lock and unlock accounts.

In most cases, ALTER USER requires the global CREATE USER privilege, or
the UPDATE privilege for the mysql system schema. The exceptions are:

o Any client who connects to the server using a nonanonymous account
can change the password for that account. (In particular, you can
change your own password.) To see which account the server
authenticated you as, invoke the CURRENT_USER() function:

SELECT CURRENT_USER();

o For DEFAULT ROLE syntax, ALTER USER requires these privileges:

o Setting the default roles for another user requires the global
CREATE USER privilege, or the UPDATE privilege for the
mysql.default_roles system table.

o Setting the default roles for yourself requires no special
privileges, as long as the roles you want as the default have been
granted to you.

o Statements that modify secondary passwords require these privileges:

o The APPLICATION_PASSWORD_ADMIN privilege is required to use the
RETAIN CURRENT PASSWORD or DISCARD OLD PASSWORD clause for ALTER
USER statements that apply to your own account. The privilege is
required to manipulate your own secondary password because most
users require only one password.

o If an account is to be permitted to manipulate secondary passwords
for all accounts, it requires the CREATE USER privilege rather than
APPLICATION_PASSWORD_ADMIN.

When the read_only system variable is enabled, ALTER USER additionally
requires the CONNECTION_ADMIN privilege (or the deprecated SUPER
privilege).

By default, an error occurs if you try to modify a user that does not
exist. If the IF EXISTS clause is given, the statement produces a
warning for each named user that does not exist, rather than an error.

URL: https://dev.mysql.com/doc/refman/8.0/en/alter-user.html

Example

bottom of page