Hierarchiczne uprawnienia w hierarchii przechowywanej w tabeli

Zakładając następującą strukturę bazy danych (w razie potrzeby modyfikowalną) ...

Szukam dobrego sposobu na określenie „efektywnych uprawnień” dla danego użytkownika na danej stronie w sposób, który pozwoli mi zwrócić wiersz zawierający stronę i efektywne uprawnienia.

Myślę, że idealnym rozwiązaniem może być funkcja wykorzystująca CTE do wykonania rekurencji potrzebnej do oceny „efektywnych uprawnień” dla danego wiersza strony dla bieżącego użytkownika.

Tło i szczegóły realizacji

Powyższy schemat stanowi punkt początkowy systemu zarządzania treścią, w którym użytkownikom można przyznawać uprawnienia poprzez dodawanie i usuwanie z ról.

Zasoby w systemie (np. Strony) są powiązane z rolami, aby nadać grupie użytkowników powiązanych z tą rolą uprawnienia, które przyznaje.

Chodzi o to, aby móc łatwo zablokować użytkownika, po prostu odmawiając roli i dodając stronę poziomu głównego w drzewie do tej roli, a następnie dodając użytkownika do tej roli.

Umożliwiłoby to utrzymanie struktury uprawnień, gdy (na przykład) kontrahent pracujący dla firmy nie byłby dostępny przez długi czas, umożliwiłoby to również takie samo udzielenie pierwotnych uprawnień, po prostu usuwając użytkownika z tej jednej roli .

Uprawnienia są oparte na typowych regułach typu ACL, które mogą mieć zastosowanie do systemu plików, przestrzegając tych reguł.

Uprawnienia CRUD mają być zerowalnymi bitami, więc dostępne wartości są prawdziwe, fałszywe, nie są zdefiniowane, gdy spełnione są następujące warunki:

• false + cokolwiek = false
• true + nie zdefiniowano = true
• prawda + prawda = prawda
• nie zdefiniowano + nie zdefiniowano = nie zdefiniowano

Jeśli którekolwiek z uprawnień jest fałszywe -> fałszywe 
W przeciwnym razie prawda jest> prawda
W przeciwnym razie (wszystkie nie zdefiniowane) -> fałsz

Innymi słowy, nie otrzymujesz żadnych uprawnień, chyba że uzyskasz je poprzez członkostwo w roli, a reguła odmowy zastępuje regułę zezwalania.

„Zestaw” uprawnień, których dotyczy, to wszystkie uprawnienia zastosowane do drzewa do bieżącej strony włącznie, innymi słowy: jeśli fałsz ma jakąkolwiek rolę zastosowaną do dowolnej strony w drzewie tej strony, wynik jest fałszywy , ale jeśli całe drzewo do tej pory nie jest zdefiniowane, bieżąca strona zawiera prawdziwą regułę, wynik jest tutaj prawdziwy, ale dla rodzica byłby fałszywy.

Chciałbym luźno zachować strukturę db, jeśli to możliwe, należy również pamiętać, że moim celem tutaj jest możliwość zrobienia czegoś takiego: select * from pages where effective permissions (read = true) and user = ?więc każde rozwiązanie powinno pozwolić mi mieć zestaw zapytań z efektywnymi uprawnieniami w nich w jakiś sposób (ich zwrot jest opcjonalny, o ile można określić kryteria).

Zakładając, że istnieją 2 strony, na których 1 jest dzieckiem drugiej, a istnieją 2 role, jedna dla użytkowników administracyjnych i 1 dla użytkowników tylko do odczytu, obie są połączone tylko ze stroną poziomu głównego.

Admin user:
Id, Parent, Name, Create, Read, Update, Delete
1,  null,   Root, True  , True, True  , True 
2,  1,      Child,True  , True, True  , True 

Read only user:
Id, Parent, Name, Create, Read, Update, Delete
1,  null,   Root, False , True, False , False 
2,  1,      Child,False , True, False , False

Dalszą dyskusję wokół tego pytania można znaleźć w głównym czacie na stronie, zaczynając tutaj .

Korzystając z tego modelu, wymyśliłem sposób zapytania do tabeli Pages w następujący sposób:

SELECT
  p.*
FROM
  dbo.Pages AS p
  CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, @PermissionName) AS ps
WHERE
  ps.IsAllowed = 1
;

Wynik funkcji wycenionej w tabeli GetPermissionStatus może być pustym zestawem lub wierszem z jedną kolumną. Gdy zestaw wyników jest pusty, oznacza to, że nie ma żadnych pozycji innych niż NULL dla określonej kombinacji strona / użytkownik / uprawnienie. Odpowiedni wiersz Strony jest automatycznie odfiltrowywany.

Jeśli funkcja zwraca wiersz, wówczas jedyna kolumna ( IsAllowed ) będzie zawierać 1 (co oznacza prawda ) lub 0 (co oznacza fałsz ). Filtr WHERE dodatkowo sprawdza, czy wartość musi wynosić 1, aby wiersz mógł zostać uwzględniony w danych wyjściowych.

