`MySQL`的`用户`权限`模型`：`GRANT`表`和`角色`（`Role`）的`管理`。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 用户权限模型：GRANT 表和角色管理

大家好，今天我们来深入探讨 MySQL 的用户权限模型，重点关注 GRANT 表和角色 (Role) 的管理。理解这些概念对于构建安全可靠的数据库应用程序至关重要。

1. 用户权限模型概述

MySQL 的权限模型基于两个核心概念：

用户（User）: 数据库的用户身份，用于标识谁可以访问数据库。用户由用户名和主机名组成，例如 ‘user’@’localhost’ 或 ‘admin’@’%’, 后者表示允许来自任何主机的 ‘admin’ 用户。
权限（Privilege）: 允许用户执行的特定操作。例如，SELECT, INSERT, UPDATE, DELETE 等数据操作权限，以及 CREATE, DROP, ALTER 等数据库对象管理权限。

MySQL 使用一组系统表来存储用户和他们的权限。其中最重要的是 mysql.user, mysql.db, mysql.tables_priv, mysql.columns_priv, 和 mysql.procs_priv。这些表存储了全局权限，数据库级别权限，表级别权限，列级别权限以及存储过程/函数级别的权限。

2. GRANT 语句：权限授予的核心

GRANT 语句是授予用户权限的主要方式。其基本语法如下：

GRANT privilege_list ON database.table TO 'user'@'host' [WITH GRANT OPTION];

privilege_list: 要授予的权限列表，多个权限用逗号分隔。例如 SELECT, INSERT, UPDATE。可以使用 ALL PRIVILEGES 授予所有权限。
database.table: 权限适用的数据库和表。可以使用 *.* 表示所有数据库的所有表，使用 database.* 表示特定数据库的所有表。
'user'@'host': 要授予权限的用户。
WITH GRANT OPTION: 如果指定此选项，被授予权限的用户可以将他们获得的权限再授予其他用户。这需要谨慎使用，因为它可能导致权限扩散。

示例：

-- 授予 'readonly'@'localhost' 用户对 'mydatabase' 数据库中所有表的 SELECT 权限
GRANT SELECT ON mydatabase.* TO 'readonly'@'localhost';

-- 授予 'webapp'@'%' 用户对 'webappdb.users' 表的 SELECT, INSERT, UPDATE 权限
GRANT SELECT, INSERT, UPDATE ON webappdb.users TO 'webapp'@'%';

-- 授予 'admin'@'localhost' 用户对所有数据库的所有表的 ALL PRIVILEGES 权限，并允许其授予其他用户权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

3. REVOKE 语句：权限撤销

REVOKE 语句用于撤销用户之前授予的权限。其语法与 GRANT 语句类似：

REVOKE privilege_list ON database.table FROM 'user'@'host';

示例：

-- 撤销 'readonly'@'localhost' 用户对 'mydatabase' 数据库中所有表的 SELECT 权限
REVOKE SELECT ON mydatabase.* FROM 'readonly'@'localhost';

-- 撤销 'webapp'@'%' 用户对 'webappdb.users' 表的 INSERT 权限
REVOKE INSERT ON webappdb.users FROM 'webapp'@'%';

-- 撤销 'admin'@'localhost' 用户授予其他用户的 GRANT OPTION，但保留其自身的权限。
REVOKE GRANT OPTION ON *.* FROM 'admin'@'localhost';

4. GRANT 表的内部结构

理解 GRANT 表的内部结构有助于更好地理解权限模型。以下是几个关键的系统表：

mysql.user: 存储全局用户权限。每行代表一个用户，包含用户名、主机名以及全局级别的权限标志（例如 Select_priv, Insert_priv, Update_priv 等）。

字段名	数据类型	描述
Host	char(60)	用户的主机名。’%’ 表示允许来自任何主机。
User	char(32)	用户名。
Password	char(41)	加密后的密码。
Select_priv	enum(‘N’,’Y’)	用户是否拥有全局 SELECT 权限。
Insert_priv	enum(‘N’,’Y’)	用户是否拥有全局 INSERT 权限。
…	…	其他权限标志，例如 `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv` 等。
ssl_type	enum(”, ‘ANY’, ‘X509’, ‘SPECIFIED’)	SSL 连接类型。
ssl_cipher	BLOB	SSL 密码。
x509_issuer	BLOB	X.509 颁发者。
x509_subject	BLOB	X.509 主题。
max_questions	int(11)	每小时允许的最大查询数。
max_updates	int(11)	每小时允许的最大更新数。
max_connections	int(11)	每小时允许的最大连接数。
max_user_connections	int(11)	用户允许的最大并发连接数。
plugin	char(64)	身份验证插件。
authentication_string	TEXT	身份验证字符串。

mysql.db: 存储数据库级别的权限。每行代表一个数据库的权限设置，包含主机名、数据库名以及数据库级别的权限标志。

字段名	数据类型	描述
Host	char(60)	用户的主机名。’%’ 表示允许来自任何主机。
Db	char(64)	数据库名。
User	char(32)	用户名。
Select_priv	enum(‘N’,’Y’)	用户是否拥有数据库级别的 SELECT 权限。
Insert_priv	enum(‘N’,’Y’)	用户是否拥有数据库级别的 INSERT 权限。
…	…	其他权限标志，例如 `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv` 等。
Grant_priv	enum(‘N’,’Y’)	用户是否拥有数据库级别的 GRANT OPTION 权限。

mysql.tables_priv: 存储表级别的权限。每行代表一个表的权限设置，包含主机名、数据库名、表名以及表级别的权限类型。

字段名	数据类型	描述
Host	char(60)	用户的主机名。’%’ 表示允许来自任何主机。
Db	char(64)	数据库名。
User	char(32)	用户名。
Table_name	char(64)	表名。
Grantor	char(77)	授予权限的用户。
Timestamp	timestamp	权限授予的时间戳。
Table_priv	set(‘Select’,’Insert’,’Update’,’Delete’,’Create’,’Drop’,’Grant’,’References’,’Index’,’Alter’,’Create View’,’Show view’,’Trigger’,’Create Routine’,’Alter Routine’,’Execute’,’Event’,’Lock Tables’,’Write File’,’Reload’,’Shutdown’,’Process’,’File’,’Super’,’Create TableSpace’,’Repl Client’,’Repl Slave’,’Show DB’,’Create User’,’All’)	表级别的权限列表。
Column_priv	set(‘Select’,’Insert’,’Update’,’References’)	列级别的权限列表（已弃用）。

mysql.columns_priv: 存储列级别的权限（不常用，推荐使用视图或存储过程来实现列级别的权限控制）。

字段名	数据类型	描述
Host	char(60)	用户的主机名。’%’ 表示允许来自任何主机。
Db	char(64)	数据库名。
User	char(32)	用户名。
Table_name	char(64)	表名。
Column_name	char(64)	列名。
Timestamp	timestamp	权限授予的时间戳。
Column_priv	enum(‘Select’,’Insert’,’Update’,’References’)	列级别的权限。

mysql.procs_priv: 存储存储过程和函数的权限。

字段名	数据类型	描述
Host	char(60)	用户的主机名。’%’ 表示允许来自任何主机。
Db	char(64)	数据库名。
User	char(32)	用户名。
Routine_name	char(64)	存储过程或函数名。
Routine_type	enum(‘FUNCTION’,’PROCEDURE’)	存储过程或函数类型。
Grantor	char(77)	授予权限的用户。
Timestamp	timestamp	权限授予的时间戳。
Proc_priv	set(‘Execute’,’Alter Routine’,’Grant’)	存储过程或函数级别的权限列表。

重要提示： 修改这些系统表不应该直接进行。应该使用 GRANT 和 REVOKE 语句来管理权限，MySQL 会自动更新这些表。直接修改这些表可能会导致数据库损坏或权限不一致。

5. 权限检查流程

当用户尝试执行数据库操作时，MySQL 会按照以下顺序进行权限检查：

全局权限 (mysql.user): 首先检查用户是否拥有全局级别的权限。如果用户拥有足够的全局权限，则允许操作。
数据库权限 (mysql.db): 如果没有足够的全局权限，则检查用户是否拥有对目标数据库的权限。
表权限 (mysql.tables_priv): 如果数据库权限不足，则检查用户是否拥有对目标表的权限。
列权限 (mysql.columns_priv): 如果表权限不足，则检查用户是否拥有对目标列的权限。 (不常用, 通常通过视图或者存储过程进行控制)。
存储过程/函数权限 (mysql.procs_priv): 如果访问的是存储过程或函数，则检查用户是否拥有执行权限。

MySQL 会选择最具体的权限设置。例如，如果用户拥有全局 SELECT 权限，但被显式拒绝了对特定表的 SELECT 权限，则用户将无法访问该表。

6. 角色 (Role) 的引入

在 MySQL 8.0 中，引入了角色 (Role) 的概念，它是一组权限的集合。使用角色可以简化权限管理，提高效率。

创建角色：

CREATE ROLE 'developer'@'%';

授予角色权限：

GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'developer'@'%';

将角色授予用户：

GRANT 'developer'@'%' TO 'webapp'@'localhost';

激活角色：

用户需要激活角色才能使用角色授予的权限。可以通过 SET ROLE 语句激活角色：

SET ROLE 'developer'@'%';

也可以同时激活多个角色：

SET ROLE 'developer'@'%', 'administrator'@'localhost';

激活 ALL 角色：

SET ROLE ALL;

激活 DEFAULT 角色（用户默认拥有的角色）：

SET ROLE DEFAULT;

撤销角色：

REVOKE 'developer'@'%' FROM 'webapp'@'localhost';

删除角色：

DROP ROLE 'developer'@'%';

使用角色带来的优势：

简化权限管理: 可以将一组常用的权限打包到一个角色中，然后将角色授予多个用户，避免重复授予权限。
提高安全性: 可以更方便地撤销用户的权限，只需要撤销角色即可。
易于维护: 当需要修改权限时，只需要修改角色的权限，所有拥有该角色的用户都会自动获得新的权限。

7. 最佳实践和安全建议

最小权限原则: 只授予用户完成其工作所需的最小权限。避免授予 ALL PRIVILEGES。
使用角色管理权限: 尽可能使用角色来管理权限，简化管理，提高安全性。
定期审查权限: 定期审查用户的权限，确保权限设置仍然合理。
避免直接修改系统表: 始终使用 GRANT 和 REVOKE 语句来管理权限。
使用强密码: 为所有用户设置强密码，并定期更换密码。
限制主机访问: 只允许来自特定主机的用户访问数据库。避免使用 'user'@'%'，除非确实需要允许来自任何主机的用户访问。
使用 SSL 连接: 使用 SSL 加密数据库连接，防止数据在传输过程中被窃取。
启用审计日志: 启用审计日志，记录所有数据库操作，便于安全审计和故障排除。
避免在应用程序中存储数据库密码: 使用环境变量或配置文件来存储数据库密码，并确保这些文件受到保护。
参数化查询: 使用参数化查询来防止 SQL 注入攻击。
定期更新 MySQL: 定期更新 MySQL 到最新版本，以获取最新的安全补丁。

8. 使用示例演示

假设我们有一个名为 ecommerce 的数据库，其中包含 users, products, orders 三个表。我们需要创建以下用户角色：

product_manager: 负责管理商品信息，拥有 products 表的 SELECT, INSERT, UPDATE 权限。
order_processor: 负责处理订单信息，拥有 orders 表的 SELECT, UPDATE 权限。
customer_service: 负责处理客户服务，拥有 users 表的 SELECT 权限，以及 orders 表的 SELECT 权限。

步骤 1: 创建角色

CREATE ROLE 'product_manager'@'%';
CREATE ROLE 'order_processor'@'%';
CREATE ROLE 'customer_service'@'%';

步骤 2: 授予角色权限

GRANT SELECT, INSERT, UPDATE ON ecommerce.products TO 'product_manager'@'%';
GRANT SELECT, UPDATE ON ecommerce.orders TO 'order_processor'@'%';
GRANT SELECT ON ecommerce.users TO 'customer_service'@'%';
GRANT SELECT ON ecommerce.orders TO 'customer_service'@'%';

步骤 3: 创建用户并授予角色

CREATE USER 'product_admin'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'order_clerk'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'support_agent'@'localhost' IDENTIFIED BY 'password';

GRANT 'product_manager'@'%' TO 'product_admin'@'localhost';
GRANT 'order_processor'@'%' TO 'order_clerk'@'localhost';
GRANT 'customer_service'@'%' TO 'support_agent'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

步骤 4: 用户登录并激活角色

-- 以 'product_admin'@'localhost' 身份登录
mysql -u product_admin -p -h localhost

-- 激活角色
SET ROLE 'product_manager'@'%';

-- 现在可以对 ecommerce.products 表进行 SELECT, INSERT, UPDATE 操作
SELECT * FROM ecommerce.products;

9. 权限问题排查

当用户遇到权限问题时，可以采取以下步骤进行排查：

确认用户身份: 使用 SELECT USER(); 确认当前登录的用户。
检查用户权限: 使用 SHOW GRANTS FOR 'user'@'host'; 查看用户的权限。
检查对象权限: 确认用户尝试访问的对象 (数据库, 表, 列, 存储过程) 是否存在，以及用户是否拥有相应的权限。
检查角色: 如果用户使用了角色，确认角色是否已激活，以及角色是否拥有足够的权限。
检查权限覆盖: 检查是否存在更具体的 REVOKE 语句覆盖了 GRANT 语句授予的权限。
刷新权限: 如果权限修改后没有生效，尝试执行 FLUSH PRIVILEGES; 刷新权限。
查看错误日志: 查看 MySQL 的错误日志，可能会有权限相关的错误信息。

10. 总结概括

本次讲座深入探讨了 MySQL 的用户权限模型，重点介绍了 GRANT 表的结构，以及如何使用 GRANT 和 REVOKE 语句来管理用户权限，并详细讲解了 Role 角色的使用。理解这些知识点对于确保数据库安全至关重要，希望大家在实际应用中能够灵活运用。

MySQL 用户权限模型：GRANT 表和角色管理

发表回复 取消回复

发表回复取消回复