MySQL 8.0用戶和角色管理

作者：王偉 2018-05-03 10:33:14

數據庫 MySQL

MySQL8.0新加了很多功能，其中在用戶管理中增加了角色的管理，默認的密碼加密方式也做了調整，由之前的sha1改為了sha2，同時加上5.7的禁用用戶和用戶過期的設置，這樣方面用戶的管理和權限的管理，也增加了用戶的安全性。

MySQL8.0中，MySQL庫中表的文件合并到數據根目錄中的mysql.ibd中（MySQL8.0 Innodb引擎重構）。

同時MySQL8.0可以使用SET PERSIST動態修改參數并保存在配置文件中（mysqld-auto.cnf，保存的格式為JSON串），這個是DBA同學的福音，不必擔心設置之后忘記保存在配置文件中，重啟之后會被還原的問題了。

查閱了MySQL8.0的官方文檔，通過官方的示例來查看新的管理方式。

1、MySQL用戶管理

1.1、驗證插件和密碼加密方式的變化

在MySQL 8.0中，caching_sha2_password是默認的身份驗證插件而不是之前版本的mysql_native_password，默認的密碼加密方式是sha2。

如果需要保持之前的驗證方式并保持之前版本的密碼加密方式需要在配置文件中修改，暫不支持動態修改，需要重啟生效：default_authentication_plugin = mysql_native_password。

將8.0已有的sha2密碼修改為sha1的模式：

ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'passowrd' PASSWORD EXPIRE NEVER; #修改加密規則為永不過期  
ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'password'; #更新一下用戶的密碼加密方式為之前版本的方式  
FLUSH PRIVILEGES; #刷新權限

1.2 用戶授權和修改密碼

MySQL8.0的用戶授權和之前有所區別，老版本的常用授權語句在8.0中會報錯：

MySQL8.0之前版本：

GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd' WITH GRANT OPTION;

MySQL8.0版本：

CREATE USER `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd';  
GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` WITH GRANT OPTION;

MySQL8.0中帶過期時間用戶的創建：

