在本教學中，您將學習如何使用MySQL角色來簡化許可權管理。
注意：本教學要求 MySQL 8+ 版本以上操作和執行，或自行參考：http://dev.mysql.com/doc/refman/8.0/en/roles.html
通常，MySQL資料庫擁有多個相同許可權集合的使用者。以前，向多個使用者授予和復原許可權的唯一方法是單獨更改每個使用者的許可權，假如使用者數量比較多的時候，這是非常耗時的。
為了使用者許可權管理更容易，MySQL提供了一個名為role的新物件，它是一個命名的特權集合。
如果要向多個使用者授予相同的許可權集，則應如下所示：
如果要更改使用者的許可權，則需要僅更改授權角色的許可權。這些更改角色的許可權將對授予角色的所有使用者生效。
首先，建立一個名為crmdb的新資料庫，用於儲存客戶關係管理資料。
CREATE DATABASE crmdb;
接下來，切換到crmdb資料庫：
USE crmdb;
然後，在crmdb資料庫中建立一個客戶資訊表：customer，其結構如下 -
USE crmdb;
CREATE TABLE `crmdb`.`customer`(
id INT PRIMARY KEY AUTO_INCREMENT,
first_name varchar(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
phone VARCHAR(32) NOT NULL,
email VARCHAR(255)
);
之後，將一些資料插入到客戶(customer)表中。
INSERT INTO customer(first_name,last_name,phone,email)
VALUES('Max','Su','(+86)-0898-66887654','[email protected]'),
('Lily','Bush','(+86)-0898-66887985','[email protected]');
最後，使用以下SELECT語句驗證插入結果：
mysql> SELECT * FROM customer;
+----+------------+-----------+---------------------+----------------------+
| id | first_name | last_name | phone | email |
+----+------------+-----------+---------------------+----------------------+
| 1 | Max | Su | (+86)-0898-66887654 | [email protected] |
| 2 | Lily | Bush | (+86)-0898-66887985 | [email protected] |
+----+------------+-----------+---------------------+----------------------+
2 rows in set
假設您開發了一個使用crmdb資料庫的應用程式。要與crmdb資料庫進行互動，您需要為需要完全存取資料庫的開發人員建立帳戶。此外，需要為僅需讀取存取許可權的使用者建立帳戶，以及為讀取/寫入存取許可權的使用者建立帳戶。
要避免單獨為每個使用者帳戶授予許可權，您可以建立一組角色，並為每個使用者帳戶授予相應的角色。
要建立新角色，請使用CREATE ROLE語句，我們根據上面所述，一共要建立三個角色：
CREATE ROLE IF NOT EXISTS 'crm_dev', 'crm_read', 'crm_write';
角色名稱類似於由使用者和主機部分組成的使用者帳戶：role_name@host_name 。
如果省略主機部分，則預設為「%」，表示任何主機。
要授予角色許可權，您可以使用GRANT語句。 以下語句是向crm_dev角色授予crmdb資料庫的所有許可權：
GRANT ALL ON crmdb.* TO crm_dev;
以下語句授予crm_read角色SELECT許可權：
GRANT SELECT ON crmdb.* TO crm_read;
以下語句賦予crm_write角色INSERT，UPDATE和DELETE許可權：
GRANT INSERT, UPDATE, DELETE ON crm.* TO crm_write;
假設您需要一個使用者帳戶是開發人員，一個是具有唯讀存取許可權的使用者帳戶和兩個具有讀/寫存取許可權的使用者帳戶。
要建立新使用者，請使用CREATE USER語句，如下所示：
-- developer user
CREATE USER [email protected] IDENTIFIED BY 'passwd1990';
-- read access user
CREATE USER [email protected] IDENTIFIED BY 'passwd1990';
-- read/write users
CREATE USER [email protected] IDENTIFIED BY 'passwd1990';
CREATE USER [email protected] IDENTIFIED BY 'passwd1990';
為了方便演示使用，所有使用者密碼都設定成一樣的。
要為使用者分配角色，請使用GRANT語句：
GRANT crm_dev TO [email protected];
GRANT crm_read TO [email protected];
GRANT crm_read, crm_write TO [email protected], [email protected];
請注意，crm_write1@localhost和crm_write2@localhost帳戶的GRANT語句同時授予crm_read和crm_write角色。
要驗證角色分配，請使用SHOW GRANTS語句，如下所示：
SHOW GRANTS FOR [email protected];
該語句返回以下結果集：
+-----------------------------------------------+
| Grants for [email protected] |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `crm_dev1`@`localhost` |
| GRANT `crm_dev`@`%` TO `crm_dev1`@`localhost` |
+-----------------------------------------------+
2 rows in set (0.02 sec)
正如你所看到的，它只返回授予角色。要顯示角色所代表的許可權，請使用USING子句和授權角色的名稱，如下所示：
SHOW GRANTS FOR [email protected] USING crm_write;
該語句返回以下輸出：
+---------------------------------------------------------------------+
| Grants for [email protected] |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `crm_write1`@`localhost` |
| GRANT INSERT, UPDATE, DELETE ON `crm`.* TO `crm_write1`@`localhost` |
| GRANT `crm_read`@`%`,`crm_write`@`%` TO `crm_write1`@`localhost` |
+---------------------------------------------------------------------+
現在，如果您使用crm_read1使用者帳戶連線到MySQL，並嘗試存取yiibaidb資料庫：
mysql -u crm_read1 -p
Enter password: ***********
mysql>USE crmdb;
上面語句發出以下錯誤資訊：
ERROR 1044 (42000): Access denied for user 'crm_read1'@'localhost' to database 'crmdb'
這是因為在向使用者帳戶授予角色時，當使用者帳戶連線到資料庫伺服器時，它不會自動使角色變為活動狀態。
如果呼叫CURRENT_ROLE()函式：
SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
它返回NONE，意味著沒有啟用角色。
要在每次使用者帳戶連線到資料庫伺服器時指定哪些角色應該處於活動狀態，請使用SET DEFAULT ROLE語句。
以下語句為crm_read1@localhost帳戶的所有分配角色設定預設值。
SET DEFAULT ROLE ALL TO crm_read[email protected];
現在，如果當使用crm_read1使用者帳戶連線到MySQL資料庫伺服器並呼叫CURRENT_ROLE()函式：
> mysql -u crm_read1 -p
Enter password: ***********
>SELECT CURRENT_ROLE();
您將看到crm_read1使用者帳戶的預設角色，如下所示 -
可以通過將當前資料庫切換到crmdb資料庫，執行SELECT語句和DELETE語句來測試crm_read帳戶的許可權，如下所示：
mysql> use crmdb;
Database changed
mysql> SELECT COUNT(*) FROM customer;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> DELETE FROM customer;
ERROR 1142 (42000): DELETE command denied to user 'crm_read1'@'localhost' for table 'customer'
如上面結果所示，它的確按預期那樣工作。當我們發出DELETE語句時，就收到一個錯誤，因為crm_read1使用者帳戶只能讀取存取許可權。
使用者帳戶可以通過指定哪個授權角色處於活動狀態來修改當前使用者在當前對談中的有效許可權。
以下語句將活動角色設定為NONE，表示沒有活動角色。
SET ROLE NONE;
要將活動角色設定為所有授予的角色，請使用：
SET ROLE ALL;
要將活動角色設定為由SET DEFAULT ROLE語句設定的預設角色，請使用：
SET ROLE DEFAULT;
要設定活動的命名角色，請使用：
SET ROLE granted_role_1, granted_role_2, ...
要從特定角色復原許可權，請使用REVOKE語句。REVOKE語句不僅起到角色的作用，而且也賦予任何授予角色的帳戶。
例如，要臨時使所有讀/寫使用者唯讀，您可以更改crm_write角色，如下所示：
REVOKE INSERT, UPDATE, DELETE ON crmdb.* FROM crm_write;
要恢復許可權，需要重新授予它們許可權，如下所示：
GRANT INSERT, UPDATE, DELETE ON crmdb.* FOR crm_write;
要刪除一個或多個角色，請使用DROP ROLE語句，如下所示：
DROP ROLE role_name, role_name, ...;
像REVOKE語句一樣，DROP ROLE語句從其授予的每個使用者帳戶中復原角色。
例如，要刪除crm_read，crm_write角色，請使用以下語句：
DROP ROLE crm_read, crm_write;
MySQL將使用者帳戶視為角色，因此，可以將使用者帳戶授予另一個使用者帳戶，例如向該使用者帳戶授予角色。這允許將使用者的許可權複製到另一個使用者。
假設您需要crmdb資料庫的另一個開發人員帳戶：
首先，建立新的使用者帳戶：
CREATE USER [email protected] IDENTIFIED BY 'passwd1990';
其次，將crm_dev1使用者帳戶的許可權複製到crm_dev2使用者帳戶，如下所示：
GRANT [email protected] TO [email protected];
在本教學中，您已經學會了如何使用MySQL角色來管理使用者帳戶的許可權。