I have 10 users set up but it is showing 11

[Kevin Pelzman]

I have 10 users split between three different groups which have access to different pages. For some reason Confluence thinks I have 11.

[AnnWorley · Accepted solution]

The query I intended should have returned 2 columns, lower_user_name and directory_name. I am baffled that it returned user and last login for you. To be clear, the query is:

SELECT DISTINCT u.lower_user_name, d.directory_name
FROM cwd_user u
JOIN cwd_membership m ON u.id = child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
JOIN cwd_directory d on u.directory_id = d.id
WHERE PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T'
ORDER BY d.directory_name;

If you have an LDAP user directory you are familiar with the Confluence Admin>User Directories page, where the connection to LDAP was configured.There is also a Confluence Internal user directory on that page.

I did not mean to imply that you had duplicate users in your LDAP user directory groups, rather that you could have a user in the Confluence Internal Directory which has the same user name as a user in the LDAP user directory. Because of aggregating group memberships across directories you may verify that the user is not in the group in the LDAP directory, yet that user may be getting permissions from group membership in the other user directory. 

Please review the example scenario under Managing Multiple Directories (You have to expand the example.) I would paste it here for your convenience but it has graphics.

[Kevin Pelzman]

yeah, this is what I get from the SQL query

[AnnWorley]

The article with the query warns: 

This SQL query may not return accurate results if you are using nested groups in LDAP or Crowd, or if you have users with duplicated usernames across multiple directories.

Could I trouble you to run this one to see if there are any duplicate users?

SELECT 
lower_user_name
FROM 
cwd_user
GROUP BY
lower_user_name
having count(lower_user_name) > 1;

[Kevin Pelzman]

Sure, this is what I get. This is my non-admin account that does not have access. I also had the local account under this name, but it is disabled and does not have access.

[AnnWorley]

I just did some testing and it looks like aggregating group memberships apply even to disabled users.

Please attach a screen shot of the kpelzman user's View User page in User Management and a screen shot or shots that show the global permissions, from Confluence Admin > Global Permissions.

Thanks for collecting all these materials and running the queries, etc.

[Kevin Pelzman]

I see what you are getting at. I removed the built in accounts from that user and it appears to be reporting correctly now.

[AnnWorley]

That's great to hear. Thanks so much for following up so the matter would not pester my mind, and for the sake of anyone else with a similar issue.

[AnnWorley]

Hi Kevin,

As so eloquently stated in How to get a list of active users counting towards the Confluence license:

Confluence's license count is based on Global Permissions. Users will count towards the license in the following ways:

If the user is a member of a group that has global permissions to use Confluence

If the user is individually granted global permissions to use Confluence

The article linked above has SQL queries to find the users with permission to use Confluence. However, in your case, since you only have 10 or 11 users you could list them on the Confluence Admin>User Management page using the show all users link, and see who the 11 are.

As part of the Confluence installation, a super user is created to administer Confluence. Is it possible that this is the 11th user? If so, it should show in the list.

I look forward to hearing what you find out.

Thanks,

Ann

[Kevin Pelzman]

Yeah, the only problem is that I am pulling in all my users from LDAP, so I cant just look at the users page. Ill have to look the SQL way. As for the Global permissions, there are only 10 users with those permissions, and that is why I am so confused. It was working for a day or two, and then just stopped.

I thought about the super user as well, but, I disabled that account. (I know, it is not recommended).

[AnnWorley]

I understand you are pulling in more users from LDAP than you are granting Global can-use permission to. In that case, if you cannot chase down the extra user in the UI, the SQL queries should hopefully do the job. I hope you have time to update this thread to tell us how it turns out.

[Kevin Pelzman]

Ha! it shows 9 users (I removed one from one of the groups so that I was able to edit pages.

[AnnWorley]

I am curious how you generated the list you posted.

I expected the results of the query to look something like this, so we could see what directory each licensed user is in:

The list shows 9 users but the Licensed users field says 10. Because of aggregating group membership, you can remove a user from a group in one directory but it can retain membership in that group in another directory Managing Multiple Directories:

If the same username exists in more than one directory, the application will aggregate (combine) group membership from all directories where the username appears.

That means a user can be removed from a group in the LDAP user directory, but if the same username is in the Confluence Internal directory it could retain the membership there and be granted permission to use Confluence.

Please try the queries from How to get a list of active users counting towards the Confluence license. They aren't perfect but may give us a hint as to why we can't account for all your licensed users.

[Kevin Pelzman]

The output of the first screenshot was with using that querie from the link you posted. I do not have any of the users duplicated in any of the groups, so that cant be the problem. I have three groups that have access; they are shown in the screenshots.