I'm trying to get users that are part of groups, but groups can be members of groups as well. So the question is about who has "effective" access to certain groups. I've seen many examples of using CTE on SQL Server and this seems like the same case, but all the examples are pretty simple about a single table like an org chart. In my case, I have to join multiple tables and just can't grasp the whole recursion thing.
The tables are:
usergroup: what users have which groups
uid int
gid int
groupgroup: which groups give which groups
parentid int
childid int
group: many groups are in a single sourceid.
gid int
sourceid int
name varchar
For example:
usergroup
uid gid
------------
1 101
1 102
2 102
2 120
groupgroup
parentid childid
-------------------
103 101
110 103
120 110
group
gid sourceid name
-----------------------
101 5 group101
102 5 group102
103 5 group103
110 8 group110
120 8 group120
Where the difference between parentid and childid is that parentid GRANTS childid. So if you are a member of parentid , you have effective access to childid. As expected, parentid and childid WILL EXIST in the group table.
My goal is to simply get a list of all users that have access to which groups in a given source (sourceid). Without the nesting, it would be simple for sourceid of 5:
select *
from usergroup
inner join group on group.gid = usergroup.gid
where group.sourceid = 5
And this query would give me that user 1 has groups 101, 102, and user 2 has 102.
But the issue is that a user could have a parent gid that gives a childid via any level of nesting, and those childids can be a gid in the sourceid of 5.
I would instead hope to get that user 1 still has 101 and 102 directly, and that user 2 has 102 directly AND 101 and 103 because of nesting.
At a basic level, I just need to know users XYS have groups ABC in the source either directly OR via nesting and a flag or other marker if it's nesting vs direct. If we could get something like a level (0 = direct, 1 = one level up, etc...) that's all the better.
I can do this in Java code by making a big map/list and looping, but is there an elegant way to get it from SQL Server directly and perhaps faster than in code?
Thank you!
A recursive CTE consists of an anchor query followed by a UNION ALL to one or more recursive queries. (Typically, there is only one recursive part, but in some scenarios, such as binary trees, there may be more than one.)
For your case, the anchor will be a select of all of the direct group memberships from the UserGroup table. This part is just a normal query that seeds the reciursive CTE with an initial set of rows.
The recursive part of the query expands on the existing rows with an additional query (or queries) that joins the existing rows with whatever is needed to recursively expand the results. In your case, it will join the CTE with the GroupGroup table to walk the chain of and include any parent groups.
For each row in the seed set, the recursive query might return zero, one or many additional rows. These results are both added to the combined result set and fed back into the recursion, possibly yielding even more results. There must be a limit though. BY default, SQL Server allows 100 levels or recursion, but this can be overridden with the MAXRECURSION option.
with ExpandedUserGroups as (
-- Anchor part
select ug.uid, ug.gid
from UserGroup ug
union all
-- Recursive part
select xug.uid, gg.parentid
from ExpandedUserGroups xug
join GroupGroup gg
on gg.childid = xug.gid
)
select xug.uid, xug.gid, g.sourceid, g.name
from ExpandedUserGroups xug
join [Group] g
on g.gid = xug.gid
order by xug.uid, xug.gid;
Results
| uid | gid | sourceid | name |
|---|---|---|---|
| 1 | 101 | 5 | group101 |
| 1 | 102 | 5 | group102 |
| 1 | 103 | 5 | group103 |
| 1 | 110 | 8 | group110 |
| 1 | 120 | 8 | group120 |
| 2 | 102 | 5 | group102 |
| 2 | 120 | 8 | group120 |
See this db<>fiddle for a demo.