The Ultimate Guide To MySQL Roles By Examples

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

MySQL角色介绍

通常，您有多个具有相同权限集的用户。以前，向多个用户授予和撤销权限的唯一方法是单独更改每个用户的权限，这非常耗时。

为了变得更容易，MySQL 提供了一个名为角色的新对象。角色是命名的权限集合。

与用户帐户一样，您可以向角色授予权限并撤消其权限。

如果您想向多个用户授予同一组权限，请按照下列步骤操作：

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

如果您想更改用户的权限，只需更改授予角色的权限即可。这些更改将对授予该角色的所有用户生效。

MySQL 角色示例

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

CREATE DATABASE crm;
Code language: SQL (Structured Query Language) (sql)

接下来，使用crm数据库：

USE crm;
Code language: SQL (Structured Query Language) (sql)

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

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)
);
Code language: SQL (Structured Query Language) (sql)

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

INSERT INTO customers(first_name,last_name,phone,email)
VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'),
      ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)

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

SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)

创建角色

假设您开发一个使用CRM数据库的应用程序。要与CRM数据库交互，您需要为需要完全访问数据库的开发人员创建帐户。此外，您需要为仅需要读取访问权限的用户和需要读取/写入访问权限的其他用户创建帐户。

为了避免单独向每个用户帐户授予权限，您可以创建一组角色并向每个用户帐户授予适当的角色。

要创建新角色，请使用CREATE ROLE语句：

CREATE ROLE 
    crm_dev, 
    crm_read, 
    crm_write;Code language: SQL (Structured Query Language) (sql)

角色名称类似于用户帐户，由两部分组成：名称和主机：

role_name@host_nameCode language: SQL (Structured Query Language) (sql)

如果省略主机部分，则默认为“%”，表示任何主机。

为角色授予权限

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

GRANT ALL 
ON crm.* 
TO crm_dev;
Code language: SQL (Structured Query Language) (sql)

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

GRANT SELECT 
ON crm.* 
TO crm_read;Code language: SQL (Structured Query Language) (sql)

以下语句向crm_write角色授予INSERT 、 UPDATE和DELETE权限：

GRANT INSERT, UPDATE, DELETE
ON crm.* 
TO crm_write;Code language: SQL (Structured Query Language) (sql)

将角色分配给用户帐户

假设您需要一个用户帐户作为开发人员、一个可以具有只读访问权限的用户帐户和两个可以具有读/写访问权限的用户帐户。

要创建新用户，请使用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';
Code language: SQL (Structured Query Language) (sql)

要将角色分配给用户，可以使用GRANT语句。

以下语句将crm_rev角色授予用户帐户crm_dev1@localhost ：

GRANT crm_dev 
TO crm_dev1@localhost;Code language: SQL (Structured Query Language) (sql)

以下语句将crm_read角色授予用户帐户crm_read1@localhost ：

GRANT crm_read 
TO crm_read1@localhost;Code language: SQL (Structured Query Language) (sql)

以下语句将crm_read和crm_write角色授予用户帐户crm_write1@localhost和crm_write2@localhost ：

GRANT crm_read, 
    crm_write 
TO crm_write1@localhost, 
    crm_write2@localhost;
Code language: SQL (Structured Query Language) (sql)

要验证角色分配，请使用SHOW GRANTS语句，如下例所示：

SHOW GRANTS FOR crm_dev1@localhost;
Code language: SQL (Structured Query Language) (sql)

该语句返回以下结果集：

正如您所看到的，它只是返回了授予的角色。要显示角色代表的权限，请使用USING子句和授予的角色名称，如下所示：

SHOW GRANTS 
FOR crm_write1@localhost 
USING crm_write;
Code language: SQL (Structured Query Language) (sql)

该语句返回以下输出：

设置默认角色

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

>mysql -u crm_read1 -p
Enter password: ***********
mysql>USE crm;
Code language: SQL (Structured Query Language) (sql)

