如何为访问控制列表（ACL）构建数据模型 [英] How to build a data model for an access control list (ACL)

问题描述

当你只处理具有某种级别的离散资源访问权限的离散用户时，如何建模一个作为访问控制列表（ACL）的数据库表格，这是相当明显的。像这样：

It's fairly obvious how to model a database table that would act as an access control list (ACL) when you're just dealing with discrete users who have some level of access to a discrete resource. Something like this:

TABLE acl（
user_id INT，
resource_id INT，
access_type INT
）

...其中access_type是一个类似以下的数字：

... where access_type is a number representing something like:

0（或缺少user_id和resource_id的记录）意味着无访问

0 (or lack of record for user_id and resource_id) means no access

1表示只读

2表示完全控制

但是，如果用户等场景可以是一个或多个组的成员，而组可以包含其他组，则开始变得棘手。然后资源可以是包含其他资源的文件夹。

However it starts getting trickier when you've got scenarios like users can be a member of one or more groups and groups can contain other groups. Then a resource could be a folder that contains other resources.

除了在运行时执行一堆递归查询以确定访问级别用户应该有一个资源，这些情况下如何处理呢？

Other than the obviously poor approach of doing a whole bunch of recursive queries at runtime to determine the level of access a user should have to a resource, how do these scenarios tend to get handled? Are there commonly-accepted designs for modelling an ACL like this?

推荐答案

是否使用支持<$ c $的DB c> connect by ，或类似的东西？
在oracle中，我已经实现了以下操作。

Are you using a DB with support for connect by, or something similar? In oracle, I've implemented the following.

Table Group //Just the parent groups
{
    groupCode varchar
    groupDesc
}

Table groupMap //associates groups with other groups
{
    parentGroup
    childGroup
}

table userGroup //can assign user to more than one group
{
    userId
    groupCode
}

然后使用通过连接以获取用户的所有子组

then use connect by to get all child groups for user

SELECT rm.CHILDGroup as roleCode
FROM groupMap rm
CONNECT BY PRIOR rm.CHILDGroup = rm.PARENTGroup
START WITH rm.CHILDGroup in
  (SELECT ur.groupCode
   FROM userGroup ur
   WHERE ur.userId = &userId);

此查询将获取在 userGroup中分配给用户的所有组以及分配给用户所属组的所有子组。

This query will get all the groups that were assigned to the user in userGroup and all the child groups assigned to the groups that the user belongs to.

这篇关于如何为访问控制列表（ACL）构建数据模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！