MySQL · 源码分析 · 无法revoke单库或单表权限

现象

对于拥有全局权限的用户，无法revoke单库或单表的权限，示例如下

mysql> grant select on *.* to 'xx1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> revoke select * test.* from 'xx1'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'xx1' on host 'localhost'

mysql> revoke select * test.t1 from 'xx1'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'xx1' on host 'localhost'

分析

根据报错信息，确定revoke select * test.* from 'xx1'@'localhost'报错在函数replace_db_table里面，调用栈如下

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

报错条件是如下，操作是revoke，且mysql.db中找不到对应权限

grant select on *.* to 'xx1'@'localhost'调用栈如下

0  replace_user_table () at /home/xijia.xj/rds_5518/sql/sql_acl.cc:2361
1  0x00000000005ebf44 in mysql_grant () at /home/xijia.xj/rds_5518/sql/sql_acl.cc:4220
2  0x00000000006ac74e in mysql_execute_command () at /home/xijia.xj/rds_5518/sql/sql_parse.cc:4255
3  0x00000000006b630c in mysql_parse () at /home/xijia.xj/rds_5518/sql/sql_parse.cc:6591
4  0x000000000069ed9a in dispatch_command () at /home/xijia.xj/rds_5518/sql/sql_parse.cc:1214
5  0x000000000069d072 in do_command () at /home/xijia.xj/rds_5518/sql/sql_parse.cc:837
6  0x000000000081d9c1 in do_handle_one_connection () at /home/xijia.xj/rds_5518/sql/sql_connect.cc:1426
7  0x000000000081d19b in handle_one_connection () at /home/xijia.xj/rds_5518/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 select on *.* to 'xx1'@'localhost'只修改了mysql.user表
而revoke select * test.* from 'xx1'@'localhost' 需要删除mysql.db表中相应记录，所以会报错

小结

mysql权限分三个粒度，全局权限， db权限，table权限，分别保存在mysql.user, mysql.db. mysql.tables_priv(存储过程，和proxy权限有单独的表)

grant/revoke privilege on *.* 修改mysql.user表
grant/revoke privilege on db.* 修改mysql.db表
grant/revoke privilege on db.table 修改mysql.table表
三种操作互不影响，赋予一个用户大粒度的权限，并不能收回小粒度的权限

执行drop user操作后，会调用sql/sql_acl.cc:handle_grant_data修改上述三个表中，所有与被drop用户相关的记录