从一个问题看MySQL的权限访问控制

首先提出一个问题：假设一个root管理员创建了一个用户admin，然后授权admin访问test数据库下的所有表权限，在test下创建一个表test_table(id int,age varchar(20));然后可以插入些数据，此时root管理员将这个数据库删除。当然了，此时admin登录的话，show database肯定是看不到test的。那么此时root再登进去，再创建test数据库，然后在test数据库下创建test_table(id,age,sex)表。admin再登录进去的时候可以看到吗？

答案是可以的。

MySQL OverView

MySQL服务器(实际上就是mysqld)有一个线程专门负责监听端口（默认3306），然后接受请求，并为每个请求建立一个线程，并由这个线程对这个连接上的请求数据进行处理。我们通过使用show processlist可以看到MySQL现在到底有多少个连接，当然了这些连接不一定是活跃的，因为服务器也会建立连接池，来维护着连接。
【注：这里有两个请求，第一个是请求建立连接的请求，也可以说是SYN请求，因为MySQL使用TCP协议，而后一个请求是当连接建立好之后，通过这个连接发过来的请求命令。】
【注：这里说建立一个线程，实际上有点糙，因为这个线程很可能是，而且大部分应该是缓存的线程，每次创建新的线程太耗资源】

然后呢，MySQL服务器会有个权限访问控制模块，会对进来的每个请求进行过滤，只有通过了过滤模块之后，才会将命令向后传递到比如语法分析器等等。

MySQL 访问控制

1、MySQL靠什么标识一个用户？

我之前想当然以为是用户名，实际上是用户名+域名（也就是请求发出的机器的域），其实我很奇怪，为啥要将域名也加进来，如果我来设计，我会认证用户名、密码，然后根据登录用户的域来比对，看当前是否有权限登录，当然了，MySQL也是一样的。只是，如果我来设计的话，我每次grant的时候，只会更新访问权限列表，但是MySQL当做是新建一个用户。
比如我们执行如下命令
grant all on test.* to admin@’%’;
grant all on test.* to admin@localhost;
grant all on test.* to admin@127.0.0.1 identified by ‘123456’

这些命令执行完毕之后，然后执行select * from mysql.user；即查询MySQL中user表的数据时会发现新加了三条记录，也就是说MySQL将这三者当成三个不同的用户一样。所以如果有一天，你发现使用同一用户名和密码登录，在远程和本地端有不同的访问权限，请不要意外，因为这压根就没有什么可意外的。

使用这种方式有个好处就是可以进行更加细粒度化的进行访问控制，比如对本地登录的用户，有更高的权限，而从远处访问的用户权限更低。而如果当做一个用户来看，那么无论远端还是本地，都是一样的权限了。
【注：从上面三条授权语句发现，localhost域与127.0.0.1并不是一回事，事实上，如果你在本地登录的时候，使用MySQL -u admin -p 登录，那么被认为是localhost登录，而如果使用mysql -u admin -p -h 127.0.0.1才会被当做127域登录，即需要提供密码】
【注：在前两条授权语句中没有identified，表示密码为”，这和不设密码是两回事，如果你随便输入一个字符，那么都会deny掉，因为它的密码正好是空字符串】
【注：虽然使用grant语句为不存在的用户授权，那么grant将会自动添加用户，但是mysql并不建议这样使用，而是先create user，然后再为用户授权，并在5.7版本之后彻底废弃了grant创建用户的方法】

2、访问控制信息如何存储，存储在什么地方？

想想如果自己来设计，不外乎有两种策略。

• 将角色信息与数据库、表甚至列绑定，当请求通过语法分析器后开始执行mysql指令时，先去查看当前指令有没有权限去访问数据库、表甚至列，考虑到数据库中表是存储数据的唯一形式，我们可以考虑为每个数据库、每张表、每个列创建一个.meta table，里面只存储授权信息。
• 将授权信息提取出来，集中存放，这样，在请求进入语法分析器之前就可以对请求进行过滤了。这种方法至少有如下几个优点：
  
  • 由于每个数据库、每个表甚至每个列所涉及到的用户都非常少，如果分散存储，那么会使用更大的空间，而如果集中存储，可以更优的利用空间。只要创建一个table_priv的表，里面存储着所有关于表的访问权限，和用户之间的对应。
  • 由于这种meta信息非常少，如果集中存储的话，在系统启动的时候就可以将所有信息都load到内存里面，加快访问速度，而如果分散存储，是没办法加载到内存的，至少不容易做到。
  • 相对权限访问控制而言，语法分析可能是更耗时的操作，如果采用分散存储，那么势必所有的请求都会经过语法分析，这是不明智的。
    
    • 假设，过滤所需要时间为x，过滤率为m(m<1)，语法分析时间为k。
    • 那么采用分散存储，时间将会达到k+x
    • 而采用集中存储，那么时间会叨叨x+mk
    • 所以集中存储效率会更高。

自然，mysql采用了第二种方案。 这个集中存储的地方都是mysql服务器中的mysql数据库。
正是由于MySQL采用了集中存储的方案，那么当用户把数据库删除时，授权信息并没有受到影响。而事实上，对访问授权会产生影响的就只有两个方法，第一是使用grant和revoke方法，第二直接操作mysql中的表，而第二种方法是不鼓励使用的。事实上，使用grant和revoke本质上还是对mysql中的表进行操作。而这个操作对于到底是否存储这个数据库，并没有检查。

比如说，我们希望为某个用户授权abcd数据库的访问权限，如grant all on abcd.* to admin@”%”，但是这个数据库是不存在的，那么这个语句会通过吗？答案是没问题，可以通过，这条语句执行完毕后，会在mysql.db中添加一条数据，数据显示admin对abcd拥有全部权限，只是abcd是不存在的，而当abcd创建之后，不管是谁创建的，使用admin登录，一定可以访问这个数据库。

说到这里，关于开头的问题就应该很清楚了，删除数据库的时候，并没有将访问授权信息删掉，事实上，这两者可以独立存在。

3、访问授权表中都有哪些信息
mysql服务器中的mysql数据库下有这样几个表：

这些表中与访问控制相关的由这样几个：

• user：用户账户、全部权限等；
• db：数据库级的访问权限
• host：已经废弃掉了
• table_priv：表级权限
• column_priv：列级权限
• procs_priv：存储过程权限

详细看一下结构：

【user表结构】
可以看到host和user是作为联合主键的，这也印证了前面所说的，通过user和host来标识用户。

【db表结构】
主要是对数据库的一些访问权限

【tables_priv表结构】
主要是对数据库表的一些控制

【procs_priv和column_priv就不再多说了】

mysql何时更新授权信息

这个问题也很关键，因为前面说过，mysql服务器在启动的时候就会加载全部的授权信息，这个信息将维护在内存里，那么假设我们更新了用户权限，mysql会立刻生效吗？这是我们需要知道的。
前面说过，更新用户权限，只有两种，要么使用grant、revoke，要么直接操作mysql数据库。

当mysql服务器检测到grant和revoke命令后，在命令执行完毕后，会立刻加载mysql数据库中的数据，所以，可以认为调用grant和revoke之后，用户权限立即生效。
但是当直接操作mysql数据库时，mysql服务器并不会重新reload，那何时load呢？下次mysql重启的时候才会重新load，这个在应用上是不可容忍的。

这也是为什么mysql推荐使用grant和revoke命令的原因。