在 MySQL 中使用角色来简化权限管理

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

对于一个线上的 MySQL 数据库服务器来说，它可能有很多使用者。其中某些用户可能具有相同的权限。

作为一个数据库管理员或者运维人员，可能要对多个用户赋予相同的权限。这个过程很耗时，也很容易带来错误。

MySQL 中的角色可以让你简化授权的过程。您只需要为权限相同的用户创建一个角色，并将角色赋予这些用户即可。

您还可以创建多个角色，授权给不同的用户。您也可以为一个用户赋予多个不同的角色。

MySQL 角色语法

如果要将同一组权限授予多个用户，请执行以下步骤：

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

创建角色语法

以下是创建角色语法：

CREATE ROLE role_name1 [, role_name2 [, ...]];

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

如果需要在一个语句中创建多个角色，请使用逗号分隔不同的角色名。

删除角色语法

以下是删除角色语法：

DROP ROLE role_name1 [, role_name2 [, ...]];

如果需要在一个语句中删除多个角色，请使用逗号分隔不同的角色名。

为角色授权语法

要为角色授权，请使用以下 GRANT 语句：

GRANT privilege_type [,privilege_type],..
ON privilege_object
TO role_name;

撤销角色的权限语法

要撤销授予角色的权限，请使用以下 REVOKE 语句：

REVOKE
   priv1 [, priv2 [, ...] ]
ON privilege_object
FROM role_name;

MySQL 角色实例

这个实例展示了使用角色为用户授权完整步骤。

准备

  1. 创建一个名为 sqlizdb 的新数据库：

    CREATE DATABASE sqlizdb;

  2. 选择数据库 sqlizdb ：

    USE sqlizdb;

  3. 在数据库 sqlizdb 中新建一个表 test_table：

    CREATE TABLE test_table(
    id int AUTO_INCREMENT PRIMARY KEY,
    txt varchar(100) NOT NULL
);

  4. 在表 test_table 中插入一行：

    INSERT INTO test_table(txt)
VALUES('Hello World.');

  5. 从表 test_table 中查询数据：

    SELECT * FROM test_table;

    这是输出：

    +----+--------------+
| id | txt          |
+----+--------------+
|  1 | Hello World. |
+----+--------------+

创建角色

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

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

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

CREATE ROLE
    sqlizdb_dev,
    sqlizdb_read,
    sqlizdb_write;

这里省略主机部分，那么这些角色在任何主机都可以使用。

授予角色权限

角色 sqlizdb_dev 为开发人员使用，应该授予所有权限：

GRANT ALL ON sqlizdb.* TO sqlizdb_dev;

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

GRANT SELECT ON sqlizdb.* TO sqlizdb_read;

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

GRANT INSERT, UPDATE, DELETE ON sqlizdb.* TO sqlizdb_write;

为用户帐户分配角色

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

请使用如下语句要创建新用户：

-- 开发者用户
CREATE USER sqlizdb_dev_user1@'%' IDENTIFIED BY 'SqLiZ9879123!';
-- 只读用户
CREATE USER sqlizdb_read_user1@'%' IDENTIFIED BY 'SqLiZ9879123!';
-- 读写用户
CREATE USER sqlizdb_write_user1@'%' IDENTIFIED BY 'SqLiZ9879123!';
CREATE USER sqlizdb_write_user2@'%' IDENTIFIED BY 'SqLiZ9879123!';

要为用户分配角色，请使用 GRANT 语句。以下语句分别为 4 个用户授予角色：

GRANT sqlizdb_dev TO sqlizdb_dev_user1@'%';
GRANT sqlizdb_read TO sqlizdb_read_user1@'%';
GRANT sqlizdb_read, sqlizdb_write
TO sqlizdb_write_user1@'%', sqlizdb_write_user2@'%';

要验证角色分配，请使用该 SHOW GRANTS 语句作为以下实例：

SHOW GRANTS FOR sqlizdb_dev_user1@'%';

+----------------------------------------------------+
| Grants for sqlizdb_dev_user1@%                     |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `sqlizdb_dev_user1`@`%`      |
| GRANT `sqlizdb_dev`@`%` TO `sqlizdb_dev_user1`@`%` |
+----------------------------------------------------+

如您所见，该语句只是返回授予的角色。要显示角色代表的权限，请使用带有授予角色名称的 USING 子句，如下所示：

SHOW GRANTS FOR sqlizdb_write_user1@'%'
USING sqlizdb_write;

该语句返回以下输出：

+---------------------------------------------------------------------------+
| Grants for sqlizdb_write_user1@%                                          |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sqlizdb_write_user1`@`%`                           |
| GRANT INSERT, UPDATE, DELETE ON `sqlizdb`.* TO `sqlizdb_write_user1`@`%`  |
| GRANT `sqlizdb_read`@`%`,`sqlizdb_write`@`%` TO `sqlizdb_write_user1`@`%` |
+---------------------------------------------------------------------------+

设置默认角色

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

mysql -u sqlizdb_read_user1 -p
Enter password: ***********

USE sqlizdb;

该语句发出以下错误消息：

ERROR 1044 (42000): Access denied for user 'sqlizdb_read_user1'@'%' to database 'sqlizdb'

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

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

SELECT current_role();

+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+

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

以下语句为 sqlizdb_read_user1@'%' 帐户的所有分配角色设置默认值。

SET DEFAULT ROLE ALL TO sqlizdb_read_user1@'%';

现在，如果您使用 sqlizdb_read_user1 用户帐户连接到 MySQL 数据库服务器并调用该 CURRENT_ROLE() 函数，您将看到 sqlizdb_read_user1 用户帐户的默认角色。

+--------------------+
| current_role()     |
+--------------------+
| `sqlizdb_read`@`%` |
+--------------------+

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

SELECT COUNT(*) FROM test_table;

+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

执行一下 DELETE 语句：

DELETE FROM test_table;

ERROR 1142 (42000): DELETE command denied to user 'sqlizdb_read_user1'@'%' for table 'test_table'

当我们发出 DELETE 语句时，MySQL 发出错误，因为 sqlizdb_read 角色只有读取权限。

设置活跃角色

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

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

SET ROLE NONE;

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

SET ROLE ALL;

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

SET ROLE DEFAULT;

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

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

以下步骤用来你您创建另一个 sqlizdb 数据库的开发者帐户：

  1. 创建新用户帐户：

    CREATE USER sqlizdb_dev_user2@'%'
IDENTIFIED BY 'SqLiZ9879123!';

  2. 将权限从 sqlizdb_dev_user1 用户帐户复制到 sqlizdb_dev_user2 用户帐户，如下所示：

    GRANT sqlizdb_dev_user1@'%'
TO sqlizdb_dev_user2@'%';

结论

在 MySQL 中，您可以使用 MySQL 角色来更轻松地管理用户帐户的权限。