SQL Server: Хранение иерархических данных ACL

Я хотел бы реализовать базу данных, содержащую иерархические данные ACL

Мои столы

  • USERS: idUser,имя пользователя,...
  • GROUPS: idGroups,имя...
  • GROUPSENTITIES: idGroup, idChild, childType (1 для пользователей, 2 из групп)
  • ROLES : idRole,имя...

  • ROLESENTITIES: idRole, IsDeny, idChild, childType (1 для пользователей, 2 из групп)

  • Каждый пользователь может принадлежать к 0 или более группам

  • Каждая группа может принадлежать 0 или более группам
  • Каждый пользователь и каждая группа могут принадлежать к 0 или более ролям, а роли могут быть разрешены или запрещены.
  • Если обнаружен явный отказ, роль отклоняется

Как я могу хранить такие данные? Мой дизайн правильный?

Можно ли получить список пользователей со всеми разрешенными ролями?

Не могли бы вы написать мне запрос (на основе T-SQL) для извлечения этой информации из БД?

заранее спасибо


sql-server hierarchy hierarchical-data hierarchical
person user2277672    schedule 17.08.2013    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете написать таблицы так, как вы ожидаете. Например, чтобы получить всех пользователей в группе, когда есть иерархические группы, вы должны использовать рекурсивное 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;
person Gordon Linoff    schedule 17.08.2013
comment
Вы можете объяснить лучше, пожалуйста? как насчет ролей? GROUPENTITIES — это перекрестная таблица для объединения ГРУПП и ПОЛЬЗОВАТЕЛЕЙ, а также ГРУПП с ГРУППАМИ (если childType=1, childId содержит userId, если childType=2, то childId содержит groupId). как я могу запросить эту структуру с помощью cte? - person user2277672; 18.08.2013
comment
извините, но не могу опубликовать DDL таблицы, потому что имя столбца локализовано... :( - person user2277672; 18.08.2013