Hi,
i have a report "Permissions on a folder", which prints the permissions of a whole folderstructure:
select dtree.name as "Folder name",
/* rightid=-1 means Public access */
decode (da.rightid,
-1, 'PUBLIC ACCESS',
decode(k.type, 0,
'User: ' || k.name, 1, 'Group: ' || k.name, k.name)
) as "Groups/User",
k.id "kuaf.ID"
from dtree
inner join dtreeacl da on dtree.dataid = da.dataid
left join kuaf k on k.id=da.rightid
where dtree.subtype=0 /* only folder */ and
da.rightid <> -2 and
da.dataid in
(
select DataID from DTree
connect by prior DataID=ParentID
start with DataID in
(
( select -DataID from DTree
connect by prior DataID=ParentID
start with DataID=XXXXX)
union
( select DataID from DTree
connect by prior DataID=ParentID
start with DataID=****)
)
)
order by 1,2
Works fine and print the users and also the groups in the permissions
My problem is now, that I want not only the entered users and the name of entered group , but also the users INSIDE the group, because I have to check afterwards, if there are from special countries (I do this via kuaf.officelocation)
But: Of course the group can contain another group and this again a group etc. , that makes it more difficult.
Its not necessary, that I know the group names (via that are entered), the person name (kuaf.name) and later the kuaf.officelocation is sufficient for me. :-)
I have an SQL statement to list all users in a group (rekursive)
select k.name
from kuaf k
left join kuafchildren kc on k.id=kc.childid
START WITH kc.ID =XY /* XY=ID of a group */
CONNECT BY kc.ID = PRIOR kc.childid
order by 1
But I have problem to connect these both sql's.
Does anybody know, how I can do that?
My problem is the start with/connect by sentence and the joins and how to connect them, so that it works.
Does anybody have the same problem and has solved it?
Thank you in advance!
Isa
