[SQL] Query waarbij alle elementen matchen

[Admiral Freebee]

Ik zit al een tijdje na te denken hoe ik volgende query het beste kan uitvoeren in T-SQL.

Situatie

Tabel A bevat de naam van rollen met daaraan gekoppeld de entitlements binnen een applicatie. De data is als volgt gestructureerd:

Rol	Entitlement
Beheerder	OpenApplication
Beheerder	DoMagic
Beheerder	DeleteStuff
Gebruiker	OpenApplication
Gebruiker	DoNormalStuff

Tabel B bevat de gebruikers met daaraan gekoppeld de entitlements die men heeft binnen de applicatie.

Gebruiker	Entitlement
Manager1	OpenApplication
Manager1	DoMagic
Manager1	DeleteStuff
User1	OpenApplication
User1	DoNormalStuff

Ik ben op zoek naar het volgend resultaat:

Gebruiker	Rol
Manager1	Beheerder
User1	Gebruiker

Er mag dus enkel een resultaat komen als een Gebruiker alle Entitlements heeft die bij een rol horen. Met mijn kennis kom ik niet verder dan een simpele JOIN en dus foutieve resultaten omdat bepaalde entitlements bij verschillende rollen horen. Ik dacht ook al aan het tellen of ik evenveel matchen had als er bij de rol horen maar dan ben ik de entitlements per rol manueel aan het specifiëren en dat is niet werkbaar.

Bijvoorbeeld:

code:

SELECT B.Gebruiker, A.Rol
FROM A JOIN B
ON A.Entitlement = B.Entitlement
WHERE A.Entitlement IN ('OpenApplication', 'DoNormalStuff')
HAVING COUNT(B.Gebruiker) = 2

Ik zou graag meer inzet kunnen laten zien maar na uren nadenken blijken mijn T-SQL skills gewoon tekort te schieten en de juiste google termen lijk ik ook niet te vinden Kan er iemand mij op het goede pad zertten? Al is het maar met de juiste termen

[ Voor 6% gewijzigd door Admiral Freebee op 14-10-2022 22:28 ]

[MrTinux]

Allereerst, dit is natuurlijk qua model niet heel handig. Logischer zou zijn om per gebruiker een rol toe te kennen (ipv een lijst van entitlements), en zo via de rol de entitlements van een gebruiker te bepalen. Maar dat heb je vast al door, gezien je gewenste uitkomst.

Als je datamodel echter vast is, zou je het kunnen aanvliegen door een query te schrijven die van een rol alle entitlements achter elkaar te plakt (bvb als CSV) en hetzelfde te doen voor de gebruikers en hun entitlements.

Dan krijg je bijvoorbeeld:

Beheerder	DeleteStuff,DoMagic,OpenApplication
Gebruiker	DoNormalStuff,OpenApplication

en

Manager1	DeleteStuff,DoMagic,OpenApplication
User1	DoNormalStuff,OpenApplication

Als je beide in een eigen subquery stopt, kan je deze resultsets op elkaar joinen op de "volledige lijst" van entitlements, en krijg je dus de gebruikers die matchen op een (of meer) rol(len).

spoiler:

Ik heb hier al een werkende variant, het is dus mogelijk

[ Voor 8% gewijzigd door MrTinux op 14-10-2022 17:32 ]

[MSteverink]

In je voorgespiegelde resultaatset spreek je over Gebruiker1, maar die staat niet in je tabellen. Is dit een typefout?

Verder doe ik de laatste tijd meer in Oracle dan in TSQL. Maar kun je een HAVING doen zonder een GROUP BY?

[MrTinux]

MSteverink schreef op vrijdag 14 oktober 2022 @ 17:36:
Verder doe ik de laatste tijd meer in Oracle dan in TSQL. Maar kun je een HAVING doen zonder een GROUP BY?

