MySQL数据库用户管理详解
4536字,阅读需时16分钟

数据库中存储大量的数据,保护数据不受内部和外部的侵害是数据库管理的重要组成部分。MySQL系统提供了一整套保护数据安全的机制,包括角色、用户、权限等方法和手段。

本篇主要以CentOS为例,探讨MySQL对登录用户的管理,用户远程连接MySQL数据库时,需要提供连接用户名和登录密码,MySQL对用户名和密码进行验证,只有验证通过的用户才能有权限访问数据库。MySQL安装完成后,已经默认建立了数据库管理员root用户,通过root用户可以建立其他的登录用户,MySQL用户管理主要由查看MySQL用户信息、创建新的用户、删除用户和修改密码组成,下面分别予以讲解。

1、查看MySQL用户信息

MySQL的用户信息存储在名称为mysql数据库user表中,查看user表结构步骤如下。

(1)登录MySQL数据库

用SSH客户端连接CentOS服务器,打开终端命令输入窗口,在终端输入窗口输入命令:

mysql -uroot –p

终端窗口提示输入密码,输入root用户登录密码,如图1所示。

 image.png

图 1 用root账户登录MySQL数据库

(2)打开mysql数据库

使用use命令可以打开mysql数据库,在终端窗口输入命令:

use mysql;

回车执行命令后,终端窗口输出“Database changed”信息,说明mysql数据库成功打开,如图2所示。

image.png

图 2 使用use命令打开mysql数据库

(3)查看mysql数据库表信息

使用show tables命令可以查看mysql数据库表信息,在终端窗口输入命令:

show  tables;

回车执行命令后,终端窗口输出mysql数据库表信息,如图3所示。从图4-3可以看出,mysql数据库负责存储MySQL的用户、权限设置、关键字等MySQL自己需要使用的控制和管理信息,其中就包括user表。

注意:如果对mysql不是很了解,也不要轻易修改这个数据库里面的表信息。

image.png

图 3 查看MySQL数据库表信息

(4)查看user表结构

可以使用desc命令查看表结构,在终端窗口输入命令:

desc user;

回车执行命令后,终端窗口输出user表结构信息,如图4所示。

image.png

图 4  user表信息结构

从图4可以看出,user表有39个字段,这些字段可以分为四类,分别是用户列、权限列、安全列、资源控制列。

● 用户列

用户列包括Host、User、authentication_string(5.7以前的版本为Password)字段,分别表示主机名、用户名和密码,root用户的信息也存储在这个表中。

● 权限列

权限列包括Select_priv、Insert_priv等以priv结尾的字段,这些字段的值只有Y和N。Y表示该权限可以用到Host所指数据库上;N表示该权限不能用到Host所指数据库上。

● 安全列

安全列有四个字段,分别是ssl_type、ssl_cipher、x509_issuer、x509_subject。ssl用于传输加密,x509标准用来标识用户。

● 资源列

资源控制列也有四个字段,分别说明如下:

max_questions:每小时可以执行的查询次数;

max_updates:每小时可以执行的数据更新次数;

max_connections:每小时可以建立的连接数;

max_user_connections:单个用户可以同时具有的连接数。默认值为0,表示无限制。

(5)查看user记录

可以使用SQL查询命令查询user表中的用户记录,在终端窗口输入命令:

select Host,User from user;

该查询命令从user表中查找Host,User字段,并返回包含这两个字段的所有记录。如图5所示。

 image.png

图 5 查看user表中的所有记录

从图5可以看出,默认的user用户表有4条记录,root用户有两条记录,分别对应值为%和localhost的Host字段,Host的值为%表示匹配所有主机,允许客户端远程连接MySQL服务器;Host的值为localhost表示匹配本地主机,不允许客户端远程连接。

2、创建MySQL新用户

MySQL默认的root用户具有最高权限,因为为了安全起见,远程连接MySQL数据库时,需要创建一个新的用户。

在MySQL数据库中,创建用户有三种方式:

●  使用CREATE USER命令来创建新的用户;

●  直接在mysql.user表中使用SQL的INSERT命令插入一条用户记录;

●  使用GRANT语句来新建用户;

(1)使用CREATE USER命令创建用户

用CREATE USER命令创建用户,必须具有CREATE USER权限,root用户默认具有全部权限。CREATE USER命令格式为:

CREATE USER user[IDENTIFIED BY [PASSWORD] 'password'];

其中,user参数表示新建用户的名称,user由用户名(User)和主机名(Host)构成;IDENTIFIED BY关键字用来设置用户的密码。

例如创建一个用户名为test,密码为MyNewPass2!的用户,在终端窗口输入命令:

CREATE USER 'test'@'%' IDENTIFIED BY ' MyNewPass2!';

执行该命令后user表会增加一行记录,但权限暂时全部为‘N’。如图6所示。

image.png

图 6 使用CREATE USER命令创建用户

(2)使用SQL的INSERT命令新建用户

