How to List Pages with Restrictions
プラットフォームについて: Data Center のみ。 - This article only applies to Atlassian apps on the Data Center プラットフォーム。
この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。 Server* 製品のサポートは 2024 年 2 月 15 日に終了しました。Server 製品を実行している場合は、 アトラシアン Server サポート終了 のお知らせにアクセスして、移行オプションを確認してください。
*Fisheye および Crucible は除く
要約
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.
ソリューション
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.
この内容はお役に立ちましたか?