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

Question

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?

Mark McCormack (Adaptavist) · Answer

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.

Wayne Wylupski · Answer

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