深入 `MySQL` `权限`系统的`底层`实现：`user`、`role` 和`grant`表的`物理`结构。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，开始今天的讲座。

今天我们来深入探讨 MySQL 权限系统的底层实现，重点关注 user、role 和 grant 这三个核心概念及其对应的物理表结构。理解这些表的结构和它们之间的关系，对于诊断权限问题、定制权限管理策略以及进行安全审计都至关重要。

一、MySQL 权限体系概述

MySQL 的权限体系是一个分层结构，从全局到数据库，再到表和列，提供了细粒度的访问控制。核心概念包括：

User (用户): 代表一个连接到 MySQL 服务器的身份。用户由用户名和主机名组成，例如 'user1'@'localhost'。
Role (角色): 是一组权限的集合。可以将角色授予用户，从而简化权限管理，避免重复授予相同的权限给多个用户。
Privilege (权限): 允许用户执行特定操作，例如 SELECT、INSERT、UPDATE、DELETE 等。
Grant (授权): 将权限授予用户或角色的行为。

这些概念在底层通过一系列系统表来实现，这些表存储了用户、角色、权限以及授权关系。

二、核心系统表：user、role_mapping、global_grants 等

在 MySQL 8.0 中，权限相关的系统表主要位于 mysql 数据库中。我们将重点介绍以下几个关键表：

mysql.user: 存储用户账户信息和全局权限。
mysql.role_mapping: 存储用户与角色之间的映射关系。
mysql.global_grants: 存储全局级别的权限授予信息。
mysql.db: 存储数据库级别的权限授予信息。
mysql.tables_priv: 存储表级别的权限授予信息。
mysql.columns_priv: 存储列级别的权限授予信息。
mysql.procs_priv: 存储存储过程和函数级别的权限授予信息。
mysql.proxies_priv: 存储代理用户相关的权限信息。

我们主要关注 user、role_mapping 和 global_grants，因为它们是理解用户、角色和全局授权的基础。其他的表结构与它们类似，只是作用域不同。

1. mysql.user 表

mysql.user 表是权限系统的核心，存储了用户账户信息和全局权限。

表结构示例:

