MySQL-用户管理

ゝ一纸荒年。 2024-04-06 09:28 67阅读 0赞

> 有志者，事竟成  
> 文章持续更新，可以微信搜索【小奇JAVA面试】第一时间阅读，回复【资料】获取福利，回复【项目】获取项目源码，回复【简历模板】获取简历模板，回复【学习路线图】获取学习路线图。

![在这里插入图片描述][13b0efb5f9ef4a7a89f2057eab98c67a.png_pic_center]

#### 文章目录 ####

*  前言
 *  一、权限表
 *   *  1、user表
     *  2、db表和host表
     *  3、tables\_priv表和columns\_priv表
     *  4、procs\_priv表
 *  二、账户管理
 *   *  1、登录和退出MySQL服务器
     *  2、新建普通用户
     *  3、删除普通用户
     *  4、root用户修改自己的密码
     *  5、root用户修改普通用户密码
     *  6、普通用户修改密码
     *  7、root用户密码丢失的解决办法
 *  三、权限管理
 *   *  1、MySQL的各种权限
     *  2、授权
     *  3、收回权限
     *  4、查看权限
 *  四、总结

--------------------

## 前言 ##

MySQL用户主要包括普通用户和root用户。这两种用户的权限是不一样的。root用户是超级管理员，拥有所有的权限。root用户的权限包括创建用户、删除用户和修改普通用户的密码等管理权限。而普通用户只拥有创建该用户时赋予它的权限。用户管理包括管理用户的账户、权限等。

## 一、权限表 ##

安装MySQL时会自动安装一个名为mysql的数据库。mysql数据库下面存储的都是权限表。用户登录以后，MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表和host表。除此之外，还有tables\_priv表、columns\_priv表和proc\_priv表等。

### 1、user表 ###

user表是MySQL中最重要的一个权限表。读者可以使用desc语句来查看user表的基本结构。user表有39个字段。这些字段大致可以分为4类，分别是用户列、权限列、安全列和资源控制列。

1、用户列

user表的用户列包括Host、User、Password,分别表示主机名、用户名和密码。用户登录时，首先要判断的就是这3个字段。如果这3个字段同时匹配，MySQL数据库系统才会允许其登录。而且，创建新用户时，也是设置这3个字段的值。修改用户密码时，实际就是修改user表的Password字段的值。因此，这3个字段决定了用户能否登录。

2、权限列

user表的权限列包括select\_priv、Inset\_priv等以priv结尾的字段。这些字段决定了用户的权限。这其中包括查询权限、修改权限等普通权限，还包括了关闭服务的权限、超级权限和加载用户等高级管理权限。普通权限用户操作数据库。高级管理权限用于对数据库进行管理。

这些字段的值只有Y和N。Y表示该权限可以用到所有数据库上；N表示该权限不能用到所有数据库上。从安全角度考虑，这些字段的默认值都为N。可以使用GRANT语句为用户赋予一些权限，也可以通过UPDATE语句更新user表的方式来设置权限。

3、安全列

user表的安全列只有4个字段，分别是ssl\_type、ssl\_cipher、x509\_issuer和x509\_subject。ssl用于加密；x509标准可以用来标识用户。通常标准的发行版不支持ssl，读者可以使用show variables like 'have\_openssl’语句来查看是否具有ssl功能。如果have\_openssl的取值为disabled，那么则没有支持ssl加密功能。

4、资源控制列

user表的4个资源控制列是max\_question、max\_updates、max\_connections和max\_user\_connection。max\_question和max\_updates分别规定每小时可以允许执行多少次查询和更新；max\_connections规定每小时可以建立多少连接；max\_user\_connections规定单个用户可以同时具有的连接数。这些字段的默认值为0，表示没有限制。

### 2、db表和host表 ###

db表和host表也是MySQL数据库中非常重要的权限表。db表中存储了某个用户对一个数据库的权限。db表比较常用，而host表很少会用到。读者可以使用desc语句来查看这两个表的基本结构。这两个表的表结构差不多。db表和host表的字段大致可以分为两类，分别为用户列和权限列。

1、用户列

