nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Stephen Deutsch

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

All comments

Stephen Deutsch

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

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

Topicus

That did the trick, thank you good sir!

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

sql

confluence-administration

postgres