Randy Evans here…I am a Principal Architect on the Information Security Tools team. During the Risk Tracker project (something well be talking more about in the coming weeks), we found that a natural hierarchy of roles was inherent in the RBA authorization design. For example, the “Task User” role has a set of permissions and the “Task Manager” role requires all the same permissions of a “Task User” plus some additional permissions. The “Risk Manager” role requires all the same permissions of a “Task Manager” plus some additional permissions and so on. To model this hierarchy we added a recursive table self reference to the Role table by adding a ParentRoleID column.
CREATE TABLE [dbo].[Role]( [RoleID] [int] IDENTITY(1,1) NOT NULL, [RoleName] [nvarchar](50) NOT NULL, [ParentRoleID] [int] NULL)
Traversing a recursive table self reference can be a little confusing to code using T-SQL; especially if you don’t have a defined number of recursions. Below is the approach we used to find all descendants of any particular role using T-SQL. The first “insert into” primes the temp table by finding all child roles with a ParentRoleID matching the top level role that we’re interested in. The second “insert into” populates the temp table with all remaining descendants by looping until no more records are selected. The second query looks for all children with ParentRoleIDs that match any RoleID in the temp table. It uses a correlated subquery to prevent previously selected roles from being selected again. The final query simply returns all roles found in the temp table. This pattern will work regardless of the number of levels that exist in the role hierarchy.
DELCARE @role as nvarchar(50) = 'Risk Manager' CREATE TABLE [dbo].[#TopRoles] (RoleID int, ParentRoleID int) /*Get the immediate children of the passed in role. */
INSERT INTO [dbo].[#TopRoles] SELECT child.RoleID , child.ParentRoleID FROM [dbo].[Role] parent JOIN [dbo].[Role] child ON child.ParentRoleID = parent.RoleID AND parent.RoleName = @role WHILE @@ROWCOUNT > 0 BEGIN /*Get the children's children. */
INSERT INTO [dbo].[#TopRoles] SELECT child.RoleID , child.ParentRoleID FROM [dbo].[#TopRoles] parent JOIN [dbo].[Role] child ON child.ParentRoleID = parent.RoleID --Gets the children AND not exists ( --Don't return rows that already exist. SELECT parent2.RoleID FROM [dbo].[#TopRoles] parent2 WHERE parent2.RoleID = child.RoleID) END SELECT r.RoleName FROM [dbo].[#TopRoles] tr JOIN [dbo].[Role] r ON r.RoleID = tr.RoleID
Hope you find this useful if you are faced solving similar problems!