MySQL 8.0使用者和角色管理

標籤：串連伺服器   關聯   images   撤銷   DEV1   發展趨勢   調整   建立   形式   

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修改使用者密碼：
ALTER USER ‘wangwei‘@‘127.0.0.1‘ IDENTIFIED BY ‘wangwei‘;

1.3、密碼到期時間管理

要全域建立自動密碼到期策略，請使用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.4、MySQL使用者密碼重用原則設定

MySQL允許限制重複使用以前的密碼。可以根據密碼更改次數、經過時間或兩者來建立重用限制。帳戶的密碼曆史由過去分配的密碼組成。MySQL可以限制從此記錄中選擇新密碼：
1：如果根據密碼更改次數限制帳戶，則無法從指定數量的最新密碼中選擇新密碼。例如，如果密碼更改的最小數量設定為3，則新密碼不能與任何最近的3個密碼相同。
2：如果帳戶因時間的限制而被限制，則無法從記錄中的新密碼中選擇新密碼，該新密碼不會超過指定的天數。例如，如果密碼重用間隔設定為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‘;
現在假設您最初需要一個開發人員帳戶，兩個需要唯讀訪問權的使用者以及一個需要讀取/寫入許可權的使用者。使用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 [email protected] |
+-------------------------------------------------+
| GRANT USAGE ON . TO dev1@localhost |
| GRANT app_developer@% TO dev1@localhost |
+-------------------------------------------------+
?但是，它會顯示每個授予的角色，而不會將其顯示為角色所代表的許可權。如果要顯示角色許可權，添加一個 USING來顯示：
mysql> SHOW GRANTS FOR ‘dev1‘@‘localhost‘ USING ‘app_developer‘;
+----------------------------------------------------------+
| Grants for [email protected] |
+----------------------------------------------------------+
| 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 [email protected] |
+--------------------------------------------------------+
| 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 [email protected] |
+------------------------------------------------------------------------------+
| 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 [email protected]% |
+---------------------------------------+
| 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 [email protected] |
+----------------------------------------------------------------+
| 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資料庫專家吳炳錫老師在百忙中抽空對本文進行校對。

MySQL 8.0使用者和角色管理