Een HAVING is een filter op een groep. Heb je geen GROUP BY, dan is er maar één groep: je hele resultset. En is de HAVING dus effectief hetzelfde als een WHERE-regel. Dus ja, het kan. Maar het biedt in dat geval geen meerwaarde ten opzichte van hetzelfde filter opnemen in de WHERE.

[Admiral Freebee]

MrTinux schreef op vrijdag 14 oktober 2022 @ 17:22:
Allereerst, dit is natuurlijk qua model niet heel handig. Logischer zou zijn om per gebruiker een rol toe te kennen (ipv een lijst van entitlements), en zo via de rol de entitlements van een gebruiker te bepalen. Maar dat heb je vast al door, gezien je gewenste uitkomst.

Als je datamodel echter vast is, zou je het kunnen aanvliegen door een query te schrijven die van een rol alle entitlements achter elkaar te plakt (bvb als CSV) en hetzelfde te doen voor de gebruikers en hun entitlements.

Dan krijg je bijvoorbeeld:

Beheerder	DeleteStuff,DoMagic,OpenApplication
Gebruiker	DoNormalStuff,OpenApplication

en

Manager1	DeleteStuff,DoMagic,OpenApplication
User1	DoNormalStuff,OpenApplication

Als je beide in een eigen subquery stopt, kan je deze resultsets op elkaar joinen op de "volledige lijst" van entitlements, en krijg je dus de gebruikers die matchen op een (of meer) rol(len).

spoiler:

Ik heb hier al een werkende variant, het is dus mogelijk

Het model is niet handig maar ik ben gebonden aan wat er in deze applicatie zit. Tabel A bevat de link tussen AD groepen en entitlements en Tabel B de link tussen accounts en entitlements. Niet alle rollen komen uit AD overigens.

Tijdens het eten dacht ik al aan een approach gelijkaardig aan wat je voorstelt. Grootste issue kan zijn dat gebruikers extra entitlements kunnen hebben door een combinatie van meerdere rollen en/of entitlements hebben vanuit de applicatie zelf. Een echt zootje dus. Ik zou wel wat kunnen knoeien om proberen een match te vinden maar echt handig gaat dit nog niet worden.

[Admiral Freebee]

MSteverink schreef op vrijdag 14 oktober 2022 @ 17:36:
In je voorgespiegelde resultaatset spreek je over Gebruiker1, maar die staat niet in je tabellen. Is dit een typefout?

Verder doe ik de laatste tijd meer in Oracle dan in TSQL. Maar kun je een HAVING doen zonder een GROUP BY?

Ja, foutje in mijn post ik ga het aanpassen. In de query zit inderdaad ook nog een foutje. Misschien is een post maken op vrijdagmiddag geen goed idee

[questo]

Een gebruiker heeft dus een rol als er voor alle entitlements voor die rol een record bestaat van de gebruiker met diezelfde entitlement. Een paar van gebruiker en rol moet gerapporteerd worden als er geen enkele entitlement bij die rol bestaat die de gebruiker niet heeft.

code:

WITH AxB AS 
(SELECT DISTINCT A.Rol, B.Gebruiker
FROM A, B)

SELECT AxB.Rol, AxB.Gebruiker
FROM AxB
WHERE NOT EXISTS (
  SELECT 1 
  FROM A 
  WHERE A.Rol=AxB.Rol 
  AND NOT EXISTS (
    SELECT 1 
    FROM B 
    WHERE A.Entitlement=B.Entitlement 
    AND AxB.Gebruiker=B.Gebruiker
  )
)

[Harrie_]

MrTinux schreef op vrijdag 14 oktober 2022 @ 17:22:
Allereerst, dit is natuurlijk qua model niet heel handig. Logischer zou zijn om per gebruiker een rol toe te kennen (ipv een lijst van entitlements), en zo via de rol de entitlements van een gebruiker te bepalen. Maar dat heb je vast al door, gezien je gewenste uitkomst.

