How to retrieve all TIBCO Spotfire library folder permissions (including inherited permissions)

Product:TIBCO Spotfire Server
Versions:All Versions

Summary:
This article summarizes an approach to retrieving all Spotfire library folder permissions from the Spotfire applicaiton database

Details:
It may be required to get a list of all the group-based folder permissions (including inherited permissions) in the TIBCO Spotfire library.

Resolution:
Use the show-library-permissions command to create a report file that shows the permissions in the library. See the show-library-permissions reference for more details.

Additionally, to get a list of all group-based folder permissions in the TIBCO Spotfire library you can run the following example queries on the TIBCO Spotfire application database (use the appropriate example depending on what database type your application database is using).

Note: These will even include folders whose permissions are inherited from Parent folder.

Microsoft SQL Server:
1. Run the following SQL to create the new view "folderPermissions":

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
GO

2. 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

Oracle:
1. Run the following SQL:

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

Disclaimer: The content of this article is for informational purposes only. The subject material may change in any new versions with no notice and there is no responsibility by TIBCO to maintain or support future access to this internal application content. Modification of any internal application content is not recommended and can lead to an unsupported configuration.  It is not intended to be used "As Is" in a Production environment. Always test in a Development environment.