mysql 用户管理-权限表

「爱情、让人受尽委屈。」 2024-04-20 22:17 69阅读 0赞

学习了《[mysql5.7安装][mysql5.7]》，就先再了解下用户管理，先了解下权限表。

MySQL是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定允许

的权限。MySQL用户可以分为普通用户和root用户。root 用户是超级管理员,拥有所有权限，

包括创建用户、删除用户和修改用户的密码等管理权限：普通用户只拥有被授予的各种权限。

用户管理包括管理用户账户、权限等。

####  ####

### 1，权限表 ###

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在**MySQL**数据库中，

由MySQL\_istall db脚本初始化。存储账户权限信息表主要有： user、db、host、tables\_priv、columnns\_priv和procs\_priv.

####  ####

### 2，user 表 ###

user表是MySQL中最重要的一个权限表， 记录允许连接到服务器的账号信息，里面的权限是全局级的。

例如：一个用户在user表中被授予了DELETE权限，则该用户可以删除MySQL服务器上所有数据库中的任何记录。MySQL 5.7中user表有42个字段，如表1所示，这些字段可以分为4类，分别是用户列、权限列、安全列和资源控制列。

表1 user 表结构

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> 字段名 </td> 
 <td colspan="1"> 数据类型 </td> 
 <td colspan="1"> 默认值 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Host </td> 
 <td colspan="1"> char(60) </td> 
 <td colspan="1"> </td> 
 </tr> 
 <tr> 
 <td colspan="1"> User </td> 
 <td colspan="1"> char(16) </td> 
 <td colspan="1"> </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Password </td> 
 <td colspan="1"> char(41) </td> 
 <td colspan="1"> </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Select_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Insert_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Update_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Delete_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Create&nbsp;priv </td> 
 <td colspan="1"> enum("N','Y") </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Drop_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Reload_priv </td> 
 <td colspan="1"> enum('N','Y") </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Shutdown_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Process_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> File_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Grant_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> References_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Index_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Alter_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Show_db_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Super_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Create_tmp_table_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Lock_tables&nbsp;priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Execute_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Repl_slave_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Repl_client_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Create_view_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Show_view_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Create_routine_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Alter_routine_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Create_user_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Event_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Trigger_priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> Create_tablespace&nbsp;priv </td> 
 <td colspan="1"> enum('N', 'Y') </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> ssl_type </td> 
 <td colspan="1"> enum('', 'ANY', 'X509', 'SPECIFIED') </td> 
 <td colspan="1"> </td> 
 </tr> 
 <tr> 
 <td colspan="1"> ssl_cipher </td> 
 <td colspan="1"> blob </td> 
 <td colspan="1"> NULL </td> 
 </tr> 
 <tr> 
 <td colspan="1"> x509_issuer </td> 
 <td colspan="1"> blob </td> 
 <td colspan="1"> NULL </td> 
 </tr> 
 <tr> 
 <td colspan="1"> x509_subject </td> 
 <td colspan="1"> blob </td> 
 <td colspan="1"> NULL </td> 
 </tr> 
 <tr> 
 <td colspan="1"> max_questions </td> 
 <td colspan="1"> int(11) unsigned </td> 
 <td colspan="1"> 0 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> max_updates </td> 
 <td colspan="1"> int(11) unsigned </td> 
 <td colspan="1"> 0 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> max_connections </td> 
 <td colspan="1"> int(11) unsigned </td> 
 <td colspan="1"> 0 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> max_user_connections </td> 
 <td colspan="1"> char(64) </td> 
 <td colspan="1"> N </td> 
 </tr> 
 <tr> 
 <td colspan="1"> plugin </td> 
 <td colspan="1"> text </td> 
 <td colspan="1"> NULL </td> 
 </tr> 
 </tbody> 
</table>

#### **1，用户列** ####

user表的用户列包括Host、User、 Password, 分别表示主机名、用户名和密码。其中User

和Host为User表的联合主键。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配User表中对应的字段，只有3个值都匹配的时候，才允许连接建立。这3个字段的值就是创建账户时保存的账户信息。修改用户密码时，实际就是修改user表的Password字段的值。

#### **2，权限列** ####

权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。

包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等高级权限。普通权限用于操作数据库;高级权限用于数据库管理。

user表中对应的权限是针对所有用户数据库的。这些字段值的类型为ENUM,可以取的值只能为Y和N, Y表示该用户有对应的权限： N表示用户没有对应的权限。查看user表的结构可以看到，这些字段的值默认都是N。如果要修改权限，可以使用GRANT语句或UPDATE语句更改user表的这些字段来修改用户对应的权限。

#### **3，安全列** ####

安全列只有6个字段，其中两个是ssl相关的，两个是x509相关的，另外两个是授权插件相关的。ssl 用于加密; x509 标准可用于标识用户： Plugin 字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户身份。可以通过

SHOW VARIABLES LIKE 'have_openssl';

语句来查询服务器是否支持ssl功能。

#### **4，资源控制列** ####

资源控制列的字段用来限制用户使用的资源，包含4个字段，分别为：

(1) max\_questions -- 用户 每小时允许执行的查询操作次数。

(2) max\_updates -- 用户每小时允许执行的更新操作次数。

(3) max\_connections -- 用 户每小时允许执行的连接操作次数。

(4) max\_user\_connections -- 用户允许同时建立的连接次数。

一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时才

可以在此执行对应的操作。可以使用GRANT语句更新这些字段的值。

### 2，db表和host表 ###

db表和host表是MySQL数据中非常重要的权限表。db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。host 表中存储了某个主机对数据库的操作权限，配合db权限表对给定主机上数据库级操作权限做更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。db 表比较常用，host 表一般很少使用。 db表和host表结构相似，字段大致可以分为两类：用户列和权限列。db表和host表的结构分别如表2和表3所示。

表2 db 表结构

表3 host 表结构

#### **1，用户列** ####

db表用户列有3个字段，分别是Host、User. Db,标识从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。host 表不存储用户名称，用户列只有2个字段，分别是Host和Db,表示从某个主机连接的用户对某个数据库的操作权限，其主键包括Host和Db两个字段。host很少用到,一般情况 下db表就可以满足权限控制需求了。

#### **2，权限列** ####

db表和host表的权限列大致相同，表中create\_routine\_priv 和alter\_routine\_priv 这两个字段表明用户是否有创建和修改存储过程的权限。

user表中的权限是针对所有数据库的，如果希望用户只对某个数据库有操作权限，那么需要将user表中对应的权限设置为N,然后在db表中设置对应数据库的操作权限。

例如，有一个名称为Zhangting 的用户分别从名称为large domain.com和small.domain.com的两个主机连接到数据库，并需要操作books数据库。这时，可以将用户名称Zhangting添加到db表中，而db表中的host字段值为空，然后将两个主机地址分别作为两条记录的host字段值添加到host表中，并将两个表的数据库字段设置为相同的值books。当有用户连接到MySQL服务器时，db表中没有用户登录的主机名称，则MySQL会从host表中查找相匹配的值，并根据查询的结果决定用户的操作是否被允许。

### 3，tables\_priv 表和columns\_priv 表 ###

tables\_priv 表用来对表设置操作权限，columns\_priv 表用来对表的某一列设置权限。

tables\_priv表和columns\_priv表的结构分别如表4和表5所示。

表4 tables\_priv 表结构

表5 columns\_priv 表结构

tables\_priv 表有8个字段，分别是Host、Db、 User、 Table\_name、Grantor、 Timestamp、Table\_priv和Column\_priv，各个字段说明如下：

（1）Host、Db、User和Table\_name4个字段分表示主机名、数据库名、用户名和表名。

（2）Grantor 表示修改该记录的用户。

（3）Timestamp 字段表示修改该记录的时间。

（4）Table\_priv 表示对表的操作权限，包括Select、 Insert、 Update、 Delete、 Create. Drop、Grant、References、 Index 和Alter 等。

（5）Column\_priv字段表示对表中的列的操作权限，包括Select、 Insert、 Update 和

References。

columns\_priv 表只有7个字段，分别是Host、Db、 User、 Table\_name、Column\_name、Timestamp、Column\_priv。其中，Column\_name用来指定对哪些数据列具有操作权限。

### 4，procs\_priv表 ###

procs\_priv表可以对存储过程和存储函数设置操作权限。procs\_priv的表结构如表6所示。

表6 procs\_priv 表结构

procs\_priv 表包含8个字段分别是Host、Db、User、Routine\_name、Routine\_type. Grantor、Proc\_priv和Timestamp，各个字段的说明如下：

（1）Host、 Db和User字段分别表示主机名、数据库名和用户名。Routine\_name表示存

储过程或函数的名称。

（2）Routine\_type表示存储过程或函数的类型。Routine\_type字段有两个值，分别是

FUNCTION和PROCEDURE。FUNCTION表示这是一个函数; PROCEDURE 表示这是一个

存储过程。

（3）Grantor 是插入或修改该记录的用户。

（4）Proc\_priv表示拥有的权限，包括Execute、Alter Routine、Grant3 种。

（5）Timestamp表示记录更新时间。

### 5，总结 ###

权限表，进入mysql数据库，看表结构，用DESC，比如： desc user; 看用户表的表结构。用户表平时感觉会比其他的多一些，看有哪些用户；其次是权限表tables\_priv。

上一篇：《[mysql 命令行常用操作和客户端][mysql]》

下一篇： 《[mysql 用户管理][mysql 1]》

[mysql5.7]: https://blog.csdn.net/qq_35461948/article/details/135682283
[mysql]: https://blog.csdn.net/qq_35461948/article/details/135682729
[mysql 1]: https://blog.csdn.net/qq_35461948/article/details/136901969