MySQL角色
简介：在本教程中，您将学习如何使用MySQL角色来简化特权管理。
MySQL角色简介
通常，您有多个具有相同特权集的用户。
以前，向多个用户授予和撤消特权的唯一方法是分别更改每个用户的特权，这很耗时。
为了简化操作，MySQL提供了一个称为角色的新对象。
角色是特权的命名集合。
像用户帐户一样，您可以向角色授予特权并从角色撤消特权。
如果要向多个用户授予同一组特权，请按照以下步骤操作：
首先，创建一个新角色。
其次，授予角色特权。
第三，将角色授予用户。
如果要更改用户的特权，则只需更改授予角色的特权。
更改将对授予该角色的所有用户生效。
MySQL角色示例
首先，创建一个名为CRM的新数据库，该数据库代表客户关系管理。
CREATE DATABASE crm;
接下来，使用crm数据库：
USE crm;
然后，在CRM数据库中创建客户表。
CREATE TABLE customers(
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)
);
之后，将数据插入到customers表中。
INSERT INTO customers(first_name,last_name,phone,email)
VALUES('John','Doe','(408)-987-7654','[email protected]'),
('Lily','Bush','(408)-987-7985','[email protected]');
最后，使用以下SELECT语句验证插入：
SELECT * FROM customers;
创建角色
假设您开发了一个使用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;
以下语句向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 'Secure82'; -- read access user CREATE USER crm_read1@localhost IDENTIFIED BY 'Secure32'; -- read/write users CREATE USER crm_write1@localhost IDENTIFIED BY 'Secure75'; CREATE USER crm_write2@localhost IDENTIFIED BY 'Secure52';
要将角色分配给用户，请使用GRANT语句。
以下语句将crm_rev角色授予用户帐户crm_dev1 @ localhost：
GRANT crm_dev TO crm_dev1@localhost;
以下语句将crm_read角色授予用户帐户crm_read1 @ localhost：
GRANT crm_read TO crm_read1@localhost;
以下语句将crm_read和crm_write角色授予用户帐户crm_write1 @ localhost和crm_write2 @ localhost：
GRANT crm_read,
crm_write
TO crm_write1@localhost,
crm_write2@localhost;
要验证角色分配，请使用SHOW GRANTS语句，如下例所示：
SHOW GRANTS FOR crm_dev1@localhost;
该语句返回以下结果集：
如您所见，它只是返回了授予的角色。
要显示角色代表的特权，请使用USING子句以及授予的角色的名称，如下所示：
SHOW GRANTS FOR crm_write1@localhost USING crm_write;
该语句返回以下输出：
设置默认角色
现在，如果您使用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()函数，它将返回NONE，表示没有活动角色。
SELECT current_role();
这是输出：
+----------------+ | current_role() | +----------------+ | NONE | +----------------+ 1 row in set (0.00 sec)
要指定每次用户帐户连接到数据库服务器时应激活的角色，请使用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: *********** mysql> select current_role();
您将看到crm_read1用户帐户的默认角色。
+----------------+ | current_role() | +----------------+ | `crm_read`@`%` | +----------------+ 1 row in set (0.00 sec)
您可以通过将当前数据库切换到CRM，执行SELECT语句和DELETE语句来测试crm_read帐户的特权，如下所示：
mysql> use crm; Database changed mysql> SELECT COUNT(*) FROM customers; +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) mysql> DELETE FROM customers; ERROR 1142 (42000): DELETE command denied to user 'crm_read1'@'localhost' for table 'customers'
它按预期工作。
当我们发出DELETE语句时，MySQL发出一个错误，因为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 'Secure75';
其次，将特权从crm_dev1用户帐户复制到crm_dev2用户帐户，如下所示：
GRANT crm_dev1@localhost TO crm_dev2@localhost;
在本教程中，您学习了如何使用MySQL角色来更轻松地管理用户帐户的特权。
