How to List Pages with Restrictions
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
For auditing or administration purposes, an administrator may want to see which pages have an Edit or View restriction applied to them. This can be done via a SQL query.
Solution
These SQL queries return restriction data directly from the database. The results reveal which users and groups have restricted access to which pages — treat the output as sensitive and share it only with administrators who are entitled to review this information.
List pages restricted to a specific group
Run the following SQL queries against the Confluence database, replacing <group_name> with the group name:
SELECT c.CONTENTID, c.TITLE, s.SPACEKEY, cps.CONT_PERM_TYPE, cp.GROUPNAME
FROM SPACES s
JOIN CONTENT c ON s.SPACEID = c.SPACEID
JOIN CONTENT_PERM_SET cps ON c.CONTENTID = cps.CONTENT_ID
JOIN CONTENT_PERM cp ON cps.ID = cp.CPS_ID
WHERE cp.GROUPNAME = '<group_name>';List pages restricted to a specific user
Run the following SQL queries against the Confluence database, replacing <user_name> with the user name:
SELECT c.CONTENTID, c.TITLE, s.SPACEKEY, cps.CONT_PERM_TYPE, map.username
FROM SPACES s
JOIN CONTENT c ON s.SPACEID = c.SPACEID
JOIN CONTENT_PERM_SET cps ON c.CONTENTID = cps.CONTENT_ID
JOIN CONTENT_PERM cp ON cps.ID = cp.CPS_ID
JOIN user_mapping map ON cp.USERNAME=map.user_key
WHERE map.lower_username = lower('<user_name>');List pages restricted on a specific space
This query will return pages that have direct restrictions applied to them. Replace <space_key> with the correct key you want to filter for:
SELECT c.CONTENTID, c.TITLE, s.SPACEKEY, cps.CONT_PERM_TYPE
FROM SPACES s
JOIN CONTENT c ON s.SPACEID = c.SPACEID
JOIN CONTENT_PERM_SET cps ON c.CONTENTID = cps.CONTENT_ID
JOIN CONTENT_PERM cp ON cps.ID = cp.CPS_ID
WHERE s.SPACEKEY = '<space_key>';List pages restricted on all spaces
This query will return all pages from all spaces that have direct restrictions applied to them. It will also return the restriction type and the username for each restriction.
Run this query at your own risk. Since it involves multiple JOIN operations on several tables, these operations can take a significant amount of time and system resources, potentially impacting performance, particularly if you have a large amount of spaces, pages, and restrictions.
Before running the "list pages restricted on all spaces" query in a production environment, test it in a staging environment first to confirm the runtime is acceptable for your instance size — the Summary above already flags this as resource-intensive on large instances.
SELECT c.CONTENTID, c.TITLE, s.SPACEKEY, s.SPACENAME, cps.CONT_PERM_TYPE, map.username
FROM SPACES s
JOIN CONTENT c ON s.SPACEID = c.SPACEID
JOIN CONTENT_PERM_SET cps ON c.CONTENTID = cps.CONTENT_ID
JOIN CONTENT_PERM cp ON cps.ID = cp.CPS_ID
JOIN user_mapping map ON cp.USERNAME=map.user_key;List child pages that inherit restrictions on a specific space
To search for pages that are inheriting restrictions from the ones above, run the following query replacing <space_key> accordingly:
SELECT c.CONTENTID, c.TITLE, c.PARENTID, a.ANCESTORID
FROM CONFANCESTORS a
JOIN CONTENT c
ON a.DESCENDENTID = c.CONTENTID
WHERE a.ANCESTORID in (
SELECT c.CONTENTID
FROM SPACES s
JOIN CONTENT c ON s.SPACEID = c.SPACEID
JOIN CONTENT_PERM_SET cps ON c.CONTENTID = cps.CONTENT_ID
JOIN CONTENT_PERM cp ON cps.ID = cp.CPS_ID
WHERE cps.CONT_PERM_TYPE = 'View'
and s.SPACEKEY = '<space_key>'
);Notice we are filtering for the View restriction since that is the only type that is inherited by the children. On that same query, parentid is the immediate ancestor, whereas ancestorid is the root of the page tree (where the restriction comes from).
List of all the Confluence pages with Edit Restrictions.
SELECT c.CONTENTID, c.TITLE, s.SPACEKEY, cps.CONT_PERM_TYPE, map.username
FROM SPACES s
JOIN CONTENT c ON s.SPACEID = c.SPACEID
JOIN CONTENT_PERM_SET cps ON c.CONTENTID = cps.CONTENT_ID
JOIN CONTENT_PERM cp ON cps.ID = cp.CPS_ID
JOIN user_mapping map ON cp.USERNAME=map.user_key
where cps.CONT_PERM_TYPE = 'Edit';If pages that appear in these query results are unexpectedly absent from Confluence search results, this may indicate a stale search index — see How to Rebuild the Content Indexes From Scratch before assuming the restriction data is wrong.
Was this helpful?