MySQL成员如何存储_MySQL用户信息与权限存储结构解析教程

MySQL通过mysql系统数据库存储用户和权限信息，核心表为mysql.user，记录用户账户、密码哈希、认证插件及全局权限；权限分级管理，依次为全局、数据库、表、列级别，遵循“最具体优先”和累加原则；用户认证时先匹配Host和User，再根据plugin字段调用对应认证机制验证密码；可通过SHOW GRANTS、GRANT、REVOKE等命令查看和管理权限，推荐遵循最小权限原则以保障安全。

MySQL存储用户信息和权限，核心在于其内部的

mysql

系统数据库。所有关于用户身份验证和授权的信息，都被精心地组织并保存在这个数据库的特定表中，尤其是

user

表，它是所有权限的起点，定义了谁能连接以及他们拥有哪些全局权限。

解决方案

要深入理解MySQL的用户信息与权限存储，我们得从

mysql

这个特殊的系统数据库说起。它不是用来存我们业务数据的，而是MySQL服务器自身的“大脑”，管理着用户、权限、日志、时区等各种元数据。其中，与用户和权限最相关的，莫过于以下几张表：

mysql.user

表：这是最关键的一张表，它存储了所有可以连接到MySQL服务器的用户账户信息，以及这些用户所拥有的全局权限。
- Host
  
  和
  
  user
  
  字段：这两个字段共同构成了用户账户的唯一标识。
  Host
  指定了用户可以从哪个IP地址或主机名连接，
  user
  则是用户名。这是MySQL进行身份验证时的首要匹配条件。
- authentication_string
  
  字段：这个字段存储的是用户密码的哈希值，或者说是与认证插件相关的数据。当用户尝试连接时，MySQL会用客户端提供的密码进行哈希，然后与这里的值进行比对。
- plugin
  
  字段：非常重要，它指明了这个用户账户使用哪种认证插件进行身份验证。比如，
```
mysql_native_password
```
  是早期版本常用的，而
  caching_sha2_password
  是MySQL 8.0及以后版本的默认选项，提供了更好的安全性。如果这里是
```
auth_socket
```
  ，那通常意味着可以通过操作系统用户直接登录而无需密码。
- 各种
  
  _priv
  
  字段：比如
```
Select_priv
```
  ,
```
Insert_priv
```
  ,
```
Update_priv
```
  ,
```
Delete_priv
```
  ,
```
Create_priv
```
  ,
```
Grant_priv
```
  ,
```
Super_priv
```
  等等。这些布尔值（’Y’或’N’）标志着用户在整个MySQL服务器层面（全局）拥有的权限。例如，
```
Select_priv='Y'
```
  意味着该用户可以在所有数据库的所有表上执行SELECT操作，除非有更具体的权限限制。
- 资源限制字段：像
```
max_questions
```
  ,
```
max_updates
```
  ,
```
max_connections
```
  ,
```
max_user_connections
```
  等，用于限制用户在特定时间段内可以执行的查询、更新次数，以及同时连接到服务器的最大数量。这对于防止资源滥用非常有用。
mysql.db

表：这张表存储了用户在特定数据库上的权限。它的主键通常是
Host
,
Db
,
user
。如果一个用户被授予了
mydatabase
数据库的
SELECT
权限，那么在这里就会有一条记录，指明该用户对
mydatabase
拥有
```
Select_priv='Y'
```
。这些权限是数据库级别的，意味着用户可以在该数据库的所有表上执行相应的操作。
mysql.tables_priv

表：比
mysql.db
更细粒度，它存储了用户在特定数据库的特定表上的权限。主键通常是
Host
,
Db
,
user
,
```
Table_name
```
。例如，用户可能只被允许对
```
mydatabase.mytable
```
进行
INSERT
操作，而不能对
mydatabase
中的其他表进行
INSERT
。
mysql.columns_priv