Als je datamodel echter vast is, zou je het kunnen aanvliegen door een query te schrijven die van een rol alle entitlements achter elkaar te plakt (bvb als CSV) en hetzelfde te doen voor de gebruikers en hun entitlements.

Dan krijg je bijvoorbeeld:

Beheerder	DeleteStuff,DoMagic,OpenApplication
Gebruiker	DoNormalStuff,OpenApplication

en

Manager1	DeleteStuff,DoMagic,OpenApplication
User1	DoNormalStuff,OpenApplication

Als je beide in een eigen subquery stopt, kan je deze resultsets op elkaar joinen op de "volledige lijst" van entitlements, en krijg je dus de gebruikers die matchen op een (of meer) rol(len).

spoiler:

Ik heb hier al een werkende variant, het is dus mogelijk

Zonder de exacte usecase te kennen is het voor mij natuurlijk ook maar gissen maar ik zie toch een paar mogelijke problemen met deze oplossing. Grote vraag is ook of het altijd een 1 to 1 relatie is, want TS zeg in zijn OP:

Admiral Freebee schreef op vrijdag 14 oktober 2022 @ 16:43:
Er mag dus enkel een resultaat komen als een Gebruiker alle Entitlements heeft die bij een rol horen.

Ik maak hieruit op dat een gebruiker een aantal entitlements kan hebben wat niet per definitie tot een rol leidt. Wat nu als de gebruiker nog aanvullende entitlements heeft (kan dat dan ook?) die zich niet beperken tot enkel de entitlements van één enkele rol. Stel dat er ook nog een Manager2 rondloopt die naast de 'normale' beheerder-rechten ook nog rechten heeft voor DoNormalStuff? Dan gaat deze concat-methode fout.

En is het mogelijk dat een gebruiker meerdere rollen heeft? Hoe daarmee om te gaan? Zit er een factor van anciënniteit in waaruit duidelijk wordt welke rol 'hoger' is?

[Admiral Freebee]

questo schreef op dinsdag 18 oktober 2022 @ 15:45:
Een gebruiker heeft dus een rol als er voor alle entitlements voor die rol een record bestaat van de gebruiker met diezelfde entitlement. Een paar van gebruiker en rol moet gerapporteerd worden als er geen enkele entitlement bij die rol bestaat die de gebruiker niet heeft.

code:

WITH AxB AS 
(SELECT DISTINCT A.Rol, B.Gebruiker
FROM A, B)

SELECT AxB.Rol, AxB.Gebruiker
FROM AxB
WHERE NOT EXISTS (
  SELECT 1 
  FROM A 
  WHERE A.Rol=AxB.Rol 
  AND NOT EXISTS (
    SELECT 1 
    FROM B 
    WHERE A.Entitlement=B.Entitlement 
    AND AxB.Gebruiker=B.Gebruiker
  )
)

Ik heb moeite met deze query te begrijpen. Wanneer ik hem domweg uitvoer op mijn data krijg ik als resultaat alle rollen die exact één entitlement bevatten en dan alle gebruikers die (onder andere) die ene entitlement hebben.

Kan je mij uitleggen wat de verschillende delen doen met de data? Ik zie dat we eerst unieke paren van rollen en gebruikers oplijsten maar ik heb moeite om het verder te begrijpen

Harrie_ schreef op dinsdag 18 oktober 2022 @ 20:12:
[...]


Zonder de exacte usecase te kennen is het voor mij natuurlijk ook maar gissen maar ik zie toch een paar mogelijke problemen met deze oplossing. Grote vraag is ook of het altijd een 1 to 1 relatie is, want TS zeg in zijn OP:


[...]


Ik maak hieruit op dat een gebruiker een aantal entitlements kan hebben wat niet per definitie tot een rol leidt. Wat nu als de gebruiker nog aanvullende entitlements heeft (kan dat dan ook?) die zich niet beperken tot enkel de entitlements van één enkele rol. Stel dat er ook nog een Manager2 rondloopt die naast de 'normale' beheerder-rechten ook nog rechten heeft voor DoNormalStuff? Dan gaat deze concat-methode fout.

