SQL query to return roles and groups a user has that are not related to a toplevel role

Question

I have a set of tables in SQL Server that all join to each other and I'm trying to find a way to find a negative relationship. My tables also have recursion.

The data is here: https://sqlfiddle.com/sql-server/online-compiler?id=6e751c79-9e50-456d-a31f-1757f05bcbcf

I have objects users, groups, and roles, as well as join tables. I put all of the tables below. Users can have groups and user can be in roles. Roles grant access to groups. So in completeness, users can have groups either direct OR because of a role.

Then the next complication is that a role can grant access to another role. This is where the recursion comes in. The parent role grants access to the child role. So if a user has the parent, they also get all of the children on down the link.

Finally, a role can be a toplevel role. Just a bit.

The question then is can we get in a single statement all of the roles and groups that a user has that are NOT part of a toplevel role that the user has access to.

I can get a list of the groups a user has assigned directly, as well as a listing of the roles a user has, with the groups.

But I can't figure out how to move beyond that. If a role is a parent/child, how do I include the groups in the child? Then, the most important part, is how do I exclude from these results anything that is in a toplevel role hierarchy?

In my sample data, let's take Alice, uid=1. Alice has roles 1 and 3. Alice has ALL groups assigned directly. Role 1 is a parent and grants role 2. Role 1 is a toplevel role. I want to show everything that Alice has that is NOT related to any of her toplevel roles. Alice has role 1 which grants role 2. Role 2 grants groups 1 and 2.

My result should looks like this:

uid	name	roleid	rolename	gid	groupname
1	alice	3	Role C
1	alice			3	group 3

Alice has group3 and role 3 because they are not in the chain of anything that the toplevel role1 grants.

I can do this with queries and looping in code, but can it be done with SQL, even if with a CTE? In general, I could have 5 or more levels of a role within a role.

Thank you.

CREATE TABLE [dbo].[groups]
(
    [gid] [int] NOT NULL,
    [groupname] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[roles]
(
    [roleid] [int] NOT NULL,
    [rolename] [varchar](50) NOT NULL,
    [toplevel] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[users]
(
    [uid] [int] NOT NULL,
    [name] [varchar](50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[usergroup]
(
    [uid] [int] NOT NULL,
    [gid] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[userrole]
(
    [uid] [int] NOT NULL,
    [roleid] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rolegroup]
(
    [roleid] [int] NOT NULL,
    [gid] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rolerole]
(
    [partentid] [int] NOT NULL,
    [childid] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO [users] VALUES (1,'alice')
INSERT INTO [users] VALUES (2,'bob')
INSERT INTO [users] VALUES (3,'chris')

INSERT INTO [groups] VALUES (1,'group 1')
INSERT INTO [groups] VALUES (2,'group 2')
INSERT INTO [groups] VALUES (3,'group 3')

INSERT INTO [roles] VALUES (1,'Role A',1)
INSERT INTO [roles] VALUES (2,'Role B',0)
INSERT INTO [roles] VALUES (3,'Role C',0)

INSERT INTO rolerole VALUES (1,2)

INSERT INTO rolegroup VALUES (2,1)
INSERT INTO rolegroup VALUES (2,2)
INSERT INTO rolegroup VALUES (3,1)
INSERT INTO rolegroup VALUES (3,2)

INSERT INTO userrole VALUES (1,1)
INSERT INTO userrole VALUES (1,3)
INSERT INTO userrole VALUES (2,1)
INSERT INTO userrole VALUES (3,2)

INSERT INTO usergroup VALUES (1,1)
INSERT INTO usergroup VALUES (1,2)
INSERT INTO usergroup VALUES (1,3)

INSERT INTO usergroup VALUES (3,1)
INSERT INTO usergroup VALUES (3,2)


SELECT * 
FROM groups
INNER JOIN usergroup ON usergroup.gid = groups.gid
INNER JOIN users ON users.uid = usergroup.uid
WHERE users.uid = 1


SELECT * 
FROM users
LEFT JOIN userrole ON userrole.uid = users.uid  
LEFT JOIN roles ON roles.roleid = userrole.roleid
LEFT JOIN rolegroup ON rolegroup.roleid = userrole.roleid
LEFT JOIN groups ON groups.gid = rolegroup.gid
WHERE users.uid = 1

Answer 1

I'm not sure why you'd want these two resultsets bodged together, as the two requirements seem entirely separate:

• Roles which are descended from other roles the user has, but are not toplevel = 1 and are not directly assigned to the user.
• Groups which the user is directly assigned, which are not connected via a role or descendant role that the user has.

Either way, both these requirements can be satisfied using a recursive CTE to build up a list of all roles and descendant roles for a given user. UNION ALL is used to put together these disparate resultsets.

In the recursive part, we keep track of each role's ultimate ancestor's toplevel column, which we can then use on the outside.

• In the first requirement, we simply show all roles which have ancestorIsTopLevel = 0.
• In the second requirement, we join these roles to all their associated groups, union to all the usergroups, and then group up by group (because a group may be attached in multiple ways). We can then check if there is any association via ancestorIsTopLevel=1.

WITH allRoles AS (
    SELECT
      ur.uid,
      ur.roleid AS parentid,
      ur.roleid,
      parent.toplevel AS ancestorIsTopLevel
    FROM userrole ur
    JOIN roles parent ON parent.roleid = ur.roleid
    WHERE ur.uid = 1
    
    UNION ALL

    SELECT
      ar.uid,
      ar.parentid,
      rr.childid,
      ar.ancestorIsTopLevel
    FROM allRoles ar
    JOIN rolerole rr ON rr.parentid = ar.roleid
),
allGroups AS (
    SELECT ar.uid, rg.gid, ar.ancestorIsTopLevel
    FROM allRoles ar
    JOIN rolegroup rg ON rg.roleid = ar.roleid

    UNION ALL

    SELECT ug.uid, ug.gid, 0
    FROM usergroup ug
)
SELECT
  u.uid,
  u.name,
  r.roleid,
  r.rolename,
  NULL AS gid,
  NULL AS groupname
FROM allRoles ar
JOIN users u ON u.uid = ar.uid
JOIN roles r ON r.roleid = ar.roleid
WHERE ar.ancestorIsTopLevel = 0

UNION ALL

SELECT
  u.uid,
  u.name,
  NULL,
  NULL,
  g.gid,
  g.groupname
FROM (
    SELECT
      ag.uid,
      ag.gid
    FROM allGroups ag
    GROUP BY
      ag.uid,
      ag.gid
    HAVING SUM(ag.ancestorIsTopLevel) = 0
) ug
JOIN users u ON u.uid = ug.uid
JOIN groups g ON g.gid = ug.gid
WHERE ug.uid = 1;

db<>fiddle