I have these tables: USER, GROUP, MEMBER AND GRANTED_PRIV.

A USER can be a MEMBER of a(many) GROUP(s).
A GROUP can be a MEMBER of a(many) GROUP(s).
USERs and GROUPs can have GRANTED_PRIV.

IN A SELECT STATEMENT (no T-SQL), if I want to retrive a USER GRANTED_PRIV along with all the GRANTED_PRIV granted to a GROUP in which the USER is a MEMBER and... along with all the GRANTED_PRIV granted to all GROUP member of a GROUP granted to the USER and so on......

I could (this is not a valid syntax, just to explain my point):


SELECT GRANTED_PRIV.*
FROM GRANTED_PRIV GP
WHERE GP.USER = my_user

UNION

SELECT GRANTED_PRIV.*
FROM GRANTED_PRIV GP,
MEMBER M
WHERE M.USER = my_user
AND GP.USER = M.GROUP

UNION

SELECT GRANTED_PRIV.*
FROM GRANTED_PRIV GP,
MEMBER M1,
MEMBER M2
WHERE M2.USER = my_user
AND M2.GROUP = m1.USER
AND GP.USER = M1.GROUP

UNION

SELECT GRANTED_PRIV.*
FROM GRANTED_PRIV GP,
MEMBER M1,
MEMBER M2,
MEMBER M3
WHERE M3.USER = my_user
AND M3.GROUP = m2.USER
AND M2.GROUP = m1.USER
AND GP.USER = M1.GROUP


UNION.... you get the picture... but this is limited (in this example) to 3 level of nesting...


DO you have a 'magic' ;-) statement that whould'nt be limited to a fixed level of nesting and also be performant...

Thanks, have a good one!