mysql8.0的角色管理

歡迎來(lái)到藍(lán)隊(duì)云技術(shù)小課堂，每天分享一個(gè)技術(shù)小知識(shí)。

1. 角色的理解

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

引入角色的目的是方便管理?yè)碛邢嗤瑱?quán)限的用戶。恰當(dāng)?shù)臋?quán)限設(shè)定，可以確保數(shù)據(jù)的安全性，這是至關(guān)重要的。

2. 創(chuàng)建角色

在實(shí)際應(yīng)用中，為了安全性，需要給用戶授予權(quán)限, 當(dāng)用戶數(shù)量較多時(shí)，為了避兔單獨(dú)給每一個(gè)用戶授予多個(gè)權(quán)限，可以先將權(quán)限集合放入角色中，再賦予用戶相應(yīng)的角色。

創(chuàng)建角色使用 CREATE ROLE 語(yǔ)句，語(yǔ)法如下：

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

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

示例：我們現(xiàn)在需要?jiǎng)?chuàng)建一個(gè)經(jīng)理的角色，就可以用下面的代碼：

CREATE ROLE 'manager'@'localhost';

這里創(chuàng)建了一個(gè)角色，角色名稱是"manager",角色可以登錄的主機(jī)是"localhost"，意思是只能從數(shù)據(jù)厙服務(wù)器運(yùn)行的這臺(tái)計(jì)算機(jī)登錄這個(gè)賬號(hào)。你也可以不寫主機(jī)名，直接創(chuàng)建角色 "manager"。

CREATE ROLE 'manager';

如果不寫主機(jī)名MySQL默認(rèn)是通配符%，意思是這個(gè)賬號(hào)可以從任何一臺(tái)主機(jī)上登錄數(shù)據(jù)庫(kù)。

同樣道理，如果我們要?jiǎng)?chuàng)建庫(kù)管的角色，就可以用下面的代碼

CREATE ROLE 'stocker';

還可以通過(guò)如下命令，一次性創(chuàng)建3個(gè)角色：

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

3. 給角色賦予權(quán)限

創(chuàng)建角色之后，默認(rèn)這個(gè)角色是沒(méi)有任何權(quán)限的，我們需要給角色授權(quán)。給角色授權(quán)的語(yǔ)法結(jié)構(gòu)是：

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

上述語(yǔ)句中privileges代表權(quán)限的名稱，多個(gè)權(quán)限以逗號(hào)隔開(kāi)?？墒褂肧HOW語(yǔ)句查詢權(quán)限名稱，圖中列出了部分權(quán)限列表。

SHOW PRIVILEGES\\\\G

示例1: 我們現(xiàn)在想給經(jīng)理角色授予商品信息表、盤點(diǎn)表和應(yīng)付賬款表的只讀權(quán)限，就可以用下面的代碼來(lái)實(shí)現(xiàn)：

GRANT SELECT ON demo.settlement TO 'manager';

GRANT SELECT ON demo.goodsmaster TO 'manager';

GRANT SELECT ON demo.invcount TO 'manager';

如果我們需要賦予庫(kù)管角色盤點(diǎn)表的增刪改權(quán)限、商品信息表的只讀權(quán)限，對(duì)應(yīng)付賬款表沒(méi)有權(quán)限，就可以這樣：

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數(shù)據(jù)庫(kù)中的所有表的所有權(quán)限

GRANT SELECT ON app_db.* TO 'app_read'; -- 給app_db數(shù)據(jù)庫(kù)中的所有表的查詢權(quán)限

GRANT INSERT,DELETE,UPDATE  ON app_db.* TO 'app_write'; -- 給app_db數(shù)據(jù)庫(kù)中的所有表的修改權(quán)限

示例3 創(chuàng)建三個(gè)角色，分別擁有全部權(quán)限、查詢權(quán)限和讀寫權(quán)限, 步驟如下所示·

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

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

(2) 給每個(gè)角色授予對(duì)應(yīng)的權(quán)限，school_admin可以對(duì)數(shù)據(jù)庫(kù)shool中的所有表進(jìn)行任何操作, school_read只能對(duì)數(shù)據(jù)厙中的表進(jìn)行查詢, school_write可以對(duì)數(shù)據(jù)庫(kù)shool 中的表進(jìn)行讀與操作，SQ語(yǔ)句如下。

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. 查看角色的權(quán)限

