MySQL 权限管理 DCL数据控制语言

1.权限基础

1.1 如何识别一个用户

采⽤⽤户名+主机名来识别⽤户的⾝份。

1.2 权限验证过程

1. 连接数据库，此时mysql会根据你的⽤户名及你的来源（ip或者主机名称）判断是否有权限连接。
2. 阶段2：对mysql服务器发起请求操作，如create table、select、delete、update等操作，此时mysql会判断你是否有权限操作这些指令。

1.3 权限生效时间

⽤户及权限信息放在库名为mysql的库中。mysql启动时，这些内容被读进内存并且从此时⽣效，所以如果通过直接操作这些表来修改⽤户及权限信息后，需要重启mysql 或者执行 FLUSH PRIVILEGES; 才可以⽣效。
⽤户登录之后，mysql会和当前⽤户之间创建⼀个连接，此时⽤户相关的权限信息都保存在这个连接，存放在内存中，此时如果有其他地⽅修改了当前⽤户的权限，这些变更的权限会在下⼀次登录时才⽣效。

2.权限相关操作

2.1 创建用户

⽤户信息在 mysql.user 表中。

查看mysql中所有用户
USE mysql;
SELECT USER,HOST FROM USER;

创建用户
   语法：CREATE USER 用户名[@主机名] [IDENTIFIED BY '密码'];
   注意：
	1.主机名默认值为 %，表示该用户可以从任何主机连接MySQL服务器
	2.密码可以省略，表示无密码登录
CREATE USER test1;

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

说明：test2的主机为localhost表示本机，此用户只能在本机登陆mysql;
CREATE USER 'test2'@'localhost' IDENTIFIED BY '123';

说明：test3可以从任何机器连接到mysql服务器;
CREATE USER 'test3'@'%' IDENTIFIED BY '123';

说明：test4可以从192.168.1段的机器连接mysql;
CREATE USER 'test4'@'192.168.1.%' IDENTIFIED BY '123';

2.2 修改密码

方式一：管理员直接修改密码
SET PASSWORD FOR ‘test1’@’%’ = PASSWORD(‘123456’);
注意：在mysql 5.7.9以后废弃了password字段和password()函数；密码字段为authentication_string。
方式二：通过修改表来修改密码（推荐）
ALTER USER ‘test1’@’%’ IDENTIFIED BY ‘123456789’;
FLUSH PRIVILEGES;

2.3 用户权限

2.3.1 查看权限

• 查看某个用户的权限
  SHOW GRANTS FOR ‘test1’@’%’;
• 查看当前用户的权限
  SHOW GRANTS;

2.3.2 授予权限

• 语法：GRANT PRIVILEGES ON database.table TO ‘username’[@‘host’] [WITH GRANT OPTION]
• 说明：

priveleges (权限列表)，可以是 ALL ，表示所有权限，也可以是 select、update 等权限，多个权限之间用逗号分开。
ON 用来指定权限针对哪些库和表，格式为 数据库.表名，点号前用来来指定数据库名，点号后用来来指定表名，*.* 表示所有数据库所有表。
TO 表示将权限赋予某个用户, 格式为 username@host ，@前为用户名，@后接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。
WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。
注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。
备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给⽤用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。

• 举例：

给test1授权可以操作所有库的所有权限，相当于DBA
GRANT ALL ON *.* TO 'test1'@'%';

test1可以对test_db库中所有表执行 select
GRANT SELECT ON test_db.* TO 'test1'@'%';

test1可以对test_db库中所有表执行select、update
GRANT SELECT,UPDATE ON test_db.* TO 'test1'@'%';

test1用户只能查询mysql.user表的 user,host字段
GRANT SELECT(USER,HOST) ON mysql.`user` TO 'test1'@'%';

2.3.3 撤销权限

语法:
REVOKE PRIVILEGES ON database.table FROM ‘用户名’[@‘主机’];

权限小练习：创建用户，查看权限，赋予权限，查看权限，撤销权限，查看权限

CREATE USER 'test2'@'%' IDENTIFIED BY '123456789root';
SHOW GRANTS FOR 'test2'@'%';
GRANT SELECT(USER,HOST) ON mysql.`user` TO 'test2'@'%';
SHOW GRANTS FOR 'test2'@'%';
REVOKE SELECT(HOST) ON mysql.`user` FROM 'test2'@'%';
SHOW GRANTS FOR 'test2'@'%';

2.4 删除用户

• 方式一：
  DROP USER ‘test2’@’%’;
• 方式二：
  DELETE FROM USER WHERE USER=‘test2’ AND HOST=’%’;
  FLUSH PRIVILEGES;

3. 说明总结

授权原则说明：

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

总结：

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