Reasons Why You Would Do This

Confluence was set up with an external user directory that does not automatically place users in a given group.  A number of people log in, but are confronted without having sufficient permissions to access Confluence.  The external user directory is then set up to have users automatically placed in a permitted group.  We now need to go back and find those users that have logged in, but are not a part of a permitted group and thus are denied access.  This is best done through a database query

The Query

• Determine the default group name from your External User Directory connector.

  select user_name
  from cwd_user 
  where id not in (	select m.child_user_id 
  					from cwd_membership m 
  					left join cwd_group g on (m.parent_id = g.id) 
  					where g.group_name = '<group_name>') 
  			
  and id in (			select user_id  
  					from cwd_user_attribute 
  					where attribute_name = 'lastAuthenticated')

  We are looking for the cwd_user_attribute 'lastAuthenticated" because it is created only after the user has logged on.

  The other fields are of dubious use in this case

  • "requiresPasswordChange"
  • "passwordLastChanged"
  • "lastAuthenticated"
  • "lastAuthenticated"
  • "invalidPasswordAttempts"