MySQL的SQL语句 - 数据库管理语句 - 账户管理语句 - ALTER USER语句（1）

数据库杂货铺 2021-04-12

451

ALTER USER 语句

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 Section 6.2.4, “Specifying Account Names”)


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
}
复制

ALTER USER 语句修改 MySQL 帐户。它允许修改现有帐户的身份验证、角色、SSL/TLS、资源限制和密码管理属性。它也可以用来锁定和解锁帐户。

在大多数情况下，ALTER USER 需要全局 CREATE USER 特权，或者mysql系统架构的 UPDATE 特权。例外情况如下：

● 任何使用非匿名帐户连接到服务器的客户端都可以更改该帐户的密码。（特别是，您可以更改自己的密码。）要查看服务器将您验证为哪个帐户，请调用 CURRENT_USER() 函数：

SELECT CURRENT_USER();
复制

● 对于 DEFAULT ROLE 语法，ALTER USER 需要以下权限：

■ 为其他用户设置默认角色需要全局 CREATE USER 权限或 mysql.default_roles 系统表的 UPDATE 权限。

■ 为自己设置默认角色不需要特别权限，只要想要的默认角色已被授予给您。

● 修改辅助密码的语句需要以下权限：

■ 对于应用于自己帐户的 ALTER USER 语句，使用 RETAIN CURRENT PASSWORD 或 DISCARD OLD PASSWORD 子句需要 APPLICATION_PASSWORD_ADMIN 权限。大多数用户只需要一个密码，需要此权限才能操作自己的辅助密码。

■ 如果允许一个帐户操作所有帐户的辅助密码，它需要 CREATE USER 权限而不是 APPLICATION_PASSWORD_ADMIN 权限。

启用 read_only 系统变量时，ALTER USER 还需要 CONNECTION_ADMIN 权限（或不推荐使用的 SUPER 权限）。

默认情况下，如果尝试修改不存在的用户，则会发生错误。如果使用 IF EXISTS 子句，则该语句将为每个不存在的命名用户生成一个警告，而不是一个错误。

下面分主题中从几个方面对 ALTER USER 语句进行描述：

ALTER USER 概述

对于每个受影响的帐户，ALTER USER 修改 mysql.user 系统表中相应的记录，以反映语句中指定的属性。未指定的属性保留其当前值。

帐户名的主机名部分（如果省略）默认为 '%'。也可以指定 CURRENT_USER 或 CURRENT_USER() 来引用与当前会话关联的帐户。

对于一种语法，可以使用 USER() 函数指定帐户：

ALTER USER USER() IDENTIFIED BY 'auth_string';
复制

此语法允许更改自己的密码，而不必指定帐户。

MySQL 8.0.21 及更高版本支持用户注释和用户属性。可以使用 ALTER USER 分别结合 COMMENT 和 ATTRIBUTE 选项来修改它们。不能在同一 ALTER USER 语句中同时指定这两个选项；尝试这样做会导致语法错误。

用户注释和用户属性以JSON对象形式存储在 INFORMATION_SCHEMA.USER_ATTRIBUTES 表中；用户注释作为 comment 键的值存储在此表的 ATTRIBUTE 列中，如本文后面所示。COMMENT 文本可以是任意用引号引用的文本，并替换现有的用户注释。ATTRIBUTE 值必须是 JSON 对象的有效字符串表示形式。这将与任何现有的用户属性合并，就像在现有的用户属性和新的用户属性上使用了 JSON_MERGE_PATCH() 函数一样；对于任何重复使用的键，新值将覆盖旧值，如下所示：

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+----------------+
| USER | HOST      | ATTRIBUTE      |
+------+-----------+----------------+
| bill | localhost | {"foo": "bar"} |
+------+-----------+----------------+
1 row in set (0.11 sec)


mysql> ALTER USER 'bill'@'localhost' ATTRIBUTE '{"baz": "faz", "foo": "moo"}';
Query OK, 0 rows affected (0.22 sec)


mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+------------------------------+
| USER | HOST      | ATTRIBUTE                    |
+------+-----------+------------------------------+
| bill | localhost | {"baz": "faz", "foo": "moo"} |
+------+-----------+------------------------------+
1 row in set (0.00 sec)
复制

要从用户属性中删除键及其值，请将键设置为JSON null（必须是小写和无引号的），如下所示：