表：这是最细粒度的权限控制，存储了用户在特定数据库的特定表的特定列上的权限。主键是
Host
,
Db
,
user
,
```
Table_name
```
,
```
Column_name
```
。虽然强大，但在实际应用中，由于管理成本较高，通常不会频繁使用。
mysql.procs_priv

表：存储了用户对存储过程（Stored Procedures）和存储函数（Stored Functions）的权限。
mysql.proxies_priv

表：用于代理用户（Proxy Users）的配置，允许一个用户以另一个用户的身份连接。

当用户尝试连接和执行操作时，MySQL会按照一个特定的顺序来检查这些权限表：首先是

user

表进行身份验证，然后是

user

表（全局权限）、

Db

表（数据库权限）、

tables_priv

表（表权限）、

columns_priv

表（列权限），从最具体到最不具体的权限进行评估，最终决定用户是否有权执行请求的操作。

MySQL用户认证机制是怎样的？

谈到MySQL的用户认证，这可不是一个简单的密码比对过程，它背后有一套相对精密的机制在运作。在我看来，理解这个过程对于排查连接问题和提升安全性至关重要。

当你尝试用客户端连接到MySQL服务器时，大致会经历以下几个步骤：

客户端发起连接请求：客户端（比如MySQL命令行工具、应用程序）会向服务器发送一个连接请求，其中包含用户名和用于认证的信息（通常是密码的哈希值）。
服务器接收请求，匹配

Host

和

user

：MySQL服务器收到请求后，第一件事就是查看
mysql.user
表。它会尝试根据客户端的来源IP地址（或主机名）和提供的用户名，找到一个匹配的记录。这里的
Host
字段支持通配符（如
```
%
```
代表所有主机），匹配规则是“最具体优先”。如果客户端是从
```
192.168.1.100
```
连接，而
user
表中既有
```
'myuser'@'192.168.1.100'
```
也有
```
'myuser'@'%'
```
，那么
```
'myuser'@'192.168.1.100'
```
会被优先匹配。如果找不到匹配的
Host
和
user
组合，连接就会被拒绝。
确定认证插件：一旦找到了匹配的用户记录，MySQL就会查看该记录的
plugin
字段。这个字段告诉服务器应该使用哪种认证插件来验证用户提供的凭据。
- mysql_native_password
  
  ：这是MySQL早期的默认插件，安全性相对较低。它使用SHA-1哈希算法存储密码。
- caching_sha2_password
  
  ：MySQL 8.0及更高版本的默认插件。它基于SHA-256哈希算法，提供了更强的安全性，并且支持缓存，使得后续连接的认证效率更高。
- sha256_password
  
  ：也是基于SHA-256，但没有缓存机制。
- auth_socket
  
  ：这个插件允许操作系统用户直接登录MySQL，而无需密码。例如，如果你以
```
root
```
  用户登录Linux系统，那么你可能可以直接以
```
root
```
  用户身份连接到MySQL而无需提供密码。
- PAM (Pluggable Authentication Modules)：允许MySQL与外部认证系统集成，比如LDAP、Kerberos等。
执行认证插件验证：服务器会调用
plugin
字段指定的认证插件，用客户端提供的密码哈希值（或相关认证数据）与
```
mysql.user.authentication_string
```
字段中存储的值进行比对。如果两者匹配，认证成功；否则，认证失败，连接被拒绝。

整个过程中，

Host

字段的精确匹配和

plugin

字段的选择是至关重要的。如果你遇到“Access denied”错误，除了检查用户名和密码，也得看看

Host

是否正确配置，以及客户端是否支持服务器为该用户指定的认证插件。尤其是从旧版本升级到MySQL 8.0后，

caching_sha2_password

可能会导致一些老旧客户端无法连接，这时候就需要调整用户的

plugin

或客户端配置。

MySQL权限管理是如何分级的？

MySQL的权限管理体系，说白了，就是一套层层递进、由粗到细的授权机制。它不是一刀切的，而是提供了一种灵活的粒度控制，让我们能够精确地定义每个用户能做什么、不能做什么。在我看来，这种分级管理是其强大之处，但也常常是权限配置复杂性的来源。