賦予角色權(quán)限之后，我們可以通過(guò) SHOW GRANTS語(yǔ)句，來(lái)查看權(quán)限是否創(chuàng)建成功了

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`@`%` |
+-------------------------------------------------------+

只要你創(chuàng)建了一個(gè)角色，系統(tǒng)就會(huì)自動(dòng)給你一個(gè)“ USAGE ”權(quán)限，意思是 連接登錄數(shù)據(jù)庫(kù)的權(quán)限。代碼的最后三行代表了我們給角色“manager”賦予的權(quán)限，也就是對(duì)商品信息表、盤點(diǎn)表和應(yīng)付賬款表的只讀權(quán)限。

再示例：來(lái)看看庫(kù)管角色的權(quán)限：

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`@`%` |
+-----------------------------------------------------------------------------+

結(jié)果顯示，庫(kù)管角色擁有商品信息表的只讀權(quán)限和盤點(diǎn)表的增刪改查權(quán)限。

5. 回收角色的權(quán)限

角色授權(quán)后，可以對(duì)角色的權(quán)限進(jìn)行維護(hù)，對(duì)權(quán)限進(jìn)行添加或撤銷。添加權(quán)限使用GRANT語(yǔ)句，與角色授權(quán)相同。撤銷角色或角色權(quán)限使用REVOKE語(yǔ)句。

修改了角色的權(quán)限，會(huì)影響擁有該角色的賬戶的權(quán)限。

撤銷角色權(quán)限的SQL語(yǔ)法如下:

REVOKE privileges ON tablename FROM 'rolename';

示例1：撤銷school_write角色的權(quán)限。

（1）使用如下語(yǔ)句撤銷school_write角色的權(quán)限。

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

（2）撤銷后使用SHOW語(yǔ)句查看school_write對(duì)應(yīng)的權(quán)限，語(yǔ)句如下。

SHOW GRANTS FOR 'school_write'@'%';

示例2:

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

6. 刪除角色

當(dāng)我們需要對(duì)業(yè)務(wù)重新整合的時(shí)候，可能就需要對(duì)之前創(chuàng)建的角色進(jìn)行清理，刪除一些不會(huì)再使用的角色。刪除角色的操作很簡(jiǎn)單。語(yǔ)法如下：

DROP ROLE role1 [, role2]...;

注意：如果刪除了角色，那么用戶也就失去了通過(guò)這個(gè)角色所獲得的所有權(quán)限。

示例：刪除角色school_read。

DROP ROLE 'school_read'@'%'

7. 給用戶賦予角色

角色創(chuàng)建并授權(quán)后，要賦給用戶并處于 激活狀態(tài) 才能發(fā)揮作用。給用戶添加角色可使用GRANT語(yǔ)句，語(yǔ)法形式如下：

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

在上述語(yǔ)句中，role代表角色，user代表用戶。可將多個(gè)角色同時(shí)賦予多個(gè)用戶，用逗號(hào)隔開(kāi)即可。

示例：給kangshifu用戶添加角色school_read權(quán)限。

（1）使用GRANT語(yǔ)句給zhang3添加school_read權(quán)限，SQL語(yǔ)句如下。

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

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

SHOW GRANTS FOR 'zhang3'@'%';

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

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

上面結(jié)果是NONE，說(shuō)明用戶未具備相應(yīng)的角色。

或者你用賦予了角色的用戶去登錄、操作，你會(huì)發(fā)現(xiàn)，這個(gè)賬號(hào)沒(méi)有任何權(quán)限。這是因?yàn)椋?strong style="box-sizing: border-box">MySQL中創(chuàng)建了角色之后，默認(rèn)都是沒(méi)有被激活，也就是不能用，須要手動(dòng)激活，激活以后用戶才能擁有角色對(duì)應(yīng)的權(quán)限。

藍(lán)隊(duì)云官網(wǎng)上擁有完善的技術(shù)支持庫(kù)可供參考，大家可自行查閱，更多技術(shù)問(wèn)題，可以直接咨詢。同時(shí)，藍(lán)隊(duì)云整理了運(yùn)維必備的工具包免費(fèi)分享給大家使用，需要的朋友可以直接咨詢。

更多技術(shù)知識(shí)，藍(lán)隊(duì)云期待與你一起探索。