# 角色管理 在本教程中，您将学习如何使用MySQL角色来简化权限管理。 > 注意：本教程要求 *MySQL 8+* 版本以上操作和执行，或自行参考： ## MySQL角色简介 通常，MySQL数据库拥有多个相同权限集合的用户。以前，向多个用户授予和撤销权限的唯一方法是单独更改每个用户的权限，假如用户数量比较多的时候，这是非常耗时的。 为了用户权限管理更容易，MySQL提供了一个名为`role`的新对象，它是一个命名的特权集合。 如果要向多个用户授予相同的权限集，则应如下所示： * *首先*，创建新的角色。 * *第二*，授予角色权限。 * *第三*，授予用户角色。 如果要更改用户的权限，则需要仅更改授权角色的权限。这些更改角色的权限将对授予角色的所有用户生效。 ## MySQL角色的例子 首先，创建一个名为`crmdb`的新数据库，用于存储客户关系管理数据。 ```sql CREATE DATABASE crmdb; ``` 接下来，切换到`crmdb`数据库： ```sql USE crmdb; ``` 然后，在`crmdb`数据库中创建一个客户信息表：`customer`，其结构如下 - ```sql 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`)表中。 ```sql INSERT INTO customer(first_name,last_name,phone,email) VALUES('Max','Su','(+86)-0898-66887654','max.su@yiibai.com'), ('Lily','Bush','(+86)-0898-66887985','lily.bush@yiibai.com'); ``` 最后，使用以下[SELECT语句](http://www.yiibai.com/mysql/select-statement-query-data.html)验证插入结果： ```sql mysql> SELECT * FROM customer; +----+------------+-----------+---------------------+----------------------+ | id | first_name | last_name | phone | email | +----+------------+-----------+---------------------+----------------------+ | 1 | Max | Su | (+86)-0898-66887654 | max.su@yiibai.com | | 2 | Lily | Bush | (+86)-0898-66887985 | lily.bush@yiibai.com | +----+------------+-----------+---------------------+----------------------+ 2 rows in set ``` ### 创建角色 假设您开发了一个使用`crmdb`数据库的应用程序。要与`crmdb`数据库进行交互，您需要为需要完全访问数据库的开发人员创建帐户。此外，需要为仅需读取访问权限的用户创建帐户，以及为读取/写入访问权限的用户创建帐户。 要避免单独为每个用户帐户授予权限，您可以创建一组角色，并为每个用户帐户授予相应的角色。 要创建新角色，请使用`CREATE ROLE`语句，我们根据上面所述，一共要创建三个角色： ```sql CREATE ROLE IF NOT EXISTS 'crm_dev', 'crm_read', 'crm_write'; ``` 角色名称类似于由用户和主机部分组成的用户帐户：`role_name@host_name` 。 如果省略主机部分，则默认为“`%`”，表示任何主机。 ### 授予角色权限 要授予角色权限，您可以使用`GRANT`语句。 以下语句是向`crm_dev`角色授予`crmdb`数据库的所有权限： ```sql GRANT ALL ON crmdb.* TO crm_dev; ``` 以下语句授予`crm_read`角色`SELECT`权限： ```sql GRANT SELECT ON crmdb.* TO crm_read; ``` 以下语句赋予`crm_write`角色`INSERT`，`UPDATE`和`DELETE`权限： ```sql GRANT INSERT, UPDATE, DELETE ON crm.* TO crm_write; ``` ### 将角色分配给用户帐户 假设您需要一个用户帐户是开发人员，一个是具有只读访问权限的用户帐户和两个具有读/写访问权限的用户帐户。 要创建新用户，请使用[CREATE USER](http://www.yiibai.com/mysql/create-user.html)语句，如下所示： ```sql -- 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`语句： ```sql 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; ``` 该语句返回以下结果集： ```sql +-----------------------------------------------+ | 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`子句和授权角色的名称，如下所示： ```sql SHOW GRANTS FOR crm_write1@localhost USING crm_write; ``` 该语句返回以下输出： ```sql +---------------------------------------------------------------------+ | 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`数据库： ```sql mysql -u crm_read1 -p Enter password: *********** mysql>USE crmdb; ``` 上面语句发出以下错误信息： ```sql ERROR 1044 (42000): Access denied for user 'crm_read1'@'localhost' to database 'crmdb' ``` 这是因为在向用户帐户授予角色时，当用户帐户连接到数据库服务器时，它不会自动使角色变为活动状态。 如果调用`CURRENT_ROLE()`函数： ```sql SELECT current_role(); +----------------+ | current_role() | +----------------+ | NONE | +----------------+ 1 row in set (0.00 sec) ``` 它返回`NONE`，意味着没有启用角色。 要在每次用户帐户连接到数据库服务器时指定哪些角色应该处于活动状态，请使用`SET DEFAULT ROLE`语句。 以下语句为`crm_read1@localhost`帐户的所有分配角色设置默认值。 ```sql SET DEFAULT ROLE ALL TO crm_read1@localhost; ``` 现在，如果当使用`crm_read1`用户帐户连接到MySQL数据库服务器并调用`CURRENT_ROLE()`函数： ```sql > mysql -u crm_read1 -p Enter password: *********** >SELECT CURRENT_ROLE(); ``` 您将看到`crm_read1`用户帐户的默认角色，如下所示 - ``` ``` 可以通过将当前数据库切换到`crmdb`数据库，执行`SELECT`语句和`DELETE`语句来测试`crm_read`帐户的权限，如下所示： ```sql 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`，表示没有活动角色。 ```sql SET ROLE NONE; ``` 要将活动角色设置为所有授予的角色，请使用： ```sql SET ROLE ALL; ``` 要将活动角色设置为由`SET DEFAULT ROLE`语句设置的默认角色，请使用： ```sql SET ROLE DEFAULT; ``` 要设置活动的命名角色，请使用： ```sql SET ROLE granted_role_1, granted_role_2, ... ``` ### 撤销角色的权限 要从特定角色撤销权限，请使用`REVOKE`语句。`REVOKE`语句不仅起到角色的作用，而且也赋予任何授予角色的帐户。 例如，要临时使所有读/写用户只读，您可以更改`crm_write`角色，如下所示： ```sql REVOKE INSERT, UPDATE, DELETE ON crmdb.* FROM crm_write; ``` 要恢复权限，需要重新授予它们权限，如下所示： ```sql GRANT INSERT, UPDATE, DELETE ON crmdb.* FOR crm_write; ``` ### 删除角色 要删除一个或多个角色，请使用`DROP ROLE`语句，如下所示： ```sql DROP ROLE role_name, role_name, ...; ``` 像`REVOKE`语句一样，`DROP ROLE`语句从其授予的每个用户帐户中撤销角色。 例如，要删除`crm_read`，`crm_write`角色，请使用以下语句： ```sql DROP ROLE crm_read, crm_write; ``` ### 将权限从用户帐户复制到另一个用户 MySQL将用户帐户视为角色，因此，可以将用户帐户授予另一个用户帐户，例如向该用户帐户授予角色。这允许将用户的权限复制到另一个用户。 假设您需要`crmdb`数据库的另一个开发人员帐户： 首先，创建新的用户帐户： ```sql CREATE USER crm_dev2@localhost IDENTIFIED BY 'passwd1990'; ``` 其次，将`crm_dev1`用户帐户的权限复制到`crm_dev2`用户帐户，如下所示： ```sql GRANT crm_dev1@localhost TO crm_dev2@localhost; ``` 在本教程中，您已经学会了如何使用MySQL角色来管理用户帐户的权限。