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.username = '<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 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).