How to know the number of users with access to a page

Question

Hi. Through SQL I need to know how many users have access to a page.

In this example, there are 70 users with access, but I need to retrieve that data through SQL.

Confluence 8.5.11 version

Thanks in advance.

Gustavo Tejo_uo9cha · Answer

Hi

SELECT u.username , c.cp_type , c.creationdate , c.lastmoddate

FROM content_perm AS c

JOIN user_mapping AS u

ON c.username = u.user_key

WHERE c.cps_id in (SELECT id FROM content_perm_set WHERE content_id in (SELECT contentid FROM content WHERE title= '[BFCO][APP] Integración con AZ Digital DC MVP1'

ORDER BY version DESC LIMIT 1));

Total rows: 0

Table Content_perm_set not matching with content

Jim Knepley - ReleaseTEAM · Answer

Hi @Gustavo Tejo, welcome to the community.

This query doesn't return counts, but you could either adapt the query or post-process the results.

SELECT u.username , c.cp_type , c.creationdate , c.lastmoddate
FROM content_perm AS c
JOIN user_mapping AS u
ON c.username = u.user_key
WHERE c.cps_id in (SELECT id FROM content_perm_set WHERE content_id in (SELECT contentid FROM content WHERE title= '<page title here>' 
ORDER BY version DESC LIMIT 1));

I'm unclear if that will include counts of users who are members of groups granted access.

ref: https://confluence.atlassian.com/confkb/how-to-check-permissions-for-a-specific-page-via-sql-queries-946627309.html