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

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.