--------------------------------------------------------------------------
-- David Brown 14/2/2001
-- Ver 1.1
-- david.brown@oracle.com
-- Report on access rights to folders/configurations and workareas by user
-- NOTE - If PUBLIC access rights are granted these override individual user
-- access rights 
-- login as repository owner
-- No workarea context is set
-- Provided as an example only
-- Tested against 6i 4.1 
--------------------------------------------------------------------------
set lines 240
set pages 80
set wrap off
set feedback off

ttitle left'Access to Workareas by Repository User' skip 2


column username format a20 heading Username
column Workarea format a30 heading Workarea

column Del format a7 heading Delete
column Adm format a7 heading Admin
column Ins format a7 heading Insert
column Sel format a7 heading Select
column Upd format a7 heading Update
column Ver format a10 heading Version
column Com format a10 heading Compile
column Usp format a10 heading 'Upd Spec'

break on username on workarea

select u.username username
,w.name Workarea
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(w.irid,u.username)),1,1) Del
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(w.irid,u.username)),2,1) Adm
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(w.irid,u.username)),3,1) Ins
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(w.irid,u.username)),4,1) Sel
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(w.irid,u.username)),5,1) Upd
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(w.irid,u.username)),6,1) Ver
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(w.irid,u.username)),7,1) Com
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(w.irid,u.username)),8,1) USp
from sdw_users u 
,i$sdd_workareas w
order by u.username
,w.name
/


ttitle left'Access to Containers by Repository User' skip 2

column folder format a30 heading Folder


select distinct u.username username
,f.name folder 
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(f.irid,u.username)),1,1) Del
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(f.irid,u.username)),2,1) Adm
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(f.irid,u.username)),3,1) Ins
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(f.irid,u.username)),4,1) Sel
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(f.irid,u.username)),5,1) Upd
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(f.irid,u.username)),6,1) Ver
from sdw_users u 
,i$sdd_folders f
order by u.username
,f.name
/


ttitle left'Access to Configurations by Repository User' skip 2

column folder format a30 heading Folder


select distinct u.username username
,c.name folder 
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(c.irid,u.username)),1,1) Del
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(c.irid,u.username)),2,1) Adm
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(c.irid,u.username)),3,1) Ins
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(c.irid,u.username)),4,1) Sel
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(c.irid,u.username)),5,1) Upd
,SUBSTR(jr_acc_rights.ar_to_str(jr_acc_rights.sel(c.irid,u.username)),6,1) Ver
from sdw_users u 
,i$sdd_configurations c
order by u.username
,c.name
/