权限等级从最高到最低，大致可以这样理解：

全局权限（Global Privileges）：
- 存储位置：
  mysql.user
  表。
- 作用范围：应用于MySQL服务器上的所有数据库、所有表。这是最广泛的权限，一旦授予，用户就可以在整个实例范围内执行相应操作。
- 常见例子：
```
SUPER
```
  （超级权限，几乎无所不能），
```
PROCESS
```
  （查看服务器进程），
```
RELOAD
```
  （重载权限表），
```
CREATE USER
```
  （创建用户），
```
FILE
```
  （读写服务器文件系统），
```
REPLICATION SLAVE
```
  （作为复制从库）。
- 我的理解：全局权限就像是“总司令”的权力，非常强大，通常只授予给DBA或极少数高级用户。滥用全局权限可能带来巨大的安全风险。
数据库级权限（Database-Level Privileges）：
- 存储位置：
  mysql.db
  表。
- 作用范围：应用于指定数据库中的所有表、所有存储过程等对象。
- 常见例子：
  SELECT
  ,
  INSERT
  ,
```
UPDATE
```
  ,
```
DELETE
```
  （对某个数据库中的所有表），
```
CREATE
```
  ,
```
ALTER
```
  ,
```
DROP
```
  （对数据库中的表、视图、存储过程等）。
- 我的理解：这是日常应用中最常用的一种权限级别。比如，一个Web应用通常只需要对某个特定的数据库拥有读写权限。通过这种方式，我们可以将不同应用的权限隔离开来，互不影响。
表级权限（Table-Level Privileges）：
- 存储位置：
```
mysql.tables_priv
```
  表。
- 作用范围：应用于指定数据库中的特定表。
- 常见例子：
  SELECT
  ,
  INSERT
  ,
```
UPDATE
```
  ,
```
DELETE
```
  （仅对某个数据库的某张表）。
- 我的理解：当我们需要对特定数据表进行更精细的控制时，表级权限就派上用场了。例如，你可能希望某个用户只能查询
```
users
```
  表，但可以插入
```
logs
```
  表。
列级权限（Column-Level Privileges）：
- 存储位置：
```
mysql.columns_priv
```
  表。
- 作用范围：应用于指定数据库中特定表的特定列。
- 常见例子：
```
UPDATE(salary)
```
  （只允许更新
```
employees
```
  表中的
```
salary
```
  列），
```
SELECT(name, email)
```
  （只允许查询
```
users
```
  表的
```
name
```
  和
```
email
```
  列）。
- 我的理解：这是最细粒度的权限控制，可以用来保护敏感数据。比如，你可能希望某个部门的员工只能查看客户的姓名和地址，但不能查看他们的财务信息。虽然强大，但管理起来也最复杂，容易出错，所以实际项目中用得相对较少。

除了这四种主要级别，还有针对存储过程/函数（

mysql.procs_priv

）和代理用户（

mysql.proxies_priv

）的权限。

权限的生效原则：MySQL在判断用户是否有权执行某个操作时，会遵循一个“最具体优先”的原则，并且权限是累加的。也就是说，如果一个用户在全局层面被授予了

SELECT

权限，但在某个特定数据库或表上被明确地

REVOKE

（撤销）了

SELECT

权限，那么撤销会生效。反之，如果全局没有

SELECT

，但在某个数据库或表上被授予了

SELECT

，那么该用户就可以在该特定范围进行

SELECT

。简单来说，只要在任何一个层级有足够的权限，操作就可以执行。但如果有明确的拒绝，那么拒绝优先。遵循最小权限原则是最佳实践，即只授予用户完成其工作所需的最低限度权限。

如何查看和管理MySQL用户与权限？

管理MySQL用户和权限，是数据库管理员的日常工作，也是保障数据安全的核心环节。我个人觉得，熟练掌握这些命令，能让你在面对权限问题时游刃有余。