CREATE TABLE `user` (
  `Host` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Host Name',
  `User` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'User Name',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '' COMMENT 'Password',
  `Select_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Select privilege',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Insert privilege',
  `Update_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Update privilege',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Delete privilege',
  `Create_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Create privilege',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Drop privilege',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Reload privilege',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Shutdown privilege',
  `Process_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Process privilege',
  `File_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'File privilege',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Grant privilege',
  `References_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'References privilege',
  `Index_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Index privilege',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Alter privilege',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Show db privilege',
  `Super_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Super privilege',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Create tmp table privilege',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Lock tables privilege',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Execute privilege',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Repl slave privilege',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Repl client privilege',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Create view privilege',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Show view privilege',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Create routine privilege',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Alter routine privilege',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Create user privilege',
  `Event_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Event privilege',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Trigger privilege',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Create tablespace privilege',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'SSL Type',
  `ssl_cipher` blob NULL COMMENT 'SSL Cipher',
  `x509_issuer` blob NULL COMMENT 'X509 Issuer',
  `x509_subject` blob NULL COMMENT 'X509 Subject',
  `max_questions` int unsigned NOT NULL DEFAULT '0' COMMENT 'Max questions',
  `max_updates` int unsigned NOT NULL DEFAULT '0' COMMENT 'Max updates',
  `max_connections` int unsigned NOT NULL DEFAULT '0' COMMENT 'Max connections',
  `max_user_connections` int unsigned NOT NULL DEFAULT '0' COMMENT 'Max user connections',
  `plugin` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Authentication plugin',
  `authentication_string` text CHARACTER SET latin1 COLLATE latin1_bin NULL COMMENT 'Authentication string',
  `password_requires_current` enum('Y','N') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'N' COMMENT 'Indicates whether old password must be provided on password change',
  `account_locked` enum('Y','N') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Is account locked',
  `password_expired` enum('Y','N') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT 'Is password expired',
  `password_last_changed` timestamp NULL DEFAULT NULL COMMENT 'Password last changed time',
  `password_lifetime` smallint unsigned NULL DEFAULT NULL COMMENT 'Password lifetime in days, NULL means unlimited',
  `default_role` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Default role',
  `max_statement_time` decimal(12,6) NOT NULL DEFAULT '0.000000' COMMENT 'Max statement time in seconds',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='Users and global privileges';

关键字段解释:

Host: 用户连接的主机名或 IP 地址。 '%' 表示允许从任何主机连接。
User: 用户名。
Password: 加密后的用户密码。在 MySQL 8.0 中，密码存储方式更加安全，推荐使用 caching_sha2_password 插件。
Select_priv, Insert_priv, Update_priv, Delete_priv, … : 一系列 enum('N','Y') 字段，表示用户是否拥有对应的全局权限。'Y' 表示拥有，'N' 表示没有。这些权限是全局级别的，影响用户对所有数据库的操作。
plugin: 用户使用的身份验证插件。
authentication_string: 身份验证字符串，通常是加密后的密码。
default_role: 用户的默认角色。
account_locked: 账户是否被锁定。
password_expired: 密码是否过期。

示例查询:

SELECT Host, User, Select_priv, Insert_priv FROM mysql.user WHERE User = 'testuser';

这个查询会返回 testuser 的主机名、用户名、全局 SELECT 权限和全局 INSERT 权限。

2. mysql.role_mapping 表

mysql.role_mapping 表存储了用户与角色之间的映射关系。它定义了哪些用户被授予了哪些角色。

表结构示例:

CREATE TABLE `role_mapping` (
  `User` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'User Name',
  `Host` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Host Name',
  `Role` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Role Name',
  `Role_host` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Role Host',
  PRIMARY KEY (`User`,`Host`,`Role`,`Role_host`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='Mapping between roles and users';

关键字段解释:

User: 用户名。
Host: 用户连接的主机名。
Role: 角色名。
Role_host: 角色定义的主机名。角色也需要指定主机名，这允许创建具有相同名称但在不同主机上有效的角色。

示例查询:

SELECT User, Host, Role, Role_host FROM mysql.role_mapping WHERE User = 'testuser';

这个查询会返回 testuser 被授予的所有角色及其对应的主机名。

3. mysql.global_grants 表

mysql.global_grants 表存储了全局级别的权限授予信息，用于支持角色。它允许将权限直接授予角色，而无需单独授予每个用户。

表结构示例:

CREATE TABLE `global_grants` (
  `User` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'User name',
  `Host` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Host name',
  `Priv` char(31) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'Privilege name',
  `Create_options` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'Create options',
  `Grantor` char(77) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Grantor',
  `Grantor_Host` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Grantor host',
  `Grantor_User` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Grantor user',
  PRIMARY KEY (`User`,`Host`,`Priv`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='Global privileges';

关键字段解释:

User: 用户名或角色名。
Host: 主机名。
Priv: 权限名称，例如 'SELECT'、'INSERT' 等。
Grantor: 授予权限的用户。
Grantor_Host: 授予权限的用户的主机。
Grantor_User: 授予权限的用户名。

示例查询:

SELECT User, Host, Priv FROM mysql.global_grants WHERE User = 'my_role';

这个查询会返回角色 my_role 被授予的所有全局权限。

三、权限授予流程和系统表更新

当我们使用 GRANT 语句授予权限时，MySQL 会更新相应的系统表。例如：

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON database1.* TO 'testuser'@'localhost';
GRANT INSERT, UPDATE ON database2.table1 TO 'testuser'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

CREATE ROLE 'developer'@'%';
GRANT SELECT, INSERT ON database3.* TO 'developer'@'%';
GRANT 'developer'@'%' TO 'testuser'@'localhost';
SET DEFAULT ROLE 'developer'@'%' FOR 'testuser'@'localhost';

这些 GRANT 语句会导致以下系统表的更新：

创建用户 'testuser'@'localhost' 时，会在 mysql.user 表中添加一行，并设置初始密码。
授予 SELECT 权限后，会在 mysql.db 表中添加或更新一行，记录 'testuser'@'localhost' 在 database1 上的 SELECT 权限。
授予 INSERT 和 UPDATE 权限后，会在 mysql.tables_priv 表中添加或更新一行，记录 'testuser'@'localhost' 在 database2.table1 上的 INSERT 和 UPDATE 权限。
授予 ALL PRIVILEGES 权限后，会在 mysql.user 表中添加一行，设置 'admin'@'%' 的所有全局权限为 'Y'。
创建角色 'developer'@'%后，会在内部创建一个代表角色的用户，但不在mysql.user表中直接体现。
授予角色'developer'@'% 权限后，会在mysql.db，mysql.tables_priv等表中添加相应的记录，记录 'developer'@'% 在 database3上的权限。
将角色授予用户 testuser'@'localhost'后，会在 mysql.role_mapping 表中添加一行，记录 testuser 被授予了 developer 角色。
设置默认角色后，会更新mysql.user表的default_role字段。

重要提示: 在修改权限之后，通常需要执行 FLUSH PRIVILEGES 语句，以强制服务器重新加载权限表。在 MySQL 8.0 中，某些权限更改会自动生效，无需手动刷新，但为了确保一致性，建议在修改权限后执行 FLUSH PRIVILEGES。

四、权限检查流程

当用户尝试执行某个操作时，MySQL 会进行权限检查。权限检查流程大致如下：

身份验证: 首先，MySQL 验证用户的身份。
权限查找: MySQL 根据用户的用户名、主机名和当前数据库，查找相关的权限信息。它会依次检查以下表：
- mysql.user: 查找全局权限。
- mysql.db: 查找数据库级别的权限。
- mysql.tables_priv: 查找表级别的权限。
- mysql.columns_priv: 查找列级别的权限。
- 如果启用了角色，还会检查 mysql.role_mapping 和 mysql.global_grants 以确定用户所属的角色及其拥有的权限。
权限评估: MySQL 将查找到的权限信息进行合并和评估。如果用户拥有执行该操作所需的权限，则允许执行；否则，拒绝执行并返回权限错误。
角色激活： 如果用户拥有多个角色，并且没有设置默认角色，则用户需要手动激活某个角色才能使用该角色的权限。

示例代码：模拟权限检查

以下代码片段使用 Python 模拟了简化的权限检查过程（仅为演示，不适用于生产环境）：

def check_permission(user, host, database, table, privilege, user_table, db_table, tables_priv_table, role_mapping_table, global_grants_table):
    """
    模拟权限检查过程.
    """

    # 1. 查找全局权限
    user_record = next((row for row in user_table if row['User'] == user and row['Host'] == host), None)
    if user_record and user_record.get(privilege + '_priv') == 'Y':
        print(f"用户 {user}@{host} 拥有全局 {privilege} 权限.")
        return True

    # 2. 查找数据库级别权限
    db_record = next((row for row in db_table if row['User'] == user and row['Host'] == host and row['Db'] == database), None)
    if db_record and db_record.get(privilege + '_priv') == 'Y':
        print(f"用户 {user}@{host} 在数据库 {database} 上拥有 {privilege} 权限.")
        return True

    # 3. 查找表级别权限
    tables_priv_record = next((row for row in tables_priv_table if row['User'] == user and row['Host'] == host and row['Db'] == database and row['Table_name'] == table and row['Privilege'] == privilege), None)
    if tables_priv_record:
        print(f"用户 {user}@{host} 在表 {database}.{table} 上拥有 {privilege} 权限.")
        return True

    # 4. 检查角色
    role_mappings = [row for row in role_mapping_table if row['User'] == user and row['Host'] == host]
    for mapping in role_mappings:
        role = mapping['Role']
        role_host = mapping['Role_host']
        # 查找角色权限
        global_grant_record = next((row for row in global_grants_table if row['User'] == role and row['Host'] == role_host and row['Priv'] == privilege), None)
        if global_grant_record:
             print(f"用户 {user}@{host} 通过角色 {role}@{role_host} 拥有 {privilege} 权限.")
             return True

    print(f"用户 {user}@{host} 没有 {privilege} 权限访问 {database}.{table}.")
    return False

# 模拟数据
user_table = [
    {'Host': 'localhost', 'User': 'testuser', 'Select_priv': 'N', 'Insert_priv': 'N'},
    {'Host': '%', 'User': 'admin', 'Select_priv': 'Y', 'Insert_priv': 'Y'}
]

db_table = [
    {'Host': 'localhost', 'User': 'testuser', 'Db': 'database1', 'Select_priv': 'Y', 'Insert_priv': 'N'}
]

tables_priv_table = [
    {'Host': 'localhost', 'User': 'testuser', 'Db': 'database2', 'Table_name': 'table1', 'Privilege': 'INSERT'}
]

role_mapping_table = [
    {'Host': 'localhost', 'User': 'testuser', 'Role': 'developer', 'Role_host': '%'}
]

global_grants_table = [
    {'Host': '%', 'User': 'developer', 'Priv': 'SELECT'}
]

# 测试
user = 'testuser'
host = 'localhost'
database = 'database1'
table = 'table1'

check_permission(user, host, database, table, 'SELECT', user_table, db_table, tables_priv_table, role_mapping_table, global_grants_table)  # 拥有SELECT权限
check_permission(user, host, database, table, 'INSERT', user_table, db_table, tables_priv_table, role_mapping_table, global_grants_table)  # 没有INSERT权限

user = 'admin'
host = '%'
database = 'database1'
table = 'table1'

check_permission(user, host, database, table, 'SELECT', user_table, db_table, tables_priv_table, role_mapping_table, global_grants_table) # 拥有SELECT权限

五、安全最佳实践

最小权限原则: 只授予用户执行其工作所需的最小权限。
使用角色: 使用角色来简化权限管理，避免重复授予相同的权限。
定期审计: 定期审计权限设置，确保权限策略的有效性。
强密码: 使用强密码，并定期更换。
限制主机访问: 限制用户可以连接的主机，避免未经授权的访问。
监控登录尝试: 监控登录尝试，及时发现异常行为。
启用审计日志: 启用审计日志，记录所有权限相关的操作，以便进行安全分析。

六、总结

理解 MySQL 权限系统的底层实现对于保障数据库安全至关重要。通过深入研究 user、role_mapping 和 global_grants 等系统表的结构和权限检查流程，我们可以更好地管理用户权限、诊断权限问题和实施安全策略。

今天的讲座就到这里。希望大家对MySQL的权限系统有了更深入的理解。

核心概念和表结构：

回顾了用户、角色、权限和授权的概念，以及 mysql.user、mysql.role_mapping 和 mysql.global_grants 等核心系统表的结构和作用。

权限授予流程和权限检查：

讲解了 GRANT 语句如何更新系统表，以及 MySQL 如何进行权限检查以确定用户是否可以执行特定操作。

发表回复 取消回复

发表回复取消回复