List of space and condluence user having specific set of permissions

MRANJ April 5, 2023

Hi All,

 

We are looking to list space and individual users where they HAVE ONE of the ('EDITSPACE', 'REMOVEPAGE', 'EDITBLOG', 'REMOVEBLOG', 'CREATEATTACHMENT', 'REMOVEATTACHMENT') permissions, but NONE of the ('SETPAGEPERMISSION', 'REMOVEMAIL', 'EXPORTSPACE', 'SETSPACEPERMISSION') permissions.

 

We are executing below SQL query

 

SELECT distinct s.SPACENAME,s.SPACEKEY,s.SPACETYPE,sp.PERMTYPE as 'PermissionType', um.username, cu.display_name,cu.email_address
FROM SPACEPERMISSIONS sp
JOIN SPACES s ON s.SPACEID = sp.SPACEID
JOIN user_mapping um ON um.user_key = sp.PERMUSERNAME
JOIN cwd_user cu ON um.username =cu.user_name
WHERE
sp.PERMTYPE IN ('EDITSPACE', 'REMOVEPAGE', 'EDITBLOG', 'REMOVEBLOG', 'CREATEATTACHMENT', 'REMOVEATTACHMENT) and NOT IN ( 'SETPAGEPERMISSION', 'REMOVEMAIL', 'EXPORTSPACE', 'SETSPACEPERMISSION')
AND cu.active ='T' AND s.SPACETYPE !='personal' AND s.SPACESTATUS = 'CURRENT'
ORDER BY SPACENAME;

 

We are not getting desired result. As it provides output for example user1 with 'EDITSPACE' Permission who also have 'SETSPACEPERMISSION' in same space.

0 answers

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events