db表的用户列有3个字段，分别是Host、Db和User。这3个字段分别表示主机名、数据库名和用户名。host表的用户列有两个字段，分别是Host和Db。这两个字段分别表示主机名和数据库名。

host表是db表的扩展。如果db表中找不到Host字段的值，就需要到host表中去寻找。但是host表很少用到，通常db表的设置以及满足要求了。

2、权限列

db表和host表的权限列几乎一样，只是db表中多了一个Create\_routine\_priv 字段和Alter\_routine\_priv字段。这两个字段决定用户是否具有创建和修改存储过程的权限。

user表中的权限是针对所有数据库。如果user表中的Select\_priv字段取值为Y，那么该用户可以查询所有数据库中的表；如果为某个用户只设置了查询test表的权限，那么user表的Select\_priv字段的取值为N。而这个select权限则记录在db表中。db表中Select\_priv字段的取值将会是Y。由此可知，用户先根据user表的内容获取权限，然后再根据db表的内容获取权限。

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

tables\_priv表可以对单个表进行权限设置。columns\_priv表可以对单个数据列进行权限设置。读者可以使用desc语句来查看这两个表的基本结构。

tables\_priv表包含8个字段，分别是Host、Db、User、Table\_name、Table\_priv、Column\_priv、Timestamp和Grantor。前4个字段分别表示主机名、数据库名、用户名和表名。Table\_priv表示对表进行操作的权限。这些权限包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter。Column\_priv表示对表中的数据列进行操作的权限。这些权限包括Select、Insert、Update和References。Timestamp表示修改权限的时间。Grantor表示权限是谁设置的。

columns\_priv表包括7个字段，分别是Host、Db、User、Table\_name、Column\_name、Column\_priv和Timestamp。与tables\_priv表不同的是，这里多出了Column\_name字段，其表示可以对哪些数据列进行操作。

### 4、procs\_priv表 ###

procs\_priv表可以对存储过程和存储函数进行权限设置。读者可以使用Desc语句来查看procs\_priv表的基本结构。

procs\_priv表包含8个字段，分别是Host、Db、User、Routine\_name、Routine\_type、Proc\_priv、Timestamp和Grantor。前3个字段分别表示主机名、数据库名和用户名。Routine\_name字段表示存储过程或函数的名称。Routine\_type字段表示类型。该字段有两个取值，分别是FUNCTION和procedure。function表示这是一个存储函数；procedure表示这是一个存储过程，Proc\_priv字段表示拥有的权限。权限分为3类，分别是Execute、Alter Routine和Grant。Timestamp字段存储更新的时间；Grantor字段存储权限是谁设置的。

## 二、账户管理 ##

账户管理是MySQL用户管理的最基本的内容。账户管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。通过账户管理，可以保证MySQL数据库的安全性。

### 1、登录和退出MySQL服务器 ###

用户可以通过mysql命令来登录MySQL服务器。启动MySQL服务后，可以通过mysql命令来登录MySQL服务器。命令如下：

mysql -h hostname | hostIP -P port -u username -p DatabaseName -e "SQL语句"

这个命令后面有几个参数，详细介绍如下：

\-h参数后面接主机名或者主机IP，hostname为主机名，hostIP为主机IP；

\-P参数后面接MySQL服务的端口。通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号；

\-u参数后面接用户名。username为用户名；

\-p参数会提示输入密码；

DatabaseName参数指明登录到哪一个数据库中。如果没有该参数，会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库；

\-e参数后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器。

### 2、新建普通用户 ###

在MySQL数据库中，可以使用create user语句来创建新的用户，也可以直接在mysql.user表中添加用户。还可以使用grant语句来新建用户。

1、用create user语句来新建普通用户

使用create user语句来创建新用户时，必须拥有create user权限。create user语句的基本语法形式如下：

create user user [identified by [password] 'password']
    [,user [identified by [password] 'password']] ...

其中，user参数表示新建用户的账户，user由用户名（User）和主机名（Host）构成；identified by关键字用来设置用户的密码；password参数表示用户的密码。如果密码是一个普通的字符串，就不需要使用password关键字。create user语句可以同时创建多个用户。新用户可以没有初始密码。

下面使用create user语句来创建名为test1的用户，密码也是test1，其主机名为localhost。命令如下：

