How can I get a list of orphaned users in Stash?

In our Stash instance we use JIRA as the source for our user directory, and that user directory is specified in a corporate LDAP instance.  If the user has been deactivated in LDAP, I can see the user is not in the list of Users in Stash anymore.  However, the entry for the user name will still persist in the Global Permissions table as well as the permissions for a Project and a Repository. 

Is there a way to find these 'orphaned' entries so they can be cleaned up by an administrator?  In JIRA I can do this by running an SQL query that checks data from project against cwd_user.  Is there a similar query that can be done in Stash?

2 answers

Wayne,

You could try using our Add-on ScriptRunner for Stash which has recently become available in the Atlassian Marketplace.

With this Add-on you can run your own groovy scripts using the Script Console.

You could try using this snippet to get a list of your inactive users:

import com.atlassian.sal.api.component.ComponentLocator
import com.atlassian.stash.user.UserService
import com.atlassian.stash.util.PageRequestImpl
def userService = ComponentLocator.getComponent(UserService)
userService.findUsers(new PageRequestImpl(0, 9999)).values.findAll{
    user -> 
        user.isActive()
}

Or you can find it in our BitBucket Snippet.

I've not been able to test this properly so please let me know if you try it and get any problems.

I looked at the schema, and the following seemed to do the job; could someone please verify? -- To get a list of orphaned users in the Global Permissions: select * from cwd_user cu, sta_normal_user nu, sta_global_permission gp where gp.user_id = nu.user_id and nu.name = cu.user_name and (cu.is_active = 'F' or nu.deleted_timestamp is not null); -- To get a list of orphaned users from the Project permissions: select * from cwd_user cu, sta_normal_user nu, sta_project_permission pp where pp.user_id = nu.user_id and nu.name = cu.user_name and (cu.is_active = 'F' or nu.deleted_timestamp is not null); -- To get a list of orphaned users from the Repository permissions select * from cwd_user cu, sta_normal_user nu, sta_repo_permission rp, repository r where rp.user_id = nu.user_id and nu.name = cu.user_name and (cu.is_active = 'F' or nu.deleted_timestamp is not null);

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Aug 21, 2018 in Bitbucket

Branch Management with Bitbucket

As a project manager, I have discovered that different developers want to bring their previous branching method with them when they join the team. Some developers are used to performing individual wo...

2,350 views 9 12
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