En is het mogelijk dat een gebruiker meerdere rollen heeft? Hoe daarmee om te gaan? Zit er een factor van anciënniteit in waaruit duidelijk wordt welke rol 'hoger' is?

Klopt. Een gebruiker kan in principe meerdere rollen hebben en/of extra entitlements hebben bovenop zijn gebruikelijke rol. Voor het resultaat is dit op zich geen issue. Van zodra iemand zijn entitlements overeenkomen met alle entitlements die aan een rol hangen dan mag die persoon bij die rol geschaard worden. Een persoon mag dus ook meerdere keren in het resultaat voorkomen.

[Harrie_]

Admiral Freebee schreef op woensdag 19 oktober 2022 @ 10:10:
[...]
Klopt. Een gebruiker kan in principe meerdere rollen hebben en/of extra entitlements hebben bovenop zijn gebruikelijke rol. Voor het resultaat is dit op zich geen issue. Van zodra iemand zijn entitlements overeenkomen met alle entitlements die aan een rol hangen dan mag die persoon bij die rol geschaard worden. Een persoon mag dus ook meerdere keren in het resultaat voorkomen.

Nou dat is voor het resultaat op zich wel een issue, want dit maakt het natuurlijk wel allemaal nodeloos gecompliceerd. Ik neem aan dat je in de OP pseudo-tabellen hebt gezet, zitten er nog overige columns in je tabellen waar we iets mee kunnen? Sowieso zou ik adviseren om dit (als het nog gaat met de hoeveelheid data) anders op te zetten...

[Mortis__Rigor]

De eerste vraag die ik mij in deze situaties altijd stel is: waarom wil je dit via SQL gaan oplossen? Is deze complexiteit niet veel eenvoudiger op te lossen in de backend?

[Harrie_]

Ik heb me er toch even aan gewaagd met een nogal 'vuile' oplossing. Om te testen heb ik de gebruikerlijst aangepast en een extra user toegevoegd waarvan de entitlements overeenkomen met de rol voor Beheerder en voor Gebruiker. Als volgt:

Gebruiker	Entitlement
Manager1	OpenApplication
Manager1	DoMagic
Manager1	DeleteStuff
User1	OpenApplication
User1	DoNormalStuff
SuperUser1	DoNormalStuff
SuperUser1	DoMagicStuff
SuperUser1	DeleteStuff
SuperUser1	OpenApplication

Dan de vuile query met een hoop inner selects.

Wat deze doet is een outer join op de entitlements van een gebruiker. Voor iedere entitlement worden er dus de rollen bijgezocht die deze entitlement 'bezitten'. Vervolgens joinen we daar een tabel tegenaan met alle rollen + een count van entitlements.

Als laatste stap doen we een count van rollen die tegen de entitlements van gebruikers zijn gejoind en die count vergelijken we met de count van entitlements per rol.

Platgeslagen maken we dus een vergelijk: Deze gebruiker heeft 3 entitlements die passen bij de rol Beheerder en de rol Beheerder heeft in totaal 3 entitlements -> dan moet deze gebruiker wel Beheerder zijn.

SQL:

SELECT
      Gebruiker
    , Rol
FROM (
    SELECT 
          B.Gebruiker
        , B.Rol
        , COUNT(B.Rol) AS AantalBijGebruiker
        , C.Aantal AS AantalInRol
    FROM (
        SELECT
              B.Gebruiker
            , A.Rol
        FROM dbo.B
        LEFT OUTER JOIN dbo.A ON B.Entitlement = A.Entitlement
    ) AS B
    LEFT JOIN (
        SELECT
              Rol
            , COUNT(Entitlement) AS Aantal
        FROM dbo.A
        GROUP BY Rol
    ) AS C ON B.Rol = C.Rol
    GROUP BY B.Gebruiker, B.Rol, C.Aantal
) AS result
WHERE AantalBijGebruiker = AantalInRol

