MySQL架构与底层原理之：`MySQL`的`SQL`语句执行权限：`Grant`和`Revoke`的底层实现。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL的SQL语句执行权限：Grant和Revoke的底层实现

大家好，今天我们深入探讨MySQL中SQL语句执行权限的控制机制，重点解析GRANT和REVOKE语句的底层实现。权限管理是数据库安全的核心组成部分，理解其原理对于构建安全可靠的数据库系统至关重要。

1. 权限控制概述

MySQL的权限控制系统旨在限制用户对数据库对象（如数据库、表、视图、存储过程等）的操作。这种控制基于用户身份和授予的权限。权限可以授予用户执行特定的SQL语句，例如SELECT、INSERT、UPDATE、DELETE等。

MySQL的权限层级结构如下：

全局权限 (Global Privileges): 影响所有数据库。在mysql.user表中管理。
数据库权限 (Database Privileges): 影响特定的数据库。在mysql.db表中管理。
表权限 (Table Privileges): 影响特定的表。在mysql.tables_priv表中管理。
列权限 (Column Privileges): 影响特定的列。在mysql.columns_priv表中管理。
存储过程权限 (Stored Procedure Privileges): 影响特定的存储过程。在mysql.procs_priv表中管理。
代理用户权限 (Proxy User Privileges): 允许一个用户模拟另一个用户的身份。在mysql.proxies_priv表中管理。

这些权限信息都存储在MySQL的系统数据库mysql的多个授权表中。服务器启动时，会将这些授权表加载到内存中，形成权限缓存，以便快速进行权限验证。

2. `GRANT`语句详解

GRANT语句用于授予用户特定的权限。其基本语法如下：

GRANT privilege_type [(column_list)]
ON {table_name | database_name.* | *.*}
TO 'user'@'host'
[IDENTIFIED BY 'password']
[WITH GRANT OPTION];

privilege_type: 要授予的权限类型，例如SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION等。可以使用 ALL PRIVILEGES 代表所有权限。
column_list: 如果权限针对特定的列，则在此处指定列名。
table_name | database_name.* | *.*: 指定权限的作用范围。 table_name针对特定表，database_name.*针对特定数据库的所有表，*.*针对所有数据库的所有表（全局权限）。
'user'@'host': 指定要授予权限的用户。 'user'是用户名，'host'是用户连接的主机。 'user'@'%' 代表用户可以从任何主机连接。
IDENTIFIED BY 'password': 如果用户不存在，则创建用户并设置密码。
WITH GRANT OPTION: 允许用户将自己拥有的权限授予其他用户。

示例：

授予用户'john'@'localhost'对数据库mydb的所有表的SELECT权限：
```
GRANT SELECT ON mydb.* TO 'john'@'localhost';
```
授予用户'jane'@'%'对表mydb.mytable的SELECT、INSERT和UPDATE权限：
```
GRANT SELECT, INSERT, UPDATE ON mydb.mytable TO 'jane'@'%';
```

创建用户'admin'@'%'并授予所有权限，允许其从任何主机连接：

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

GRANT语句的底层实现：

当执行GRANT语句时，MySQL服务器会执行以下步骤：

解析语句: 解析GRANT语句，提取权限类型、作用范围、用户名和主机等信息。
查找用户: 在mysql.user表中查找用户是否存在。如果用户不存在，并且语句中包含IDENTIFIED BY子句，则创建新用户。
更新授权表: 根据权限的作用范围，更新相应的授权表：
- 如果权限是全局权限（*.*），则更新mysql.user表。
- 如果权限是数据库权限（database_name.*），则更新mysql.db表。
- 如果权限是表权限（table_name），则更新mysql.tables_priv表。
- 如果权限是列权限，则更新mysql.columns_priv表。
- 如果权限是存储过程权限，则更新mysql.procs_priv表。
- 如果权限是代理用户权限，则更新mysql.proxies_priv表。
刷新权限: 执行FLUSH PRIVILEGES语句，将授权表的更改加载到内存中的权限缓存中。如果没有执行 FLUSH PRIVILEGES，权限的更改可能不会立即生效。

