How to Check Permissions for a Space via SQL Queries
This article pertains to Confluence versions 5.2 and greater, and has been tested to work in Confluence 7.19. 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>';
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 | | adminSpace Permission Mappings
The "permtype" values from from the output map to the following Space Permissions:
| Permission Name | Value 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 |