I have 10 users split between three different groups which have access to different pages. For some reason Confluence thinks I have 11.
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:
<span>SELECT</span> <span>DISTINCT</span> u<span>.</span>lower_user_name<span>,</span> <span>d</span><span>.</span>directory_name <span>FROM</span> cwd_user u <span>JOIN</span> cwd_membership m <span>ON</span> u<span>.</span>id <span>=</span> child_user_id <span>JOIN</span> cwd_group g <span>ON</span> m<span>.</span>parent_id <span>=</span> g<span>.</span>id <span>JOIN</span> SPACEPERMISSIONS sp <span>ON</span> g<span>.</span>group_name <span>=</span> sp<span>.</span>PERMGROUPNAME <span>JOIN</span> cwd_directory <span>d</span> <span>on</span> u<span>.</span>directory_id <span>=</span> <span>d</span><span>.</span>id <span>WHERE</span> PERMTYPE<span>=</span><span>'USECONFLUENCE'</span> <span>AND</span> u<span>.</span>active <span>=</span> <span>'T'</span> <span>AND</span> <span>d</span><span>.</span>active <span>=</span> <span>'T'</span> <span>ORDER</span> <span>BY</span> <span>d</span><span>.</span>directory_name<span>;</span>
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.
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
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).
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.
yeah, this is what I get from the SQL query
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_nameFROM cwd_userGROUP BYlower_user_namehaving 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.
It looks like you're new here. Sign in or register to get started.