En dat geeft dit als resultaat:

Gebruiker	Rol
Manager1	Beheerder
SuperUser1	Beheerder
SuperUser1	Gebruiker
User1	Gebruiker

Ik hoop dat je hier iets verder mee komt, maar ik adviseer nog steeds om eens na te denken of je de structuur van je tabellen niet wat om kunt gooien.

[Admiral Freebee]

@Harrie_ Spijtig genoeg heb ik geen enkele invloed op de manier waarop de tabellen opgebouwd zijn. Het gaat om een extern product waarbij we geen wijzigingen kunnen aanbrengen. Ik ga eens naar jouw oplossing kijken en zien of ik daar verder mee kom.

@Mortis__Rigor Eigenlijk wil ik dit niet oplossen maar het komt voort uit een vraag om een vergelijking te maken tussen rollen in de applicatie en beheerd via access management. Door de aard van de applicatie zitten er verschillen op. Er zijn behoorlijk wat gebruikers die de applicatie gebruiken en ik wil er dus geen manuele actie van maken. Als er geen SQL oplossing is dan gooi ik wel gewoon de lijst met entitlements naar het andere team en lossen ze het maar op. Normaal los ik wel eens graag een SQL puzzel op maar op deze bijt ik mijn tanden stuk.

[Harrie_]

Admiral Freebee schreef op woensdag 19 oktober 2022 @ 10:50:
@Harrie_ Spijtig genoeg heb ik geen enkele invloed op de manier waarop de tabellen opgebouwd zijn. Het gaat om een extern product waarbij we geen wijzigingen kunnen aanbrengen. Ik ga eens naar jouw oplossing kijken en zien of ik daar verder mee kom.

Prima. Selecteer ook vooral in SSMS de afzonderljike inner queries met een SELECT * om te zien wat er uit de tussenstappen komt gerold.

[Janoz]

Admiral Freebee schreef op woensdag 19 oktober 2022 @ 10:50:
@Mortis__Rigor Eigenlijk wil ik dit niet oplossen maar het komt voort uit een vraag om een vergelijking te maken tussen rollen in de applicatie en beheerd via access management. Door de aard van de applicatie zitten er verschillen op. Er zijn behoorlijk wat gebruikers die de applicatie gebruiken en ik wil er dus geen manuele actie van maken. Als er geen SQL oplossing is dan gooi ik wel gewoon de lijst met entitlements naar het andere team en lossen ze het maar op. Normaal los ik wel eens graag een SQL puzzel op maar op deze bijt ik mijn tanden stuk.

Het punt van @Mortis__Rigor gaat niet perse over waar de vraag vandaan komt, maar dat de combinatie van datamodel en gevraagde data nu eenmaal heel slecht op te lossen is met een set gebaseerde aanpak. SQL is voornamelijk set gebaseerd. Zou je dit imperatief aanpakken, met bijvoorbeeld een simpel python of powershell scriptje, dan ben je zo klaar.

[KabouterSuper]

Kan je niet per rol een count doen van het aantal entitlements en die intersecten met een count van de inner join op de entitlements/rollen van die gebruiker?

code:

select rol, count(distinct entitlement) cc
from A
group by rol
intersect
select rol, count(distinct entitlement) cc
from A,B
where A.entitlement=B.entitlement and B.user='Manager'
group by rol

Edit: pseudocode toegevoegd

[ Voor 44% gewijzigd door KabouterSuper op 19-10-2022 12:14 ]

[Admiral Freebee]

Harrie_ schreef op woensdag 19 oktober 2022 @ 10:46:
Ik heb me er toch even aan gewaagd met een nogal 'vuile' oplossing. Om te testen heb ik de gebruikerlijst aangepast en een extra user toegevoegd waarvan de entitlements overeenkomen met de rol voor Beheerder en voor Gebruiker. Als volgt:

