SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[groups](
	[gid] [int] NOT NULL,
	[groupname] [varchar](50) NOT NULL
) ON [PRIMARY]

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


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


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


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


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

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

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 [groups] values (4,'group 4')
insert into [groups] values (10,'group 10')
insert into [groups] values (20,'group 20')


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 [roles] values (31,'Role X',0)
insert into [roles] values (32,'Role Y',0)
insert into [roles] values (33,'Role Z',0)

insert into [roles] values (21,'Role AAA',0)
insert into [roles] values (22,'Role BBB',0)
insert into [roles] values (23,'Role CCC',0)

insert into rolerole values (1,2)
insert into rolerole values (1,21)
insert into rolerole values (21,22)
insert into rolerole values (22,23)

insert into rolerole values (3,31)
insert into rolerole values (31,32)
insert into rolerole values (32,33)


-- select * from rolegroup
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 rolegroup values (3,4)
insert into rolegroup values (23,10)
insert into rolegroup values (33,10)
insert into rolegroup values (33,20)


-- select * from userrole

insert into userrole values (1,1)
insert into userrole values (1,3)
insert into userrole values (2,1)
insert into userrole values (3,2)

-- select * from usergroup
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


gid
groupname
uid
gid
uid
name


1
group 1
1
1
1
alice

2
group 2
1
2
1
alice

3
group 3
1
3
1
alice




uid
name
uid
roleid
roleid
rolename
toplevel
roleid
gid
gid
groupname


1
alice
1
1
1
Role A
True
null
null
null
null

1
alice
1
3
3
Role C
False
3
1
1
group 1

1
alice
1
3
3
Role C
False
3
2
2
group 2

1
alice
1
3
3
Role C
False
3
4
4
group 4


-- Create a view that will be usable:
-- - for maintenance on roles and groups (detecting duplicates, infinite loops, and so on)
-- - for individual user checking (does it have groups redundant with the ones from its roles?)
-- - for mass users reporting
CREATE VIEW rolehierarchy AS
WITH
  -- rolerole is the only recursive part, do it right now.
  rr AS
  (
    SELECT parentid as roleid, childid as subroleid, CAST(parentid AS VARCHAR(99)) breadcrumb, roles.toplevel
    FROM rolerole
	inner join roles on roles.roleid = rolerole.parentid
    UNION ALL -- In addition to its recursive role, UNION ALL (not UNION) will make sure that duplicates are preserved: we'll be able to use this recursive CTE to detect them.
    SELECT roleid, childid, LEFT(CONCAT(breadcrumb, '>', parentid), 99), toplevel
    FROM rr JOIN rolerole ON parentid = subroleid
  )
SELECT rr.roleid, 'r' type, subroleid subroleid, null as groupid, breadcrumb,toplevel FROM rr
UNION ALL
SELECT rr.roleid, 'g', null, gid, CONCAT(breadcrumb, '>', subroleid),toplevel
FROM rr JOIN rolegroup rg ON rg.roleid = rr.subroleid
UNION ALL
SELECT rg.roleid, 'g', null, gid, CAST(rg.roleid AS VARCHAR(99)),roles.toplevel
FROM rolegroup rg
left join roles on roles.roleid = rg.roleid
-- Special case: toplevel are part of a hierarchy (contrary to isolated roles);
-- However toplevel can be computed, so compute it with a NOT EXISTS.
UNION ALL
SELECT parentid, 'r', parentid, NULL, NULL, r2.toplevel
FROM rolerole p 
inner join roles r2 on r2.roleid = p.parentid
WHERE NOT EXISTS (select 1 from rolerole c WHERE c.childid = p.parentid)
;
select * from rolehierarchy
order by roleid, subroleid, groupid, breadcrumb


roleid
type
subroleid
groupid
breadcrumb
toplevel


1
g
null
1
1>2
True

1
g
null
2
1>2
True

1
g
null
10
1>21>22>23
True

1
r
1
null
null
True

1
r
1
null
null
True

1
r
2
null
1
True

1
r
21
null
1
True

1
r
22
null
1>21
True

1
r
23
null
1>21>22
True

2
g
null
1
2
False

2
g
null
2
2
False

3
g
null
1
3
False

3
g
null
2
3
False

3
g
null
4
3
False

3
g
null
10
3>31>32>33
False

3
g
null
20
3>31>32>33
False

3
r
3
null
null
False

3
r
31
null
3
False

3
r
32
null
3>31
False

3
r
33
null
3>31>32
False

21
g
null
10
21>22>23
False

21
r
22
null
21
False

21
r
23
null
21>22
False

22
g
null
10
22>23
False

22
r
23
null
22
False

23
g
null
10
23
False

31
g
null
10
31>32>33
False

31
g
null
20
31>32>33
False

31
r
32
null
31
False

31
r
33
null
31>32
False

32
g
null
10
32>33
False

32
g
null
20
32>33
False

32
r
33
null
32
False

33
g
null
10
33
False

33
g
null
20
33
False


