MySQL:限制每个用户对记录子集的访问

Question

我们正在使用几个大致如下所示的表:

| ID  | DepartmentId | Data |
| 1   | 1            | ...  |
| 2   | 1            | ...  |
| 3   | 2            | ...  |
| 4   | 3            | ...  |
| 5   | 2            | ...  |

我们有大约 200 个 MySQL 用户，每个用户都与一个或多个部门相关联(通过一个简单的 Username/DepartmentId 表)我们希望将访问(SELECT、UPDATE)限制为仅对每个用户的正确记录子集。

例如，一个

SELECT * FROM DataTable

与部门 1 和 3 关联的用户“Bob”应该返回记录 1、2 和 4。与部门 1 关联的用户“Alice”的相同查询应该只返回记录 1 和 2。

实现此目标的最佳方法是什么？

Answer 1

MySQL 没有基于行的权限。您能做的最好的事情就是构建 View 来显示某些记录。一种简单的方法是将带有 mysql 用户名的映射表添加到他们有权访问的部门 ID，并让该映射表上的 View 选择连接到仅限于当前请求用户的原始表。您可以使用 CURRENT_USER() 获取当前的 mysql 用户。

然后将用户限制为 View ，而不是原始表。

以下是步骤的分割:

   CREATE TABLE `mysqluser_dept` (
     `mysqluser` varchar(255) NOT NULL DEFAULT '',
     `DepartmentId` int(11) NOT NULL DEFAULT '0',
       PRIMARY KEY (`mysqluser`,`DepartmentId`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    CREATE SQL SECURITY INVOKER VIEW filtered_view AS 
           SELECT o.* FROM original_table o 
               JOIN mysqluser_dept m on m.DepartmentId = o.DepartmentId
                    WHERE m.mysqluser = current_user() GROUP by o.id;

如果您不想显示原始表的所有列，您可以根据需要将 View 中的select o.* 限制为一组特定的字段。现在根据需要将 mysqlusers 的映射表填充到 departmet 列。当前用户返回确切的用户，因此使用完整的用户字符串(username@localhost、username@%，等等...)或更改 View 以在比较中切断主机。

现在用户可以访问 filtered_view 并且只能按部门查看他们有权访问的行。额外奖励:由于这是多对多的，如果需要，您可以让多个部门的人员——在原始表的主键上分组可以避免重复记录。

您可能还想锁定映射表 mysqluser_department 的权限。