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

时间:2022-09-18 08:14:47

一. 概述

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

  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] ...

    创建用户,更多的语法选项,请参考官方文档,里面也有很多创建用户案例:https://dev.mysql.com/doc/refman/5.7/en/grant.html

-- 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权限表写入的数据不会改变,这是因为全局权限覆盖局部权限。
    mysql 开发进阶篇系列 53 权限与安全(账号管理的各种权限操作 上)

 

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

GRANT ALL PRIVILEGES  ON  *.* TO z1@localhost WITH GRANT OPTION

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

 

  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连接数据库成功,如下面所示:
    mysql 开发进阶篇系列 53 权限与安全(账号管理的各种权限操作 上)
    mysql 开发进阶篇系列 53 权限与安全(账号管理的各种权限操作 上)
    但通过windows客户端电脑,使用sqlyog工具来连接运程mysql服务器时,报错误代码1045,原因是z1用户连接的Host只能是localhost也就是本机。连接错误如下所示:
    mysql 开发进阶篇系列 53 权限与安全(账号管理的各种权限操作 上)

 

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

-- 直接修改表的z1 连接来源限制。允许任意地址连接
UPDATE mysql.`user` SET `Host`='%'  WHERE `Host`='localhost' AND `User`='z1'
-- 刷新权限
FLUSH PRIVILEGES

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

  上面修改后,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 开发进阶篇系列 53 权限与安全(账号管理的各种权限操作 上)
    注意: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');