MySQL角色管理

在本教學中，您將學習如何使用MySQL角色來簡化許可權管理。

注意：本教學要求 MySQL 8+ 版本以上操作和執行，或自行參考：http://dev.mysql.com/doc/refman/8.0/en/roles.html

MySQL角色簡介

通常，MySQL資料庫擁有多個相同許可權集合的用戶。以前，向多個用戶授予和撤銷許可權的唯一方法是單獨更改每個用戶的許可權，假如用戶數量比較多的時候，這是非常耗時的。

為了用戶許可權管理更容易，MySQL提供了一個名為role的新對象，它是一個命名的特權集合。

如果要向多個用戶授予相同的許可權集，則應如下所示：

• 首先，創建新的角色。
• 第二，授予角色許可權。
• 第三，授予用戶角色。

如果要更改用戶的許可權，則需要僅更改授權角色的許可權。這些更改角色的許可權將對授予角色的所有用戶生效。

MySQL角色的例子

首先，創建一個名為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','max.su@xuhuhu.com'),
      ('Lily','Bush','(+86)-0898-66887985','lily.bush@xuhuhu.com');

最後，使用以下SELECT語句驗證插入結果：

mysql> SELECT * FROM customer;
+----+------------+-----------+---------------------+----------------------+
| id | first_name | last_name | phone               | email                |
+----+------------+-----------+---------------------+----------------------+
|  1 | Max        | Su        | (+86)-0898-66887654 | max.su@xuhuhu.com    |
|  2 | Lily       | Bush      | (+86)-0898-66887985 | lily.bush@xuhuhu.com |
+----+------------+-----------+---------------------+----------------------+
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 crm_dev1@localhost IDENTIFIED BY 'passwd1990';
-- read access user
CREATE USER crm_read1@localhost IDENTIFIED BY 'passwd1990';
-- read/write users
CREATE USER crm_write1@localhost IDENTIFIED BY 'passwd1990';
CREATE USER crm_write2@localhost IDENTIFIED BY 'passwd1990';

為了方便演示使用，所有用戶密碼都設置成一樣的。

要為用戶分配角色，請使用GRANT語句：

GRANT crm_dev TO crm_dev1@localhost;

GRANT crm_read TO crm_read1@localhost;

GRANT crm_read, crm_write TO crm_write1@localhost, crm_write2@localhost;

請注意，crm_write1@localhost和crm_write2@localhost帳戶的GRANT語句同時授予crm_read和crm_write角色。

要驗證角色分配，請使用SHOW GRANTS語句，如下所示：

SHOW GRANTS FOR crm_dev1@localhost;

該語句返回以下結果集：

+-----------------------------------------------+
| Grants for crm_dev1@localhost                 |
+-----------------------------------------------+
| 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 crm_write1@localhost USING crm_write;

該語句返回以下輸出：

+---------------------------------------------------------------------+
| Grants for crm_write1@localhost                                     |
+---------------------------------------------------------------------+
| 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，並嘗試訪問zaixiandb資料庫：

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_read1@localhost;

現在，如果當使用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 crm_dev2@localhost IDENTIFIED BY 'passwd1990';

其次，將crm_dev1用戶帳戶的許可權複製到crm_dev2用戶帳戶，如下所示：

GRANT crm_dev1@localhost TO crm_dev2@localhost;

在本教學中，您已經學會了如何使用MySQL角色來管理用戶帳戶的許可權。