• IF EXISTS的含義
• user的指定
• auth_option選項
• IDENTIFIED BY 'auth_string'
• IDENTIFIED WITH auth_plugin
• IDENTIFIED WITH auth_plugin BY 'auth_string'
• IDENTIFIED WITH auth_plugin AS 'auth_string'
• tls_option選項
• resource_option選項
• password_option選項
• lock_option選項

修改用戶的語法

在MySQL中修改用戶的語法如下：

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] ...

ALTER USER [IF EXISTS]
    USER() IDENTIFIED BY 'auth_string'

user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

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
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

看了這么一大坨東西，感覺啥也沒有告訴我，一臉懵逼。下面我們一起來解讀一下具體的每一個選項的含義。

IF EXISTS的含義

對于這個大家應(yīng)該有所了解。

[IF EXISTS]

首先它是被中括號包裹起來的，表示是可選的不是必須的，也就是說在我們的alter user語句中，這個IF EXISTS可以不出現(xiàn)不是必須要寫的。但是為什么還要有這個呢，這個選項出現(xiàn)的目的是為了讓你在執(zhí)行一個alter user語句的時候，如果語句中指定的數(shù)據(jù)庫用戶不存在(或者你寫用戶名稱的是寫錯了)，而不至于出現(xiàn)一個錯誤的信息，如果指定了IF EXISTS這個詞，他就會以一個警告的信息提示你而不是直接給你一個錯誤信息。

更直白一下就是：如果你的alter user的語句中指定的用戶確實存在，那么就執(zhí)行你的語句，如果不存在呢，就不執(zhí)行你的語句，跳過這個alter user的指令。看示例：

mysql> alter user 'zhangsanfeng' identified by 'zhangsanfeng';
ERROR 1396 (HY000): Operation ALTER USER failed for 'zhangsanfeng'@'%'
mysql>
mysql> alter user if exists 'zhangsanfeng' identified by 'zhangsanfeng';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Note  | 3162 | User 'zhangsanfeng'@'%' does not exist. |
+-------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql>

在上面的例子中，第一個修改用戶的語句，沒有使用if exists關(guān)鍵詞直接修改了一個不存在的用戶的信息，結(jié)果直接出現(xiàn)了錯誤信息，接著我們使用了if exists關(guān)鍵詞，結(jié)果SQL語句執(zhí)行成功，只是出現(xiàn)了一個warning警告，然后我們通過show warnings命令查看警告信息的內(nèi)容，提示我們用戶'zhangsanfeng'@'%'不存在。這就是if exists關(guān)鍵詞的存在的意義。

注意：當(dāng)我們指定MySQL數(shù)據(jù)庫用戶的時候，如果我們不顯示的指定用戶的host網(wǎng)段，則默認(rèn)使用%來代替。所以，我們看到我們輸入的是'zhangsanfeng'但是提示的卻是'zhangsanfeng'@'%'。

user的指定

當(dāng)我們修改一個用戶的信息的時候，我們需要指定用戶的名稱。這個名稱，不僅僅使我們平時登錄數(shù)據(jù)庫的時候，輸入的用戶的名字，還有一個隱含的部分就是用戶登錄的host網(wǎng)段，這個網(wǎng)段表示只允許該用戶通過這個網(wǎng)段登錄，如果用戶不在這個定義的網(wǎng)段內(nèi)，即便是用戶名和密碼正確，也不能爭取的登錄。

下面的創(chuàng)建用戶是兩個不同的用戶。

create user 'zhangsanfeng'@'10.10.10.11' identified by '123456';
create user 'zhangsanfeng'@'192.168.1.100' identified by 'zhangsanfeng';

上面的兩個用戶中，分別表示如下：

• zhangsanfeng只允許在IP地址為10.10.10.11的主機(jī)上用密碼123456登錄。如果它在IP地址為192.168.1.100的主機(jī)上用密碼123456是不能登錄成功的。
• zhangsanfeng只允許在IP地址為192.168.1.100的主機(jī)上用密碼zhangsanfeng登錄。如果它在IP地址為10.10.10.11的主機(jī)上用密碼zhangsanfeng是不能登錄成功的。

當(dāng)然，我們可以使用模糊匹配的方式來限定用戶登錄的網(wǎng)段，比如我們創(chuàng)建下面第一個用戶表示只要這個用戶登錄的時候，使用的主機(jī)的IP地址是在10.10.0.0~10.10.255.255這個網(wǎng)段質(zhì)檢的任何一個IP地址都可以登錄。

create user 'zhangsanfeng'@'10.10.%' identified by '123456';

特殊的網(wǎng)段localhost。如果我們創(chuàng)建如下的用戶，則表示該用戶只能在MySQL數(shù)據(jù)庫所在的服務(wù)器本機(jī)上登錄。

create user 'zhangsanfeng'@'localhost' identified by '123456';