可以使用INSERT命令直接将用户的信息添加到mysql.user表。但必须拥有mysql.user表的INSERT权限。在终端窗口输入命令:

INSERT INTO mysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject) VALUES('%','test1',PASSWORD('MyNewPass3!'),'','','');

执行该命令后,要使用FLUSH PRIVILEGES命令来使用户生效。如图7所示。

image.png

图 7 使用SQL的INSERT命令新建用户

(3)使用GRANT命令来新建用户

使用GRANT命令创建用户时,可以直接为新建用户授权,命令格式如下:

GRANT priv_type ON database.table

TO user[IDENTIFIED BY [PASSWORD] 'password']

[,user [IDENTIFIED BY [PASSWORD] 'password']...]

该命令可以创建多个用户,其中priv_type参数表示新用户的权限;databse.table参数表示新用户的权限范围;user参数表示新用户的名称,由用户名和主机构成;IDENTIFIED BY关键字用来设置密码;password表示新用户的密码。

例如,创建名称为test3,密码为MyNewPass4!,对所有数据库具有SELECT权限,并允许远程连接的新用户,在终端窗口输入命令:

GRANT SELECT ON *.* TO test4@'%' IDENTIFIED BY 'MyNewPass4!';

执行该命令后,user表多了一条用户记录,如图8所示。

image.png

图 8 使用GRANT命令来新建用户

3、删除MySQL用户

MySQL也提供了删除用户的命令,当创建的用户不再被需要时,可以使用DROP USER命令删除用户,使用该命令时,数据库登录用户必须拥有对mysql.user表的DELETE权限。

注意: root用户不能被删除。

DROP USER的命令格式如下:

DROP USER user[,user]...;

其中user是需要删除的用户,由用户名(User)和主机名(Host)构成,可以删除多个用户。

例如,删除名称为test1的用户,在终端窗口输入命令:

DROP USER 'test1'@'%';

执行该命令后,用户名称为test1的记录从user表中删除,删除完成后,同样需要执行FLUSH PRIVILEGES才生效。如图9所示。

image.png

图 9 用DROP USER删除MySQL用户

4、修改用户密码

从数据库安全性考虑,需要定期修改登录数据库的用户密码,修改用户密码有以下方式:

●  使用mysqladmin命令修改用户密码;

●  使用SQL的UPDATE命令修改user表的用户密码;

●  使用SET语句修改用户密码;

●  使用GRANT语句修改用户密码;

(1)使用mysqladmin命令修改用户密码

mysqladmin客户端程序是MySQL官方提供的shell命令行工具,可以在终端窗口输入命令参数,执行对MySQL用户的密码修改、关闭MySQL服务、输出服务器的基本状态信息等操作。

例如,把root用户的原密码“MyNewPass1!”修改为新密码“MyNewPass6!”,在终端窗口输入命令:

mysqladmin -u root –pMyNewPass1! password MyNewPass6!

执行该命令后,root用户的密码被修改为MyNewPass6!。

(2)使用SQL的UPDATE命令修改user表的用户密码

可以使用SQL的UPDATE命令直接修改user表中的authentication_string字段值,也可以达到修改密码的目的。

例如,修改user表test用户的密码为“TestNew1!”,在终端窗口输入命令:

UPDATE user SET authentication_string = PASSWORD('TestNew1!') WHERE USER = 'test';

执行该命令后,test用户的密码被修改为“TestNew1!”,如图10所示。

 image.png

图 10  用UPDATE命令修改用户密码

(3)使用SET语句来修改密码

可以使用SET PASSWORD 命令修改密码,使用root账户登录到MySQL后,可以直接使用SET PASSWORD 命令修改root的登录密码。在终端窗口输入命令:

SET PASSWORD = PASSWORD("MyNewPass1!");

如果要修改其他用户的密码,在终端窗口输入如下命令:

SET PASSWORD FOR 'test'@'%'=PASSWORD("MyNewPass2!");

(4)使用GRANT语句来修改用户的密码

使用GRANT语句修改普通用户的密码,必须拥有GRANT权限,命令格式如下:

GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password'];

其中,user由用户名和主机名构成,IDENTIFIED BY后面为密码。

例如,修改名称为test用户的密码为“MyNewPass2!”,在终端窗口输入如下命令:

GRANT SELECT ON *.* TO 'test'@'%' IDENTIFIED BY ' MyNewPass2!';

执行该命令后,test用户的密码被修改为“MyNewPass2!”。

我要评论
全部评论
郎宏林
授课老师
授课老师简介
项目经理,系统分析和架构师,从事多年中文信息处理技术。熟悉项目管理、擅长项目需求分析和设计、精通Java、C#、Python等编程语言。
下载APP

手机、电脑同步学

用微信或手机浏览器扫描二维码,即可下载APP。

  • 备案号:鲁ICP备15001146号
  • @1997-2018 潍坊米粒花网络技术有限公司版权所有