SQL Query to show all users with Use Confluence permission

We're testing an upgrade from Confluence 2.8.2 to 4.1.4. Both instances authenticate to the same two Active Directory servers and the same groups have Use Confluence permissions in both. However, the 4.1.4 instance shows an extra 70 users as compared to the 2.8.2 instance. In troubleshooting this, it would help to be able to output lists of eervyone with Use Confleunce perms from each instance; failing that, at least having this from 4.1.4 would be great. Ideally I would want to see what group they're inheriting Use Confluence from, as that is how most users get in.

2 answers

1 accepted

Update: There is also a good way in getting the same information through Confluence User Macro as discussed here: https://answers.atlassian.com/questions/36898/how-to-query-active-users-in-confluence
You can try to use the following SQL query for any pre-3.5 Confluence versions:
  • Local users:
    SELECT DISTINCT
        (name) AS local_user
    FROM
        users
    WHERE
        name IN
                 (
                 SELECT DISTINCT
                     (permusername)
                 FROM
                     SPACEPERMISSIONS
                 WHERE
                     permtype='USECONFLUENCE')
    OR  name IN
                 (
                 SELECT DISTINCT
                     (u.name)
                 FROM
                     users u
                 JOIN
                     local_members lm
                 ON
                     u.id=lm.userid
                 JOIN
                     groups g
                 ON
                     lm.groupid=g.id
                 JOIN
                     SPACEPERMISSIONS sp
                 ON
                     g.groupname=sp.permgroupname
                 WHERE
                     permtype='USECONFLUENCE')
    ORDER BY
        1;
  • LDAP users:
    SELECT DISTINCT
        (name) AS external_user
    FROM
        external_entities
    WHERE
        name IN
                 (
                 SELECT DISTINCT
                     (permusername)
                 FROM
                     SPACEPERMISSIONS
                 WHERE
                     permtype='USECONFLUENCE')
    OR  name IN
                 (
                 SELECT DISTINCT
                     (ee.name)
                 FROM
                     external_entities ee
                 JOIN
                     external_members em
                 ON
                     ee.id=em.extentityid
                 JOIN
                     groups g
                 ON
                     em.groupid=g.id
                 JOIN
                     SPACEPERMISSIONS sp
                 ON
                     g.groupname=sp.permgroupname
                 WHERE
                     permtype='USECONFLUENCE')
    ORDER BY
        1;

The above will list down all LDAP users that can access Confluence and counted towards the license. It caters from Global Permission 'can-use' given to the group or individual. This is only based on local groups since LDAP groups are not stored in the database in pre-3.5.

For Confluence 3.5.x or higher, you can use the following SQL query:

SELECT DISTINCT
    (users.lower_user_name)
FROM
    cwd_user users
JOIN
    cwd_directory userdir
ON
    users.directory_id=userdir.id
WHERE
    users.lower_user_name IN
                          (
                          SELECT DISTINCT
                              lower(permusername)
                          FROM
                              SPACEPERMISSIONS
                          WHERE
                              permtype='USECONFLUENCE')
OR  users.lower_user_name IN
    (
        SELECT
            u.lower_user_name
        FROM
            cwd_user u
        JOIN
            cwd_directory d
        ON
            u.directory_id = d.id
        JOIN
            cwd_membership m
        ON
            u.id = m.child_user_id
        JOIN
            cwd_group g
        ON
            g.id = parent_id
        JOIN
            SPACEPERMISSIONS sp
        ON
            g.lower_group_name =lower(sp.permgroupname)
        WHERE
            permtype='USECONFLUENCE' )
AND users.active='T'
AND userdir.active='T'
ORDER BY
    1;

Hi there,

Here is the query for Confluence 3.5 and above.

The SQL query below will listed all Confluence licensed users which is direct members of groups

SELECT
DISTINCT (u.user_name),
o.list_index,
d.active,
d.id as DirectoryID,
d.directory_name,
d.directory_type,
u.id as UserID
FROM
cwd_user u 
JOIN cwd_directory d
ON
u.directory_id = d.id 
JOIN cwd_membership m 
ON
u.id = m.child_user_id
JOIN cwd_group g 
ON
g.id = parent_id 
JOIN cwd_app_dir_mapping o 
ON
d.id=o.directory_id
JOIN
SPACEPERMISSIONS sp
ON
g.group_name=sp.permgroupname
WHERE
permtype='USECONFLUENCE'
AND
d.active = 'T'
AND
u.active = 'T'
ORDER BY 2 ASC, 1 ASC;

If you have configured a nested groups on your end, you could add the above list with the licensed nested users list by executing this SQL query

SELECT DISTINCT(u.user_name) 
FROM cwd_membership a 
JOIN cwd_user u 
ON a.child_user_id = u.id
JOIN
  cwd_directory d
ON
  u.directory_id = d.id
WHERE 
d.active = 'T'
AND
u.active = 'T'
AND
a.parent_id IN 
(
SELECT m.child_group_id 
FROM cwd_membership m 
JOIN cwd_group g ON m.parent_id = g.id 
WHERE child_group_id 
IS NOT NULL AND g.group_name 
IN 
(
SELECT permgroupname 
FROM SPACEPERMISSIONS 
WHERE PERMTYPE = 'USECONFLUENCE'))
AND
u.lower_user_name 
NOT IN
(
                              SELECT DISTINCT
                                  (u.lower_user_name)
                              FROM
                                  cwd_user u
                              JOIN
                                  cwd_directory d
                              ON
                                  u.directory_id = d.id
                              JOIN
                                  cwd_membership m
                              ON
                                  u.id = m.child_user_id
                              JOIN
                                  cwd_group g
                              ON
                                  g.id = m.parent_id
                              JOIN
                                  cwd_app_dir_mapping o
                              ON
                                  d.id=o.directory_id
                              JOIN
                                  SPACEPERMISSIONS sp
                              ON
                                  g.group_name=sp.permgroupname
                              WHERE
                                  permtype='USECONFLUENCE'
                              AND d.active = 'T'
                              AND u.active = 'T')
;

Hope it helps.

Cheers,

Septa Cahyadiputra

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Kesha Thillainayagam
Posted Apr 13, 2018 in Confluence

We want to hear how your non-technical teams are using Confluence!

Hi Community! Kesha (kay-sha) from the Confluence marketing team here! Can you share stories with us on how your non-technical (think Marketing, Sales, HR, legal, etc.) teams are using Confluen...

385 views 21 10
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you