理解MySQL中权限控制
2017-04-17 14:55:35 
阿炯
MySQL有比较细的权限控制粒度，从主机、用户、库、表、列都有相关的权限组合，本文就详细介绍它的权限系统的各个方面。权限级别
1、global 全局权限控制，所有的信息都保存在mysql.user表中。
2、database 作用域为指定某个数据库中的所有对象，所有权限信息保存在mysql.db中。当执行grant命令时，通过“database.*”来限定作用域为database整个数据库；也可以通过use命令选定授权的数据库，然后通过“*”来限定作用域，这样授权的作用域实际上就是当前选定的整个数据库。
3、table 作用范围是授权语句中指定数据库的指定表(database.table)，权限信息保存在tables_priv中。
4、column 作用域为某个指定的列，权限信息保存在columns_priv中。column level级别的权限仅有insert、select、update这三种。语法格式：grant select(id,value)on test.t2 to ‘abc’@‘%’。给abc用户授予 test数据库t2表的id、value列select权限。
5、routine 针对的主要对象时procedure和function。目前暂时只有execute和alter routine两种。语法格式：grant execute on test.p1 to 'abc' @'%';
6、with grant option。在授权时加上此命令，被授权用户有传递权限的权限。
具体权限
命令标识|权限表中对应的列|说明
CREATE|Create_priv|创建数据库、表或索引
CREATE TEMPORARY TABLES|Create_tmp_table_priv|创建临时数据表
CREATE ROUTINE|Create_routine_priv|创建函数或存储
CREATE VIEW|Create_view_priv|创建视图
CREATE USER|Create_user_priv|创建用户
EXECUTE|Execute_priv|执行函数或存储过程
INDEX|Index_priv|建立索引
REFERENCES|References_priv|建立约束
DROP|Drop_priv|删除表
SELECT|Select_priv|查询数据
INSERT|Insert_priv|插入数据
UPDATE|Update_priv|更新数据
DELETE|Delete_priv|删除数据
LOCK TABLES|Lock_tables_priv|锁定表格
SHOW DATABASES|Show_db_priv|列出数据库
SHOW VIEW|Show_view_priv|列出视图
USAGE||只有登录权限，其他权限都没有
ALL||所有权限,除了WITH GRANT OPTION
ALTER|Alter_priv|更改数据表
ALTER ROUTINE|Alter_routine_priv|更改函数或存储过程
PROCESS|Process_priv|显示连接进程和中断连接进程
FILE|File_priv|载入文件
RELOAD|Reload_priv|可以用FLUSH
REPLICATION CLIENT|Repl_client_priv|可以检查Masters和Slaves
REPLICATION SLAVE|Repl_slave_priv|在Slave里的特殊权限
SHUTDOWN|Shutdown_priv|关闭MySQL
WITH GRANT OPTION|Grant_priv|可以将自己拥有的权限赋给其他用户
SUPER|Super_priv|执行kill线程，change master、purge master logs、set global等命令的权限
create tablespace|Create_tablespace_priv|创建表空间
Event|Event_priv|确定用户能否创建、修改和删除事件
Trigger|Trigger_priv|确定用户能否创建和删除触发器
权限表中的规则
1、user 、host 、db表中值的规则
· 通配符字符“%”和“_”可用于表的Host和Db列。它们与用LIKE操作符执行的模式匹配操作具有相同的含义。如果授权时你想使用某个字符，必须使用反斜现引用。例如，要想在数据库名中包括下划线(‘_’)，在GRANT语句中用‘\_’来指定。
·在db表的'%'Host值意味着“任何主机”，在db表中空Host值意味着“对进一步的信息咨询host表”。
·在host表的'%'或空Host值意味着“任何主机”。
·在三个表中的'%'或空Db值意味着“任何数据库”。
·在user、db表中的空User值匹配匿名用户
2、tables_priv和columns_priv表中值得规则
·通配符“%”并“_”可用在使用在两个表的Host列。
·在两个表中的'%'或空Host意味着“任何主机”。
·在两个表中的Db、Table_name和Column_name列不能包含通配符或空。
3、mysql.host表的特殊地方
mysql.host不是通过grant或revoke权限来授予或去除的，必须手工通过insert、update和delete命令来修改其中的数据。其中的权限无法单独生效，必须与mysql.db权限表一起才能生效。当mysql.db中的信息不完整时，采取访问mysql.host。
当想在db表的范围之内扩展一个条目时，就会用到host表。举例来说，如果某个db允许通过多个主机访问的话，那么超级用户就可以让db表内将host列为空，然后用必要的主机名填充host表。
权限查看和更改
1、新加权限或者用户
GRANT 权限 ON 库名.表名 TO 新用户名@主机名 IDENTIFIED BY ‘密码‘；
例：grant select,insert,update,delete on *.* to freeoa@"%" Identified by "abc";新加的用户名为test1 ，密码为abc，对所有表有增删查改的权限，在任何主机上可以登录。
2、查看权限
使用show grants 语句查看指定账户的权限；例如，要检查Host和User值分别为pc.freeoa.net和freeoa的账户所授予的权限，应通过语句：
mysql> SHOW GRANTS FOR 'freeoa'@'pc.freeoa.net';
3、更改权限
若通过直接修改权限表来更改权限，则修改完后都必须要执行“flush privileges”，通知mysql重新加载MySQL的权限信息；如果通过grant、revoke或drop user命令来修改权限，则不需要执行“flush privileges”命令。
4、权限更改何时生效
当mysqld启动时，所有授权表的内容被读进内存并且从此时生效。当服务器注意到授权表被改变了时，现存的客户端连接有如下影响：
· 表和列权限在客户端的下一次请求时生效。
· 数据库权限改变在下一个USE db_name命令生效。
· 全局权限的改变和密码改变在下一次客户端连接时生效。
如果用GRANT、REVOKE或SET PASSWORD对授权表进行修改，服务器会注意到并立即重新将授权表载入内存。
如果你手动地修改授权表(使用INSERT、UPDATE或DELETE等等)，你应该执行mysqladmin flush-privileges或mysqladmin reload告诉服务器再装载授权表，否则你的更改将不会生效，除非你重启服务器。
如果你直接更改了授权表但忘记重载，重启服务器后你的更改方生效。
5、修改密码
当使用set password、insert、update更改密码时，必须使用PASSWORD()函数加密密码。若果不使用PASSWORD()函数，密码将不工作。
例如，下面的语句设置密码，但没能加密，因此用户后面不能连接：
mysql> SET PASSWORD FOR 'freeoa'@'host_name' = 'freeoa_paswd';
相反，应这样设置密码：
mysql> SET PASSWORD FOR 'freeoa'@'host_name' = PASSWORD('freeoa_paswd');
当使用GRANT或CREATE USER语句或mysqladmin password命令指定密码时，不需要PASSWORD()函数，它们会自动使用PASSWORD()来加密密码。
认证和授权
阶段1：连接核实
当你试图连接MySQL服务器时，服务器基于你的身份以及你是否能通过供应正确的密码验证身份来接受或拒绝连接。如果不是，服务器完全拒绝你的访问，否则，服务器接受连接，然后进入阶段2并且等待请求。
身份基于2个信息：
·从那个主机连接
·MySQL用户名
身份检查使用3个user表(Host, User和Password)范围列执行。服务器只有在user表记录的Host和User列匹配客户端主机名和用户名并且提供了正确的密码时才接受连接。
阶段2：请求核实
一旦你建立了连接，服务器进入访问控制的阶段2。对在此连接上进来的每个请求，服务器检查你想执行什么操作，然后检查是否有足够的权限来执行它，这正是在授权表中的权限列发挥作用的地方。这些权限可以来自user、db、host、tables_priv或columns_priv表。
外部操作所需权限
1、备份
用户会通过mysqldump来做备份，一般只需要用到select和lock tables 两项权限。如果使用带-tab选项的mysqldump来做tab分界符文件的导出，或者是用select into outfile，那么还需要一个file权限。
例：grant select,lock tables,file on *.* to backup@dbhost
为了保证许多备份操作的一致性，还会用到flush tables with read lock，所以还reload权限也是必须的。
2、操作和监控
维护系统或修复故障需要用到kill或show命令，还需要关闭服务器。所以需要用到process和shutdown权限。
权限校验处理流程图
