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.
