SQL Query to get list of projects where an user has access

Jira Development Jira Server
, ,
1

We need to generate a list of projects for an user having access

Basically we need like this - Userid, Projects, Project Category
Example
user1, project1, projectcategoryA
user1, project2, projectcategoryB
user2, project1, projectcategoryA
user2, project2, projectcategoryA

Believe this can be possible by SQL query or groovy or Rest

Any thoughts on this possibility ?

Thanks in advance

2

If you are writing an add-on for JIRA Server, you could get a list of users from the UserSearchService:

/**
 * Get Users based on a query string.
 * <p>
 * Matches on start of username, full name and email as well as all the tokenised (on spaces, '.', '@' etc) words 
 * Only returns active users.
 * <p>
 * Results are sorted according to the {@link com.atlassian.jira.issue.comparator.UserCachingComparator}.
 *
 * @param jiraServiceContext Jira Service Context
 * @param query              String to search for.
 * @return List of {@link ApplicationUser} objects that match criteria.
 *
 * @see #findUsers(JiraServiceContext, String, UserSearchParams)
 */
List<ApplicationUser> findUsers(JiraServiceContext jiraServiceContext, String query);

… and for each user you could then get a list of accessible projects from the PermissionManager:

/**
 * Retrieve a list of project objects this user has the permission for
 *
 * @param permissionKey must NOT be a global permission
 * @param user user
 * @return a collection of {@link Project} objects
 * @since v6.3
 */
Collection<Project> getProjects(@Nonnull ProjectPermissionKey permissionKey, @Nullable ApplicationUser user);

If you want/need to use the REST API instead, then you may have to do it the other way around: fetch a list of projects, and then search for users who have BROWSE_PROJECTS permission for each project.

2 Likes
3

Hi David,

We need such list for an JIRA account used as functional account for integration of HPQC with JIRA
In other words we use this list to find out which JIRA Projects are already enabled for Sync with HPQC, provided it is granted access for that project

Is there a possibility to run a SQL Query for such list, which could be good thought ?

4

Go for it. JIRA’s database schema is available.

I always baulk at going directly to the database, since it is subject to change in a manner that the published API is not; but heck, if it’s just a one-off report…

5

If you don’t want to build a full blown add-on for this use case and don’t want to go to the database schema, either, then you can export user accounts, their group memberships, project memberships, etc. to Excel using this tutorial:

excel-user-export.png920×450 35.9 KB

6

If he the target user issues the REST call, the result should already be scoped to his permissions.

7

That’s true. Since the user wants to produce a report (across multiple users) like this:

{code}
user1, project1, projectcategoryA
user1, project2, projectcategoryB
user2, project1, projectcategoryA
user2, project2, projectcategoryA
{code}

… I think he/she will need to run some requests against JIRA as an administrator.