November 2008

Intralink stores folder authorizations in four Oracle tables. Two for user authorizations (PDM_USERFOLAUTH and PDM_USERFOLRLAUTH) and two for group authorizations (PDM_USERGRFOLAUTH and PDM_USERGRFOLRLAUTH).

The PDM_USERFOLAUTH and PDM_USERGRFOLAUTH tables contain folder role authorizations. The PDM_USERFOLRLAUTH and PDM_USERGRFOLRLAUTH tables contain folder authorizations by release level.

Query for user folder role authorizations:

-- user folder auth
--
set linesize 135
col folpath format a60
col userexpname format a12
col rolename format a15
--
select 
  FOLPATH, 
  USEREXPNAME, 
  ROLENAME 
from 
  pdm.PDM_USERFOLAUTH ufa, 
  pdm.PDM_ROLE role, 
  pdm.PDM_FOLDER fol, 
  pdm.PDM_USER usr
where
  role.ROLEID=ufa.ROLEID and 
  ufa.FOLID=fol.FOLID and 
  ufa.USERID=usr.USERID
;

Query for user folder/releaselevel role authorizations:

-- user folder/releaselevel auth
--
set linesize 135
col folpath format a60
col userexpname format a12
col rlname format a15
col rolename format a15
--
select 
  FOLPATH, 
  USEREXPNAME, 
  RLNAME, 
  ROLENAME 
from 
  pdm.PDM_USERFOLRLAUTH ufrla, 
  pdm.PDM_RELEASELEVEL rl, 
  pdm.PDM_ROLE role, 
  pdm.PDM_FOLDER fol, 
  pdm.PDM_USER usr
where
  role.ROLEID=ufrla.ROLEID and 
  ufrla.RLID=rl.RLID and 
  ufrla.FOLID=fol.FOLID and 
  ufrla.USERID=usr.USERID
;

Query for group folder role authorizations:

-- group folder auth
--
set linesize 135
col folpath format a60
col rolename format a15
col grexpname format a25
--
select 
  FOLPATH, 
  GREXPNAME 
  ROLENAME, 
from 
  pdm.PDM_USERGRFOLAUTH gfa, 
  pdm.PDM_ROLE role, 
  pdm.PDM_FOLDER fol, 
  pdm.PDM_USERGROUP grp 
where
  role.ROLEID=gfa.ROLEID and 
  gfa.FOLID=fol.FOLID and 
  gfa.GRID=grp.GRID 
;

Query for group folder/releaselevel role authorizations:

-- group folder/releaselevel auth
--
set linesize 135
col folpath format a60
col grexpname format a25
col rlname format a15
col rolename format a15
--
select 
  FOLPATH, 
  GREXPNAME, 
  RLNAME, 
  ROLENAME 
from 
  pdm.PDM_USERGRFOLRLAUTH gfrla, 
  pdm.PDM_RELEASELEVEL rl, 
  pdm.PDM_ROLE role, 
  pdm.PDM_FOLDER fol, 
  pdm.PDM_USERGROUP grp
where
  role.ROLEID=gfrla.ROLEID and 
  gfrla.RLID=rl.RLID and 
  gfrla.FOLID=fol.FOLID and 
  gfrla.GRID=grp.GRID
;

Putting it all together

To streamline these queries, unions can be used to combine the output into one report. In the following example, user and group names are reported under the 'ID' column. The final where clause can be used to filter for a specific user or group. Leave it out to report authorizations for everyone.

The report is also enhanced in that the order of the release levels in the output matches the output one would see in the folder authorization window in Intralink Admin.

The syntax is somewhat inefficient because it is setup to allow outer joins based on folders (see end of article). However, even for very large numbers of folders, user, and groups, the combined query is still pretty fast.

-- user & group auth
--
set linesize 200
column folpath format a70
column rolename format a20
column rlname format a15
column id format a25
--
select FOLPATH,id,rlname,rolename,rlseqnum
from
  (
    select
      folpath,
      (select userexpname from pdm.pdm_user where userid=ufa.userid) as id,
      '' as rlname,
      to_number('') as rlseqnum,
      (select rolename from pdm.pdm_role where roleid=ufa.roleid) as rolename
    from
      pdm.pdm_userfolauth ufa,
      pdm.pdm_folder fol
    where
      ufa.folid=fol.folid
  UNION
    select 
      FOLPATH,
      (select userexpname from pdm.pdm_user where userid=ufrla.userid) as id,
      (select rlname from pdm.pdm_releaselevel where rlid=ufrla.rlid) as rlname,
      (select rlseqnum from pdm.pdm_releaselevel where rlid=ufrla.rlid) as rlseqnum,
      (select rolename from pdm.pdm_role where roleid=ufrla.roleid) as rolename
    from 
      pdm.PDM_USERFOLRLAUTH ufrla, 
      pdm.PDM_FOLDER fol
    where
      ufrla.FOLID=fol.FOLID
  UNION
    select
      folpath,
      (select grexpname from pdm.pdm_usergroup where grid=ugfa.grid) as id,
      '' as rlname,
      to_number('') as rlseqnum,
      (select rolename from pdm.pdm_role where roleid=ugfa.roleid) as rolename
    from
      pdm.pdm_usergrfolauth ugfa,
      pdm.pdm_folder fol
    where
      ugfa.folid=fol.folid
  UNION
    select 
      FOLPATH,
      (select grexpname from pdm.pdm_usergroup where grid=gfrla.grid) as id, 
      (select rlname from pdm.pdm_releaselevel where rlid=gfrla.rlid) as rlname,
      (select rlseqnum from pdm.pdm_releaselevel where rlid=gfrla.rlid) as rlseqnum,
      (select rolename from pdm.pdm_role where roleid=gfrla.roleid) as rolename
    from 
      pdm.PDM_USERGRFOLRLAUTH gfrla, 
      pdm.PDM_FOLDER fol
    where
      gfrla.FOLID=fol.FOLID
  )
where
  id like '%smith%'
order by folpath,id,rlseqnum
;

Outer Joins for More Information

To show all folders, regardless of whether they contain any authorization settings, add the following outer join syntax to the above: