Hi,
I am trying to fetch the list of users who don't have space admin permissions to their related spaces but have permissions to delete the page, attachment or blog post.
I am using this query:
SELECT s.spacekey, s.spacename, cu.user_name, cu.first_name || ' ' || cu.last_name AS username, sp.permtype
FROM SPACEPERMISSIONS sp
JOIN SPACES s ON s.SPACEID = sp.SPACEID
JOIN user_mapping u ON sp.permusername = u.user_key
JOIN cwd_user cu ON cu.user_name = u.username
WHERE sp.permtype = 'REMOVEPAGE' OR sp.permtype = 'REMOVEBLOG' OR sp.permtype = 'REMOVEATTACHMENT' OR sp.permtype = 'REMOVECOMMENT' OR sp.permtype = 'REMOVEMAIL' AND sp.permtype != 'SETSPACEPERMISSIONS'
GROUP BY s.spacekey, s.spacename, sp.permtype, cu.user_name, cu.first_name || ' ' || cu.last_name, sp.permtype
ORDER BY s.spacename, cu.user_name, sp.permtype
But, this is returning the list of users who have space admin permissions as well.
Anything that I missed?
Thanks,
Vamsi
SELECT s.spacekey, s.spacename, cu.user_name, cu.first_name || ' ' || cu.last_name AS username, sp.permtype, sp.permusername
FROM SPACEPERMISSIONS sp
JOIN SPACES s ON s.SPACEID = sp.SPACEID
JOIN user_mapping u ON sp.permusername = u.user_key
JOIN cwd_user cu ON cu.user_name = u.username
WHERE (sp.permtype = 'REMOVEPAGE' OR
sp.permtype = 'REMOVEBLOG' OR
sp.permtype = 'REMOVEATTACHMENT' OR
sp.permtype = 'REMOVECOMMENT' OR
sp.permtype = 'REMOVEMAIL') AND
sp.permusername NOT IN
(SELECT distinct permusername FROM spacepermissions WHERE permtype = 'SETSPACEPERMISSIONS' AND permusername = sp.permusername AND spaceid = sp.spaceid)
GROUP BY s.spacekey, s.spacename, sp.permtype, cu.user_name, cu.first_name || ' ' || cu.last_name, sp.permtype, sp.permusername
ORDER BY s.spacename, cu.user_name, sp.permtype
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.