create user 'tes1'@'localhost' identified by 'test1';

命令执行结果如下：

![在这里插入图片描述][fe16b296a9d74ea6b882c55aeb6c1096.png]

2、用insert语句来新建普通用户

可以使用insert语句直接将用户的信息添加到mysql.user表中。但必须拥有对mysql.user表的insert权限。通常insert语句只有添加Host、User和Password这3个字段的值。insert语句的基本语法形式如下：

insert into mysql.user(Host,User,Password) values ('hostname','username',Password ('passw-ord'));

其中，password()函数是用来给密码加密的。因为只设置了这3个字段的值，那么其他字段的取值为其默认值。如果这3个字段以外的某个字段没有默认值，这个语句将不能执行。需要将没有默认值的字段也设置值。通常ssl\_sipher、x509\_issuer和x509\_subject这3个字段没有默认值。因此必须为这3个字段设置初始值。insert语句的代码如下：

insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) values ('hostname','username',password('password'),",",");

3、用grant语句来新建普通用户

可以使用grant语句来创建新的用户。在创建用户时可以为用户授权。但必须拥有对grant权限。这里只使用grant语句来创建新的用户，grant语句的基本语法形式如下：

grant priv_type on database.table
    	to user[identified by [password] 'password']
    	[,user [identified by [password] 'password']] ...

其中，priv\_type参数表示新用户的权限；database.table参数表示新用户的权限范围，即只能在指定的数据库和表上使用自己的权限；user参数新用户的账号，由用户名和主机名构成；identified by关键字用来设置密码；password参数表示新用户的密码。grant语句可以同时创建多个用户。这里的grant语句只是其中创建新用户的部分的参数。

### 3、删除普通用户 ###

在MySQL数据库中，可以使用DROP USER语句来删除普通用户，也可以直接在mysql.user表中删除用户。

1、用drop user语句来删除普通用户

使用drop user语句来删除用户时，必须拥有drop user权限。drop user语句的基本语法形式如下：

drop user user [,user] ...

其中，user参数是需要删除的用户，由用户用户名(User)和主机名(Host)组成。drop user语句可以同时删除多个用户，各用户之间用逗号隔开。

下面使用DROP USER语句来删除用户test2，其Host值为localhost。DROP USER语句如下：

drop user 'test2'@'localhost';

代码执行如下：

drop user 'test2'@'localhost';

2、用delete语句来删除普通用户

可以使用delete语句直接将用户的消息从mysql.user表中删除。但必须拥有对mysql.user表的delete权限。delete语句的基本语法形式如下：

delete from mysql.user where host = 'hostname' and user='username';

Host和User这两个字段都是mysql.user表的主键。因此，两个字段的值才能唯一的确定一条记录。

下面使用delete语句删除名为test3的用户，该用户的主机名是localhost。delete语句如下：

delete from mysql.user where host='localhost' and user

### 4、root用户修改自己的密码 ###

root用户拥有很高的权限，因此必须保证root用户的密码的安全。root用户可以通过多种方式来修改密码。

1、使用mysqladmin命令来修改root用户的密码

root用户可以使用mysqladmin命令来修改密码。mysqladmin命令的基本语法如下：

mysqladmin -u username -p password "new_password";

2、修改mysql数据库下的user表

使用root用户登录到MySQL服务器后，可以使用update语句来更新mysql数据库下的user表。在user表中修改password字段的值，这就达到了修改密码的目的。update语句的代码如下：

update mysql.user  set password = password("new_password") where user = "root" and host = "localhost";

新密码必须使用password()函数来加密。执行update语句以后，需要执行flush privileges语句来加载权限。

3、使用set语句来修改root用户的密码

使用root用户登录到MySQL服务器后，可以使用set语句来修改密码。set语句的代码如下：

set password = password("new_password");

新密码必须使用password()函数来加密。

### 5、root用户修改普通用户密码 ###

root用户不仅可以修改自己的密码，还可以修改普通用户的密码。root用户登录MySQL服务器后，可以通过set语句、修改user表和GRANT语句来修改普通用户的面。

1、使用set语句来修改普通用户的密码

使用root用户登录到MySQL服务器后，可以使用set语句来修改普通用户的密码。

set语句的代码如下：

set password for 'username'@'hostname' = password("new_password");

其中，username参数是普通用户的用户名；hostname参数是普通用户的主机名；新密码必须使用password()函数来加密。

2、修改mysq数据库下的user表

使用root用户登录到MySQL服务器后，可以使用update语句来修改mysql数据库下的user表。update语句的代码如下：

update mysql.user set password = password("new_password") where user = "username" and host = "hostname";

其中，username参数是普通用户的用户名；hostname参数是普通用户的主机名；新密码必须使用password()函数来加密。执行update语句以后，需要执行flush privileges语句来加载权限。

3、用grant语句来修改普通用户的密码

可以使用grant语句来修改普通用户的密码，但必须拥有对grant权限。这里只使用grant语句来修改普通用户的密码。grant语句的基本语法形式如下：

grant priv_type on database.table
    	to user[identified by [password] 'password'];

其中，priv\_type参数表示普通用户的权限；database.table参数表示用户的权限范围，即只能在指定的数据库和表上使用自己的权限；user参数表示新用户的账户，由用户名和主机名构成；

### 6、普通用户修改密码 ###

普通用户也可以修改自己的密码。这样普通用户就不需要每次需要修改密码时都通知管理员。普通用户登录到MySQL服务器后，可以通过SET语句来设置自己的密码。

set password = password("new_password");

这里必须使用password()函数来为新密码加密。如果不使用password()函数加密，那么用户将无法登录。

下面将test3用户的密码改为‘test’。set语句如下：

set password = password('test');

### 7、root用户密码丢失的解决办法 ###

如果root用户密码丢失了，会给用户造成很大的麻烦。但是，可以通过某种特殊方法登录到root用户下。然后，在root用户下设置新的密码。下面是解决root用户密码丢失的方法，执行步骤如下：

1、使用–skip-grant-tables选项启动MySQL服务

skip-grant-tables选项将使MySQL服务器停止权限判断，任何用户都有访问数据库的权力。这个选项是跟在MySQL服务的命令后面的。Windows操作系统中，使用mysqld或者mysqld-nt来启动MySQL服务。也可以使用net start mysql命令，来启动MySQL服务。mysqld命令如下：

mysqld --skip-grant-tables

mysqld-nt命令如下：

mysqld-nt --skip-grant-tables

net start mysql命令如下：

net start mysql --skip-grant-tables

Linux操作系统中，使用mysqld\_safe来启动MySQL服务，也可以使用/etc/init.d/mysql来启动MySQL服务。mysqld\_safe命令如下：

mysqld_safe --skip-grant-tables user=mysql

使用/etc/init.d/mysql的执行语句如下：

/etc/init.d/mysql start --mysqld --skip-grant-tables

启动MySQL服务后，就可以使用root用户登录了。

2、登录root用户，并且设置新的密码

通过上述方式启动MySQL服务以后，可以不输入密码就登录root用户。登录以后，可以使用update语句来修改密码。

3、加载权限表

修改完密码以后，必须用flush privileges语句来加载权限表。加载权限表后，新密码开始有效。而且，MySQL服务器开始进行权限认证。用户必须输入用户名和密码才能登陆MySQL数据库。加载权限表的代码执行结果如下：

flush privileges;

这样，root用户的密码就已经设置成功。

## 三、权限管理 ##

权限管理主要是对登录到数据库的用户进行权限验证。所有用户的权限都存储在MySQL的权限表中。数据库管理员要对权限进行管理。合理的权限管理能够保证数据库的系统安全，不合理的权限设置可能会给数据库系统带来意想不到的危害。

### 1、MySQL的各种权限 ###

MySQL数据库中有很多种类的权限，这些权限都存储在mysql数据库下的权限表中。其中，user表中的权限种类很多。以下简单举例几种权限。

<table> 
 <thead> 
  <tr> 
   <th>权限名称</th> 
   <th>对应user表中的列</th> 
   <th>权限范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>create</td> 
   <td>Create_priv</td> 
   <td>数据库、表或索引</td> 
  </tr> 
  <tr> 
   <td>drop</td> 
   <td>Drop_priv</td> 
   <td>数据库或表</td> 
  </tr> 
  <tr> 
   <td>grantoption</td> 
   <td>Grant_priv</td> 
   <td>数据库、表、存储过程或函数</td> 
  </tr> 
  <tr> 
   <td>references</td> 
   <td>References_priv</td> 
   <td>数据库或表</td> 
  </tr> 
  <tr> 
   <td>alter</td> 
   <td>Alter_priv</td> 
   <td>修改表</td> 
  </tr> 
  <tr> 
   <td>delete</td> 
   <td>Delete_priv</td> 
   <td>删除表</td> 
  </tr> 
 </tbody> 
</table>

### 2、授权 ###

授权就是为某个用户赋予某些权限。例如，可以为新建的用户赋予查询所有数据库的表的权限。合理的授权能够保证数据库的安全。不合理的授权会使数据库存在安全隐患。MySQL中使用GRANT关键字来为用户设置权限。

MySQL中，必须拥有GRANT权限的用户才可以执行GRANT语句。GRANT语句的基本语法如下：

grant priv_type [(column_list)] on database.table 
    	to user [identified by [password] 'password']
    	[,user [identified by [password] 'password']] ...
    	[with with_option [with_option]...]

其中，priv\_type参数表示权限的类型；column\_list参数表示权限作用于哪些列上，没有该参数时作用于整个表上；user参数由用户名和主机名构成，形式是“‘username’@‘hostname’”;identified by参数用来为用户设置密码；password参数是用户的新密码。

with关键字后面带有一个或多个with\_option参数。这个参数有5个选项，详细介绍如下：

grant option:被授权的用户可以将这些权限赋予给别的用户；  
max\_queries\_per\_hour count: 设置每个小时可以允许执行count次查询；  
max\_updates\_per\_hour count: 设置每个小时可以允许执行count次更新；  
max\_connections\_per\_hour count: 设置每小时可以建立count连接；  
max\_user\_connections count: 设置单个用户可以同时具有的count个连接数。

下面使用grant命令来创建一个新的用户‘test5’。‘test5’对所有数据库有select和update的权限。密码设置为‘test5’，而且加上with grant option子句。grant语句的代码如下：

grant select,update on *.*
    	to 'test5'@'localhost' identified by 'test5'
    	with grant option;

### 3、收回权限 ###

收回权限就是取消某个用户的某些权限。例如，如果数据库管理员觉得某个用户不应该拥有delete权限，那么就可以将delete权限收回。收回权限的方式可以保证数据库的安全。MySQL中使用revoke关键字来为用户设置权限。收回指定权限的revoke语句的基本语法如下：

revoke priv_type [(column_list)]...
    	on database.table
    	from user [,user]...

revoke语句中的参数与grant语句的参数意思相同。其中，priv\_type参数表示权限的类型；column\_list参数表示权限作用于哪些列上，没有该参数时作用于整个表上；user参数由用户名和主机名构成，形式是“‘username’@‘hostname’”。

收回全部权限的revoke语句的基本语法如下：

revoke all privileges,grant option from user [,user]...

下面收回test5用户的update权限。revoke语句的代码如下：

revoke update on **
    	from 'test5'@'localhost';

### 4、查看权限 ###

在MySQL中，可以使用select语句来查询user表中各用户的权限，也可以直接使用show grants语句来查看权限。mysql数据库下的user表中存储着用户的基本权限，可以使用select语句来查看。select语句的代码如下：

select * from mysql.user;

## 四、总结 ##

这里的相关内容还没有整理完毕，文章后面持续更新，建议收藏。

文章中涉及到的命令大家一定要像我一样每个都敲几遍，只有在敲的过程中才能发现自己对命令是否真正的掌握了。

可以微信搜索【小奇JAVA面试】第一时间阅读，回复【资料】获取福利，回复【项目】获取项目源码，回复【简历模板】获取简历模板，回复【学习路线图】获取学习路线图。

[13b0efb5f9ef4a7a89f2057eab98c67a.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/d5c4ee3e2b594dbc92839de31197151e.png
[fe16b296a9d74ea6b882c55aeb6c1096.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/b0f27e0dc63a4f63bfcf21e12d884ad1.png