T-SQL Tuesday #18 - CTEs - The permission hierarchy problem

May 10, 2011 00:01 by Matt Whitfield

Recently, I was looking at a problem which involved an arbitrary tree structure for permissions. The idea was similar to NTFS security, in that the permission that was defined furthest from the root of the tree would apply to any items below it.

So, consider the following structure:

Users

  • Bob
  • James
  • Tim

Roles

  • Administrator
  • Reporter
  • Engineer

Containers

Company A
    North
    South
    East
    West
        RecursiveTown
        Tableton

So, Bob is the manager at Company A. He gets the admin role at all levels. James is the manager for Company A/North. He gets the admin role for Company A/North, but he is allowed to run reports on Company A's other regions. Tim is an engineer for RecursiveTown - and he just has the engineer role for that town.

Let's have some (very) basic table structures that meet our needs, data-wise:

CREATE TABLE [dbo].[Users] (    ID     INT           IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,    [Name] NVARCHAR (50));CREATE TABLE [dbo].[Roles] (    ID     INT           IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,    [Name] NVARCHAR (50));CREATE TABLE [dbo].[Containers] (    ID                INT           IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,    ParentContainerID INT           FOREIGN KEY REFERENCES [dbo].[Containers] (ID)                                     ON DELETE NO ACTION ON UPDATE NO ACTION,    [Name]            NVARCHAR (50));CREATE TABLE [dbo].[UserContainerRoles] (    ID          INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED,    ContainerID INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Containers] ([ID])                              ON DELETE NO ACTION ON UPDATE NO ACTION,    RoleID      INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Roles] ([ID])                              ON DELETE NO ACTION ON UPDATE NO ACTION,    UserID      INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Users] ([ID])                              ON DELETE NO ACTION ON UPDATE NO ACTION);

Simple... Let's look at the data that might be within them:

Users

IDName
1Bob
2James
3Tim

Roles

IDName
1Administrator
2Reporter
3Engineer

Containers

IDParentContainerIDName
1<NULL>Company A
21North
31South
41East
51West
65RecursiveTown
75Tableton

UserContainerRoles

IDContainerIDRoleIDUserID
1111
1122
1212
1633

So - how can we, succinctly, get a list of the containers and roles that apply to a particular user?

Enter the recursive CTE. Now, recursive CTEs can be a bit interesting. They can also suck, performance-wise. But, for smaller data-access patterns, such as this one, they can provide some very flexible solutions to some problems that previously were not easy to solve at all.

Recursive CTEs follow a simple pattern, but it can seem a bit daunting at first. They all follow this pattern:

Anchor
UNION
Recursor

The Anchor is the statement that returns the basic result set - the first level of recursion. This is the result set that we start with. In the example above, it is the containers to which the user has been given a direct role assignment through the UserContainerRoles table.

The Recursor is the interesting part - it's a query that references the Anchor, and provides the next level of result set. Very nice, but what happens next? Well, next the result set that was last returned by the recursor is passed to the recursor again - and this loop continues until either the recursor part returns no more rows, or the maxrecursion limit is reached.

Now I'm assuming that the fact that you're still reading means you probably just want to see the code. Who am I to stand in your way?

CREATE FUNCTION [dbo].[fn_GetContainerRolesForUserID](@UserID int)  RETURNS TABLEASRETURN (WITH   Hierarchy (ContainerID, RoleID, HierarchyLevel)  AS     (SELECT [c].[ID],                 [ucr].[RoleID],                 1 AS HierarchyLevel          FROM   [dbo].[Containers] AS [c]                 INNER JOIN                 [dbo].[UserContainerRoles] AS [ucr]                 ON [ucr].[ContainerID] = [c].[ID]          WHERE  [ucr].[UserID] = @UserID          UNION ALL          SELECT [c].[ID],                 [h].[RoleID],                 [h].[HierarchyLevel] + 1 AS HierarchyLevel          FROM   [dbo].[Containers] AS [c]                 INNER JOIN                 [Hierarchy] AS [h]                 ON [c].[ParentContainerID] = [h].[ContainerID])  SELECT [ContainerID],         [Name],         [RoleID]  FROM   (SELECT [ContainerID],                 [Name],                 [RoleID],                 [HierarchyLevel],                 ROW_NUMBER() OVER (PARTITION BY [h].[ContainerID]                                     ORDER BY [HierarchyLevel]) AS __RN          FROM   [Hierarchy] AS [h] INNER JOIN                  [dbo].[Containers] AS [c]            ON     [h].[ContainerID] = [c].[ID]) AS iDat  WHERE  [__RN] = 1)

The only non-obvious thing about this query is the use of the ROW_NUMBER function. This is what causes a permission defined at a lower level to take precedence over one defined at a higher level - this is because HeirarchyLevel increases each time it passes through the recursor section of the query, so we want to choose the container role with the lowest hierarchy level for each container.

So, let's look at each of our results:

SELECT [ContainerID], [Name], [RoleID]   FROM [dbo].[fn_GetContainerRolesForUserID](1)

ContainerIDNameRoleID
1Company A1
2North1
3South1
4East1
5West1
6RecursiveTown1
7Tableton1

Ok, so Bob has the admin role at all levels - just what we wanted to see...

SELECT [ContainerID], [Name], [RoleID]   FROM [dbo].[fn_GetContainerRolesForUserID](2)

ContainerIDNameRoleID
1Company A2
2North1
3South2
4East2
5West2
6RecursiveTown2
7Tableton2

James has the Admin role for North, but the Reporter role everywhere else - again, just what we want...

SELECT [ContainerID], [Name], [RoleID]   FROM [dbo].[fn_GetContainerRolesForUserID](3)

ContainerIDNameRoleID
6RecursiveTown3

This just shows that Tim has the Engineer role at RecursiveTown only - excellent...

Simple, right? Happy T-SQL Tuesday!

Tags: , ,

community | t-sql

Comments

5/10/2011 4:31:18 PM #

Bob Pusateri

Hi Matt - Great post and example.  Thanks for contributing to T-SQL Tuesday!

Bob Pusateri United States

5/10/2011 8:37:08 PM #

mattw@atlantis-interactive.co.uk

Bob - Hey, no worries - always a pleasure - and thanks for hosting!

mattw@atlantis-interactive.co.uk United Kingdom

5/11/2011 2:00:22 PM #

pingback

Pingback from bobpusateri.comT-SQL Tuesday #18 Wrapup | Bob Pusateri - The Outer Join

bobpusateri.com

Add comment



(Will show your Gravatar icon)



  Country flag

biuquote
  • Comment
  • Preview
Loading



CAPTCHA image




Data Inspector

Tag cloud

Calendar

<<  October 2014  >>
MoTuWeThFrSaSu
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

View posts in large calendar