mysql 开发进阶篇系列 53 权限与安全(账号管理的各种权限操作 上)
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] ...
创建用户，更多的语法选项，请参考官方文档，里面也有很多创建用户案例：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权限表写入的数据不会改变，这是因为全局权限覆盖局部权限。
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');