mysql访问控制实现原理

werwew

mysql访问控制由两个功能模块共同组成，一个是用户管理模块，可以称为“看守mysql大门的模块”。一个是负责监视来访者每一个动作的访问控制模块。

用户管理模块决定来访者能否进入大门
访问控制模块决定每个来访者进门后能拿什么不能拿什么

下面是一张MySQL 中实现访问控制的简单流程图

1，用户管理模块
1.1，在mysql中，用户访问控制部分的实现比较简单，所有授权用户都存放在mysql.user表中，这个表不仅仅存放了授权用户的基本信息，还存放了部分细化的权限信息，用户管理模块需要使用的信息很少，主要是host，user，password这三项。

一个用户要想访问，至少要提供host，user，password这三项数据，mysql才能判断是否让他“进门”。这三项实际上由两部分组成：访问者来源的主机名（或者主机IP 地址信息）和访问者的来访“暗号”（登录用户名和登录密码），这两部分中的任何一个没有能够匹配上都无法让看守大门的用户管理模块乖乖开门。其中Host 信息存放的是MySQL 允许所对应的User 的信任主机，可以是某个具体的主机名（如：mytest）或域名（如：www.domain.com），也可以是以“%”来充当通配符的某个域名集合（如：%.domain.com）；也可以是一个具体的IP 地址(如：1.2.3.4)，同样也可以是存在通配符的域名集合(如：1.2.3.%)；还可以用“%”来代表任何主机。

但是这里有一个比较特殊的访问限制，如果要通过localhost 访问的话，必须要有一条专门针对localhost 的授权信息，即使不对任何主机做限制也不行。因为mysql 在默认情况下会连接localhost。但是当通过-h 参数，明确指定了访问的主机地址之后就没问题了。

1.2，何时mysql存放在内存结构中的权限信息会被刷新：
flush privileges强行更新load到内存中的权限信息
grant、revoke、create user和drop user会直接更新内存中的权限信息
重启mysql会让mysql完成从grant tables中读取权限信息

1.3，内存结构中的权限信息更新后对已经连接上的用户何时生效
对于global level的权限信息的修改，只有更改后的新连接才会用到，已经连接上的session并不会受到影响
对于database level的权限信息的修改，只有当客户端请求了use database_name命令后，才会在重新检验中使用到新权限
对于table level和column level的权限修改，在一下次需要用到该权限的query被请求时生效

2，访问控制模块
当客户端连接通过用户管理模块的验证后，就会发送各种query和command给mysql server。当mysql接收到客户端的请求后，访问控制模块是需要检验该用户是否满足提交的请求所需要的权限的，权限检验过程是从最大范围向最少范围依次检验所涉及到的每个对象和每个权限。

在验证所有需要的权限时，mysql首先查找存储在内存结构中的权限数据，首先查找global level权限，如果所需要权限在global level都有定义，则完成权限检验；如果没有找到所需要权限，则会继续向后查找database level权限，进行global level未定义的所需权限的校验，如果还没有，mysql继续向更小范围的table level查找，最后是column level或是routine level

下面我们就以客户端通过abc@localhost连接后请求如下Query为例：
SELECT id,name FROM test.t4 where status='deleted';

3，host权限表在mysql访问控制中是什么的角色
mysql的grant tables有mysql.user，mysql.db，mysql.host，mysql.tables_priv，mysql.columns_priv这五个，除了host之外的四个都是一张表针对mysql中的一种逻辑现象，存放某一特定level的权限。只有host稍有区别，我们看看host权限表在mysql访问控制中是什么的角色。

3.1，mysql.host中的权限数据不是（也不能）通过grant或是revoke来授予或是去除，必须手工insert，update，delete来修改
3.2，mysql.host中的权限数据无法单独生效，必须通过和mysql.db权限表一起才能生效，而且仅当mysql.db中存在不完整时，才会促使访问控制模块来结合mysql.host查找是否存在相应的补充权限数据来达到校验目的，比如上图中，在mysql.db中找不到满足权限校验的所有条件（db.user='abc' and db.host='localhost' and db.database_name='test'），则说明在mysql.db中无法完成权限校验，所以不会直接校验db.select_priv的值是否为Y。但是在mysql.db中有db.user='abc' and db.database_name='test' and db.host=''权限信息，当mysql中注意到有这样一条权限信息存在的时候，就是mysql.host中存放权限出场的时候了。这时，mysql检测mysql.host中是否存在满足如下条件的仅限信息：host.host='localhost' and host.db='test'。如果有，则开始检验select_priv权限。由于权限在mysql.db与mysql.host两者之中，而且是两者合并才能满足，所以select_priv的校验需要两表都为Y才能满足，才能通过校验。

4，MySQL如何来确定权限信息呢
我们已经清楚，MySQL 的权限是授予“username@hostname”的，也就是说，至少需要用户名和主机名二者才能确定一个访问者的权限。又由于 hostname 可以是一个含有通配符的域名，也可以是一个含有通配符的 IP 地址段。那么如果同一个用户有两条权限信息，一条是针对特定域名的，另外一个是含有通配符的域名，而且前者属于后者包含。这时候 MySQL如何来确定权限信息呢？

实际上 MySQL 永远优先考虑更精确范围的权限。在 MySQL 内部会按照 username 和 hostname 作一个排序，对于相同 username 的权限，其 host 信息越接近访问者的来源 host，则排序位置越靠前，则越早被校验使用到。而且， MySQL 在权限校验过程中 ，只要找到匹配的权限之后，就不会再继续往后查找是否还有匹配的权限信息，而直接完成校验过程。

大家应该也看到了在 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性能调优与架构设计 第四章 mysql安全管理