Oracle Hierarchical Query Start Clause From Join

i have a table which contains user_rights on folders. Considering that rights are inherited on folder hierarchy, having right on a folder give right on any child folders. rights R

Solution 1:

Your r alias and the rights table it refers to are not in scope for the inline view you're creating. You need to generate the hierarchy, which you can still do in an inline view, and then join that to the rights table via its folderid.

You can get the hierarchy from:

selectconnect_by_root(folderid)asrootid,folderid,sys_connect_by_path(folderid,'/')aspathfromfoldersconnectbyparentfolderid=priorfolderidorderbyrootid,path;ROOTIDFOLDERIDPATH--------------------------------------------------5162       5162/51625162      28568/5162/285685162       6343/5162/63435534       5534/55345534      41578/5534/415785534     113867/5534/41578/1138675534     127030/5534/41578/1270305534       5162/5534/51625534      28568/5534/5162/285685534       6343/5534/5162/63435534       5538/5534/55385538       5538/5538...

Which is pretty much what you were doing, but this finds all the descendants from any starting point, and also captures the starting point as rootid. (I've thrown in path too just to visualise the hierarchy; you don't seem to want that in the results).

You can then join that to your rights table, where each user's folderid matches any rootid. That will list duplicates (e.g. 685 can get to 5538 directly or via 5534), so you can use distinct to eliminate those:

selectdistinct r.userid, f.folderid
from rights r
join (
  select connect_by_root(folderid) as rootid, folderid
  from folders
  connect by prior folderid = parentfolderid
) f
on f.rootid = r.folderid
orderby r.userid, f.folderid;

Which with your data gets 16 distinct combinations:

USERIDFOLDERID--------------------68551626855534685553868563436852856868541578685113867685127030686516268663436862856868641578686113867686127030725113867725127030

You could also use recursive subquery factoring instead of a hierarchical query:

with rcte (userid, folderid) as (
  select r.userid, f.folderid
  from rights r
  join folders f on f.folderid = r.folderid
  union all
  select rcte.userid, f.folderid
  from rcte
  join folders f on f.parentfolderid = rcte.folderid
)
selectdistinct userid, folderid
from rcte
orderby userid, folderid;

The anchor member is a simple join between the two tables to get the top-level permissions. The recursive member then looks for any child permissions of any already found. Same result, slightly different approach.

Solution 2:

You might be able to do what you are trying with the CROSS APPLY or OUTER APPLY operators introduced in Oracle 12c. The CROSS/OUTER APPLY operators allow you to create correlated joins. Unfortunately I don't have a 12c instance to play with right now so here's a way to do it in older versions of oracle. The idea is to move hierarchical portion to after you've joined the data:

with j as (
  select userid, folderid, parentfolderid
    from rights naturaljoin folders
)
select CONNECT_BY_ROOT userid userid
     , folderid
     , SYS_CONNECT_BY_PATH(folderid, '/') folder_path
     , row_number() over (partitionby CONNECT_BY_ROOT userid, folderid 
                              orderby level desc) rn
  from j
  connectby prior folderid = parentfolderid
 orderby userid, folderid;

I've included the RN row number column in the output to help show which folders being included more than once for a given user in the hierarchy. Any value greater than one indicates inherited permissions have been overridden by a direct grant.

USERIDFOLDERIDFOLDER_PATHRN------------------------------------------------------------6855162/5534/516216855534/553416855538/5534/553816855538/553826856343/5534/5162/6343168528568/5534/5162/28568168541578/5534/415781685113867/5534/41578/1138671685127030/5534/41578/12703016865162/516216866343/5162/634316866343/6343268628568/5162/28568168628568/28568268641578/415781686113867/41578/1138671686127030/41578/1270301725113867/1138671725127030/127030119rowsselected