1. 查看用户与权限

最直接、最权威的方式就是使用

SHOW GRANTS

命令。它会列出指定用户所有明确授予的权限，包括全局、数据库、表和列级别的。

查看特定用户的权限：

SHOW GRANTS FOR 'your_user'@'your_host'; -- 示例： SHOW GRANTS FOR 'app_user'@'localhost'; SHOW GRANTS FOR 'admin'@'%'; -- 查看从任意主机连接的admin用户

这个命令的输出会非常清晰地展示

GRANT

语句的形式，直接告诉你用户拥有哪些权限。

查看所有用户列表（不含权限详情）：
```
SELECT user, host FROM mysql.user;
```
这能让你快速了解当前系统中有哪些用户账户。

查看

mysql.user

表中的全局权限：

SELECT user, host, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Grant_priv, Super_priv FROM mysql.user WHERE user = 'your_user';

这可以让你看到某个用户在全局层面直接拥有的布尔权限标志。

查看

mysql.db

表中的数据库级权限：
```
SELECT host, db, user, Select_priv, Insert_priv, Update_priv FROM mysql.db WHERE user = 'your_user';
```
如果你想知道某个用户在哪些数据库上有权限，这个查询很有用。

2. 管理用户与权限

管理权限主要是通过

CREATE USER

GRANT

REVOKE

DROP USER

这些SQL命令来完成的。

创建新用户：

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'your_password'; -- 使用更安全的caching_sha2_password认证插件（MySQL 8.0+ 默认） CREATE USER 'new_user_sha2'@'%' IDENT IDENTIFIED WITH caching_sha2_password BY 'secure_password';

IDENTIFIED BY

后面跟着密码。

'%'

代表该用户可以从任意主机连接。

授予权限：

-- 全局权限：授予用户在所有数据库所有表上的SELECT和INSERT权限 GRANT SELECT, INSERT ON *.* TO 'new_user'@'localhost';  -- 数据库级权限：授予用户在mydatabase数据库上的所有权限 GRANT ALL PRIVILEGES ON mydatabase.* TO 'new_user'@'localhost';  -- 表级权限：授予用户对mydatabase.mytable表的SELECT和UPDATE权限 GRANT SELECT, UPDATE ON mydatabase.mytable TO 'new_user'@'localhost';  -- 列级权限：授予用户对mydatabase.mytable表的name列的SELECT权限 GRANT SELECT (name) ON mydatabase.mytable TO 'new_user'@'localhost';  -- 授予GRANT OPTION：允许用户将自己拥有的权限再授予给其他用户（慎用！） GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

*.*

表示所有数据库的所有表。

mydatabase.*

表示

mydatabase

数据库的所有表。

撤销权限：

REVOKE

命令的语法与

GRANT

类似，但它是用于移除权限。

-- 撤销用户在所有数据库所有表上的INSERT权限 REVOKE INSERT ON *.* FROM 'new_user'@'localhost';  -- 撤销用户在mydatabase数据库上的所有权限 REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'new_user'@'localhost';

修改用户密码：

ALTER USER 'new_user'@'localhost' IDENTIFIED BY 'new_secure_password';

删除用户：
```
DROP USER 'old_user'@'localhost';
```
刷新权限：
```
FLUSH PRIVILEGES;
```
虽然
GRANT
和
REVOKE
命令通常会自动刷新权限缓存，但如果你直接修改了
```
mysql
```
系统表（这是不推荐的做法），或者在某些特定情况下，手动执行
```
FLUSH PRIVILEGES
```
可以确保权限更改立即生效。

记住，在管理权限时，始终遵循最小权限原则。只授予用户完成其任务所需的最低权限，这样可以最大程度地降低潜在的安全风险。

mysql linux word 操作系统 access 工具 ai linux系统敏感数据 red sql mysql select delete 对象 column table 算法 database 数据库 dba linux Access