How to List Pages with Restrictions

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

目的

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.

ソリューション

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';
  


Last modified on Mar 21, 2024

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.