Gebruiker	Entitlement
Manager1	OpenApplication
Manager1	DoMagic
Manager1	DeleteStuff
User1	OpenApplication
User1	DoNormalStuff
SuperUser1	DoNormalStuff
SuperUser1	DoMagicStuff
SuperUser1	DeleteStuff
SuperUser1	OpenApplication

Dan de vuile query met een hoop inner selects.

Wat deze doet is een outer join op de entitlements van een gebruiker. Voor iedere entitlement worden er dus de rollen bijgezocht die deze entitlement 'bezitten'. Vervolgens joinen we daar een tabel tegenaan met alle rollen + een count van entitlements.

Als laatste stap doen we een count van rollen die tegen de entitlements van gebruikers zijn gejoind en die count vergelijken we met de count van entitlements per rol.

Platgeslagen maken we dus een vergelijk: Deze gebruiker heeft 3 entitlements die passen bij de rol Beheerder en de rol Beheerder heeft in totaal 3 entitlements -> dan moet deze gebruiker wel Beheerder zijn.

SQL:

SELECT
      Gebruiker
    , Rol
FROM (
    SELECT 
          B.Gebruiker
        , B.Rol
        , COUNT(B.Rol) AS AantalBijGebruiker
        , C.Aantal AS AantalInRol
    FROM (
        SELECT
              B.Gebruiker
            , A.Rol
        FROM dbo.B
        LEFT OUTER JOIN dbo.A ON B.Entitlement = A.Entitlement
    ) AS B
    LEFT JOIN (
        SELECT
              Rol
            , COUNT(Entitlement) AS Aantal
        FROM dbo.A
        GROUP BY Rol
    ) AS C ON B.Rol = C.Rol
    GROUP BY B.Gebruiker, B.Rol, C.Aantal
) AS result
WHERE AantalBijGebruiker = AantalInRol

En dat geeft dit als resultaat:

Gebruiker	Rol
Manager1	Beheerder
SuperUser1	Beheerder
SuperUser1	Gebruiker
User1	Gebruiker

Ik hoop dat je hier iets verder mee komt, maar ik adviseer nog steeds om eens na te denken of je de structuur van je tabellen niet wat om kunt gooien.

Dit lijkt te werken en ik snap de logica ook Eerst kreeg ik weer enkel foutieve resultaten maar dit kwam omdat er een kolom in de tabel zit die bepaalde technische entitlements erbij sleurde en zo het resultaat verpestte.

Ik ga het eens vergelijken met de query van @questo want daar had ik last van dezelfde kolom.

[Admiral Freebee]

KabouterSuper schreef op woensdag 19 oktober 2022 @ 12:11:
Kan je niet per rol een count doen van het aantal entitlements en die intersecten met een count van de inner join op de entitlements/rollen van die gebruiker?

code:

select rol, count(distinct entitlement) cc
from A
group by rol
intersect
select rol, count(distinct entitlement) cc
from A,B
where A.entitlement=B.entitlement and B.user='Manager'
group by rol

Edit: pseudocode toegevoegd

Kan ook maar aangezien ik nogal veel rollen heb is het onhandig om voor elke rol aparte clausules te gaan toevoegen. Het zorgt er ook voor dat als de vraag later terugkomt ik weer moet gaan kijken of er rollen zijn toegevoegd.

[Admiral Freebee]

Janoz schreef op woensdag 19 oktober 2022 @ 11:42:
[...]


Het punt van @Mortis__Rigor gaat niet perse over waar de vraag vandaan komt, maar dat de combinatie van datamodel en gevraagde data nu eenmaal heel slecht op te lossen is met een set gebaseerde aanpak. SQL is voornamelijk set gebaseerd. Zou je dit imperatief aanpakken, met bijvoorbeeld een simpel python of powershell scriptje, dan ben je zo klaar.

