先看场景
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>
简述以上的过程：
- 已知用户'HowUger'@'localhost'在mysql中拥有全局权限
- 尝试回收用户'HowUger'@'localhost'在单库test_1220的权限失败
- 尝试回收用户'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也有对应的权限）
简单总个结
- 三种粒度：全局（即用户级），库级和表级
- 三种粒度对应三张表：mysql.user，mysql.db和tables_priv
- 三种粒度相互独立，互不影响
- 三种粒度之间没有关联实现
管挖不管埋
多种粒度权限叠加场景中，权限的适用情况和具体调用又会是怎样的呢？
