深入理解mysql的权限系统
本页将讨论这些问题。
- mysql权限系统是什么？用户有哪些权限类型？
- 权限系统是怎么实现的、mysql内部究竟如何判断一个用户是否有权限？
- 如何授予或者取消用户的权限？
mysql权限系统介绍
mysql权限系统功能：实现对用户的权限控制。
具体控制这些权限：
权限 | 权限级别 | 权限说明 |
CREATE | 数据库、表或索引 | 创建数据库、表或索引权限 |
DROP | 数据库或表 | 删除数据库或表权限 |
ALTER | 表 | 更改表，比如添加字段、索引等 |
DELETE | 表 | 删除数据权限 |
INDEX | 表 | 索引权限 |
INSERT | 表 | 插入权限 |
SELECT | 表 | 查询权限 |
UPDATE | 表 | 更新权限 |
FILE | 服务器主机上的文件访问 | 文件访问权限 |
CREATE USER | 服务器管理 | 创建用户权限 |
LOCK TABLES | 服务器管理 | 锁表权限 |
SHOW DATABASES | 服务器管理 | 查看数据库权限 |
SHUTDOWN | 服务器管理 | 关闭数据库权限 |
REPLICATION CLIENT | 服务器管理 | 复制权限 |
RELOAD | 服务器管理 | 执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限 |
GRANT OPTION | 数据库、表或保存的程序 | 赋予权限选项 |
权限系统实现原理
mysql的权限信息存储在如下几个被称为grant tables的系统表中。
- mysql.User
- mysql.db
- mysql.table_priv
- mysql.column_priv
- mysql.Host （已废弃）
这些信息会被频繁访问，所以会加载到内存里。如果手动修改了上述的表，就需要调用FLUSH PRIVILEGES来重新加载内存。
通过GRANT,REVOKE, CREATE USER, DROP USER来修改权限的话，就会自动更新内存里的权限信息，无需调用FLUSH PRIVILEGES
过程图：
主要依靠这几张表中存储的权限信息。
表名 | 代表的权限级别 |
mysql.User | global level |
mysql.db | database level |
mysql.table_priv | table level |
mysql.column_priv | column level |
具体的一个例子：
查询权限的时候，也是从global -> db -> table ....level从大到小的顺序查询的。
也就是说，如果mysql.User表中有匹配到row，就会使用该行数据进行权限判断，不会再继续往后查找是否还有匹配的信息了。
如果查询到多行，则会使用host信息最接近的，其余的都不会使用。
授予或者取消用户的权限
global level权限
其作用域是所有数据库中的所有对象。要授予global level权限，只需在执行GRANT命令时，用“*.*”指定适用范围是Global即可
GRANT SELECT,UPDATE,DELETE,INSERT ON *.* TO 'username'@'localhost'
@后面的是用户的来访主机
database level
其作用域是整个数据库中所有的对象。database level相对于global level少了以下几个权限。CREATE USER、FILE、PROCESS、RELOAD、REPLICATION CLIENT、REPLICATION SLAVE、SHOW DATABASES、SHUTDOWN、SUPER和USAGE。
要授予database level权限，只需通过"database.*"即可
GRANT ALTER ON test.* TO 'username'@'localhost';
获取用户在某个来访主机下的访问权限：
SHOW GRANTS FOR username@localhost
也可以先通过USE一个数据库，把作用域限定在该数据库下，然后直接"*"即可。
USE test;
GRANT ALTER ON * TO 'username'@'localhost';
给多个用户授权，用,分隔即可。
grant create on perf.* to 'abc'@'localhost','def'@'localhost';
username@host表示授予的用户以及允许该用户登录的IP地址。
其中Host有以下几种类型：
localhost：只允许该用户在本地登录，不能远程登录。
%：允许在除本机之外的任何一台机器远程登录。
192.168.52.32：具体的IP表示只允许该用户从特定IP登录。
table level
授予table level权限，通过"database.table"即可。
仅有ALTER，CREATE，DELETE，DROP，INDEX，SELECT，INSERT，UPDATE八种权限。
grant INDEX ON `test`.`t1` TO 'abc'@'%.xxx.com';
把INDEX权限，在t1表的权限，授予abc用户。该用户的来访主机必须是.xxx.com后缀的。
column level
方法如下：
grant select(id,value) ON test.t2 TO 'abc'@'localhost';
routine level
只有EXECUTE，ALTER ROUTINE两种，针对procedure(存储过程)和function（函数）
如：grant EXECUTE ON test.p1 to 'abc'@'localhost';
注意：
GRANT权限很特殊，拥有GRANT权限的用户，可以将自身拥有的任何权限都授予其他用户。
可以通过GRANT ALL语句，把某个level的所有可用权限授予某个用户。
grant all on test.t5 to 'abc';
grant all on perf.* to 'abc';
show grants for 'abc';
注意：即使内存里的权限信息更新了。
global level的权限也不会对已经连接上的session生效，而是对新建的连接生效。
db level的权限，只有在use db命令执行后才会生效
table、column的权限，有需使用权限的query请求时就会生效。
查看用户权限
查看当前用户自己的权限：
show grants;
查看其他 MySQL 用户权限：
show grants for dba@localhost;
撤销用户权限
使用revoke 命令来注销用户的权限,具体语法：
要撤销所有权限，需使用以下语法。此语法用于取消对于已命名的用户的所有全局层级、数据库层级、表层级和列层级的权限。
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] …
指定具体的权限:
REVOKE SELECT FROM username@localhost
注意:
1 使用GRANT或REVOKE，操作者必须拥有GRANT OPTION权限.
2 使用REVOKE撤销全部权限, 操作者必须拥有mysql数据库的全局CREATE USER权限或UPDATE权限。
mysql 授权策略
- 了解来访主机
- 了解用户需求，为工作分类。尽可能少地给用户权限
- 确保GRANT OPTION权限只有超级用户才有
- 使用私有局域网络
- 使用ssl加密通道
- 访问授权限定来访主机信息