SQL query to get all Confluence spaces with anonymous enabled

[Topicus]

Hi,

I want to get all anonymous spaces in my Confluence instance through a SQL query, however I couldn't find a decent query. I have this which returns the permissions on (all) spaces, but not the anonymous ones:

SELECT sp.permid, sp.permtype, s.spacekey, s.spacename, sp.permgroupname, um.lower_username
FROM SPACEPERMISSIONS sp
JOIN SPACES s ON sp.spaceid = s.spaceid
LEFT JOIN user_mapping um ON sp.permusername = um.user_key

i have tried with where sp.permgroupname IS NULL, but that doesn't work.

Any help or pointers in the right direction?

Marc

[Stephen Deutsch · Accepted solution]

This might work:

SELECT *
  FROM spacepermissions
  WHERE COALESCE(permgroupname, '') = '' 
  and COALESCE(permusername, '') = '';

I got the information from this page: http://stackoverflow.com/questions/14172978/postgresql-select-empty-fields

[Stephen Deutsch]

note: anonymous permissions are those in which both group name and user name fields are blank :)

[Topicus]

That did the trick, thank you good sir!

[Rodrigo Girardi Adami]

Hi Marc,

If I understood you correctly, you would like to retrieve spaces which anonymous users can access correct?

This query should return all spaces which anonymous can see and access:

select distinct s.spacename
from spaces s
join spacepermissions sp on s.spaceid = sp.spaceid
where sp.permtype = 'VIEWSPACE' or 'EDITSPACE'
and permgroupname is null
and permusername is null;

The anonymous permissions are stored with the permgroupname and permusername as null.

I hope this helps!

Cheers,

Rodrigo

[Chander Inguva]

Hey Rodrigo, a small correction... where sp.permtype = 'VIEWSPACE' or sp.permtype ='EDITSPACE'

[Topicus]

That also does the trick, thanks :)

[James Thomas]

What about the pages which are having anonymous permission??

[Hans-Hermann Hunfeld]

Just as a hint for Oracle users, here you can use following

select distinct s.spacename
from spaces s
join spacepermissions sp on s.spaceid = sp.spaceid
where sp.permtype in ('VIEWSPACE','EDITSPACE')
and permgroupname is null
and permusername is null;

[sanan sanan]

Hi Hans,

we are using oracle db. 

we want to remove all the anonymous access from our Confluence spaces. 

your sql query will give us the list of such spaces. But can you suggest a command by which we can remove the anonymous access from the listed spaces?

Regards