mysql> ALTER USER 'bill'@'localhost' ATTRIBUTE '{"foo": null}';
Query OK, 0 rows affected (0.08 sec)


mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+----------------+
| USER | HOST      | ATTRIBUTE      |
+------+-----------+----------------+
| bill | localhost | {"baz": "faz"} |
+------+-----------+----------------+
1 row in set (0.00 sec)
复制

要将现有用户注释设置为空字符串，请使用 ALTER USER ... COMMENT ''。这将在 USER_ATTRIBUTES 表中留下一个空注释值；要完全删除用户注释，请使用 ALTER USER ... ATTRIBUTE ... 将列键的值设置为 JSON null（小写，不带引号）。以下 SQL 语句序列说明了这一点：

mysql> ALTER USER 'bill'@'localhost' COMMENT 'Something about Bill';
Query OK, 0 rows affected (0.06 sec)


mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+---------------------------------------------------+
| USER | HOST      | ATTRIBUTE                                         |
+------+-----------+---------------------------------------------------+
| bill | localhost | {"baz": "faz", "comment": "Something about Bill"} |
+------+-----------+---------------------------------------------------+
1 row in set (0.00 sec)


mysql> ALTER USER 'bill'@'localhost' COMMENT '';
Query OK, 0 rows affected (0.09 sec)


mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+-------------------------------+
| USER | HOST      | ATTRIBUTE                     |
+------+-----------+-------------------------------+
| bill | localhost | {"baz": "faz", "comment": ""} |
+------+-----------+-------------------------------+
1 row in set (0.00 sec)


mysql> ALTER USER 'bill'@'localhost' ATTRIBUTE '{"comment": null}';
Query OK, 0 rows affected (0.07 sec)


mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+----------------+
| USER | HOST      | ATTRIBUTE      |
+------+-----------+----------------+
| bill | localhost | {"baz": "faz"} |
+------+-----------+----------------+
1 row in set (0.00 sec)
复制

允许在 ALTER USER 语法中使用 auth_option 值跟随 user 值，auth_option 通过指定帐户身份验证插件、凭据（例如密码）来指示帐户的身份验证方式。每个 auth_option 值仅应用于紧跟在它前面的帐户。

根据用户规范，该语句可能包括 SSL/TLS、资源限制、密码管理和锁定属性的选项。所有这些选项都是语句的全局选项，并应用于语句中指定的所有帐户。

示例：更改帐户密码并使其过期。用户必须使用指定的密码进行连接，并在下一次连接时选择新密码：

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'new_password' PASSWORD EXPIRE;
复制

示例：修改帐户以使用 caching_sha2_password 验证插件和给定的密码。要求每180天选择一个新密码，并启用失败登录跟踪，这样连续三个不正确的密码会导致帐户临时锁定两天：

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'new_password'
  PASSWORD EXPIRE INTERVAL 180 DAY
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;
复制

示例：锁定或解锁帐户：

ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK;
ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;
复制

示例：要求帐户使用SSL连接，并设置每小时20个连接的限制：

ALTER USER 'jeffrey'@'localhost'
  REQUIRE SSL WITH MAX_CONNECTIONS_PER_HOUR 20;
复制

示例：更改多个帐户，指定特定帐户属性和一些全局属性：

ALTER USER
  'jeffrey'@'localhost'
    IDENTIFIED BY 'jeffrey_new_password',
  'jeanne'@'localhost',
  'josh'@'localhost'
    IDENTIFIED BY 'josh_new_password'
    REPLACE 'josh_current_password'
    RETAIN CURRENT PASSWORD
  REQUIRE SSL WITH MAX_USER_CONNECTIONS 2
  PASSWORD HISTORY 5;
复制

jeffrey 后面的 IDENTIFIED BY 值仅适用于其紧随的前一个帐户，因此它仅为 jeffrey 将密码更改为 'jeffrey_new_password'。对于 jeanne，没有指定此帐户的值（因此保持密码不变）。对于 josh，IDENTIFIED BY 建立新密码（'josh_new_password'），指定 REPLACE 来验证发出 ALTER USER 语句的用户是否知道当前密码（'josh_current_password'），并且当前密码也保留为帐户辅助密码。（因此，josh 可以使用主密码或辅助密码进行连接。）

其余属性全局应用于语句中命名的所有帐户，因此对于这两个帐户：

