MYSQL入门学习之二十九：MySQL访问控制实现原理



       MySQL 访问控制实际上由两个功能模块共同组成，一个是负责“看守MySQL 大门”的用户管理模块，另一个就是负责监控来访者每一个动作的访问控制模块。用户管理模块决定造访客人能否进门，而访问控制模块则决定每个客人进门能拿什么不能拿什么。下面是一张MySQL 中实现访问控制的简单流程图(见下图)：

1、用户管理
        （1）、在MySQL 中，用户访问控制部分的实现比较简单，所有授权用户都存放在一个系统表中：mysql.user，当然这个表不仅仅存放了授权用户的基本信息，还存放有部分细化的权限信息。用户管理模块需要使用的信息很少，主要就是Host，User，Password 这三项，都在mysql.user 表中。
        一个用户要想访问MySQL，至少需要提供上面列出的这三项数据，MySQL 才能判断是否该让他“进门”。这三项实际上由两部分组成：访问者来源的主机名（或者主机IP 地址信息）和访问者的来访“暗号”（登录用户名和登录密码），这两部分中的任何一个没有能够匹配上都无法让看守大门的用户管理模块乖乖开门。其中Host 信息存放的是MySQL 允许所对应的User 的信任主机，可以是某个具体的主机名（如：mytest）或域名（如：www.domain.com），也可以是以“%”来充当通配符的某个域名集合（如：%.domain.com）；也可以是一个具体的IP 地址(如：1.2.3.4)，同样也可以是存在通配符的域名集合(如：1.2.3.%)；还可以用“%”来代表任何主机。
        但是这里有一个比较特殊的访问限制，如果要通过localhost 访问的话，必须要有一条专门针对localhost 的授权信息，即使不对任何主机做限制也不行。如下例所示，存在def@%的用户设置，但是如果不使用-h 参数来访问，则登录会被拒绝，因为mysql 在默认情况下会连接localhost。但是当通过-h 参数，明确指定了访问的主机地址之后就没问题了。
        （2）、如果MySQL 正在运行之中的时候，对系统做了权限调整，那调整之后的权限什么时候会生效呢？
        先了解何时MySQL 存放于内存结构中的权限信息被更新：
        FLUSH PRIVILEGES 会强行让MySQL 更新Load 到内存中的权限信息；
        GRANT、REVOKE 或者CREATE USER 和DROP USER操作会直接更新内存中的权限信息；
        重启MySQL 会让MySQL 完全从grant tables 中读取权限信息。
        （3）、那内存结构中的权限信息更新之后对已经连接上的用户何时生效呢？
        对于Global Level 的权限信息的修改，仅仅只有更改之后新建连接才会用到，对于已经连接上的session 并不会受到影响。
        对于Database Level 的权限信息的修改，只有当客户端请求执行了“USE database_name”命令之后，才会在重新校验中使用到新的权限信息。所以有些时候如果在做了比较紧急的Global 和Database 这两个Level 的权限变更之后，可能需要通过“KILL”命令将已经连接在MySQL 中的session 杀掉强迫他们重新连接以使用更新后的权限。
        对于Table Level 和Column Level 的权限，则会在下一次需要使用到该权限的Query 被请求的时候生效，也就是说，对于应用来讲，这两个Level 的权限，更新之后立刻就生效了，而不会需要执行“KILL”命令。
2、访问控制
        当MySQL 接收到客户端的请求之后，访问控制模块是需要校验该用户是否满足提交的请求所需要的权限。权限校验过程是从最大范围的权限往最小范围的权限开始依次校验所涉及到的每个对象的每个权限。
        在验证所有所需权限的时候，MySQL 首先会查找存储在内存结构中的权限数据，首先查找Global Level 权限，如果所需权限在Global Level 都有定义（GRANT 或者REVOKE），则完成权限校验（通过或者拒绝），如果没有找到所有权限的定义，则会继续往后查找Database Level 权限，进行Global Level 未定义的所需权限的校验，如果仍然没有能够找到所有所需权限的定义，MySQL 会继续往更小范围的权限定义域查找，也就是TableLevel，最后则是Column Level 或者Routine Level。
        在mysql.user 这个权限表中有max_questions，max_updates，max_connections，max_user_connections 这四列，前面三列是从MySQL4.0.2 版本才开始有的，其功能是对访问用户进行每小时所使用资源的限制，而最后的max_user_connections则是从MySQL5.0.3 版本才开始有的，他和max_connections 的区别是限制耽搁用户的连接总次数，而不是每小时的连接次数。而要使这四项限制生效，需要在创建用户或者给用户授权的时候加上以下四种子句：
        max_questions ： WITH MAX_QUERIES_PER_HOUR n；
        max_updates ： WITH MAX_UPDATES_PER_HOUR n；
        max_connections ： WITH MAX_CONNECTIONS_PER_HOUR n；
        max_user_connections： MAX_USER_CONNECTIONS。
        四个子句可以同时使用，如：
        “ WITH MAX_QUERIES_PER_HOUR 5000 MAX_CONNECTIONS_PER_HOUR 10 MAX_USER_CONNECTIONS 10000”。
        
        参考：《MySQL性能调优与架构设计》