Co robi funkcja:

• spacery Pages stół w górę hierarchii, aby zebrać określoną stronę i wszystkie jego rodziców w jeden zestaw wierszy;

• buduje inny zestaw wierszy zawierający wszystkie role, w których jest podany użytkownik, wraz z jedną z kolumn uprawnień (ale tylko wartościami innymi niż NULL) - konkretnie ten odpowiadający uprawnieniu określonemu jako trzeci argument;

• wreszcie dołącza do pierwszego i drugiego zestawu za pośrednictwem tabeli RolePages, aby znaleźć pełny zestaw jawnych uprawnień pasujących do określonej strony lub dowolnego z jej elementów nadrzędnych .

Wynikowy zestaw wierszy jest sortowany w porządku rosnącym wartości uprawnień, a najwyższa wartość jest zwracana jako wynik funkcji. Ponieważ wartości zerowe są odfiltrowywane na wcześniejszym etapie, lista może zawierać tylko 0 i 1. Zatem jeśli na liście uprawnień jest co najmniej jedno „odmowa” (0), będzie to wynik działania funkcji. W przeciwnym razie najwyższy wynik będzie wynosił 1, chyba że role odpowiadające wybranym stronom nie będą miały żadnego wyraźnego „zezwala” albo nie będzie po prostu żadnych pasujących wpisów dla określonej strony i użytkownika, w którym to przypadku wynik będzie pusty zestaw wierszy.

To jest funkcja:

CREATE FUNCTION dbo.GetPermissionStatus
(
  @PageId int,
  @UserId int,
  @PermissionName varchar(50)
)
RETURNS TABLE
AS
RETURN
(
  WITH
    Hierarchy AS
    (
      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
      WHERE
        p.Id = @PageId

      UNION ALL

      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
        INNER JOIN hierarchy AS h ON p.Id = h.ParentId
    ),
    Permissions AS
    (
      SELECT
        ur.Role_Id,
        x.IsAllowed
      FROM
        dbo.UserRoles AS ur
        INNER JOIN Roles AS r ON ur.Role_Id = r.Id
        CROSS APPLY
        (
          SELECT
            CASE @PermissionName
              WHEN 'Create' THEN [Create]
              WHEN 'Read'   THEN [Read]
              WHEN 'Update' THEN [Update]
              WHEN 'Delete' THEN [Delete]
            END
        ) AS x (IsAllowed)
      WHERE
        ur.User_Id = @UserId AND
        x.IsAllowed IS NOT NULL
    )
  SELECT TOP (1)
    perm.IsAllowed
  FROM
    Hierarchy AS h
    INNER JOIN dbo.RolePages AS rp ON h.Id = rp.Page_Id
    INNER JOIN Permissions AS perm ON rp.Role_Id = perm.Role_Id
  ORDER BY
    perm.IsAllowed ASC
);

Przypadek testowy

• DDL:

  CREATE TABLE dbo.Users (
    Id       int          PRIMARY KEY,
    Name     varchar(50)  NOT NULL,
    Email    varchar(100)
  );
  
  CREATE TABLE dbo.Roles (
    Id       int          PRIMARY KEY,
    Name     varchar(50)  NOT NULL,
    [Create] bit,
    [Read]   bit,
    [Update] bit,
    [Delete] bit
  );
  
  CREATE TABLE dbo.Pages (
    Id       int          PRIMARY KEY,
    ParentId int          FOREIGN KEY REFERENCES dbo.Pages (Id),
    Name     varchar(50)  NOT NULL
  );
  
  CREATE TABLE dbo.UserRoles (
    User_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Users (Id),
    Role_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Roles (Id),
    PRIMARY KEY (User_Id, Role_Id)
  );
  
  CREATE TABLE dbo.RolePages (
    Role_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Roles (Id),
    Page_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Pages (Id),
    PRIMARY KEY (Role_Id, Page_Id)
  );
  GO

• Wstawianie danych:

  INSERT INTO
    dbo.Users (ID, Name)
  VALUES
    (1, 'User A')
  ;
  INSERT INTO
    dbo.Roles (ID, Name, [Create], [Read], [Update], [Delete])
  VALUES
    (1, 'Role R', NULL, 1, 1, NULL),
    (2, 'Role S', 1   , 1, 0, NULL)
  ;
  INSERT INTO
    dbo.Pages (Id, ParentId, Name)
  VALUES
    (1, NULL, 'Page 1'),
    (2, 1, 'Page 1.1'),
    (3, 1, 'Page 1.2')
  ;
  INSERT INTO
    dbo.UserRoles (User_Id, Role_Id)
  VALUES
    (1, 1),
    (1, 2)
  ;
  INSERT INTO
    dbo.RolePages (Role_Id, Page_Id)
  VALUES
    (1, 1),
    (2, 3)
  ;
  GO

  Tak więc używany jest tylko jeden użytkownik, ale jest on przypisany do dwóch ról, z różnymi kombinacjami wartości uprawnień między dwiema rolami w celu przetestowania logiki mieszania na obiektach potomnych.

  Hierarchia stron jest bardzo prosta: jeden rodzic, dwoje dzieci. Rodzic jest powiązany z jedną rolą, jedno dziecko z drugą rolą.

