Mysql权限表结构与原理_mysql权限系统底层实现机制剖析

蓮花仙者

发布时间：2025-08-20 09:52:01

652人浏览过

来源于php中文网

原创

MySQL权限系统通过user、db、tables_priv等层级权限表控制用户访问，遵循“拒绝优先、合并权限”原则，按user→db→tables_priv→columns_priv顺序检查权限。应遵循最小权限原则，限制主机访问，使用角色和强密码，定期审查权限，禁用匿名用户，启用审计日志，并通过CREATE USER、GRANT、REVOKE等SQL语句实现精细化权限管理，保障数据库安全。

mysql权限表结构与原理_mysql权限系统底层实现机制剖析

MySQL权限系统，简单来说，就是一套控制用户访问和操作数据库资源的机制。它通过一系列的权限表来管理用户可以执行的操作，比如读取数据、修改数据、创建表等等。理解这些权限表的结构和工作原理，对于保障数据库安全至关重要。

解决方案

MySQL的权限控制核心在于几个关键的系统表，它们存储了用户、主机以及对应的权限信息。主要包括：

```
user
```
表：存储全局级别的用户账户信息和权限。
```
db
```
表：存储数据库级别的权限信息。
```
tables_priv
```
表：存储表级别的权限信息。
```
columns_priv
```
表：存储列级别的权限信息。
```
procs_priv
```
表：存储存储过程和函数的权限信息。

这些表之间的关系是层层递进的。MySQL首先检查

user

表中的全局权限，然后检查

db

表中的数据库权限，再依次检查更细粒度的表级别和列级别权限。这种层级结构允许管理员灵活地分配和管理权限。

每个权限表都有一些关键字段：

```
Host
```
：允许连接的主机。
```
%
```
表示允许所有主机连接。
```
user
```
：用户名。
```
db
```
：数据库名（
```
db
```
表）。
```
Table_name
```
：表名（
```
tables_priv
```
表）。
权限字段（例如
```
Select_priv
```
，
```
Insert_priv
```
，
```
Update_priv
```
，
```
Delete_priv
```
，
```
Create_priv
```
等）：这些字段的值为
```
'Y'
```
或
```
'N'
```
，表示是否拥有相应的权限。

当用户尝试执行某个操作时，MySQL会根据用户的身份、连接的主机以及要操作的数据库对象，在这些权限表中进行查找，判断用户是否拥有执行该操作的权限。

举个例子，假设用户

'testuser'@'%'

想要查询数据库

'testdb'

中的表

'users'

。MySQL会按照以下步骤进行权限检查：

检查
```
user
```
表中
```
Host
```
为
```
'%'
```
，
```
user
```
为
```
'testuser'
```
的记录，查看是否有全局的
```
SELECT
```
权限。
如果没有全局
```
SELECT
```
权限，则检查
```
db
```
表中
```
Host
```
为
```
'%'
```
，
```
user
```
为
```
'testuser'
```
，
```
db
```
为
```
'testdb'
```
的记录，查看是否有
```
SELECT
```
权限。
如果数据库级别也没有
```
SELECT
```
权限，则检查
```
tables_priv
```
表中
```
Host
```
为
```
'%'
```
，
```
user
```
为
```
'testuser'
```
，
```
db
```
为
```
'testdb'
```
，
```
Table_name
```
为
```
'users'
```
的记录，查看是否有
```
SELECT
```
权限。
如果以上任何一个步骤找到了
```
SELECT
```
权限，则允许用户执行查询操作；否则，拒绝访问。

理解了这些权限表的结构和检查流程，就可以更好地管理MySQL的权限，防止未经授权的访问。

如何防止权限泄露，提升MySQL数据库的安全性？

权限泄露是数据库安全的一大隐患。为了防止这种情况发生，可以采取以下措施：

最小权限原则： 只授予用户完成工作所需的最小权限。避免授予过多的权限，特别是全局权限。例如，如果用户只需要查询某个数据库的某些表，就只授予这些表的
```
SELECT
```
权限，而不是整个数据库的权限，更不要授予全局
```
SELECT
```
权限。
定期审查权限： 定期检查用户的权限分配情况，移除不再需要的权限。尤其是在人员变动时，及时更新权限配置。可以使用如下SQL语句查询用户的权限：
```
SHOW GRANTS FOR 'user'@'host';
```
使用角色： 将权限授予角色，然后将角色分配给用户。这样可以简化权限管理，提高效率。例如，可以创建一个名为
```
'read_only'
```
的角色，授予其
```
SELECT
```
权限，然后将该角色分配给需要只读访问权限的用户。
```
CREATE ROLE 'read_only';
GRANT SELECT ON database.* TO 'read_only';
GRANT 'read_only' TO 'user'@'host';
SET DEFAULT ROLE 'read_only' FOR 'user'@'host';
```
限制主机访问： 尽量限制用户可以连接的主机。避免使用
```
'%'
```
作为
```
Host
```
，而是指定具体的IP地址或IP地址段。
使用强密码： 确保所有用户都使用强密码，并定期更换密码。可以使用MySQL的密码策略插件来强制执行密码复杂度要求。
禁用匿名用户： MySQL默认会创建匿名用户，这些用户没有任何密码，容易被利用。应该立即删除这些匿名用户。
```
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;
```
安全审计： 启用MySQL的审计日志，记录用户的操作行为。这样可以及时发现异常行为，并进行追踪。
及时更新MySQL版本： 及时更新MySQL版本，修复已知的安全漏洞。

user

、

db

、

tables_priv

权限表之间有什么优先级关系？MySQL是如何决定最终权限的？

