Hierarchical permissions in a table stored hierarchy

Question

Assuming the following database structure (modifiable if need be) ...

I am looking for a nice way to determine the "effective permissions" for a given user on a given page in a way that allows me to return a row containing the Page and the effective permissions.

I am thinking that the ideal solution may include a function that uses a CTE to perform the recursion needed to evaluate the "effective permissions" for a given page row for the current user.

Background and Implementation details

The above schema represents a start point for a content management system in which users can be granted permissions by being added and removed from roles.

Resources in the system (e.g. pages) are associated with roles to grant the group of users linked to that role the permissions it grants.

The idea is to be able to easily lock down a user by simply having a deny all role and adding the root level page in the tree to that role and then adding the user to that role.

This would allow the permission structure to remain in place when (for example) a contractor working for the company is not available for long periods, this will then also allow for the same granting of their original permissions by simply removing the user from that one role.

Permissions are based on typical ACL type rules that might apply to file system by following these rules.

The CRUD permissions are to be nullable bits so the available values are true, false, not defined where the following is true:

• false + anything = false
• true + not defined = true
• true + true = true
• not defined + not defined = not defined

If any of the permissions is false -> false 
Else if any is true -> true
Else (all not defined) -> false

In other words you get no permissions on anything unless you are granted them through role membership and a deny rule overrides an allow rule.

The "set" of permissions this applies to is all permissions applied to the tree up to and including the current page, in other words: If a false is in any role applied to any page in the tree to this page then the result is false, but if the whole tree up to here is not defined then the current page contains a true rule the result is true here but would be false for the parent.

I'd like to loosely keep the db structure if possible, also keep in mind that my goal here is to be able to do something like: select * from pages where effective permissions (read = true) and user = ? so any solution should be able to allow me have a queryable set with the effective permissions in them in some way (returning them is optional as long as the criteria can be specified).

Assuming 2 pages exist where 1 is a child of the other and 2 roles exist, one for admin users and 1 for read only users, both are linked to only the root level page I would expect to see something like this as expected output:

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

Further discussion around this question can be found in the main site chat room starting here.

Answer 1

Using this model, I have come up with a way to query the Pages table in the following manner:

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

The GetPermissionStatus inline table-valued function's result can be either an empty set or one single-column row. When the result set is empty, that means that there are no non-NULL entries for the specified page/user/permission combination. The corresponding Pages row is automatically filtered out.

If the function does return a row, then its only column (IsAllowed) will contain either 1 (meaning true) or 0 (meaning false). The WHERE filter additionally checks that the value must be 1 for the row to be included in the output.

What the function does:

• walks the Pages table up the hierarchy to collect the specified page and all its parents into one row set;

• builds another row set containing all the roles the specified user is included in, along with one of the permission columns (but only non-NULL values) – specifically the one corresponding to the permission specified as the third argument;

• finally, joins the first and second set via the RolePages table to find the complete set of explicit permissions matching either the specified page or any of its parents.

The resulting row set is sorted in the ascending order of permission values and the topmost value is returned as the result of the function. Since nulls are filtered out at an earlier stage, the list can contain just 0s and 1s. Thus, if there is at least one "deny" (0) in the list of permissions, that will be the result of the function. Otherwise the topmost result will be 1, unless the roles corresponding to the selected pages happen to have no explicit "allows" either or there are just no matching entries for the specified page and user at all, in which case the result will be an empty row set.

This is the function:

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
);

Test case

• 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
  

• Data inserts:

  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
  

  So, just one user is used but it is assigned to two roles, with various combinations of permission values between the two roles to test the blending logic on child objects.

  The page hierarchy is very simple: one parent, two children. The parent is associated with one role, one of the children with the other role.

• Test script:

  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;
  

• Cleanup:

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

Results

• for Create:

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

  There was an explicit true for Page 1.1 only. The page was returned according to the "true + not defined" logic. The others were "not defined" and "not defined + not defined" – hence excluded.

• for Read:

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

  An explicit true was found in the settings for Page 1 and for Page 1.1. Thus, for the former it was just a single "true" while for the latter "true + true". There were no explicit read permissions for Page 1.2, so it was another "true + not defined" case. So, all three pages were returned.

• for Update:

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

  From the settings, an explicit true was returned for Page 1 and a false for Page 1.1. For the pages that made it into the output the logic was the same as in case of Read. For the excluded row both false and true were found and so the "false + anything" logic worked.

• for Delete there were no rows returned. The parent and one of the children had explicit nulls in the settings and the other child did not have anything.

Get all permissions

Now if you want just to return all effective permissions, you can adapt the GetPermissionStatus function:

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
);

The function returns four columns – the effective permissions for the specified page and user. Usage example:

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

Output:

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