-- User-focused use of the view to generate a full access summary:
WITH
  u AS (SELECT 1 uid), -- Inspect Alice
  urg AS -- User's Roles and Groups
  (
    -- From the roles and groups hierarchy:
    SELECT ur.uid, rh.type, rh.subroleid, rh.groupid, 1 h, rh.breadcrumb, rh.toplevel FROM u JOIN userrole ur ON ur.uid = u.uid JOIN rolehierarchy rh ON rh.roleid = ur.roleid
    WHERE breadcrumb IS NOT NULL -- A NULL breadcrumb denotates a fictive entry in hierarchy for the toplevel: we'll find it with "From direct roles association" (see below) too, so exclude it from the hierarchy source.
    UNION ALL
    -- From direct groups association:
    SELECT ug.uid, 'g', NULL, ug.gid, NULL, NULL, NULL FROM u JOIN usergroup ug ON ug.uid = u.uid
    UNION ALL
    -- From direct roles association:
    SELECT ur.uid, 'r', ur.roleid, NULL, NULL, NULL, roles.toplevel FROM u JOIN userrole ur ON ur.uid = u.uid LEFT JOIN roles on roles.roleid = ur.roleid
  )
-- List every role and group, with the roles hierarchy that allowed to reach it:
--SELECT * FROM urg
--ORDER BY uid, type DESC, rorgid, LEN(breadcrumb) DESC, breadcrumb;
-- List roles and groups only once each, with a count of ways to reach them:
SELECT
  uid, type, subroleid, groupid, 
  ISNULL(MAX(CAST(toplevel AS INT)),0) AS toplevel,
  COUNT(h) AS reached_through_hierarchy,
  COUNT(CASE WHEN h IS NULL THEN 1 END) reached_directly
FROM urg
GROUP BY uid, type, subroleid, groupid
ORDER BY uid, toplevel, type, subroleid, groupid


uid
type
subroleid
groupid
toplevel
reached_through_hierarchy
reached_directly


1
g
null
3
0
0
1

1
g
null
4
0
1
0

1
g
null
20
0
1
0

1
r
3
null
0
0
1

1
r
31
null
0
1
0

1
r
32
null
0
1
0

1
r
33
null
0
1
0

1
g
null
1
1
2
1

1
g
null
2
1
2
1

1
g
null
10
1
2
0

1
r
1
null
1
0
1

1
r
2
null
1
1
0

1
r
21
null
1
1
0

1
r
22
null
1
1
0

1
r
23
null
1
1
0


Warning: Null value is eliminated by an aggregate or other SET operation.


-- Question: how to list directly attached roles or groups that do not appear through the roles hierarchy?
WITH
  u AS (SELECT 1 uid), -- Inspect Alice
  urg AS -- User's Roles and Groups
  (
    -- From the roles and groups hierarchy:
    SELECT ur.uid, rh.type, rh.subroleid, rh.groupid, 1 h, rh.breadcrumb, rh.toplevel FROM u JOIN userrole ur ON ur.uid = u.uid JOIN rolehierarchy rh ON rh.roleid = ur.roleid
    WHERE breadcrumb IS NOT NULL -- A NULL breadcrumb denotates a fictive entry in hierarchy for the toplevel: we'll find it with "From direct roles association" (see below) too, so exclude it from the hierarchy source.
    UNION ALL
    -- From direct groups association:
    SELECT ug.uid, 'g', NULL, ug.gid, NULL, NULL, NULL FROM u JOIN usergroup ug ON ug.uid = u.uid
    UNION ALL
    -- From direct roles association:
    SELECT ur.uid, 'r', ur.roleid, NULL, NULL, NULL, roles.toplevel FROM u JOIN userrole ur ON ur.uid = u.uid LEFT JOIN roles on roles.roleid = ur.roleid
  )
-- List every role and group, with the roles hierarchy that allowed to reach it:
--SELECT * FROM urg
--ORDER BY uid, type DESC, rorgid, LEN(breadcrumb) DESC, breadcrumb;
-- List roles and groups only once each, with a count of ways to reach them:
SELECT
  uid, type, subroleid, groupid, 
  ISNULL(MAX(CAST(toplevel AS INT)),0) AS toplevel,
  COUNT(h) AS reached_through_hierarchy,
  COUNT(CASE WHEN h IS NULL THEN 1 END) reached_directly
FROM urg

GROUP BY uid, type, subroleid, groupid
-- but this does not let me do it.
--  HAVING toplevel = 0
ORDER BY uid, toplevel, type, subroleid, groupid


uid
type
subroleid
groupid
toplevel
reached_through_hierarchy
reached_directly


1
g
null
3
0
0
1

1
g
null
4
0
1
0

1
g
null
20
0
1
0

1
r
3
null
0
0
1

1
r
31
null
0
1
0

1
r
32
null
0
1
0

1
r
33
null
0
1
0

1
g
null
1
1
2
1

1
g
null
2
1
2
1

1
g
null
10
1
2
0

1
r
1
null
1
0
1

1
r
2
null
1
1
0

1
r
21
null
1
1
0

1
r
22
null
1
1
0

1
r
23
null
1
1
0


Warning: Null value is eliminated by an aggregate or other SET operation.
uid	name	uid	roleid	roleid	rolename	toplevel	roleid	gid	gid	groupname
1	alice	1	1	1	Role A	True	null	null	null	null
1	alice	1	3	3	Role C	False	3	1	1	group 1
1	alice	1	3	3	Role C	False	3	2	2	group 2
1	alice	1	3	3	Role C	False	3	4	4	group 4