How-to find all reports path with permission & role in jasperserver repository with the sql query?

[menghong.chhorn]

I already selected with jiobjectpermission and left join with jirole but it missing some reports.

 

 

 

SELECT jiobjectpermission.uri, jirole.rolename,

 

CASE WHEN jiobjectpermission.permissionMask = 1 THEN 'admin'

 

WHEN jiobjectpermission.permissionMask = 2 THEN 'read only'

 

WHEN jiobjectpermission.permissionMask = 18 THEN 'read + delete'

 

WHEN jiobjectpermission.permissionMask = 30 THEN 'read + write + delete'

 

WHEN jiobjectpermission.permissionMask = 32 THEN 'execute only'

 

Else 'no access' END AS Permission

 

 

 

FROM jiobjectpermission

 

 

 

LEFT JOIN jirole ON jirole.id = jiobjectpermission.recipientobjectid

 

WHERE jiobjectpermission.recipientobjectclass LIKE '%RepoRole' and jiobjectpermission.permissionMask <> 0

 

UNION

 

SELECT jiobjectpermission.uri, '' AS rolename,

 

CASE WHEN jiobjectpermission.permissionMask = 1 THEN 'admin'

 

WHEN jiobjectpermission.permissionMask = 2 THEN 'read only'

 

WHEN jiobjectpermission.permissionMask = 18 THEN 'read + delete'

 

WHEN jiobjectpermission.permissionMask = 30 THEN 'read + write + delete'

 

WHEN jiobjectpermission.permissionMask = 32 THEN 'execute only'

 

Else 'no access' END AS Permission

 

FROM jiobjectpermission

 

LEFT JOIN jiuser ON jiuser.id = jiobjectpermission.recipientobjectid

 

WHERE jiobjectpermission.recipientobjectclass LIKE '%RepoUser' and jiobjectpermission.permissionMask <> 0

 

ORDER BY 1 asc

[Mehak Rajkumar]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[Anuja Vilas Bhujbal]

Hello Menghong,

 

It seems like you missed the entry for "read+write" thus this was giving some wrong output for this condition. Please refer to the below modified query.

SELECT jiobjectpermission.uri, jirole.rolename,
CASE WHEN jiobjectpermission.permissionMask = 1 THEN 'admin'
WHEN jiobjectpermission.permissionMask = 2 THEN 'read only'
WHEN jiobjectpermission.permissionMask = 6 THEN 'read + write'
WHEN jiobjectpermission.permissionMask = 18 THEN 'read + delete'
WHEN jiobjectpermission.permissionMask = 30 THEN 'read + write + delete'
WHEN jiobjectpermission.permissionMask = 32 THEN 'execute only'
Else 'no access' END AS Permission
FROM jiobjectpermission
LEFT JOIN jirole ON jirole.id = jiobjectpermission.recipientobjectid
WHERE jiobjectpermission.recipientobjectclass LIKE '%RepoRole' and jiobjectpermission.permissionMask <> 0
UNION
SELECT jiobjectpermission.uri, '' AS rolename,
CASE WHEN jiobjectpermission.permissionMask = 1 THEN 'admin'
WHEN jiobjectpermission.permissionMask = 2 THEN 'read only'
WHEN jiobjectpermission.permissionMask = 6 THEN 'read + write'
WHEN jiobjectpermission.permissionMask = 18 THEN 'read + delete'
WHEN jiobjectpermission.permissionMask = 30 THEN 'read + write + delete'
WHEN jiobjectpermission.permissionMask = 32 THEN 'execute only'
Else 'no access' END AS Permission
FROM jiobjectpermission
LEFT JOIN jiuser ON jiuser.id = jiobjectpermission.recipientobjectid
WHERE jiobjectpermission.recipientobjectclass LIKE '%RepoUser' and jiobjectpermission.permissionMask <> 0
ORDER BY 1 asc

 

Also,  JIObjectPermission is a mapping table of permission masks for certain URI to certain recipients. In JRS the recipient can be either a user or a role, this information is stored in the recipientObjectClass column. Based on the recipientObjectClass column you can join the respective table - either User or Role and get info about what kind of permission is assigned to what object.

Bear in mind that permissions in JRS are inheritable and go from top-level object to bottom-level object in the repository structure -> root folder > folder > sub folder > object. So this table will only contain explicitly set permissions, instead of all permissions on the server.