如何为树形（邻接列表）结构数据库设置用户权限？

Question

我们正在使用mysql。假设我们有一个邻接列表模型表（我知道它没有规范化）。例如，人员表具有以下字段：

Personnel
|employee|boss    |salary|
--------------------------
|CEO     |null    |999999|
|boss1   |CEO     |99    |
|boss2   |CEO     |99    |
|slvdrvr1|boss1   |9     |
|slvdrvr2|boss1   |9     |
|slave1  |slvdrvr1|1     |
|slave2  |slvdrvr1|1     |
|slave3  |slvdrvr1|1     |
|slave4  |slvdrvr2|1     |
|slave5  |boss2   |1     |
|slave6  |boss2   |1     |

我们还有一个带有一些用户的表格。例如，

Uers
|userid|role   |
----------------
|super1|b1     |
|super2|b2     |
|user1 |sd     |
|ruut  |admin  |

假设薪资信息是保密的。因此，某些用户可以查看特定分支机构的薪资信息。

例如，角色b1可以查看/编辑从boss1及以下所有员工的薪资。角色b2可以查看/编辑从boss2及以下所有员工的薪资。角色sd可以查看/编辑自slvdrvr1和slvdrvr2及以下所有员工的薪资。因此，我们需要设置一个用户-人事权限表来实现这一点。

人事表很大，有数万行。

我的问题是如何设置一个权限表，以便我们可以轻松维护和查询？

例如，选项1：我们可以为每个人员设置角色权限。但是，这样权限表将非常庞大。

选项2：我们只能为父节点设置角色权限。但是，每次用户想看到某个记录时，我们都必须查找直到我们有设置权限的每个记录的权限。

用户数量约为几百人。 角色数量少于100个。

如果不清楚，请向我提问。谢谢。

- biajee

Answer 1

为了让生活更有趣，考虑在用户之间建立一棵树形结构！

关于实现，创建另一个名为“User_EMPL_ACCESS”的表，它应该长这样：

表 USER_EMPL_ACCESS( User_ID char(..), ACCESS_PATH char(..) )

在访问路径中，存储每个用户有资格查看的最顶层节点的完全限定路径。

然后，开发一个名为hasAccess(userID, EMPLID)的UDF。

hasAccess函数应该执行以下操作：

1. 从UserID开始
2. 从USER_EMPL_ACCESS获取用户的访问路径
3. 使用CTE逻辑将邻接表展开为完全限定路径。如果您的数据库不提供CTE，请将邻接模型转换为嵌套集模型。
4. 从根节点开始获取用户访问路径和邻接路径的交集
5. 如果输入的EMPLID是上述任何交集路径的叶子节点，则返回1，否则返回0。

现在您的查询看起来像这样：

    select * from SALARY_TABLE where MONTH = 'JAN' 
    and hasAccess('myUserID', SALARY_TABLE.EMPLID) = 1

- srini.venigalla