SQL Query to show all users with Use Confluence permission

Chris Selvig March 9, 2012

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.

3 answers

1 accepted

4 votes
Answer accepted
HuseinA
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 7, 2012
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;

2 votes
Septa Cahyadiputra
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 19, 2012

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

0 votes
PDT Partners February 11, 2020

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?

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events