授权表结构举例 (简化版):

表名	主要字段	说明
`mysql.user`	`Host`, `User`, `Select_priv`, `Insert_priv`, …, `Grant_priv`	全局用户权限，例如SELECT权限、INSERT权限、以及GRANT OPTION权限等。
`mysql.db`	`Host`, `Db`, `User`, `Select_priv`, `Insert_priv`, …	数据库权限，例如SELECT权限、INSERT权限等，针对特定数据库。
`mysql.tables_priv`	`Host`, `Db`, `User`, `Table_name`, `Grantor`, `Timestamp`, `Table_priv`, `Column_priv`	表权限，存储了用户对特定表的权限信息。
`mysql.columns_priv`	`Host`, `Db`, `User`, `Table_name`, `Column_name`, `Timestamp`, `Column_priv`	列权限，存储了用户对特定表的特定列的权限信息。

代码示例 (模拟 GRANT 操作)：

以下代码只是为了说明 GRANT 操作的底层逻辑，并非 MySQL 实际源码。

class MySQLGrantSimulator:
    def __init__(self):
        self.user_table = {} # {('user', 'host'): {'Select_priv': 'N', 'Insert_priv': 'N', ...}}
        self.db_table = {} # {('user', 'host', 'db'): {'Select_priv': 'N', 'Insert_priv': 'N', ...}}
        self.tables_priv_table = {} # {('user', 'host', 'db', 'table'): {'Select_priv': 'N', 'Insert_priv': 'N', ...}}

    def grant_privilege(self, user, host, database, table, privileges):
        """
        模拟 GRANT 操作。
        """
        user_key = (user, host)
        db_key = (user, host, database)
        table_key = (user, host, database, table)

        # 1. 检查用户是否存在，如果不存在则创建
        if user_key not in self.user_table:
            self.user_table[user_key] = {'Select_priv': 'N', 'Insert_priv': 'N', 'Update_priv': 'N', 'Delete_priv': 'N'}  # 初始化权限

        # 2. 更新授权表
        if database == '*': # 全局权限
            for priv in privileges:
                if priv in self.user_table[user_key]:
                    self.user_table[user_key][priv] = 'Y'
                else:
                    print(f"Warning: Privilege '{priv}' is not a valid global privilege.")
        elif table == '*': # 数据库权限
            if db_key not in self.db_table:
                self.db_table[db_key] = {'Select_priv': 'N', 'Insert_priv': 'N', 'Update_priv': 'N', 'Delete_priv': 'N'}  # 初始化权限
            for priv in privileges:
                if priv in self.db_table[db_key]:
                    self.db_table[db_key][priv] = 'Y'
                else:
                     print(f"Warning: Privilege '{priv}' is not a valid database privilege.")
        else:  # 表权限
            if table_key not in self.tables_priv_table:
                self.tables_priv_table[table_key] = {'Select_priv': 'N', 'Insert_priv': 'N', 'Update_priv': 'N', 'Delete_priv': 'N'}  # 初始化权限
            for priv in privileges:
                if priv in self.tables_priv_table[table_key]:
                    self.tables_priv_table[table_key][priv] = 'Y'
                else:
                     print(f"Warning: Privilege '{priv}' is not a valid table privilege.")

    def show_privileges(self, user, host, database, table):
        """
        显示用户的权限。
        """
        user_key = (user, host)
        db_key = (user, host, database)
        table_key = (user, host, database, table)

        print(f"Privileges for user '{user}@{host}' on {database}.{table}:")

        if database == '*':
            if user_key in self.user_table:
                print(self.user_table[user_key])
            else:
                print("No global privileges found for this user.")
        elif table == '*':
            if db_key in self.db_table:
                print(self.db_table[db_key])
            else:
                print("No database privileges found for this user on this database.")
        else:
            if table_key in self.tables_priv_table:
                print(self.tables_priv_table[table_key])
            else:
                print("No table privileges found for this user on this table.")

# 示例用法
simulator = MySQLGrantSimulator()

