MySQL高级六：权限管理

权限管理

概述：

1. MySQL只允许你做你权力以内的事情，不可以越界。比如：

   只允许你执行SELECT操作，就不能执行UPDATE操作。只允许你从某台机器上连接MySQL，就不也能从其它机器上连接MySQL

2. 权限是指该用户针对某些数据库、库内的表甚至到列是否具有操作的权力。

一、权限列表

1. 查看权限列表命令

   show privileges;
   1

2. 权限分布

   	权限分布	可能设置的权限
   1	表权限	select，insert，update，delete，create，drop，grant，references，index，alter
   2	列权限	select，insert，update，references
   3	过程权限	execute，alter routine，grant

3. 权限说明

   ① CREATE和DROP

   可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将 MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。

   ② SELECT、INSERT、UPDATE和DELETE

   允许在一个数据库现有的表上实施操作。

   ③ SELECT

   只有在它们真正从一个表中检索行时才被用到。

   ④ INDEX

   允许创建或删除索引，INDEX适用于已 有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。

   ⑤ ALTER

   可以使用ALTER TABLE来更改表的结构和重新命名表。

   ⑥ CREATE ROUTINE

   用来创建保存的 程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，

   ⑦ EXECUTE

   用来执行保存的 程序。

   ⑧ GRANT

   允许授权给其他用户，可用于数据库、表和保存的程序。

   ⑨ FILE

   使用户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权 限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

二、授予权限的原则

1. 只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可 以了，不要给用户赋予update、insert或者delete权限。
2. 创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
3. 为每个用户 设置满足密码复杂度的密码 。
4. 定期清理不需要的用户 ，回收权限或者删除用户。

三、直接授予权限

1. 直接赋予所有权限（除了grant权限）

   GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';
   1

2. 授予部分权限

   GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
   1

   举例

   GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost ;
   1

3. 注意：如果该用户不存在，就直接被创建

四、查看权限

1. 查看当前用户的权限

   SHOW GRANTS;
   1

2. 查看指定用户的权限

   SHOW GRANTS FOR 'user'@'主机地址';
   1

五、收回权限

1. 收回权限

   REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
   1

   需要用户重新登录后才能生效

2. 举例

   #收回全库全表的所有权限
   REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
   #收回mysql库下的所有表的插删改查权限
   REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;
   1
   2
   3
   4

六、权限表

• MySQL的权限控制功能十分完善，主要源于MySQL服务器通过权限表来控制用户对数据库的访问。权限表放在mysql数据库中，这些权限表中最重要的是user表、db表，除此之外还有table_priv表、column_priv表和proc_priv表等。在MySQL服务器启动时，服务器就会将这些数据库权限表中的权限信息的内容读入内存。

1. user表

   

2. db表

   当前用户对具体的某个数据库的权限

   

3. tables_priv表

   

4. columns_priv表

   

七、访问控制

1. 连接核实阶段

   当用户试图连接MySQL服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确 定接受或者拒绝连接。即客户端用户会在连接请求中提供用户名、主机地址、用户密码，MySQL服务器 接收到用户请求后，会使用user表中的host、user和authentication_string这3个字段匹配客户端提供信 息。 服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受 连接。如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待 用户请求。

2. 请求核实阶段

   一旦建立了连接，服务器就进入了访问控制的阶段2，也就是请求核实阶段。对此连接上进来的每个请 求，服务器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发 挥作用的地方。这些权限可以来自user、db、table_priv和column_priv表。

   ① 首先 检查user表 ，如果指定的权限没有在user表中被授予，那么MySQL就会继续 检 查db表 ，

   ② db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指 定数据库的所有表中的数据；如果在该层级没有找到限定的权限，

   ③ 继续 检查tables_priv表 以 及 columns_priv表 ，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将 返回错 误信息 ，用户请求的操作不能执行，操作失败。

八、角色管理

1. 角色

   在MySQL中，角色是权限的集合，可以为角色添加或移除权限。（MySQL8.0引入的新功能）

2. 目的

   方便管理拥有相同权限的用户。用户可以被赋予角色，同时也被授予角色包含的权限。

3. 创建角色

   CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
   1

4. 给角色赋予对于具体表的权限

   GRANT privileges ON table_name TO 'role_name'[@'host_name'];
   1

   举例

   GRANT SELECT ON demo.settlement TO 'manager';
   1

5. 查看角色的权限

   SHOW GRANTS FOR 'manager';
   1

6. 回收角色的权限

   REVOKE privileges ON tablename FROM 'rolename';
   1

7. 删除角色

   DROP ROLE role [,role2]...
   1

   注：如果删除了角色，那么用户也就失去了通过这个角色所获得的所有权限

8. 给用户赋予角色

   GRANT role [,role2,...] TO user [,user2,...];
   1

   查看当前用户的角色是否被激活

   SELECT CURRENT_ROLE();
   1

9. 激活角色

   SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';
   1

10. 撤销用户的角色

    REVOKE role FROM user;
    1

11. 设置强制角色

    方式1：服务启动前设置

    [mysqld]
    mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'
    1
    2

    方式2：运行时设置

    # 系统重启后仍然有效
    SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; 
    # 系统重启后失效
    SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; 
    1
    2
    3
    4

总结

1. 尽量使用数据库自己的角色和用户机制来控制访问权限，不要轻易用root账户，因为root账户和密码都放在代码里不安全，一旦泄露，数据库就会完全失去保护。