● 使用 SSL 需要连接。

● 该帐户最多可同时用于两个连接。

● 密码更改不能重复使用最新的五个密码中的任何一个。

示例：放弃 josh 的第二个密码，只保留其主密码：

ALTER USER 'josh'@'localhost' DISCARD OLD PASSWORD;


复制

在没有特定类型选项的情况下，账户在这方面保持不变。例如，如果没有锁定选项，则不会更改帐户的锁定状态。

ALTER USER 身份验证选项

帐户名后面可能跟有 auth_option 身份验证选项，该选项指定帐户验证插件、凭据或两者都指定。它还可以包括密码验证子句，指定要替换的帐户当前密码，以及管理帐户是否具有辅助密码的子句。

● auth_plugin 命名一个身份验证插件。插件名称可以是带引号的字符串文字或不带引号的名称。插件名称存储在 mysql.user 系统表。

对于未指定验证插件的 auth_option 语法，默认插件由 default_authentication_plugin 系统变量的值指定。

● 存储在内部的凭据存储在 mysql.user 系统表。'auth_string' 值或 RANDOM PASSWORD 分别以明文（未加密）字符串或与帐户关联的身份验证插件所期望的格式指定帐户凭据：

■ 语法使用 BY 'auth_string'，字符串是明文，并传递给身份验证插件进行可能的哈希处理。插件返回的结果存储在 mysql.user 表。插件可以使用指定的值，在这种情况下不会发生哈希运算。

■ 语法使用 BY RANDOM PASSWORD，MySQL 生成一个随机密码和明文，并将其传递给身份验证插件进行可能的哈希处理。插件返回的结果存储在 mysql.user 表。插件可以使用指定的值，在这种情况下不会发生哈希运算。

从 MySQL 8.0.18 开始，可以使用随机生成的密码。

■ 语法使用 AS 'auth_string'，假定该字符串已采用身份验证插件所需的格式，并按原样存储在 mysql.user 表。如果插件需要哈希值，则该值必须已经以适合该插件的格式进行哈希处理；否则，插件将无法使用该值，并且不能对客户端连接进行正确的身份验证。

从 MySQL 8.0.17 开始，哈希字符串可以是字符串文字或十六进制值。后者对应于启用 print_identified_with_as_hex 系统变量时，SHOW CREATE USER 为包含不可打印字符的密码哈希显示的值类型。

■ 如果身份验证插件不对身份验证字符串执行哈希运算，则 BY 'auth_string' 和 AS 'auth_string' 子句具有相同的效果：身份验证字符串按原样存储在 mysql.user 系统表。

● REPLACE 'current_auth_string' 子句执行密码验证，从 MySQL 8.0.13 开始提供。如果给出：

■ REPLACE 以明文（未加密）字符串指定将要替换的帐户当前密码。

■ 如果需要对密码进行更改以指定当前密码，则必须给出该子句，以验证尝试进行更改的用户是否确实知道当前密码。

■ 如果帐户的密码更改可能但不必指定当前密码，则该子句是可选的。

■ 如果给定的子句与当前密码不匹配，则语句将失败，即使该子句是可选的。

■ 只有在更改当前用户的帐户密码时才能指定 REPLACE。

● RETAIN CURRENT PASSWORD 和 DISCARD OLD PASSWORD 子句实现双密码功能，从 MySQL 8.0.14 开始提供。两者都是可选的，但如果给定，则具有以下效果：

■ RETAIN CURRENT PASSWORD 保留帐户当前密码作为其辅助密码，替换现有的辅助密码。新密码将成为主密码，客户端可以用该帐户使用主密码或辅助密码连接到服务器。（例外：如果新密码用 ALTER USER 语句指定为空，则即使给定了 RETAIN CURRENT PASSWORD，辅助密码也将变为空。）

■ 如果为主密码为空的帐户指定 RETAIN CURRENT PASSWORD，则语句将失败。

■ 如果帐户具有辅助密码，而您更改了其主密码而未指定 RETAIN CURRENT PASSWORD，则辅助密码将保持不变。

■ 如果更改分配给帐户的身份验证插件，则会丢弃辅助密码。如果更改身份验证插件并指定 RETAIN CURRENT PASSWORD，则语句将失败。

