General information
Forum:
Microsoft SQL Server
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!
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only