角色管理

在本教程中，您将学习如何使用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','([phone]','[e-mail]'),
      ('Lily','Bush','([phone]','[e-mail]');

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

mysql> SELECT * FROM customer;
+----+------------+-----------+---------------------+----------------------+
| id | first_name | last_name | phone               | email                |
+----+------------+-----------+---------------------+----------------------+
|  1 | Max        | Su        | ([phone] | [e-mail]    |
|  2 | Lily       | Bush      | ([phone] | [e-mail] |
+----+------------+-----------+---------------------+----------------------+
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，并尝试访问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_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角色来管理用户帐户的权限。