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

Question

我们使用的几个表大致如下：

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

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

例如，

SELECT * FROM DataTable

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

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

Answer 1

MySQL没有基于行的权限。最好的方法是构造视图来显示某些记录。一个简单的方法是将一个带有mysql用户名的映射表添加到部门id中，他们可以访问这个部门id，并将该映射表上的视图select连接到原始表，其中仅限于当前请求的用户。您可以使用CURRENT_USER()获取当前mysql用户。

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

以下是这些步骤的细目：

   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;

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

现在，用户可以访问filtered_view，并且只能按部门查看它们可以访问的行。奖励:因为这是多对多的，如果需要的话，可以让多个部门的人员--按原表主键的组--避免重复记录。

您可能还希望锁定对映射表mysqluser_department的权限。