mysql 开发进阶篇系列 52 权限与安全(系统四个权限表的粒度控制关系)

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

一.概述

接着上篇的权限介绍，当用户进行连接的时候，权限表的存取过程有以下两个阶段：

(1) 先从user表中的host,user, authentication_string 这3个字段中判断连接的ip,用户名,密码是否存在于表中，如果存在，则通过身份验证。

(2) 通过验证后，则按照以下权限表的顺序得到数据库权限:user->db->table_priv -> columns_priv。

四个表分别是：user全局权限(第一阶段)，db具体数据库权限(第二阶段)，table_priv具体表权限(第三阶段),columns_priv表对应的具体列权限(第四阶段)。在这几个权限表中，权限范围依次递减，全局权限覆盖局部权限（这四个表都在mysql系统库中）。

1.1 第一阶段权限(user权限表)

下面演示创建一个新用户z1@localhost,并赋予所有数据库上的所有表的select 权限。

-- 创建新用户(创建完成后，此时该新用户没有任何权限，所有权限字段的值都是 N)
CREATE USER 'z1'@'localhost' IDENTIFIED BY '';
-- 赋予所有数据库上的所有表的select 权限(执行grant 后，此时该新用户的Select_priv权限字段的值是 Y,如下图所示)
GRANT SELECT ON *.* TO z1@localhost;

SELECT * FROM mysql.`user` WHERE `User`='z1' AND `Host`='localhost'

1.2 第二阶段权限(db权限表)

在第一阶段完成后，user表z1@localhost用户的Select_priv字段值是Y，查看db权限表并没有关于z1@localhost用户的信息，在user权限表里，拥有相同权限的用户，是不需要记入db权限表, 这就是全局权限覆盖局部权限， db权限表信息如下图所示：

SELECT * FROM mysql.`db`

下面演示第二阶段：将z1@localhost用户上的权限改为只针对某一数据库上的所有表的select 权限(这里用test库)。

-- 撤回z1@localhost用户的全局Select_priv权限(此时user权限表的Select_priv权限字段的值是 N)
REVOKE SELECT ON *.* FROM z1@localhost
-- 赋予z1@localhost用户在test数据库的Select_priv权限(此时db权限表的Select_priv权限字段的值是Y，如下图所示)
GRANT SELECT ON test.* TO z1@localhost

SELECT * FROM mysql.`db` WHERE `User`='z1' AND `Host`='localhost'

通过上面的演示，知道user权限表z1@localhost用户的Select_priv值变成 N , 而db权限表则增加了一条记录。

1.3 第三阶段权限(权限tables_priv表)

-- 撤回z1@localhost用户的局部Select_priv权限 (此时db权限表的Db字段=test的记录信息删除了)
REVOKE SELECT ON test.* FROM z1@localhost
-- 赋予z1@localhost用户在test.testbackup的Select_priv权限(此时tables_priv权限表多了一条信息,如下图所示)
GRANT SELECT ON test.testbackup TO z1@localhost

-- 这里就显示了Host的ip地址，Db哪个数据库，User哪个用户, Table_name哪个表
SELECT * FROM mysql.`tables_priv`

1.4 第四阶段权限(权限columns_priv表)

最后就是针对表中的列来做权限控制了，还是使用Select_priv权限来演示。

-- 撤回z1@localhost用户test.testbackup表的Select_priv权限 (此时tables_priv权限表的Db字段=test的记录信息删除了)
REVOKE SELECT ON test.testbackup FROM z1@localhost
-- 赋予z1@localhost用户在test.testbackup表(id,name)列的Select_priv权限(此时columns_priv权限表多了二条信息，tables_priv权限表也产生了一条信息，如下所示)
GRANT SELECT(id,`name`) ON test.testbackup TO z1@localhost

SELECT * FROM mysql.`tables_priv`;