How to find various permissions on the Spaces which have anonymous access enabled with Database Queries.
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
This article will help find various permissions on Anonymous spaces in Confluence
Solution
When we enable Anonymous access in Confluence at Space level, we can assign various permissions to the anonymous users. The anonymous access permissions look like below on Confluence UI.
Mapping these permissions to the Database, we have below mentioned permissions specifically for Spaces having Anonymous access on.
PERMTYPE |
|---|
VIEWSPACE |
REMOVEOWNCONTENT |
COMMENT |
EDITSPACE |
REMOVEPAGE |
REMOVECOMMENT |
REMOVEBLOG |
CREATEATTACHMENT |
REMOVEATTACHMENT |
EDITBLOG |
EXPORTSPACE |
USECONFLUENCE |
REMOVEMAIL |
If we want to find out a list of Spaces which have View access enabled for anonymous users, or can be viewed by anonymous users we can use below SQL query
MySQL Syntax
1SELECT SPACENAME FROM SPACES WHERE SPACEID IN (SELECT SPACEID FROM SPACEPERMISSIONS WHERE PERMTYPE = 'VIEWSPACE' AND PERMGROUPNAME IS NULL AND PERMUSERNAME IS NULL AND PERMALLUSERSSUBJECT IS NULL);
Similarly, If we want to find a list of spaces where Anonymous users can add comments can be found out by below Query,
MySQL Syntax
1SELECT SPACENAME FROM SPACES WHERE SPACEID IN (SELECT SPACEID FROM SPACEPERMISSIONS WHERE PERMTYPE = 'COMMENT' AND PERMGROUPNAME IS NULL AND PERMUSERNAME IS NULL AND PERMALLUSERSSUBJECT IS NULL);
So, using this query, you can easily find a list of Spaces with permission type which have anonymous access enabled. You just have to replace the PERMTYPE filter above in the query to any one the mentioned permissions in the table shown above. To give you another example, if we need to pull a list of Spaces where anonymous users can add attachments, we will run the below query.
MySQL Syntax
1SELECT SPACENAME FROM SPACES WHERE SPACEID IN (SELECT SPACEID FROM SPACEPERMISSIONS WHERE PERMTYPE = 'CREATEATTACHMENT' AND PERMGROUPNAME IS NULL AND PERMUSERNAME IS NULL AND PERMALLUSERSSUBJECT IS NULL);
Lastly, if you wish to remove all permissions for the anonymous user on a Space from the database such a query can be helpful.
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Please make sure to stop Confluence before performing any manual Database create, update or delete operations.
Replace the <lowerspacekey> with your lower Space Key in the query below
MySQL Syntax
1 2 3 4 5 6 7 8DELETE FROM SPACEPERMISSIONS WHERE PERMGROUPNAME IS NULL AND PERMUSERNAME IS NULL AND PERMALLUSERSSUBJECT IS NULL AND SPACEID IN (SELECT SPACEID FROM SPACES WHERE LOWERSPACEKEY = '<lowerspacekey>');
Was this helpful?