How to Determine Which Users Have Logged But Are Not A Member of a Group


アトラシアン コミュニティをご利用ください。


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 = 
    					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"
最終更新日: 2016 年 2 月 26 日


Powered by Confluence and Scroll Viewport.