mysql8.0的角色管理

歡迎來到藍隊云技術小課堂，每天分享一個技術小知識。

1. 角色的理解

角色是在MySQL8.0中引入的新功能。在MySQL中，角色是權限的集合，可以為角色添加或移除權限。用戶可以被賦予角色，同時也被授予角色包含的權限。對角色進行操作需要較高的權限,并且像用戶賬戶一樣，角色可以擁有授予和撤銷的權限。

引入角色的目的是方便管理擁有相同權限的用戶。恰當的權限設定，可以確保數據的安全性，這是至關重要的。

2. 創建角色

在實際應用中，為了安全性，需要給用戶授予權限, 當用戶數量較多時，為了避兔單獨給每一個用戶授予多個權限，可以先將權限集合放入角色中，再賦予用戶相應的角色。

創建角色使用 CREATE ROLE 語句，語法如下：

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...;

角色名稱的命名規則和用戶名類似。如果host_name省略，默認為%，role_name不可省略，不可為空。

示例：我們現在需要創建一個經理的角色，就可以用下面的代碼：

CREATE ROLE 'manager'@'localhost';

這里創建了一個角色，角色名稱是"manager",角色可以登錄的主機是"localhost"，意思是只能從數據厙服務器運行的這臺計算機登錄這個賬號。你也可以不寫主機名，直接創建角色 "manager"。

CREATE ROLE 'manager';

如果不寫主機名MySQL默認是通配符%，意思是這個賬號可以從任何一臺主機上登錄數據庫。

同樣道理，如果我們要創建庫管的角色，就可以用下面的代碼

CREATE ROLE 'stocker';

還可以通過如下命令，一次性創建3個角色：

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

3. 給角色賦予權限

創建角色之后，默認這個角色是沒有任何權限的，我們需要給角色授權。給角色授權的語法結構是：

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

上述語句中privileges代表權限的名稱，多個權限以逗號隔開。可使用SHOW語句查詢權限名稱，圖中列出了部分權限列表。

SHOW PRIVILEGES\\\\G

示例1: 我們現在想給經理角色授予商品信息表、盤點表和應付賬款表的只讀權限，就可以用下面的代碼來實現：

GRANT SELECT ON demo.settlement TO 'manager';

GRANT SELECT ON demo.goodsmaster TO 'manager';

GRANT SELECT ON demo.invcount TO 'manager';

如果我們需要賦予庫管角色盤點表的增刪改權限、商品信息表的只讀權限，對應付賬款表沒有權限，就可以這樣：

GRANT SELECT,INSERT,DELETE,UPDATE ON demo.invcount TO 'stocker';

GRANT SELECT ON demo.goodsmaster TO 'stocker';

示例2：

GRANT ALL PRIVILEGES ON app_db.* TO 'app_developer'; -- 給app_db數據庫中的所有表的所有權限

GRANT SELECT ON app_db.* TO 'app_read'; -- 給app_db數據庫中的所有表的查詢權限

GRANT INSERT,DELETE,UPDATE  ON app_db.* TO 'app_write'; -- 給app_db數據庫中的所有表的修改權限

示例3 創建三個角色，分別擁有全部權限、查詢權限和讀寫權限, 步驟如下所示·

(1) 使用如下sql，創建三個角色, 角色名為school_admin, school_read, school_write

CREATE ROLE 'school_admin','school_read','school_write';

(2) 給每個角色授予對應的權限，school_admin可以對數據庫shool中的所有表進行任何操作, school_read只能對數據厙中的表進行查詢, school_write可以對數據庫shool 中的表進行讀與操作，SQ語句如下。

GRANT ALL PRIVILEGES ON school.* TO 'school_admin'; 

GRANT SELECT ON school.* TO 'school_read'; 

GRANT SELECT,INSERT,DELETE,UPDATE  ON school.* TO 'school_write';

4. 查看角色的權限

賦予角色權限之后，我們可以通過 SHOW GRANTS語句，來查看權限是否創建成功了

mysql> SHOW GRANTS FOR 'manager'@'%';
+-------------------------------------------------------+
| Grants for manager@% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` |
| GRANT SELECT ON `demo`.`goodsmaster` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`invcount` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`settlement` TO `manager`@`%` |
+-------------------------------------------------------+

只要你創建了一個角色，系統就會自動給你一個“ USAGE ”權限，意思是 連接登錄數據庫的權限。代碼的最后三行代表了我們給角色“manager”賦予的權限，也就是對商品信息表、盤點表和應付賬款表的只讀權限。

再示例：來看看庫管角色的權限：

mysql> SHOW GRANTS FOR 'stocker'@'%';
+-----------------------------------------------------------------------------+
| Grants for manager@% |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `stocker`@`%` |
| GRANT SELECT ON `demo`.`goodsmaster` TO `stocker`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `demo`.`invcount` TO `stocker`@`%` |
+-----------------------------------------------------------------------------+

結果顯示，庫管角色擁有商品信息表的只讀權限和盤點表的增刪改查權限。

5. 回收角色的權限

角色授權后，可以對角色的權限進行維護，對權限進行添加或撤銷。添加權限使用GRANT語句，與角色授權相同。撤銷角色或角色權限使用REVOKE語句。

修改了角色的權限，會影響擁有該角色的賬戶的權限。

撤銷角色權限的SQL語法如下:

REVOKE privileges ON tablename FROM 'rolename';

示例1：撤銷school_write角色的權限。

（1）使用如下語句撤銷school_write角色的權限。

REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write'@'%';

（2）撤銷后使用SHOW語句查看school_write對應的權限，語句如下。

SHOW GRANTS FOR 'school_write'@'%';

示例2:

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write'@'%'; # 收回角色的權限

6. 刪除角色

當我們需要對業務重新整合的時候，可能就需要對之前創建的角色進行清理，刪除一些不會再使用的角色。刪除角色的操作很簡單。語法如下：

DROP ROLE role1 [, role2]...;

注意：如果刪除了角色，那么用戶也就失去了通過這個角色所獲得的所有權限。

示例：刪除角色school_read。

DROP ROLE 'school_read'@'%'

7. 給用戶賦予角色

角色創建并授權后，要賦給用戶并處于 激活狀態 才能發揮作用。給用戶添加角色可使用GRANT語句，語法形式如下：

GRANT role [,role2,...] TO user [,user2,...];

在上述語句中，role代表角色，user代表用戶。可將多個角色同時賦予多個用戶，用逗號隔開即可。

示例：給kangshifu用戶添加角色school_read權限。

（1）使用GRANT語句給zhang3添加school_read權限，SQL語句如下。

GRANT 'school_read'@'%' TO 'zhang3'@'%';

（2）添加完成后使用SHOW語句查看是否添加成功。

SHOW GRANTS FOR 'zhang3'@'%';

（3）使用zhang3用戶登錄，然后查詢當前角色，如果角色未激活，結果將顯示NONE。

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.02 sec)

上面結果是NONE，說明用戶未具備相應的角色。

或者你用賦予了角色的用戶去登錄、操作，你會發現，這個賬號沒有任何權限。這是因為，MySQL中創建了角色之后，默認都是沒有被激活，也就是不能用，須要手動激活，激活以后用戶才能擁有角色對應的權限。

藍隊云官網上擁有完善的技術支持庫可供參考，大家可自行查閱，更多技術問題，可以直接咨詢。同時，藍隊云整理了運維必備的工具包免費分享給大家使用，需要的朋友可以直接咨詢。

更多技術知識，藍隊云期待與你一起探索。