還有另外一個特殊的網(wǎng)段%。它表示用戶可以通過任何一個IP地址來登錄，不對用戶登錄的主機(jī)所在的網(wǎng)段做任何限制。如下用戶就是不對它登錄的主機(jī)IP地址做任何限制。

create user 'zhangsanfeng'@'%' identified by '123456';

auth_option選項

auth_option選項的含義表示指定用戶登錄數(shù)據(jù)庫的時候使用的驗證插件和密碼。它的選項有如下幾種，下面我們分別展開分析一下各個選項的使用場景和示例。

auth_option: {
    IDENTIFIED BY 'auth_string'  /*使用默認(rèn)的密碼認(rèn)證方式*/
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

IDENTIFIED BY 'auth_string'

如果在IDENTIFIED關(guān)鍵字后面沒有使用WITH關(guān)鍵字指定使用的密碼插件名稱，則會使用MySQL默認(rèn)的密碼插件。對于MySQL默認(rèn)的密碼插件是什么，可以使用下面的命令進(jìn)行查看：

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)

mysql>

通過上面的輸出，我們可以看出默認(rèn)的密碼插件采用的是mysql_native_password插件，參數(shù)
default_authentication_plugin的值是可以在MySQL的配置文件my.cnf中指定的，它可以有以下兩種取值：

• mysql_native_password：使用MySQL本地密碼策略，這個是默認(rèn)值。更多信息請參考：Native Pluggable Authentication
• sha256_password：使用SHA-256密碼策略。更多信息請參考：SHA-256 Pluggable Authentication

MySQL的配置文件my.cnf中配置默認(rèn)的密碼認(rèn)證插件的方式如下：

[mysqld]
default_authentication_plugin=mysql_native_password
# 或者下面的使用sha256_password作為默認(rèn)的密碼認(rèn)證插件
# default_authentication_plugin=sha256_password

IDENTIFIED WITH auth_plugin

如果我們要修改某一個用戶的密碼認(rèn)證插件，就可以使用到IDENTIFIED WITH auth_plugin這個語句了。如果一個用戶它的密碼認(rèn)證插件使用的是默認(rèn)的mysql_native_password，我們想把它的密碼認(rèn)證插件修改為sha256_password，此時我們就可以使用下面的命令來修改：

alter user xyz identified with sha256_password;

執(zhí)行完成上述命令后，xyz這個用戶的密碼就會設(shè)置為空，并更新它的密碼為已經(jīng)過期，同時更新了它的密碼認(rèn)證插件為sha256_password，結(jié)果如下所示：

mysql> select user, host, plugin, authentication_string, password_expired from mysql.user where user ='xyz';
+------+------+-----------------------+-------------------------------------------+------------------+
| user | host | plugin                | authentication_string                     | password_expired |
+------+------+-----------------------+-------------------------------------------+------------------+
| xyz  | %    | mysql_native_password | *39C549BDECFBA8AFC3CE6B948C9359A0ECE08DE2 | N                |
+------+------+-----------------------+-------------------------------------------+------------------+
1 row in set (0.03 sec)

mysql> alter user xyz identified with sha256_password;
Query OK, 0 rows affected (0.02 sec)

mysql> select user, host, plugin, authentication_string, password_expired from mysql.user where user ='xyz';
+------+------+-----------------+-----------------------+------------------+
| user | host | plugin          | authentication_string | password_expired |
+------+------+-----------------+-----------------------+------------------+
| xyz  | %    | sha256_password |                       | Y                |
+------+------+-----------------+-----------------------+------------------+
1 row in set (0.02 sec)

mysql>

更改用戶的密碼認(rèn)知方式之后，當(dāng)xyz再次嘗試登錄MySQL數(shù)據(jù)庫的時候，輸入空密碼登錄成功后，會要求其修改一下自己的密碼，然后才可以執(zhí)行其他SQL語句的操作，這個要求和我們剛安裝MySQL數(shù)據(jù)庫后，第一次使用root登錄的時候要求修改root的密碼是一樣的。下面是修改完成用戶xyz的密碼認(rèn)證插件之后，嘗試使用空密碼登錄后的操作示例：

?  Downloads mysql -uxyz -p -h10.2.1.7
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 45197703
Server version: 5.7.25-log

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> select version();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. # 這里提示需要修改完成密碼之后才可以執(zhí)行其他SQL語句命令。
mysql> alter user xyz identified by 'xyz';
Query OK, 0 rows affected (0.10 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.25-log |
+------------+
1 row in set (0.01 sec)

mysql> select user, host, plugin, authentication_string from mysql.user where user ='xyz';
+------+------+-----------------+---------------------------------------------------------------------+
| user | host | plugin          | authentication_string                                               |
+------+------+-----------------+---------------------------------------------------------------------+
| xyz  | %    | sha256_password | $5$Jmq<rS]t?F]~WUfR]$LVplZGG4HlLuHcoXEnXXFtBHARd8vkeWoLUu/uEN0ZC |
+------+------+-----------------+---------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql>

