找乐子：从一次Revoke看MySQL权限的三种粒度

先看场景

12:34:36 HowUger@HowUger_MySQL_t01:mysql>
12:34:39 HowUger@HowUger_MySQL_t01:mysql> SHOW GRANTS FOR 'HowUger'@'localhost';
+----------------------------------------------------------------------+
| Grants for HowUger@localhost                                         |
+----------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'HowUger'@'localhost' |
+----------------------------------------------------------------------+
1 row in set
Time: 0.029s
12:34:41 HowUger@HowUger_MySQL_t01:mysql>
12:34:52 HowUger@HowUger_MySQL_t01:mysql>
12:35:25 HowUger@HowUger_MySQL_t01:mysql> REVOKE SELECT, UPDATE, INSERT, DELETE ON test_1220.* FROM 'HowUger'@'localhost';
(1141, u"There is no such grant defined for user 'HowUger' on host 'localhost'")
12:35:26 HowUger@HowUger_MySQL_t01:mysql>
12:36:10 HowUger@HowUger_MySQL_t01:mysql> REVOKE SELECT, UPDATE, INSERT, DELETE ON test_1220.t_test01 FROM 'HowUger'@'localhost';
(1147, u"There is no such grant defined for user 'HowUger' on host 'localhost' on table 't_test01'")
12:36:11 HowUger@HowUger_MySQL_t01:mysql>

简述以上的过程：

1. 已知用户'HowUger'@'localhost'在mysql中拥有全局权限
2. 尝试回收用户'HowUger'@'localhost'在单库test_1220的权限失败
3. 尝试回收用户'HowUger'@'localhost'在单表test_1220.t_test01的权限失败

开始找乐子

通过查看错误信息，确定Revoke单库权限操作错误发生在函数replace_db_table调用过程中，具体调用栈如下：

    0  replace_db_table () at /home/HowUger/mysql-5.6.45/sql/sql_acl.cc:2662
    1  0x00000000005ebf44 in mysql_grant () at /home/HowUger/mysql-5.6.45/sql/sql_acl.cc:4230
    2  0x00000000006ac74e in mysql_execute_command () at /home/HowUger/mysql-5.6.45/sql/sql_parse.cc:4255
    3  0x00000000006b630c in mysql_parse () at /home/HowUger/mysql-5.6.45/sql/sql_parse.cc:6591
    4  0x000000000069ed9a in dispatch_command () at /home/HowUger/mysql-5.6.45/sql/sql_parse.cc:1214
    5  0x000000000069d072 in do_command () at /home/HowUger/mysql-5.6.45/sql/sql_parse.cc:837
    6  0x000000000081d9c1 in do_handle_one_connection () at /home/HowUger/mysql-5.6.45/sql/sql_connect.cc:1426
    7  0x000000000081d19b in handle_one_connection () at /home/HowUger/mysql-5.6.45/sql/sql_connect.cc:1332
    8  0x00007fd0c16fa851 in start_thread () from /lib64/libpthread.so.0
    9  0x0000003330ce767d in clone () from /lib64/libc.so.6 

MySQL源码中该函数代码段如下（报错逻辑在最后）：

static int replace_db_table(TABLE *table, const char *db,
                            const LEX_USER &combo,
                            ulong rights, bool revoke_grant)
{
  uint i;
  ulong priv,store_rights;
  bool old_row_exists=0;
  int error;
  char what= (revoke_grant) ? 'N' : 'Y';
  uchar user_key[MAX_KEY_LENGTH];
  Acl_table_intact table_intact;
  DBUG_ENTER("replace_db_table");

  if (!initialized)
  {
    my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--skip-grant-tables");
    DBUG_RETURN(-1);
  }

  if (table_intact.check(table, &mysql_db_table_def))
    DBUG_RETURN(-1);

  /* Check if there is such a user in user table in memory? */
  if (!find_acl_user(combo.host.str,combo.user.str, FALSE))
  {
    my_message(ER_PASSWORD_NO_MATCH, ER(ER_PASSWORD_NO_MATCH), MYF(0));
    DBUG_RETURN(-1);
  }

  table->use_all_columns();
  table->field[0]->store(combo.host.str,combo.host.length,
                         system_charset_info);
  table->field[1]->store(db,(uint) strlen(db), system_charset_info);
  table->field[2]->store(combo.user.str,combo.user.length,
                         system_charset_info);
  key_copy(user_key, table->record[0], table->key_info,
           table->key_info->key_length);

  if (table->file->ha_index_read_idx_map(table->record[0],0, user_key,
                                         HA_WHOLE_KEY,
                                         HA_READ_KEY_EXACT))
  {
    if (what == 'N')
    { // no row, no revoke
      my_error(ER_NONEXISTING_GRANT, MYF(0), combo.user.str, combo.host.str);
      goto abort;
    }
...

那么常规GRANT全局权限时，调用栈又是怎样的呢？

    0  replace_user_table () at /home/HowUger/mysql-5.6.45/sql/sql_acl.cc:2361
    1  0x00000000005ebf44 in mysql_grant () at /home/HowUger/mysql-5.6.45/sql/sql_acl.cc:4220
    2  0x00000000006ac74e in mysql_execute_command () at /home/HowUger/mysql-5.6.45/sql/sql_parse.cc:4255
    3  0x00000000006b630c in mysql_parse () at /home/HowUger/mysql-5.6.45/sql/sql_parse.cc:6591
    4  0x000000000069ed9a in dispatch_command () at /home/HowUger/mysql-5.6.45/sql/sql_parse.cc:1214
    5  0x000000000069d072 in do_command () at /home/HowUger/mysql-5.6.45/sql/sql_parse.cc:837
    6  0x000000000081d9c1 in do_handle_one_connection () at /home/HowUger/mysql-5.6.45/sql/sql_connect.cc:1426
    7  0x000000000081d19b in handle_one_connection () at /home/HowUger/mysql-5.6.45/sql/sql_connect.cc:1332
    8  0x00007fd0c16fa851 in start_thread () from /lib64/libpthread.so.0
    9  0x0000003330ce767d in clone () from /lib64/libc.so.6 

这样一来情况就非常清晰了：

• GRANT全局权限时，调用函数replace_user_table，修改mysql.user表
• REVOKE单库权限时，调用函数replace_db_table，修改mysql.db表
• 而上面没有查看调用栈的REVOKE单表权限时，调用的函数是replace_table_table（命名鬼才呀），修改mysql.tables_priv表

说了半天也就是下面这三张表

12:36:17 HowUger@HowUger_MySQL_t01:mysql>
12:36:30 HowUger@HowUger_MySQL_t01:mysql> SHOW TABLES WHERE TABLES_IN_MYSQL IN ('user', 'db', 'tables_priv');
+-----------------+
| Tables_in_mysql |
+-----------------+
| db              |
| tables_priv     |
| user            |
+-----------------+
3 rows in set
Time: 0.016s
12:36:32 HowUger@HowUger_MySQL_t01:mysql>

So，到这里正好对应了MySQL在权限设计中的三种粒度：全局权限， db权限，table权限（当然存储过程和Proxy也有对应的权限）

简单总个结

1. 三种粒度：全局（即用户级），库级和表级
2. 三种粒度对应三张表：mysql.user，mysql.db和tables_priv
3. 三种粒度相互独立，互不影响
4. 三种粒度之间没有关联实现

管挖不管埋

多种粒度权限叠加场景中，权限的适用情况和具体调用又会是怎样的呢？