mysql 权限粒度,找乐子：从一次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>

简述以上的过程：

已知用户'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

三种粒度相互独立，互不影响

三种粒度之间没有关联实现

管挖不管埋

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