nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL Query to show all users with Use Confluence permission

Chris Selvig

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.

Accepted answers

HuseinA

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;

All comments

HuseinA

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;

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;

Septa Cahyadiputra

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

PDT Partners

I am basically trying to do something similar to this. I need to find everywhere in confluence that there is a permission that is granted to a user and not a group. Can anyone help?