How to Check Permissions for a Space via SQL Queries
Platform Notice: Data Center Only - This article only applies to Atlassian apps 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
This query returns permission data directly from the database, outside of Confluence's access-control UI. The output reveals which users and groups have administrative, edit, or view access to a space — treat it as sensitive and restrict who can run it to database administrators and authorized Confluence system administrators.
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:
For page-level (rather than space-level) restriction auditing via SQL, see How to List Pages with Restrictions | Confluence | Atlassian Support.
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 |
|---|---|
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 |
|
Verification step
After running the query, cross-check the output against the Confluence UI by navigating to Space Tools > Permissions for a sample space. Confirm that the permtype values returned by the query (e.g., VIEWSPACE, EDITSPACE, SETSPACEPERMISSIONS) match the permission checkboxes shown in the UI. This is especially important after a Confluence upgrade, which may introduce schema changes.
Was this helpful?