How to Check Permissions for a Space via SQL 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 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.
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>:
1
2
3
4
5
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
>
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 |
|---|---|
All - View |
|
Pages - Add |
|
Pages - Restrict |
|
Pages - Delete |
|
Blog - Add |
|
Blog - Delete |
|
Comments - Add |
|
Comments - Delete |
|
Attachments - Add |
|
Attachments - Delete |
|
Mail - Delete |
|
Space - Export |
|
Space - Admin |
|
Was this helpful?