How to List Pages with Restrictions
Purpose
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
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.
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';