# How to Check Permissions for a Space via SQL Queries

#### Still need help?

The Atlassian Community is here for you.

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
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 |                  | 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
EDITSPACE
Pages - Restrict
SETPAGEPERMISSIONS
Pages - Delete
REMOVEPAGE
EDITBLOG
Blog - Delete
REMOVEBLOG
COMMENT
REMOVECOMMENT
CREATEATTACHMENT
Attachments - Delete
REMOVEATTACHMENT
Mail - Delete
REMOVEMAIL
Space - Export
EXPORTSPACE
SETSPACEPERMISSIONS