■ DISCARD OLD PASSWORD 丢弃辅助密码（如果存在）。该帐户只保留其主密码，客户端只能使用该帐户以主密码连接到服务器。

ALTER USER 允许以下这些 auth_option 语法：

● IDENTIFIED BY 'auth_string' [REPLACE 'current_auth_string'] [RETAIN CURRENT PASSWORD]

将帐户身份验证插件设置为默认插件，将明文 'auth_string' 值传递给插件以进行可能的哈希处理，并将结果存储在 mysql.user 系统表。

REPLACE 子句（如果给定）指定帐户当前密码，如本节前面所述。

RETAIN CURRENT PASSWORD 子句（如果给定）会将帐户当前密码保留为其辅助密码，如本节前面所述。

● IDENTIFIED BY RANDOM PASSWORD [REPLACE 'current_auth_string'] [RETAIN CURRENT PASSWORD]

将帐户身份验证插件设置为默认插件，生成随机密码，将明文密码值传递给插件以进行可能的哈希运算，并将结果存储在 mysql.user 系统表。该语句还在结果集中返回明文密码，以使执行该语句的用户或应用程序可以使用该密码。

REPLACE 子句（如果给定）指定帐户当前密码。

RETAIN CURRENT PASSWORD 子句（如果给定）会将帐户当前密码保留为其辅助密码。

● IDENTIFIED WITH auth_plugin

将帐户身份验证插件设置为 auth_plugin，将凭据清除为空字符串（凭据与旧的身份验证插件相关联，而不是与新的身份验证插件相关联），并将结果存储在 mysql.user 系统表。

此外，密码被标记为过期。用户下次连接时必须选择一个新的。

● IDENTIFIED WITH auth_plugin BY 'auth_string' [REPLACE 'current_auth_string'] [RETAIN CURRENT PASSWORD]

将帐户身份验证插件设置为 auth_plugin，将明文 'auth_string' 值传递给插件以进行可能的哈希运算，并将结果存储在 mysql.user 系统表。

REPLACE 子句（如果给定）指定帐户当前密码。

RETAIN CURRENT PASSWORD 子句（如果给定）会将帐户当前密码保留为其辅助密码。

● IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [REPLACE 'current_auth_string'] [RETAIN CURRENT PASSWORD]

将帐户身份验证插件设置为 auth_plugin，生成随机密码，将明文密码值传递给插件以进行可能的哈希运算，并将结果存储在 mysql.user 系统表。该语句在结果集中返回明文密码，以使执行该语句的用户或应用程序可以使用该密码。

REPLACE 子句（如果给定）指定帐户当前密码。

RETAIN CURRENT PASSWORD 子句（如果给定）会将帐户当前密码保留为其辅助密码。

● IDENTIFIED WITH auth_plugin AS 'auth_string'

将帐户身份验证插件设置为 auth_plugin，并按原样存储 'auth_string' 值到 mysql.user 表。如果插件需要进行哈希处理的字符串，则假定该字符串已经按照插件所需的格式进行了哈希处理。

● DISCARD OLD PASSWORD

丢弃帐户辅助密码（如果有）。

示例：将密码指定为明文；使用默认插件：

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'password';
复制

示例：指定身份验证插件以及明文密码值：

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH mysql_native_password
             BY 'password';
复制

示例：与前面的示例类似，但将当前密码指定为明文值，以满足进行更改的用户知道该密码的需要：

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH mysql_native_password
             BY 'password'
             REPLACE 'current_password';
复制

除非当前用户是 jeffrey，否则前面的语句将失败，因为 REPLACE 只允许更改当前用户的密码。

示例：建立新的主密码并保留现有密码作为辅助密码：

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'new_password'
  RETAIN CURRENT PASSWORD;
复制

示例：放弃辅助密码，只保留帐户的主密码：

ALTER USER 'jeffery'@'localhost' DISCARD OLD PASSWORD;
复制

示例：指定身份验证插件以及哈希密码值：

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH mysql_native_password
             AS '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
复制

官方网址：

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

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 - 数据库管理语句 - 账户管理语句 - ALTER USER语句（1）

评论

相关阅读

MySQL的SQL语句 - 数据库管理语句 - 账户管理语句 - ALTER USER语句 （1）

评论

相关阅读

MySQL的SQL语句 - 数据库管理语句 - 账户管理语句 - ALTER USER语句（1）