这三个表在权限判断中扮演着不同角色，且存在优先级关系。MySQL会按照一定的顺序检查这些表，最终决定用户是否拥有某个权限。

优先级顺序是：

user

db

tables_priv

columns_priv

。

具体来说：

```
user
```
表（全局权限）： 这是最高级别的权限表。如果在
```
user
```
表中授予了用户某个权限，那么该用户在所有数据库的所有表上都拥有该权限（除非在更低级别的权限表中显式地撤销了该权限）。
```
db
```
表（数据库权限）： 这是数据库级别的权限表。如果在
```
db
```
表中授予了用户某个数据库的权限，那么该用户在该数据库的所有表上都拥有该权限（除非在更低级别的权限表中显式地撤销了该权限）。
```
tables_priv
```
表（表权限）： 这是表级别的权限表。如果在
```
tables_priv
```
表中授予了用户某个表的权限，那么该用户只在该表上拥有该权限。
```
columns_priv
```
表（列权限）： 这是最低级别的权限表。如果在
```
columns_priv
```
表中授予了用户某个表的某个列的权限，那么该用户只在该表的该列上拥有该权限。

MySQL在进行权限检查时，会按照上述优先级顺序依次检查这些表。如果在一个更高级别的权限表中找到了允许的权限，那么就会停止检查更低级别的权限表（除非使用了

GRANT ... ON ... TO ... WITH GRANT OPTION

，允许用户将权限授予其他用户，这时需要检查权限授予链）。

最终权限的确定遵循以下规则：

Nanonets

基于AI的自学习OCR文档处理，自动捕获文档数据

下载

合并原则： 如果在多个权限表中都找到了相关的权限信息，那么MySQL会将这些权限信息合并起来。例如，如果在
```
user
```
表中授予了用户
```
SELECT
```
权限，在
```
db
```
表中授予了用户
```
INSERT
```
权限，那么该用户同时拥有
```
SELECT
```
和
```
INSERT
```
权限。
拒绝优先原则： 如果在任何一个权限表中找到了拒绝的权限信息，那么MySQL会拒绝用户的访问。例如，如果在
```
user
```
表中授予了用户
```
SELECT
```
权限，但在
```
db
```
表中拒绝了用户
```
SELECT
```
权限，那么该用户在该数据库上没有
```
SELECT
```
权限。

理解了这种优先级关系和权限确定规则，可以更精确地控制用户的访问权限，提高数据库的安全性。

如何使用SQL语句管理MySQL的用户权限？

MySQL提供了一系列SQL语句来管理用户权限，主要包括

CREATE USER

、

GRANT

、

REVOKE

、

DROP USER

等。

创建用户： 使用
```
CREATE USER
```
语句创建新的用户账户。
```
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
```
这会创建一个名为
```
'newuser'
```
，只能从
```
'localhost'
```
连接的用户，并设置密码为
```
'password'
```
。建议使用更安全的密码策略。

授予权限： 使用

GRANT

语句授予用户权限。

GRANT SELECT, INSERT ON database.table TO 'newuser'@'localhost';

这会授予

'newuser'@'localhost'

用户对

database

数据库的

table

表的

SELECT

和

INSERT

权限。

可以授予的权限包括：

SELECT

、

INSERT

、

UPDATE

、

DELETE

、

CREATE

、

DROP

、

ALTER

、

EXECUTE

等。

可以授予的权限级别包括：

全局级别：

GRANT SELECT ON *.* TO 'newuser'@'localhost';

数据库级别：

GRANT SELECT ON database.* TO 'newuser'@'localhost';

表级别：

GRANT SELECT ON database.table TO 'newuser'@'localhost';

列级别：

GRANT SELECT (column1, column2) ON database.table TO 'newuser'@'localhost';

可以使用

WITH GRANT OPTION

选项允许用户将权限授予其他用户。

GRANT SELECT ON database.table TO 'newuser'@'localhost' WITH GRANT OPTION;

撤销权限： 使用

REVOKE

语句撤销用户的权限。

REVOKE SELECT ON database.table FROM 'newuser'@'localhost';

这会撤销

'newuser'@'localhost'

用户对

database

数据库的

table

表的

SELECT

权限。

删除用户： 使用
```
DROP USER
```
语句删除用户账户。
```
DROP USER 'newuser'@'localhost';
```
这会删除
```
'newuser'@'localhost'
```
用户。
刷新权限： 在修改了权限之后，需要使用
```
FLUSH PRIVILEGES
```
语句刷新权限，使修改生效。
```
FLUSH PRIVILEGES;
```

这些SQL语句是管理MySQL用户权限的基本工具。通过灵活运用这些语句，可以实现精细化的权限控制，保障数据库的安全。

如何查看当前用户的权限？

查看当前用户的权限有多种方法，最常用的就是使用

SHOW GRANTS

语句。

查看当前用户的权限：
```
SHOW GRANTS FOR CURRENT_USER;
```
或者简写为：
```
SHOW GRANTS;
```
这条语句会显示当前连接用户的权限信息，包括全局权限、数据库权限、表权限等。
查看指定用户的权限：
```
SHOW GRANTS FOR 'user'@'host';
```
将
```
'user'@'host'
```
替换为要查看权限的用户名和主机名。例如：
```
SHOW GRANTS FOR 'testuser'@'%';
```
这条语句会显示
```
'testuser'@'%'
```
用户的权限信息。
使用
```
mysql.user
```
表查询：

虽然不推荐直接查询
```
mysql.user
```
表来获取权限信息（因为权限信息分散在多个表中），但可以通过查询该表来了解用户的基本信息，例如用户名、主机名等。
```
SELECT User, Host FROM mysql.user;
```