mysql 开发进阶篇系列 53 权限与安全(账号管理的各种权限操作 上)

一. 概述

　　在了解前两篇的权限系统介绍后，这篇继续讲账号的管理，这些管理包括账号的创建，权限更改，账号删除等。用户连接数据库的第一步都是从账号创建开始。

　　1.  创建账号

　　　　有两种方法可以用来授权账号:(1) 是使用grant来授权账号，(2) 是直接操作权限表。 使用grant操作简单，出错率更少。使用grant授权账号时，需先创建用户。

-- 在上一篇中有演示创建一个z1@localhost用户，脚本如下：
create user 'z1'@'localhost' identified by '123456';

-- 语法如下
create user [if not exists]
    user [auth_option] [, user [auth_option]] ...
    [require {none | tls_option [[and] tls_option] ...}]
    [with resource_option [resource_option] ...]
    [password_option | lock_option] ...

　　　　创建用户，更多的语法选项，请参考官方文档，里面也有很多创建用户案例：

-- grant 授权语法
grant
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    on [object_type] priv_level
    to user [auth_option] [, user [auth_option]] ...
    [require {none | tls_option [[and] tls_option] ...}]
    [with {grant option | resource_option} ...]

　　　　在创建完用户账号后，就需要使用grant 来授权相应的权限，语法请参考官方文档： https://dev.mysql.com/doc/refman/5.7/en/grant.html

　　

　　2.  授权给用户z1, 可以在所有数据库上执行所有权限，但只能从本地进行连接。

-- 为z1@localhost用户给予所有权限，但只能从本地连接
grant all privileges  on  *.* to z1@localhost

　　　　此时user权限表的z1用户，所有权限字段值都为 y (除了grant_priv), 上篇z1用户对tables_priv权限表和columns_priv权限表写入的数据不会改变，这是因为全局权限覆盖局部权限。
　　　　

 

　　3.  在上面的基础上，增加对z1的grant权限

grant all privileges  on  *.* to z1@localhost with grant option

　　　　

 

　　4. 在上面的基础上，将z1密码123456修改密码为: 654321

  -- 用户z1的authentication_string将被修改
grant all privileges  on  *.* to z1@localhost  identified by '654321'    with grant option

warning code : 1287
using grant statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. 
use alter user statement for this operation.
提示: grant是用来修改属性而不是特权，在以后的版本中删除，推荐使用alter user

　　　　下面尝试登录下，在xshell中查看ip地址是本机。使用z1连接数据库成功，如下面所示:
　　　　
　　　　
　　　　但通过windows客户端电脑，使用sqlyog工具来连接运程mysql服务器时，报错误代码1045，原因是z1用户连接的host只能是localhost也就是本机。连接错误如下所示：
　　　　

 

　　5. 在上面的基础上，授权可以是任意ip来连接mysql服务器。

-- 直接修改表的z1 连接来源限制。允许任意地址连接
update mysql.`user` set `host`='%'  where `host`='localhost' and `user`='z1'
-- 刷新权限
flush privileges

　　　　

　　上面修改后，host已改变值，此时再使用客户端的sqlyog工具来连接远程mysql服务器时，就成功了。mysql 数据库中是通过user表的host字段来进行控制，host可以是以下类型的赋值：

host值	user值	匹配的连接
%	'z1'	z1 可以从任何主机来连接
%	''	任何用户，可以从任何主机来连接
%.loc.gov	'z1'	z1 可以从loc.gov域的任何主机来连接
172.168.18.200	'z1'	z1 可以从172.168.18.200的ip地址的主机来连接
172.168.18.%	'z1'	z1 可以从172.168.18 的网段的任何主机来连接

　　　　如果用户连接进来，user权限表中有多个匹配，原则是：

　　　　　　(1) 服务器在启动读入user表后进行排序(select  * from  mysql.`user` order by `host` desc)。
　　　　　　(2) 然后当用户试图连接时，以排序的顺序浏览user表每行。
　　　　　　(3) 服务器使用与客户端和用户名匹配的第一行。
　　　　下面是user权限表数据排序后，存入在服务器内存中。
　　　　　　　　
　　　　注意：mysql 数据库的user 表中host 的值为*或者空，表示所有外部ip都可以连接，但是不包括本地服务器localhost，因此，如果要包括本地服务器，必须单独为localhost 赋予权限。

 

　　6.其它事项

　　　　6.1 授权用户管理权限时(51篇中讲到权限列分为：普通权限和管理权限)，管理权限如:super,process,file 等不能够指定某个数据库， on后面必须跟 *.*,如下所示：

　　grant super,process,file on *.*  to  z1@localhost

　　　　6.2 直接操作权限表也可以进行权限的创建和修改，其实grant 操作的本质就是修改权限表后进行权限的刷新。如上面的第5点修改任意ip，是直接操作的权限表。下面二个脚本是同样的效果，如下所示：

-- 使用grant
grant select,insert,update,delete on test1.* to 'z2'@'%' identified by '123';
-- 直接操作权限表
insert into db (host,db,user,select_priv,insert_priv,update_priv,delete_priv)
values('%','test1','z2','y','y','y','y');