注意：當(dāng)我們嘗試使用sha256_password作為密碼認(rèn)證插件的時候，則必須使用SSL加密連接的方式連接到MySQL數(shù)據(jù)庫，也就是需要為MySQL服務(wù)器端配置OpenSSL證書之后才可以正常連接到MySQL數(shù)據(jù)庫。

IDENTIFIED WITH auth_plugin BY 'auth_string'

指定用戶的密碼認(rèn)證插件，并設(shè)置密碼。

當(dāng)我們想給用戶指定密碼的認(rèn)證插件，并且想為其設(shè)置密碼的時候，可以使用這個命令，示例如下：

alter user xyz identified with mysql_native_password by 'xyz';

這樣用戶xyz的密碼認(rèn)證方式修改為了mysql_native_password，并且修改它的密碼為xyz。需要我們主要的是by關(guān)鍵字后面跟的是密碼的明文，也就是我們嘗試登錄的時候，輸入的密碼的值。

IDENTIFIED WITH auth_plugin AS 'auth_string'

指定用戶的密碼認(rèn)證插件，并設(shè)置密碼。

當(dāng)我們想給用戶指定密碼的認(rèn)證插件，并且想為其設(shè)置密碼的時候，可以使用這個命令，示例如下：

alter user xyz identified with mysql_native_password as '*39C549BDECFBA8AFC3CE6B948C9359A0ECE08DE2';

這樣用戶xyz的密碼認(rèn)證方式修改為了mysql_native_password，并且修改它的密碼為xyz。需要我們主要的是as關(guān)鍵字后面跟的是加密后的密碼，而不是我們的明文的密碼。如果我們想知道得到一個加密后的密碼，則可以使用下面password()函數(shù)來得到加密后的密碼。

mysql> select password('xyz');
+-------------------------------------------+
| password('xyz')                           |
+-------------------------------------------+
| *39C549BDECFBA8AFC3CE6B948C9359A0ECE08DE2 |
+-------------------------------------------+
1 row in set, 1 warning (0.02 sec)

mysql>

tls_option選項

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

這里主要是指定用戶在連接到MySQL數(shù)據(jù)庫的時候，是否需要使用加密的方式，如果使用加密的方式，則需要啟用SSL加密協(xié)議，同時也需要對MySQL進(jìn)行證書的配置。

考慮到性能的問題，使用TLS加密的方式連接MySQL數(shù)據(jù)庫會對MySQL數(shù)據(jù)庫的性能有一定的影響。非必要情況下，一般不建議啟用TLS加密連接到數(shù)據(jù)庫，因為大家的MySQL數(shù)據(jù)庫一般都是針對內(nèi)網(wǎng)開放的。所以，啟用TLS加密協(xié)議連接沒有必要。

resource_option選項

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

resource_option選項中，主要是為每一個用戶設(shè)置它所能使用到資源，做資源的使用限制。示例如下：

mysql> alter user xyz with max_queries_per_hour 100; /*限制每小時最多發(fā)起100個查詢語句*/
Query OK, 0 rows affected (0.02 sec)

mysql> alter user xyz with max_updates_per_hour 50; /*限制每小時最多發(fā)起50個更新語句*/
Query OK, 0 rows affected (0.01 sec)

mysql> alter user xyz with max_connections_per_hour 70; /*限制每小時最多建立70個連接*/
Query OK, 0 rows affected (0.01 sec)

mysql> alter user xyz with max_user_connections 80; /*限制用戶最多同時建立80個連接，超過80個同時在線的連接后，在請求建立新連接將會失敗。*/
Query OK, 0 rows affected (0.01 sec)

mysql>

上面四個參數(shù)的值如果為0，則表示對應(yīng)的資源限制不做任何限制。

password_option選項

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

password_option選項用來配置用戶的密碼，指定其密碼的效期。下面看幾個示例：

• 設(shè)置用戶xyz的密碼立即過期：

alter user 'xyz' password expire; /*設(shè)置密碼馬上過期*/

• 設(shè)置用戶xyz的密碼有效期為系統(tǒng)默認(rèn)有效期。

alter user 'xyz' password expire default; /*設(shè)置密碼過期時間為MySQL系統(tǒng)默認(rèn)的時間*/

對于MySQL系統(tǒng)默認(rèn)的有效期是多少，可以通過如下命令查看，下面的值為0表示密碼有效期為永久有效。

mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+
1 row in set (0.02 sec)

mysql>

• 設(shè)置用戶xyz的密碼有效期為永不過期。

alter user 'xyz' password expire never; /*設(shè)置密碼永不過期*/

• 設(shè)置用戶xyz的密碼有效期為90天。

alter user 'xyz' password expire interval 90 day; /*設(shè)置密碼有效期為90天*/

lock_option選項

lock_option選擇是用來鎖定和解鎖用戶的。示例如下：

• 鎖定用戶xyz的賬號

alter user 'xyz' account lock;

• 解鎖用戶xyz的賬號

alter user 'xyz' account unlock;