I have 10 users set up but it is showing 11

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

2 answers

1 accepted

This widget could not be displayed.
Ann Worley Atlassian Team Dec 07, 2017

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.

yeah, this is what I get from the SQL query2017-12-07 13_37_12-mRemoteNG - confCons.xml - COWSQL-4.png

Ann Worley Atlassian Team Dec 07, 2017

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;

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.

2017-12-07 14_40_14-mRemoteNG - confCons.xml - COWSQL-4.png

Ann Worley Atlassian Team Dec 07, 2017

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.

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

Ann Worley Atlassian Team Dec 07, 2017

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.

This widget could not be displayed.
Ann Worley Atlassian Team Dec 06, 2017

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

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).

Ann Worley Atlassian Team Dec 06, 2017

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.

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

 

2017-12-06 15_27_59-fdgdf - Infrastructure - City of Waukesha.png2017-12-06 15_29_13-License Details - City of Waukesha.png

Ann Worley Atlassian Team Dec 06, 2017

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:

Screen Shot 2017-12-06 at 3.59.59 PM.png

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.

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.2017-12-07 09_02_48-Group Members_ HelpDeskAdmins - City of Waukesha.png2017-12-07 09_03_10-Group Members_ NetworkAdmins - City of Waukesha.png2017-12-07 09_03_29-Group Members_ DevOpsAdmins - City of Waukesha.png

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Published yesterday in Confluence

Add-on evaluation with confluence templates

Atlassian market place contains number of Apps/Addons which improves the capability of out of the box Atlassian products. It is good to follow a plugin evaluation process before install add-ons. So t...

38 views 5 3
Read article

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