How to Check Permissions for a Space via SQL Queries

Still need help?

The Atlassian Community is here for you.

Ask the community

This article pertains to Confluence versions 5.2 and greater, and has been tested to work in Confluence 5.7. If you are on a significantly higher version, the info on this page may be outdated, and you should double check the SQL results against what's being shown in the UI.

Purpose

For an administrator of a Confluence instance, it may be helpful to be able to query for the permissions of a given Space from outside of the application. This might be used for reporting/auditing purposes in a large instance, without the administrator having to check each Space in the UI.

Solution

The below SQL query will give an output of all permissions on a given space, when provided the <SPACEKEY>:

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
WHERE s.spacekey = '<SPACEKEY>';

(info) You can add "ORDER BY permtype" to the end of the query if you prefer the output to sort by each permission.

We also have KBs on the reverse scenario, if you want to look up which Spaces a particular user or group can access:

Example Output

>
 permid |      permtype       | spacekey |   spacename   |  permgroupname   | lower_username 
--------+---------------------+----------+---------------+------------------+----------------
 196637 | EXPORTSPACE         | EX       | Example Space | confluence-users | 
 196636 | EXPORTPAGE          | EX       | Example Space | confluence-users | 
 196635 | COMMENT             | EX       | Example Space | confluence-users | 
 196634 | EDITBLOG            | EX       | Example Space | confluence-users | 
 196633 | CREATEATTACHMENT    | EX       | Example Space | confluence-users | 
 196632 | VIEWSPACE           | EX       | Example Space | confluence-users | 
 196631 | EDITSPACE           | EX       | Example Space | confluence-users | 
 196630 | SETPAGEPERMISSIONS  | EX       | Example Space |                  | admin
 196629 | REMOVEMAIL          | EX       | Example Space |                  | admin
 196628 | EXPORTSPACE         | EX       | Example Space |                  | admin
 196627 | EXPORTPAGE          | EX       | Example Space |                  | admin
 196626 | EDITBLOG            | EX       | Example Space |                  | admin
 196625 | REMOVEATTACHMENT    | EX       | Example Space |                  | admin
 196624 | CREATEATTACHMENT    | EX       | Example Space |                  | admin
 196623 | REMOVEBLOG          | EX       | Example Space |                  | admin
 196622 | REMOVECOMMENT       | EX       | Example Space |                  | admin
 196621 | REMOVEPAGE          | EX       | Example Space |                  | admin
 196620 | SETSPACEPERMISSIONS | EX       | Example Space |                  | admin
 196619 | EDITSPACE           | EX       | Example Space |                  | admin
 196618 | COMMENT             | EX       | Example Space |                  | admin
 196617 | VIEWSPACE           | EX       | Example Space |                  | admin

Space Permission Mappings

 

The "permtype" values from from the output map to the following Space Permissions:

Permission NameValue of permtype
All - View
EXPORTPAGE
VIEWSPACE
Pages - Add
EDITSPACE
Pages - Restrict
SETPAGEPERMISSIONS
Pages - Delete
REMOVEPAGE
Blog - Add
EDITBLOG
Blog - Delete
REMOVEBLOG
Comments - Add
COMMENT
Comments - Delete
REMOVECOMMENT
Attachments - Add
CREATEATTACHMENT
Attachments - Delete
REMOVEATTACHMENT
Mail - Delete
REMOVEMAIL
Space - Export
EXPORTSPACE
Space - Admin
SETSPACEPERMISSIONS
Last modified on Feb 26, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.