patternsqlModerate

Hierarchical permissions in a table stored hierarchy

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

storedpermissionshierarchyhierarchicaltable

Problem

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

Solution

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

Code Snippets

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

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

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

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

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;

Context

StackExchange Database Administrators Q#148856, answer score: 11

Revisions (0)

No revisions yet.