SQL Server：存储分层ACL数据

时间：2021-01-04 23:36:29

I would like implement a database containing hierarchical acl data

我想实现一个包含分层acl数据的数据库

My tables

我的桌子

  • USERS: idUser,username,...
  • 用户：idUser，用户名，...
  • GROUPS: idGroups,name...
  • 组：idGroups，名字......
  • GROUPSENTITIES: idGroup, idChild, childType (1 for users, 2 from groups)
  • GROUPSENTITIES：idGroup，idChild，childType（用户为1，组为2）
  • ROLES : idRole,name...
  • 角色：idRole，名字......

  • ROLESENTITIES: idRole, IsDeny, idChild, childType (1 for users, 2 from groups)

    ROLESENTITIES：idRole，IsDeny，idChild，childType（用户为1，组为2）

  • Every user can belong to 0 or more groups

    每个用户都可以属于0个或更多组

  • Every group can belong to 0 or more groups
  • 每个组可以属于0个或更多组
  • Every user and every group can belong to 0 or more roles and roles can be allowed or denied
  • 每个用户和每个组都可以属于0个或更多角色，并且可以允许或拒绝角色
  • If an explicit deny is found, role is denied
  • 如果发现显式拒绝，则拒绝角色

How can I store this kind of data? Is my design correct?

我该如何存储这类数据？我的设计是否正确？

Is it possible retrieve a list of users with all allowed roles?

是否可以检索具有所有允许角色的用户列表？

Can you please write me a query (T-SQL based) for extract this information from db

你能不能给我写一个查询（基于T-SQL）从db中提取这些信息

thanks in advance

提前致谢

1 个解决方案

#1

0  

You can write the tables as you would expect. For instance, to get all the users in a group, when there are hierarchical groups, you would use a recursive CTE. Assume the group table is set up as:

您可以按预期编写表格。例如，要获取组中的所有用户，当存在分层组时，您将使用递归CTE。假设组表设置为：

create table groups (
    groupid int,
    member_userId int,
    member_groupid int,
    check (member_userId is NULL or member_groupid is null)
);

with usergroups as (
      select groupid, member_userid, 1 as level
      from groups
      union all
      select g.groupid, users.member_userid, 1+level
      from users u join
           groups g
           on u.member_groupid = g.groupid
    )
select *
from usergroups;

#1

0  

You can write the tables as you would expect. For instance, to get all the users in a group, when there are hierarchical groups, you would use a recursive CTE. Assume the group table is set up as:

您可以按预期编写表格。例如，要获取组中的所有用户，当存在分层组时，您将使用递归CTE。假设组表设置为：

create table groups (
    groupid int,
    member_userId int,
    member_groupid int,
    check (member_userId is NULL or member_groupid is null)
);

with usergroups as (
      select groupid, member_userid, 1 as level
      from groups
      union all
      select g.groupid, users.member_userid, 1+level
      from users u join
           groups g
           on u.member_groupid = g.groupid
    )
select *
from usergroups;
标签：

相关文章