oracle hierarchical query start clause from join

Question

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

RIGHTS:
USERID     FOLDERID
---------------------  
685        5534      
685        5538      
686        5162      
686        6343      
686        28568     
686        41578     
725        113867    
725        127030    

FOLDERS:
FOLDERID     PARENTFOLDERID
----------------------------  
5534      
5538         5534
5162         5534      
6343         5162      
28568        5162     
41578        5534     
113867       41578    
127030       41578    

i need a list of all couple user/folder rights. I tried to use a hierarchical query with a join on the start clause like the following:

select 
from rights r, 
(select f.folder_id from folders f
 start with f.folder_id = r.folder_id
 connect by prior f.folder_id = f.parent_id)

but outbound reference r.folder_id is not accepted.

Answer 1

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 natural join folders
)
select CONNECT_BY_ROOT userid userid
     , folderid
     , SYS_CONNECT_BY_PATH(folderid, '/') folder_path
     , row_number() over (partition by CONNECT_BY_ROOT userid, folderid 
                              order by level desc) rn
  from j
  connect by prior folderid = parentfolderid
 order by 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.

    USERID   FOLDERID FOLDER_PATH                            RN
---------- ---------- ------------------------------ ----------
       685       5162 /5534/5162                              1
       685       5534 /5534                                   1
       685       5538 /5534/5538                              1
       685       5538 /5538                                   2
       685       6343 /5534/5162/6343                         1
       685      28568 /5534/5162/28568                        1
       685      41578 /5534/41578                             1
       685     113867 /5534/41578/113867                      1
       685     127030 /5534/41578/127030                      1
       686       5162 /5162                                   1
       686       6343 /5162/6343                              1
       686       6343 /6343                                   2
       686      28568 /5162/28568                             1
       686      28568 /28568                                  2
       686      41578 /41578                                  1
       686     113867 /41578/113867                           1
       686     127030 /41578/127030                           1
       725     113867 /113867                                 1
       725     127030 /127030                                 1

 19 rows selected 

Answer 2

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:

select connect_by_root(folderid) as rootid, folderid,
  sys_connect_by_path(folderid, '/') as path
from folders
connect by parentfolderid = prior folderid
order by rootid, path;

    ROOTID   FOLDERID PATH                         
---------- ---------- ------------------------------
      5162       5162 /5162                         
      5162      28568 /5162/28568                   
      5162       6343 /5162/6343                    
      5534       5534 /5534                         
      5534      41578 /5534/41578                   
      5534     113867 /5534/41578/113867            
      5534     127030 /5534/41578/127030            
      5534       5162 /5534/5162                    
      5534      28568 /5534/5162/28568              
      5534       6343 /5534/5162/6343               
      5534       5538 /5534/5538                    
      5538       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:

select distinct 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
order by r.userid, f.folderid;

Which with your data gets 16 distinct combinations:

    USERID   FOLDERID
---------- ----------
       685       5162
       685       5534
       685       5538
       685       6343
       685      28568
       685      41578
       685     113867
       685     127030
       686       5162
       686       6343
       686      28568
       686      41578
       686     113867
       686     127030
       725     113867
       725     127030

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
)
select distinct userid, folderid
from rcte
order by 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.