CREATE USER `wangwei`@`127.0.0.1` IDENTIFIED BY 'wangwei' PASSWORD EXPIRE INTERVAL 90 DAY;  
GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` WITH GRANT OPTION;

MySQL8.0修改用戶密碼：

1.2、密碼過期時間管理

要全局建立自動密碼到期策略，請使用default_password_lifetime系統變量。其默認值為0，禁用自動密碼過期。如果值default_password_lifetime正整數N，則表示允許的密碼生存期，以便密碼必須每天更改N。可以加在配置文件中：

1：要建立全局策略，密碼的使用期限大約為六個月，請在服務器my.cnf文件中使用以下行啟動服務器：

[mysqld]  
default_password_lifetime=180

2：要建立全局策略，以便密碼永不過期，請將其設置default_password_lifetime為0：

[mysqld]  
default_password_lifetime=0

這個參數是可以動態設置并保存的：

SET PERSIST default_password_lifetime = 180;  
SET PERSIST default_password_lifetime = 0;

創建和修改帶有密碼過期的用戶，帳戶特定的到期時間設置示例：

要求每90天更換密碼：

CREATE USER 'wangwei'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;  
ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

禁用密碼過期：

CREATE USER ' wangwei'@'localhost' PASSWORD EXPIRE NEVER;  
ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE NEVER;

遵循全局到期政策：

CREATE USER 'wangwei'@'localhost' PASSWORD EXPIRE DEFAULT;  
ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE DEFAULT;

1.3 MySQL用戶密碼重用策略設置

MySQL允許限制重復使用以前的密碼。可以根據密碼更改次數、已用時間或兩者來建立重用限制。帳戶的密碼歷史由過去分配的密碼組成。MySQL可以限制從此歷史記錄中選擇新密碼：

如果根據密碼更改次數限制帳戶，則無法從指定數量的最新密碼中選擇新密碼。例如，如果密碼更改的最小數量設置為3，則新密碼不能與任何最近的3個密碼相同。
如果帳戶因時間的限制而被限制，則無法從歷史記錄中的新密碼中選擇新密碼，該新密碼不會超過指定的天數。例如，如果密碼重用間隔設置為60，則新密碼不得在最近60天內選擇的密碼之間。

注意：空密碼不記錄在密碼歷史記錄中，并隨時可以重復使用。

要全局建立密碼重用策略，請使用password_history和password_reuse_interval系統變量。要在服務器啟動時指定變量值，請在服務器my.cnf文件中定義它們。

示例：

要禁止重復使用最近6個密碼或密碼超過365天的任何密碼，請將這些行放入您的服務器 my.cnf文件中：

[mysqld]  
password_history=6  
password_reuse_interval=365

要動態設置和保存配置，請使用如下所示的語句：

SET PERSIST password_history = 6;  
SET PERSIST password_reuse_interval = 365;

2、MySQL8.0的角色管理

MySQL角色是指定的權限集合。像用戶帳戶一樣，角色可以擁有授予和撤消的權限。可以授予用戶帳戶角色，授予該帳戶與每個角色相關的權限。用戶被授予角色權限，則該用戶擁有該角色的權限。

以下列表總結了MySQL提供的角色管理功能：

CREATE ROLE并 DROP ROLE角色創建和刪除；
GRANT并 REVOKE為用戶和角色分配和撤銷權限；
SHOW GRANTS 顯示用戶和角色的權限和角色分配；
SET DEFAULT ROLE 指定哪些帳戶角色默認處于活動狀態；
SET ROLE 更改當前會話中的活動角色。
CURRENT_ROLE()功能顯示當前會話中的活動角色。

2.1 創建角色并授予用戶角色權限

考慮如下幾種場景：

應用程序使用名為app_db的數據庫。

與應用程序相關聯，可以為創建和維護應用程序的開發人員以及管理員賬戶。

開發人員需要完全訪問數據庫。有的用戶只需要讀取權限，有的用戶需要讀取/寫入權限。

為清楚區分角色的權限，將角色創建為所需權限集的名稱。通過授權適當的角色，可以輕松地為用戶帳戶授予所需的權限。

要創建角色，請使用CREATE ROLE：

CREATE ROLE 'app_developer', 'app_read', 'app_write';

角色名稱與用戶帳戶名稱非常相似，由格式中的用戶部分和主機部分組成。主機部分，如果省略，則默認為%。用戶和主機部分可以不加引號，除非它們包含特殊字符。與帳戶名稱不同，角色名稱的用戶部分不能為空。為角色分配權限，使用與為用戶分配權限相同的語法執行：

GRANT ALL ON app_db.* TO 'app_developer';  
GRANT SELECT ON app_db.* TO 'app_read';  
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';CREATE ROLE 'app_developer', 'app_read', 'app_write';

現在假設您最初需要一個開發人員帳戶，兩個需要只讀訪問權的用戶以及一個需要讀取/寫入權限的用戶。使用CREATEUSER創建用戶：

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';  
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';  
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';  
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';

要為每個用戶分配其所需的權限，可以使用GRANT與剛才顯示的形式相同的語句，但這需要列舉每個用戶的個人權限。相反，使用GRANT允許授權角色而非權限的替代語法：

GRANT 'app_developer' TO 'dev1'@'localhost';  
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';  
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

結合角色所需的讀取和寫入權限，在GRANT中授權 rw_user1用戶讀取和寫入的角色。

在GRANT授權角色的語法和授權用戶的語法不同：有一個ON來區分角色和用戶的授權，有ON的為用戶授權，而沒有ON用來分配角色。

由于語法不同，因此不能在同一語句中混合分配用戶權限和角色。（允許為用戶分配權限和角色，但必須使用單獨的GRANT語句，每種語句的語法都要與授權的內容相匹配。）

2.2 檢查角色權限

要驗證分配給用戶的權限，使用 SHOW GRANTS。例如：

mysql> SHOW GRANTS FOR 'dev1'@'localhost';  
+-------------------------------------------------+  
| Grants for dev1@localhost                       |  
+-------------------------------------------------+  
| GRANT USAGE ON *.* TO `dev1`@`localhost`        |  
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |  
+-------------------------------------------------+

但是，它會顯示每個授予的角色，而不會將其顯示為角色所代表的權限。如果要顯示角色權限，添加一個 USING來顯示：

mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';  
+----------------------------------------------------------+  
| Grants for dev1@localhost                                |  
+----------------------------------------------------------+  
| GRANT USAGE ON *.* TO `dev1`@`localhost`                 |  
| GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |  
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |  
+----------------------------------------------------------+

同樣驗證其他類型的用戶：

mysql> SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';  
+--------------------------------------------------------+  
| Grants for read_user1@localhost                        |  
+--------------------------------------------------------+  
| GRANT USAGE ON *.* TO `read_user1`@`localhost`         |  
| GRANT SELECT ON `app_db`.* TO `read_user1`@`localhost` |  
| GRANT `app_read`@`%` TO `read_user1`@`localhost`       |  
+--------------------------------------------------------+  
mysql> SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';  
+------------------------------------------------------------------------------+  
| Grants for rw_user1@localhost                                                |  
+------------------------------------------------------------------------------+  
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                                 |  
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `rw_user1`@`localhost` |  
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost`               |  
+------------------------------------------------------------------------------+

