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.

[HuseinA · Accepted solution]

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;

[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?