MySQL 角色

---

简介：在本教程中，您将学习如何使用MySQL角色来简化权限管理。

---

MySQL角色简介

通常，您有许多具有相同权限集的用户。以前，授予和撤销多个用户权限的唯一方法是单独更改每个用户的权限，这非常耗时。 为了简化，MySQL提供了一个名为role的新对象，它是一个命名的特权集合。 如果要为多个用户授予相同的权限集，则应按如下方式执行：

• 首先，创建一个新角色。
• 其次，授予角色特权。
• 第三，将角色授予用户。

如果要更改用户的权限，则只需更改已授予角色的权限。更改将对角色授予的所有用户生效。

---

MySQL角色示例

首先，创建一个名为CRM 的新数据库，代表客户关系管理。

CREATE DATABASE crm;

接下来，切换到crm数据库：

USE crm;

然后，在CRM数据库中创建customer表。

CREATE TABLE customer(
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name varchar(255) NOT NULL, 
    last_name VARCHAR(255) NOT NULL, 
    phone VARCHAR(15) NOT NULL,
    email VARCHAR(255)
);

之后，将数据插入customer表中。

INSERT INTO customer(first_name,last_name,phone,email)
VALUES('John','Doe','(408)-987-7654','john.doe@begtut.com'),
      ('Lily','Bush','(408)-987-7985','lily.bush@begtut.com');

最后，使用以下SELECT语句验证插入：

mysql> SELECT * FROM customer;
+----+------------+-----------+----------------+----------------------+
| id | first_name | last_name | phone          | email                |
+----+------------+-----------+----------------+----------------------+
|  1 | John       | Doe       | (408)-987-7654 | john.doe@begtut.com  |
|  2 | Lily       | Bush      | (408)-987-7985 | lily.bush@begtut.com |
+----+------------+-----------+----------------+----------------------+
2 rows in set (0.07 sec)

创造角色

假设您开发了一个使用CRM数据库的应用程序。要与CRM数据库交互，您需要为需要完全访问数据库的开发人员创建帐户。此外，您需要为仅需要读取权限的用户和需要同时具有读/写访问权限的其他用户创建帐户。 为避免单独授予每个用户帐户的权限，您需要创建一组角色并为每个用户帐户授予相应的角色。 要创建新角色，请使用CREATE ROLE语句：

CREATE ROLE crm_dev, crm_read, crm_write;

角色名称类似于由用户和主机部分组成的用户帐户：role_name@host_name。 如果省略主机部分，则默认为'％'表示任何主机。

授予角色权限

要为角色​​授予权限，请使用GRANT语句。以下语句授予crm_dev角色所有权限：

GRANT ALL ON crm.* TO crm_dev;

以下语句授予角色SELECT权限crm_read：

GRANT SELECT ON crm.* TO crm_read;

下面的语句补助INSERT，UPDATE以及DELETE权限的crm_write角色：

GRANT INSERT, UPDATE, DELETE ON crm.* TO crm_write;

将角色分配给用户帐户

假设您需要一个用户帐户作为开发人员，一个可以具有只读访问权限的用户帐户和两个可以具有读/写访问权限的用户帐户。 要创建新用户，请使用以下CREATE USER语句：

-- developer user 
CREATE USER crm_dev1@localhost IDENTIFIED BY 'Secure$1782';
-- read access user
CREATE USER crm_read1@localhost IDENTIFIED BY 'Secure$5432';    
-- read/write users
CREATE USER crm_write1@localhost IDENTIFIED BY 'Secure$9075';   
CREATE USER crm_write2@localhost IDENTIFIED BY 'Secure$3452';

要为用户分配角色，请使用以下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.24 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`    |
+---------------------------------------------------------------------+
3 rows in set (0.10 sec)

设置默认角色

现在，如果您使用crm_read1用户帐户连接到MySQL 并尝试访问CRM数据库：

>mysql -u crm_read1 -p
Enter password: ***********
mysql>USE crm;

声明发出以下错误消息：

ERROR 1044 (42000): Access denied for user 'crm_read1'@'localhost' to database 'crm'

这是因为当您向用户帐户授予角色时，当用户帐户连接到数据库服务器时，它不会自动使角色变为活动状态。 如果您调用CURRENT_ROLE()函数：

mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.02 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用户帐户的默认角色。

+----------------+
| current_role() |
+----------------+
| `crm_read`@`%` |
+----------------+
1 row in set (0.00 sec)

您可以crm_read通过将当前数据库切换为CRM，执行SELECT语句和DELETE语句来测试帐户的权限，如下所示：

mysql> use crm;
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 crm.* FROM crm_write;

要恢复权限，您需要按如下方式重新授予权限：

GRANT INSERT, UPDATE, DELETE ON crm.* 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将用户帐户视为角色，因此，您可以将用户帐户授予其他用户帐户，例如向用户帐户授予角色。这允许您将用户的权限复制到另一个用户。 假设您需要另一个CRM数据库开发者帐户： 首先，创建新的用户帐户：

CREATE USER crm_dev2@localhost IDENTIFIED BY 'Secure$6275';

其次，将crm_dev1用户帐户的权限复制到crm_dev2用户帐户，如下所示：

GRANT crm_dev1@localhost TO crm_dev2@localhost;

在本教程中，您学习了如何使用MySQL角色来更轻松地管理用户帐户的权限。

---