Products | Versions |
---|---|
Spotfire Server | All |
It may be required to get a list of all the group-based folder permissions (including inherited permissions) in the TIBCO Spotfire library.
Create View [dbo].[folderPermissions] as WITH List1 as ( select A.ITEM_ID, A.TITLE, CAST(A.TITLE AS Varchar(MAX)) AS LIBRARY_PATH, A.ITEM_TYPE,CAST((g.GROUP_NAME) AS Varchar(MAX)) AS GROUPNAME,cast(la.PERMISSION AS Varchar(MAX)) AS PERMISSION,A.Parent_ID FROM lib_items A LEFT JOIN LIB_ACCESS la on A.ITEM_ID=la.ITEM_ID LEFT JOIN GROUPS g ON g.GROUP_ID=la.GROUP_ID LEFT JOIN LIB_ITEM_TYPES lit on lit.TYPE_ID=A.ITEM_TYPE where lit.DISPLAY_NAME='folder' and A.hidden=0 ), List2 as ( SELECT ITEM_ID, TITLE,CAST(TITLE AS Varchar(MAX)) AS LIBRARY_PATH, ITEM_TYPE, CAST((GROUPNAME) AS Varchar(MAX)) AS GROUPNAME, cast(PERMISSION AS Varchar(MAX)) AS PERMISSION, Parent_ID FROM List1 WHERE TITLE = 'root' UNION ALL select A.ITEM_ID, A.TITLE, REPLACE(CAST((B.LIBRARY_PATH + '/' + A.TITLE) AS Varchar(MAX)),'root', '') AS LIBRAR2, A.ITEM_TYPE,case when A.GROUPNAME is null then cast(isnull(B.GROUPNAME,'') AS Varchar(MAX)) else cast(A.GROUPNAME AS Varchar(MAX)) end AS GROUPNAME1, case when A.Permission is null then cast(isnull(B.PERMISSION,'') AS Varchar(MAX)) else cast(A.PERMISSION as Varchar(MAX)) end AS PERMISSION1,A.Parent_ID FROM List1 A inner join List2 B ON A.PARENT_ID = B.ITEM_ID) SELECT ITEM_ID, TITLE, LIBRARY_PATH, ITEM_TYPE, GROUPNAME,PERMISSION from List2 group by ITEM_ID, TITLE, LIBRARY_PATH, ITEM_TYPE, GROUPNAME,PERMISSION GO2. Then run a SELECT query on the view to retrieve the information. There may be more columns then you need for basic information.
SELECT * FROM dbo.folderPermissions
with List1(ITEM_ID, TITLE, LIBRARY_PATH, ITEM_TYPE, GROUPNAME,PERMISSION,PARENT_ID) as ( select A.ITEM_ID, A.TITLE, CAST(A.TITLE AS Varchar(1000)) AS LIBRARY_PATH, A.ITEM_TYPE,CAST((g.GROUP_NAME) AS Varchar(512)) AS GROUPNAME,cast(la.PERMISSION AS Varchar(20)) AS PERMISSION,A.Parent_ID FROM lib_items A LEFT JOIN LIB_ACCESS la on A.item_id=la.ITEM_ID LEFT JOIN GROUPS g ON g.GROUP_ID=la.GROUP_ID LEFT JOIN LIB_ITEM_TYPES lit on lit.TYPE_ID=A.ITEM_TYPE where lit.DISPLAY_NAME='folder' and A.hidden=0 ), List2(ITEM_ID, TITLE, LIBRARY_PATH, ITEM_TYPE, GROUPNAME,PERMISSION,PARENT_ID) as ( SELECT ITEM_ID, TITLE,CAST(TITLE AS Varchar(1000)) AS LIBRARY_PATH, ITEM_TYPE, CAST((GROUPNAME) AS Varchar(512)) AS GROUPNAME, cast(PERMISSION AS Varchar(20)) AS PERMISSION, Parent_ID FROM List1 WHERE TITLE = 'root' UNION ALL select A.ITEM_ID, A.TITLE, REPLACE(CAST((B.LIBRARY_PATH || '/' || A.TITLE) AS Varchar(1000)),'root', '') AS LIBRAR2, A.ITEM_TYPE,case when A.GROUPNAME is null then cast(nvl(B.GROUPNAME,'') AS Varchar(512)) else cast(A.GROUPNAME AS Varchar(512)) end AS GROUPNAME1, case when A.Permission is null then cast(nvl(B.PERMISSION,'') AS Varchar(20)) else cast(A.PERMISSION as Varchar(20)) end AS PERMISSION1,A.Parent_ID FROM List1 A inner join List2 B ON A.PARENT_ID = B.ITEM_ID) SELECT ITEM_ID, TITLE, LIBRARY_PATH, ITEM_TYPE, GROUPNAME,PERMISSION from List2 group by ITEM_ID, TITLE, LIBRARY_PATH, ITEM_TYPE, GROUPNAME,PERMISSION ORDER BY LIBRARY_PATH