该声明发出以下错误消息：

ERROR 1044 (42000): Access denied for user 'crm_read1'@'localhost' to database 'crm'
Code language: SQL (Structured Query Language) (sql)

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

如果您调用CURRENT_ROLE()函数，它将返回NONE ，这意味着没有活动角色。

SELECT current_role();Code language: SQL (Structured Query Language) (sql)

这是输出：

+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

要指定每次用户帐户连接到数据库服务器时哪些角色应处于活动状态，请使用SET DEFAULT ROLE语句。

以下语句为crm_read1@localhost帐户设置其所有分配角色的默认值。

SET DEFAULT ROLE ALL TO crm_read1@localhost;
Code language: SQL (Structured Query Language) (sql)

现在，如果您使用crm_read1用户帐户连接到 MySQL 数据库服务器并调用CURRENT_ROLE()函数：

>mysql -u crm_read1 -p
Enter password: ***********
mysql> select current_role();
Code language: SQL (Structured Query Language) (sql)

您将看到crm_read1用户帐户的默认角色。

+----------------+
| current_role() |
+----------------+
| `crm_read`@`%` |
+----------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

您可以通过将当前数据库切换到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'
Code language: SQL (Structured Query Language) (sql)

它按预期工作。当我们发出DELETE语句时，MySQL 发出错误，因为crm_read1用户帐户仅具有读取访问权限。

设置主动角色

用户帐户可以通过指定哪个授予的角色处于活动状态来修改当前用户在当前会话中的有效权限。

以下语句将活动角色设置为NONE ，表示没有活动角色。

SET ROLE NONE;
Code language: SQL (Structured Query Language) (sql)

要将活动角色设置为所有授予的角色，您可以使用：

SET ROLE ALL;
Code language: SQL (Structured Query Language) (sql)

要将活动角色设置为由SET DEFAULT ROLE语句设置的默认角色，请使用：

SET ROLE DEFAULT;
Code language: SQL (Structured Query Language) (sql)

要设置活动命名角色，您可以使用：

SET ROLE 
    granted_role_1
    [,granted_role_2, ...]
Code language: SQL (Structured Query Language) (sql)

撤销角色的权限

要撤销特定角色的权限，可以使用REVOKE语句。 REVOKE语句不仅对角色生效，而且对授予该角色的任何帐户生效。

例如，要暂时将所有读/写用户设置为只读，您可以按如下所示更改crm_write角色：

REVOKE INSERT, UPDATE, DELETE 
ON crm.* 
FROM crm_write;
Code language: SQL (Structured Query Language) (sql)

要恢复权限，您需要重新授予权限，如下所示：

GRANT INSERT, UPDATE, DELETE 
ON crm.* 
FOR crm_write;
Code language: SQL (Structured Query Language) (sql)

删除角色

要删除一个或多个角色，请使用DROP ROLE语句，如下所示：

DROP ROLE role_name[, role_name, ...];
Code language: SQL (Structured Query Language) (sql)

与REVOKE语句类似， DROP ROLE语句从被授予角色的每个用户帐户中撤消角色。

例如，要删除crm_read 、 crm_write角色，请使用以下语句：

DROP ROLE crm_read, crm_write;
Code language: SQL (Structured Query Language) (sql)

将权限从一个用户帐户复制到另一个用户帐户

MySQL 将用户帐户视为角色，因此，您可以将一个用户帐户授予另一个用户帐户，就像向该用户帐户授予角色一样。这允许您将权限从一个用户复制到另一个用户。

假设您需要另一个用于CRM数据库的开发人员帐户：

首先，创建新的用户帐户：

CREATE USER crm_dev2@localhost 
IDENTIFIED BY 'Secure$6275';
Code language: SQL (Structured Query Language) (sql)

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

GRANT crm_dev1@localhost 
TO crm_dev2@localhost;
Code language: SQL (Structured Query Language) (sql)

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