# 授予用户 'test'@'localhost' 对 mydb.mytable 的 SELECT 和 INSERT 权限
simulator.grant_privilege('test', 'localhost', 'mydb', 'mytable', ['Select_priv', 'Insert_priv'])
simulator.show_privileges('test', 'localhost', 'mydb', 'mytable')

# 授予用户 'test'@'localhost' 对所有数据库的 UPDATE 权限
simulator.grant_privilege('test', 'localhost', '*', '*', ['Update_priv'])
simulator.show_privileges('test', 'localhost', '*', '*')

3. `REVOKE`语句详解

REVOKE语句用于撤销用户之前授予的权限。其基本语法如下：

REVOKE privilege_type [(column_list)]
ON {table_name | database_name.* | *.*}
FROM 'user'@'host';

privilege_type: 要撤销的权限类型，例如SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION等。可以使用 ALL PRIVILEGES 代表所有权限。
column_list: 如果权限针对特定的列，则在此处指定列名。
table_name | database_name.* | *.*: 指定权限的作用范围。 table_name针对特定表，database_name.*针对特定数据库的所有表，*.*针对所有数据库的所有表（全局权限）。
'user'@'host': 指定要撤销权限的用户。 'user'是用户名，'host'是用户连接的主机。

示例：

撤销用户'john'@'localhost'对数据库mydb的所有表的SELECT权限：
```
REVOKE SELECT ON mydb.* FROM 'john'@'localhost';
```
撤销用户'jane'@'%'对表mydb.mytable的SELECT权限：
```
REVOKE SELECT ON mydb.mytable FROM 'jane'@'%';
```

撤销用户'admin'@'%'的所有权限：

REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'%';

REVOKE语句的底层实现：

当执行REVOKE语句时，MySQL服务器会执行以下步骤：

解析语句: 解析REVOKE语句，提取权限类型、作用范围、用户名和主机等信息。
查找用户: 在mysql.user表中查找用户是否存在。如果用户不存在，则报错。
更新授权表: 根据权限的作用范围，更新相应的授权表，将相应的权限标志设置为 'N' 或移除对应的记录：
- 如果权限是全局权限（*.*），则更新mysql.user表。
- 如果权限是数据库权限（database_name.*），则更新mysql.db表。
- 如果权限是表权限（table_name），则更新mysql.tables_priv表。
- 如果权限是列权限，则更新mysql.columns_priv表。
- 如果权限是存储过程权限，则更新mysql.procs_priv表。
- 如果权限是代理用户权限，则更新mysql.proxies_priv表。
刷新权限: 执行FLUSH PRIVILEGES语句，将授权表的更改加载到内存中的权限缓存中。

代码示例 (模拟 REVOKE 操作)：

class MySQLRevokeSimulator:
    def __init__(self):
        self.user_table = {} # {('user', 'host'): {'Select_priv': 'N', 'Insert_priv': 'N', ...}}
        self.db_table = {} # {('user', 'host', 'db'): {'Select_priv': 'N', 'Insert_priv': 'N', ...}}
        self.tables_priv_table = {} # {('user', 'host', 'db', 'table'): {'Select_priv': 'N', 'Insert_priv': 'N', ...}}

    def revoke_privilege(self, user, host, database, table, privileges):
        """
        模拟 REVOKE 操作。
        """
        user_key = (user, host)
        db_key = (user, host, database)
        table_key = (user, host, database, table)

        # 1. 检查用户是否存在
        if user_key not in self.user_table:
            print(f"Error: User '{user}@{host}' does not exist.")
            return

        # 2. 更新授权表
        if database == '*': # 全局权限
            for priv in privileges:
                if priv in self.user_table[user_key]:
                    self.user_table[user_key][priv] = 'N'
                else:
                    print(f"Warning: Privilege '{priv}' is not a valid global privilege.")
        elif table == '*': # 数据库权限
            if db_key not in self.db_table:
                print(f"Warning: No database privileges found for this user on this database.")
                return
            for priv in privileges:
                if priv in self.db_table[db_key]:
                    self.db_table[db_key][priv] = 'N'
                else:
                     print(f"Warning: Privilege '{priv}' is not a valid database privilege.")
        else:  # 表权限
            if table_key not in self.tables_priv_table:
                print(f"Warning: No table privileges found for this user on this table.")
                return
            for priv in privileges:
                if priv in self.tables_priv_table[table_key]:
                    self.tables_priv_table[table_key][priv] = 'N'
                else:
                     print(f"Warning: Privilege '{priv}' is not a valid table privilege.")

    def show_privileges(self, user, host, database, table):
        """
        显示用户的权限。
        """
        user_key = (user, host)
        db_key = (user, host, database)
        table_key = (user, host, database, table)

        print(f"Privileges for user '{user}@{host}' on {database}.{table}:")

        if database == '*':
            if user_key in self.user_table:
                print(self.user_table[user_key])
            else:
                print("No global privileges found for this user.")
        elif table == '*':
            if db_key in self.db_table:
                print(self.db_table[db_key])
            else:
                print("No database privileges found for this user on this database.")
        else:
            if table_key in self.tables_priv_table:
                print(self.tables_priv_table[table_key])
            else:
                print("No table privileges found for this user on this table.")

