今天遇到的mysql权限更改问题

之前对于mysql 的 revoke用法不了解。之前用过几次，歪打正着，没出问题，更是忽略了。

于是今天想取消im 账号对整个库的操作权限，更改为只能访问某几张表的权限。就遇到问题了。
先delete，再grant

delete from mysql.user where user='csk_im' ;             （ 至于为什么delete，因为我对于delete与revoke都不了解，一念之间就用delete了）
grant selete,update,delete,insert on csk.guide to csk_im@192.... indentified by '.....';  flush privilege;

发现权限并没有改变。依然可以查询csk库中所有的表。

换revoke，然后grant

revoke all on *.* from ...
grant selete,update,delete,insert on csk.guide to csk_im@192.... indentified by '.....';  flush privilege;

依然没有收回。

 

在100.11上做了测试：
1. 先测revoke
grant all on csk.members to identified by 'bbb';
revoke all on *.*  from ; flush privileges;
发现权限并没有收回。

revoke all on csk.members  from ; flush privileges;
权限收回了。可以连接数据库，但是访问不了csk库了
做了几组测试，发现一个个表的revoke可以收回权限。
问题： revoke *.* 不起作用。 须得revoke csk.members。那么revoke *.* 意义何在？ ？

 

后来通过show grants  比较测试，发现：

revoke 必须与grant 一一对应，权限，库名，表名，必须一致。

如：

1）grant all on csk.* to identified by 'ddd';    revoke all on csk.* from ;

2）grant all on *.* to identified by 'fff';      revoke all on *.* from ;

 所以，在revoke之前，最好先show grants一下子：

回到线上问题：

 show grants for ;

+-------------------------------------------------------------------------------------------------------------------+
| Grants for ....                                                                               |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO IDENTIFIED BY PASSWORD '******|
| GRANT SELECT ON `csk`.* TO ...                                                             |
| GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON `csk`.`guide` TO                         |
| GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON `csk`.`guide_session` TO               |
| GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON `csk`.`guide_score` TO                   |
+-------------------------------------------------------------------------------------------------------------------+

现在执行： revoke  select from ... 

再show grants：

+-------------------------------------------------------------------------------------------------------------------+
| Grants for ....                                                                               |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO IDENTIFIED BY PASSWORD '******|
| GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON `csk`.`guide` TO                         |
| GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON `csk`.`guide_session` TO               |
| GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON `csk`.`guide_score` TO                   |
+-------------------------------------------------------------------------------------------------------------------+

 

 

2. 现在测测delete：
delete from mysql.user where user='aaa'; flush privileges; (必须flush)
查看了一下：user表中不存在aaa这条记录了，但是table_privi表中依然存在aaa用户的members和goods 的记录
然后在 41 登录100.11 失败。

重新grant，赋予guide表权限：
grant all on csk.guide to identified by 'bbb';
41可以登录100.11了。
show tables 发现， 不仅有刚刚赋权限的guide表，之前的members表和guide表都存在。
可见，当csk库重新获得权限后，table_privi 表中members和goods记录还在，于是权限都回来了。

删除table_privi 表中members和goods记录试试：
再show tables，发现这两张表已经不存在了。

又百度了一下：

delete from mysql.user where user='aaa';

虽然用户在user表中没有了，但是show grants 会发现依然有他的权限。因为并没有撤销他的权限。

 

总结一下：

1. delete from mysql.user 删除的此用户。并不撤销其权限。此用户不能再连接数据库。

2. revoke是撤销权限，revoke须与show grants 里的内容一一对应。

    revoke之后，用户依然存在，可以连接数据库，只是没有访问数据库的权限，除了information_schema。

 

那么对于线上的问题，还是有很多疑问。
user 表中，所有权限列都是N。这是为什么？
发现权限这块的问题还挺复杂，先翻翻手册，看看权限到底是怎么个原理。授权表又是怎么回事

 

授权表包括：

user、db、host、tables_priv或columns_priv

 

1. user表在全局基础上授予赋予你的权限，该权限不管当前的数据库是什么均适用。例如，如果user表授予你DELETE权限， 你可以删除在服务器主机上从任何数据库删除行！换句话说，user表权限是超级用户权限。只把user表的权限授予超级用户如服务器或数据库主管是明智的。对其他用户，你应该把在user表中的权限设成'N'并且仅在特定数据库的基础上授权。你可以为特定的数据库、表或列授权。

 

2. db和host表授予数据库特定的权限。在这些表中的范围列的值可以采用以下方式：

• 通配符字符“%”并“_”可用于两个表的Host和Db列。它们与用LIKE操作符执行的模式匹配操作具有相同的含义。如果授权时你想使用某个字符，必须使用反斜现引用。例如，要想在数据库名中包括下划线(‘_’)，在GRANT语句中用‘\_’来指定。
• 在db表的'%'Host值意味着“任何主机”，在db表中空Host值意味着“对进一步的信息咨询host表”（本节后面将描述的一个过程）。
• 在host表的'%'或空Host值意味着“任何主机”。
• 在两个表中的'%'或空Db值意味着“任何数据库”。
• 在两个表中的空User值匹配匿名用户。

db和host表在服务器启动时被读取并排序(同时它读user表)。db表在Host、Db和User范围列上排序，并且host表在Host和Db范围列上排序。对于user表，首先根据最具体的值最后根据最不具体的值排序，并且当服务器寻找匹配条目时，它使用它找到的第一匹配。

 

3. tables_priv和columns_priv表授予表和列特定的权限。这些表的范围列的值可以如下被指定：

• 通配符“%”并“_”可用在使用在两个表的Host列。
• 在两个表中的'%'或空Host意味着“任何主机”。
• 在两个表中的Db、Table_name和Column_name列不能包含通配符或空。

tables_priv和columns_priv表根据Host、Db和User列被排序。这类似于db表的排序，因为只有Host列可以包含通配符，排序更简单。