Mysql系列—管理员必备技能（三）（必须掌握）

一、介绍Mysql权限工作原理

Mysql是如何识别一个用户的呢？

Mysql为了安全性考虑，采用：主机名 + 用户名 来识别一个用户的身份。因为在互联网中很多通过用户名来判断一个用户的身份，但是我们可以通过IP或主机名判断一台机器。某个用户通过这个机器发起连接并操作Mysql数据库，我们可以识别为一个用户，所以mysql中采用用户名 + 主机名来识别用户身份。当一个用户对Mysql发送指令的时候，Mysql就是通过用户名和来源（主机）来断定用户的权限。

Mysql权限验证分为两个阶段：

1、阶段1：连接数据库。此时Mysql会根据你的用户名及你的来源（IP或者主机名称）判断是否有权限连接；

2、阶段2：对Mysql服务器发起请求操作，如CREATE TABLE（建表）、SELECT、DELETE、UPDAE、INSERT、CREATE INDEX（建索引）等操作，此时Mysql会判断你是否有权限操作这些指令。

权限生效时间

用户及权限信息放在库名为mysql的库中，Mysql启动时，这些内容被读进内存并且从此时生效。所以如果想直接通过操作mysql库里面的表来修改用户及权限信息，需要 重启Mysql 或者执行 flush privileges; 才能生效。

二、查看所有用户

用户信息在mysql.user表中，如下所示：

三、创建用户

语法：

CREATE USER 用户名[@主机名] [IDENTIFIED BY '密码'];

说明：

1、主机名默认值为%，表示这个用户可以从任何主机连接Mysql服务器；

2、密码可以省略，表示无密码登录；

示例1：不指定主机名

不指定主机名时，表示这个用户可以从任何主机连接Mysql服务器。

上面创建了用户名为 test1无密码的用户，也没有指定主机，可以看出host的默认值为 %，表示 test1 可以从任何机器登录到Mysql中。

用户创建之后可以在 mysql库 中通过 select user,host from user; 查看到用户的用户名和主机名。

其他示例：

1、 创建一个用户名为test2、主机为localhost、登录密码为123的用户。

2、 创建一个用户名为test3、登录密码为123、可在任何机器登录的用户。

3、 创建一个用户名为test4、登录密码为123，可以从192.168.11端机器连接的用户。

四、修改密码

方式1：通过管理员修改密码

SET PASSWOED FOR ‘用户名’@‘主机’ = PASSWORD(‘新密码’);

方式2：先通过要修改密码的用户进入Mysql服务器，再执行下面的语句

set password = password(‘新密码’);

方式3：通过修改mysql.user表的authentication_string字段来修改密码

use mysql;
update user set authentication_string = password(‘新密码’) where user=‘用户名’ and host =‘主机名’;
flush privileges;
quit;

方式3，我是看着大佬的公众号写的，但是在我这里并没有修改成功。

后面我自己查阅资料，使用了下面的语句，就修改成功了：

use mysql;
update user set password = password(‘新密码’) where user=‘用户名’ and host=‘主机名’;
flush privileges;
quit;

注意：

• 通过表的方式修改之后，需要执行flush privileges; 才能对用户生效；
• 后面再次查阅资料，发现老版本的Mysql密码字段是password，新一点的版本是使用authentication_string字段表示密码。Mysql版本查询：一是 select version(); 二是：mysql --version；这是未登录的情况下使用。

五、给用户授权

创建用户后，需要给用户授权才有意义。

语法：

grant命名说明：

• privileges（权限列表），可以是all（表示所有权限），也可以是select、update等权限。多个权限之间用逗号隔开；
• ON 用来指定权限针对哪些库和表，格式为 数据库 . 表名。如果写成这样： * . * 表示所有数据库所有表。
• TO 表示将权限赋予被某个用户，格式为 ’用户名’@'主机名’ ，@前面为用户名，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何主机。
• with grant option 表示该用户可以将自己拥有的权限授予给别的用户。注意：经常有人在创建操作用户时不指定 with grant option 选项导致后面该用户不能使用 grant 命令创建用户或给其他用户授权。
• 可以使用 grant 重复给用户添加权限，权限叠加。比如你先给用户添加一个select权限，然后又可使用grant给用户新增权限，例如再添加insert权限，这时这个用户就拥有了select和insert两个权限。

示例1：给test1用户授权可以操作所有库所有表的所有权限，相当于dba

grant all on * . * to ‘test1’@’%’;

示例2：给test2用户授权对demo库中所有表执行select和insert权限

grant select,insert on demo.* to ‘test2’@‘localhost’;

示例3：给test3用户授权只能查询mysql库中user表中的user、host字段权限

grant select(user,host) on mysql.user to ‘test3’@’%’;

六、查看一下用户有哪些权限：

• show grant for ‘用户名’[@‘主机名’];
  
• show grants; 查看当前用户权限
  

七、撤销用户权限

语法：

revoke privileges ON 数据库名.表名 from ‘用户名’[@‘主机名’];

可以先通过show grants命令查询一下用户对应的权限，然后使用 revoke 命令撤销用户对应的权限，示例：

八、删除用户

方式1：

drop user ‘用户名’[@‘主机名’];

方式2：

delete from user where user=‘用户名’ and host = ‘主机名’;
flush privileges;

注意：通过表的方式删除，需要调用 flush privileges; 刷新权限信息（权限启动的时候在内存保存着，通过表的方式修改之后需要刷新一下）。

九、授权原则说明

• 只授予能满足需要的最小权限，防止用户干坏事（不要去挑战人性）。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限；
• 创建用户的时候限制用户的登录主机，一般是限制成指定IP或内网IP段；
• 初始化数据库的时候删除没有密码的用户，安装完数据库的时候会自动创建一些用户，这些用户默认没有密码；
• 为每个用户设置满足密码复杂度的密码；
• 定期清理不需要的用户，回收权限或者删除用户。

十、总结

1. 通过命令的方式操作用户和权限不需要刷新，下次登录自动生效；
2. 通过操作mysql库中表的方式修改用户信息，需要调用 flush privileges; 刷新一下，下次登录自动生效；
3. Mysql识别用户身份的方式为：用户名 + 主机；
4. 本文中讲到的一些指令中带主机的，主机都可以省略，默认值为%,表示所有机器；
5. Mysql中用户和权限的信息在库名为mysql的库中。