# 示例用法
simulator = MySQLRevokeSimulator()

# 先授予权限
simulator.user_table[('test', 'localhost')] = {'Select_priv': 'Y', 'Insert_priv': 'Y', 'Update_priv': 'N'}

# 撤销用户 'test'@'localhost' 对所有数据库的 UPDATE 权限
simulator.revoke_privilege('test', 'localhost', '*', '*', ['Update_priv'])
simulator.show_privileges('test', 'localhost', '*', '*')

# 撤销用户 'test'@'localhost' 对 mydb.mytable 的 INSERT 权限
simulator.tables_priv_table[('test', 'localhost', 'mydb', 'mytable')] = {'Select_priv': 'Y', 'Insert_priv': 'Y', 'Update_priv': 'N'}
simulator.revoke_privilege('test', 'localhost', 'mydb', 'mytable', ['Insert_priv'])
simulator.show_privileges('test', 'localhost', 'mydb', 'mytable')

4. 权限验证过程

当用户尝试执行SQL语句时，MySQL服务器会进行权限验证，以确定用户是否具有执行该语句的权限。权限验证的过程如下：

身份验证: 验证用户的身份（用户名和密码）。
权限查找: 根据用户身份和请求访问的数据库对象，在授权表中查找相关的权限信息。权限查找遵循一定的顺序，从最具体的权限开始查找，直到找到匹配的权限或者到达全局权限。例如，先查找表权限，再查找数据库权限，最后查找全局权限。
权限判断: 判断用户是否具有执行该SQL语句所需的权限。如果用户具有足够的权限，则允许执行该语句；否则，拒绝执行并返回权限错误。

5. `FLUSH PRIVILEGES`的重要性

FLUSH PRIVILEGES语句的作用是重新加载授权表到内存中。在执行GRANT或REVOKE语句后，必须执行FLUSH PRIVILEGES语句，才能使权限更改生效。如果没有执行FLUSH PRIVILEGES，服务器仍然使用旧的权限缓存，导致权限验证的结果不正确。

6. 安全建议

最小权限原则: 只授予用户执行其工作所需的最小权限。
定期审查权限: 定期审查用户的权限，确保权限设置仍然符合需求。
使用强密码: 为用户设置强密码，防止密码泄露。
限制远程访问: 限制用户从不信任的主机访问数据库。
监控数据库活动: 监控数据库活动，及时发现潜在的安全风险。

权限控制是数据库安全的关键

理解MySQL的GRANT和REVOKE语句的底层实现，以及权限验证的过程，是构建安全可靠数据库系统的基础。通过遵循最佳安全实践，可以有效地保护数据库免受未经授权的访问和恶意攻击。

授权表维护权限信息

MySQL的授权表存储了用户的权限信息，GRANT和REVOKE语句通过修改这些表来控制用户的访问权限。FLUSH PRIVILEGES语句确保权限更改及时生效。

MySQL的SQL语句执行权限：Grant和Revoke的底层实现

1. 权限控制概述

2. GRANT语句详解

3. REVOKE语句详解