• Skrypt testowy:

  DECLARE @CurrentUserId int = 1;
  SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Create') AS perm WHERE perm.IsAllowed = 1;
  SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Read'  ) AS perm WHERE perm.IsAllowed = 1;
  SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Update') AS perm WHERE perm.IsAllowed = 1;
  SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Delete') AS perm WHERE perm.IsAllowed = 1;

• Sprzątać:

  DROP FUNCTION dbo.GetPermissionStatus;
  GO
  DROP TABLE dbo.UserRoles, dbo.RolePages, dbo.Users, dbo.Roles, dbo.Pages;
  GO

Wyniki

• dla Utwórz :

  Id  ParentId  Name
  --  --------  --------
  2   1         Page 1.1

  Nastąpił wyraźny prawdziwe dla Page 1.1tylko. Strona została zwrócona zgodnie z logiką „prawda + nie zdefiniowano”. Pozostałe były „nieokreślone” i „nieokreślone + nieokreślone” - stąd wykluczone.

• do odczytu :

  Id  ParentId  Name
  --  --------  --------
  1   NULL      Page 1
  2   1         Page 1.1
  3   1         Page 1.2

  W ustawieniach dla i dla znaleziono wyraźną prawdę . Tak więc dla tego pierwszego było to tylko jedno „prawdziwe”, podczas gdy dla drugiego „prawdziwe + prawdziwe”. Nie było wyraźnych uprawnień do odczytu , więc był to kolejny przypadek „prawda + nie zdefiniowano”. Wszystkie trzy strony zostały zwrócone.Page 1Page 1.1Page 1.2

• do aktualizacji :

  Id  ParentId  Name
  --  --------  --------
  1   NULL      Page 1
  3   1         Page 1.2

  Z ustawień zwrócono jawną wartość truePage 1 i wartość false dla Page 1.1. Dla stron, które trafiły do ​​wyjścia, logika była taka sama jak w przypadku Read . Dla wykluczonego wiersza znaleziono zarówno fałsz, jak i prawda, więc zadziałała logika „fałsz + cokolwiek”.

• dla Usuń nie zwrócono żadnych wierszy. Rodzic i jedno z dzieci mieli w ustawieniach wyraźne wartości zerowe, a drugie dziecko nie miało niczego.

Uzyskaj wszystkie uprawnienia

Teraz, jeśli chcesz zwrócić wszystkie efektywne uprawnienia, możesz dostosować funkcję GetPermissionStatus :

CREATE FUNCTION dbo.GetPermissions(@PageId int, @UserId int)
RETURNS TABLE
AS
RETURN
(
  WITH
    Hierarchy AS
    (
      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
      WHERE
        p.Id = @PageId

      UNION ALL

      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
        INNER JOIN hierarchy AS h ON p.Id = h.ParentId
    ),
    Permissions AS
    (
      SELECT
        ur.Role_Id,
        r.[Create],
        r.[Read],
        r.[Update],
        r.[Delete]
      FROM
        dbo.UserRoles AS ur
        INNER JOIN Roles AS r ON ur.Role_Id = r.Id
      WHERE
        ur.User_Id = @UserId
    )
  SELECT
    [Create] = ISNULL(CAST(MIN(CAST([Create] AS int)) AS bit), 0),
    [Read]   = ISNULL(CAST(MIN(CAST([Read]   AS int)) AS bit), 0),
    [Update] = ISNULL(CAST(MIN(CAST([Update] AS int)) AS bit), 0),
    [Delete] = ISNULL(CAST(MIN(CAST([Delete] AS int)) AS bit), 0)
  FROM
    Hierarchy AS h
    INNER JOIN dbo.RolePages AS rp ON h.Id = rp.Page_Id
    INNER JOIN Permissions AS perm ON rp.Role_Id = perm.Role_Id
);

Funkcja zwraca cztery kolumny - skuteczne uprawnienia dla określonej strony i użytkownika. Przykład użycia:

DECLARE @CurrentUserId int = 1;
SELECT
  *
FROM
  dbo.Pages AS p
  CROSS APPLY dbo.GetPermissions(p.Id, @CurrentUserId) AS perm
;

Wynik:

Id  ParentId  Name      Create Read  Update Delete
--  --------  --------  ------ ----- ------ ------
1   NULL      Page 1    0      1     1      0
2   1         Page 1.1  1      1     0      0
3   1         Page 1.2  0      1     1      0