Audit tool for the library folders

Hello,

Thanks for all the feedback. I have adapted the code from Christian using the Oracle function SYS_CONNECT_BY_PATH. The li.item_type may be different from the one you have in your installation.

select
  lpad(' ', (level - 1) * 4) || SYS_CONNECT_BY_PATH(TITLE, ' / ') "Path", li.TITLE as lib_item, 
  la.PERMISSION, 
  (case la.group_id when null then null else g.group_name end) as sp_group, 
  (case la.user_id when null then null else u.user_name end) as sp_user, 
  level 
from lib_items li 
left join 
(
select item_id, user_id, group_id, listagg(permission, ', ') within group (order by item_id) as permission 
from lib_access 
group by item_id, user_id, group_id 
) la
on li.ITEM_ID = la.ITEM_ID 
left join users u 
on la.USER_ID = u.USER_ID 
left join groups g 
on la.GROUP_ID = g.GROUP_ID
WHERE li.item_type = '4f83cd41-71b5-11dd-050e-00100a64217d'
CONNECT BY li.PARENT_ID = PRIOR li.ITEM_ID
start with li.parent_id is null 
;

I can create a cross table report... and it works as expected. I will have a look at the show-library-permissions command, but at first sight, this was not suitable, as the report should have been used in the Web Player, for our ServiceDesk that does not have access to the library, but still would need to determine which Active Directory groups are required for the library access.

But still... If TIBCO can implement such report in native... :)

Thank you.

There is a command in the command line configuration tool called show-library-permissions that should do what you're asking for (it was added in 6.0).

Thanks Christian. This query gets some data indeed. Using cross table, it can be readable… The problem is that we have many folders having the same name and it becomes impossible to know which parent folders (completed tree) it refers to. The item_id should match with the item_id of the parent folder, making things complicated. I can use this query as a base for some other reporting that may work, and try to add only the folder type, as the query returns over 3,8 M rows and this will be an issue if the reports needs to be exported to MS Excel. ☺ The idea of the tool I was suggesting was the kind of.

X RX RWX
Root / Folder 1 Group 1 / Group 2
Root / Folder 1 / Folder 2 Group 1

You can run such a script in the Spotfire DB: