MySQL：限制每个用户访问记录子集

Question

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

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

我们有大约200个MySQL用户，每个用户都与一个或多个部门相关联（通过一个简单的用户名/部门ID表）。我们希望仅限制每个用户对其正确记录子集的访问（SELECT，UPDATE）。

例如，一个用户可能只能查看和更新与他们所属部门相关的记录。

SELECT * FROM DataTable

用户"Bob"与1和3部门相关联，应返回记录1、2和4。对于与1部门相关联的用户"Alice"，应仅返回记录1和2。

如何最好地实现这一点？

- ErikvdW

---

你需要从应用程序端进行操作。实际上，这非常容易。 - Alexander

当你说Mysql用户时，你是指连接到数据库的mysql.user表中列出的MySQL用户，还是指应用程序中名为“User”的表中的“用户”？ - Ray

我指的是实际的MySQL用户。我们尽可能让MySQL处理用户权限。这样，只有当用户能够连接和访问数据库时，他们才能登录到我们的应用程序中。 - ErikvdW

@ErikvdW，看到你想让他们能够像Sequel Pro或phpMyAdmin这样的管理员工具直接登录并使用表格。此外，我将修改我的答案，展示如何添加部门映射表，因为它不是行到用户，而是行到部门。逻辑基本上仍然相同。 - Ray

Answer 1

MySQL没有基于行的权限。你最好的选择是构建视图以显示特定记录。一个简单的方法是添加一个映射表，将MySQL用户名映射到他们有访问权限的部门ID，并让视图在连接到原始表时选择该映射表并限制为当前请求用户。你可以使用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.*到特定字段集。现在根据需要填充mysqlusers到department列的映射表。当前用户返回确切的用户，因此使用完整的用户字符串（username@localhost，username@％等...）或更改视图以在比较中去掉主机。现在用户可以访问filtered_view，并且只能看到他们按部门访问的行。奖励：由于这是多对多，如果需要，您可以将人员分配到多个部门--原始表的主键上的组合避免了重复记录。您可能还想锁定mysqluser_department映射表的权限。

- Ray

---

虽然这不是完美的解决方案（不想直接限制每个记录到特定用户，而是通过部门键），但它确实对我有很大帮助。我没有想到可以使用 current_user() 创建视图。我曾考虑过为每个用户在登录时创建临时视图，但这种方法更加简单。谢谢！ - ErikvdW

此外，我意识到这篇文章已经超过5年了，但我有一个问题：除非我错过了什么，否则使用INVOKER安全选项需要限制用户对基础表具有选择权限。选择INVOKER有什么理由吗？使用DEFINER安全性和USER（）函数不是更好吗？ - favq

@favq，我在尝试使用DEFINER安全性而不是INVOKER的Ray方法几个小时后才看到了你的评论。然后我突然发现我一直缺失的东西：在where语句中，我没有将Current_user()更改为User()。我总是得到空白结果，因为where语句总是返回false hahaha。感谢您的重要指出。 - yu quan