Ah, daar knelt het schoentje. Ik ben niet gehinderd door enige programmeerkennis en hoewel Python en Powershell op mijn todo lijstje staan ben ik er nog niet aan toegekomen. In principe valt deze vraag ook buiten mijn jobomschrijving maar ik neem het erbij om het team te ontlasten én omdat ik wel de functionele kennis heb.

[KabouterSuper]

Admiral Freebee schreef op woensdag 19 oktober 2022 @ 15:09:
[...]

Kan ook maar aangezien ik nogal veel rollen heb is het onhandig om voor elke rol aparte clausules te gaan toevoegen. Het zorgt er ook voor dat als de vraag later terugkomt ik weer moet gaan kijken of er rollen zijn toegevoegd.

Waar zie jij aparte clausules voor elke rol? Mijn voorbeeld-query heeft alleen een filter op user (die er overigens gemakkelijk uit te werken is).

[Admiral Freebee]

KabouterSuper schreef op woensdag 19 oktober 2022 @ 16:10:
[...]

Waar zie jij aparte clausules voor elke rol? Mijn voorbeeld-query heeft alleen een filter op user (die er overigens gemakkelijk uit te werken is).

Oeps wat snel gelezen. Ik ga er morgen nog mee aan de slag. Ik heb vandaag nog geen tijd gehad om de resultaten te valideren.

[mbe81]

Is dit niet op te lossen door te tellen hoeveel entitlements per gebruiker per rol overeenkomen en dit te vergelijken met het aantal entitlements van de rol? Als dit aantal gelijk is dan zijn minimaal alle entitlements voor een bepaalde rol ook toegekend aan de gebruiker.

Edit: Ik zie dat @KabouterSuper iets soortgelijks doet.

code:

select tb1.gebruiker, ta1.rol 
  from table_a ta1 
  join table_b tb1
    on ta1.entitlement = tb1.entitlement 
 group by tb1.gebruiker, ta1.rol 
having count(1) = (select count(1) 
                     from taba ta2 
                    where ta2.rol = ta1.rol)

[ Voor 5% gewijzigd door mbe81 op 20-10-2022 00:37 ]

[Maniakje]

Ik vond dit ook wel een leuke puzzel, en kwam tot de volgende oplossing:

Je zoekt:

• Alle rollen waarvoor de gebruiker tenminste één entitlement heeft,
• ...en voor die rol géén andere entitlement bestaat
• ...die de gebruiker niet heeft

code:

-- Alle rollen waarvoor de gebruiker tenminste één entitlement heeft
SELECT DISTINCT B.gebruiker, a.rol
FROM A
JOIN B ON A.entitlement = B.entitlement

WHERE NOT EXISTS(
        -- en voor die rol géén andere entitlement bestaat        
        SELECT 1
        FROM A AS another
        WHERE another.rol = a.rol
        AND another.entitlement <> a.entitlement

        -- die de gebruiker niet heeft
        AND NOT EXISTS(
              SELECT 1
              FROM B as B2
              WHERE B2.entitlement = another.entitlement
              AND B2.gebruiker = b.gebruiker
          )
    )

[Admiral Freebee]

Een laat antwoord en ik ben met jullie suggesties aan de slag gegaan. Met alle antwoorden samen en dan wat gepruts om de edge cases er in te krijgen ben ik tot een werkend resultaat gekomen. Het is niet zoals ik het wil want er zitten alsnog hardcoded values in maar het is een oerwoud van overlappende entitlements en entitlements die net worden uitgesloten bij het toekennen van een bepaalde rol, enzvoort

Ik heb het antwoord van @Maniakje als beste antwoord geselecteerd omdat voor één of andere reden zijn in line comments de doorslag gaven om mijn hoofd mee te laten zijn

Bedankt voor jullie hulp!