2.3 撤消角色或角色權限

正如可以授權某個用戶的角色一樣，可以從帳戶中撤銷這些角色：

REVOKE role FROM user;

REVOKE可以用于角色修改角色權限。這不僅影響角色本身權限，還影響任何授予該角色的用戶權限。假設想臨時讓所有用戶只讀，使用REVOKE從該app_write角色中撤消修改權限：

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';

碰巧，某個角色完全沒有任何權限，正如可以看到的那樣SHOW GRANTS （這個語句可以和角色一起使用，而不僅僅是查詢用戶權限可用）：

mysql> SHOW GRANTS FOR 'app_write';  
+---------------------------------------+  
| Grants for app_write@%                |  
+---------------------------------------+  
| GRANT USAGE ON *.* TO `app_write`@`%` |  
+---------------------------------------+

從角色中撤銷權限會影響到該角色中任何用戶的權限，因此 rw_user1現在已經沒有表修改權限（INSERT， UPDATE，和 DELETE權限已經沒有了）：

mysql> SHOW GRANTS FOR 'rw_user1'@'localhost'  
       USING 'app_read', 'app_write';  
+----------------------------------------------------------------+  
| Grants for rw_user1@localhost                                  |  
+----------------------------------------------------------------+  
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                   |  
| GRANT SELECT ON `app_db`.* TO `rw_user1`@`localhost`           |  
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost` |  
+----------------------------------------------------------------+

實際上，rw_user1讀/寫用戶已成為只讀用戶。對于被授予app_write角色的任何其他用戶也會發生這種情況，說明修改使用角色而不必修改個人帳戶的權限。

要恢復角色的修改權限，只需重新授予它們即可：

GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

現在rw_user1再次具有修改權限，就像授權該app_write角色的其他任何帳戶一樣。

2.4 刪除角色

要刪除角色，請使用DROP ROLE：

DROP ROLE 'app_read', 'app_write';

刪除角色會從授權它的每個帳戶中撤消該角色。

2.5 角色和用戶在實際中的應用

假設遺留應用開發項目在MySQL中的角色出現之前開始，因此與該項目相關聯的所有用戶都是直接授予權限（而不是授予角色權限）。其中一個帳戶是最初被授予權限的開發者用戶，如下所示：

CREATE USER 'old_app_dev'@'localhost' IDENTIFIED BY 'old_app_devpass';  
GRANT ALL ON old_app.* TO 'old_app_dev'@'localhost';

如果此開發人員離開項目，則有必要將權限分配給其他用戶，或者項目參與人增多，則可能需要多個用戶。以下是解決該問題的一些方法：

不使用角色：更改帳戶密碼，以便原始開發人員不能使用它，并讓新的開發人員使用該帳戶：

ALTER USER 'old_app_dev'@'localhost' IDENTIFIED BY 'new_password';

使用角色：鎖定帳戶以防止任何人使用它來連接服務器：

ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;

然后將該帳戶視為角色。對于每個新開發項目的開發者，創建一個新帳戶并授予其原始開發者帳戶：

CREATE USER 'new_app_dev1'@'localhost' IDENTIFIED BY 'new_password';  
GRANT 'old_app_dev'@'localhost' TO 'new_app_dev1'@'localhost';

其效果是將原始開發者帳戶權限分配給新帳戶。

MySQL8.0的用戶和角色管理也越來越像Oracle了，8.0中有不少新的特性，變化還是很大的，需要DBA不斷的學習和測試，更新對MySQL新版的認知，更好地運維MySQL數據庫。未來MySQL數據庫自治和智能數據庫是必然發展趨勢，對DBA來說是解放，也是挑戰。

同時也非常感謝好友知名MySQL數據庫專家吳炳錫老師在百忙中抽空對本文進行校對。

責任編輯：龐桂玉來源：老葉茶館