How to retrieve a list of users and groups granted permissions to repositories and projects from the database

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

Please note:

The SQL queries seen in this article have been tested against Fisheye / Crucible 4.8.6 and MySQL 5.7 / PostgreSQL 9.6 databases. There is no guarantee they will work with other Fisheye / Crucible versions, MySQL versions, PostgreSQL versions, or another database type.

Purpose

In Fisheye context, each repository might have a different set of permissions configured.

In Crucible context, projects use permission schemes, and these are granular because each of the 15 actions may be granted to specific users, specific groups, specific review roles, to all logged in users, or to anonymous users.

Therefore, it might be quite challenging to have a complete list of which repositories have permissions granted to users and groups, and a list of actions specific people can perform in Crucible reviews.

Fisheye and Crucible permissions are stored at database level, and in this article you will find the SQL queries that can be used for retrieving that information for all repositories at once, and all projects at once.

Solution

  • For retrieving a list of Fisheye repositories that have specific permissions configured this SQL query can be used (compatible with MySQL and PostgreSQL):

    (
    SELECT uperm.cru_repository_name AS "Repository Name", 
           usr.user_name AS "User / Group Name", 
           uperm.cru_repository_permission  AS "Permission"
    FROM   cru_repo_user_perm uperm
    INNER JOIN cwd_user usr ON uperm.cru_user_id = usr.id
    )
    UNION ALL
    (
    SELECT gperm.cru_repository_name AS "Repository Name", 
           gperm.cru_group_name AS "User / Group Name", 
           gperm.cru_repository_permission  AS "Permission"
    FROM   cru_repo_group_perm gperm
    )
    ORDER BY 1, 2

    In a local instance, this was the query output:

    Repository Name  User / Group Name  Permission  
    ---------------  -----------------  ----------  
    Git              testuser           CAN_READ    
    Git              testuser           IS_ADMIN    
    SVN              user1              CAN_READ    
    SVN              user1              IS_ADMIN    
    SVN              internal-group     CAN_READ    

    Notes: 

    • User testuser is an administrator of Git repository. Due to that, this user also has implicit CAN_READ permission. The same thing happens with user user1 and repository SVN.

    • Group internal-group is not administrator of SVN repository, it just has CAN_READ permission.
    • As can be seen, all possible permission levels for each user and group is returned by the query, not only the highest permission.
    • Please note, though, that repositories may not have any specific permissions set, and in that case any user or group granted "Fisheye User" access type at Global Permissions will be able to find and browse these repositories.

  • For retrieving a list of projects, their respective permission schemes, and which actions are granted to which users, groups or roles, this SQL query can be used:

    Click here to see the query compatible with MySQL
    (
    SELECT proj.cru_name AS "Project Name",
           ps.cru_name AS "Permission Scheme",
    	   "Anonymous" AS "Grantee",
           psAnon.cru_action_name AS "Permissions Granted"
    FROM   cru_project AS proj
    INNER JOIN cru_perm_scheme AS ps     ON proj.cru_permission_scheme = ps.cru_ps_id
    INNER JOIN cru_ps_anon     AS psAnon ON psAnon.cru_ps = ps.cru_ps_id
    )
    UNION ALL
    (
    SELECT proj.cru_name AS "Project Name",
           ps.cru_name AS "Permission Scheme",
    	   "All Logged In" AS "Grantee",
           psAllUsr.cru_action_name AS "Permissions Granted"
    FROM   cru_project AS proj
    INNER JOIN cru_perm_scheme AS ps       ON proj.cru_permission_scheme = ps.cru_ps_id
    INNER JOIN cru_ps_all_user AS psAllUsr ON psAllUsr.cru_ps = ps.cru_ps_id
    )
    UNION ALL
    (
    SELECT proj.cru_name AS "Project Name",
           ps.cru_name AS "Permission Scheme",
    	   psGroup.cru_pid AS "Grantee",
           psGroup.cru_action_name AS "Permissions Granted"
    FROM   cru_project AS proj
    INNER JOIN cru_perm_scheme AS ps       ON proj.cru_permission_scheme = ps.cru_ps_id
    INNER JOIN cru_ps_group    AS psGroup ON psGroup.cru_ps = ps.cru_ps_id
    )
    UNION ALL
    (
    SELECT proj.cru_name AS "Project Name",
           ps.cru_name AS "Permission Scheme",
    	   psUser.cru_pid AS "Grantee",
           psUser.cru_action_name AS "Permissions Granted"
    FROM   cru_project AS proj
    INNER JOIN cru_perm_scheme AS ps     ON proj.cru_permission_scheme = ps.cru_ps_id
    INNER JOIN cru_ps_user     AS psUser ON psUser.cru_ps = ps.cru_ps_id
    )
    UNION ALL
    (
    SELECT proj.cru_name AS "Project Name",
           ps.cru_name AS "Permission Scheme",
           psRole.cru_pid AS "Grantee",
           psRole.cru_action_name AS "Permissions Granted"
    FROM   cru_project AS proj
    INNER JOIN cru_perm_scheme     AS ps     ON proj.cru_permission_scheme = ps.cru_ps_id
    INNER JOIN cru_ps_review_role  AS psRole ON psRole.cru_ps = ps.cru_ps_id
    )
    ORDER BY 1, 3
    Click here to see the query compatible with PostgreSQL
    (
    SELECT proj.cru_name AS "Project Name",
           ps.cru_name AS "Permission Scheme",
    	   'Anonymous' AS "Grantee",
           psAnon.cru_action_name AS "Permissions Granted"
    FROM   cru_project AS proj
    INNER JOIN cru_perm_scheme AS ps     ON proj.cru_permission_scheme = ps.cru_ps_id
    INNER JOIN cru_ps_anon     AS psAnon ON psAnon.cru_ps = ps.cru_ps_id
    )
    UNION ALL
    (
    SELECT proj.cru_name AS "Project Name",
           ps.cru_name AS "Permission Scheme",
    	   'All Logged In' AS "Grantee",
           psAllUsr.cru_action_name AS "Permissions Granted"
    FROM   cru_project AS proj
    INNER JOIN cru_perm_scheme AS ps       ON proj.cru_permission_scheme = ps.cru_ps_id
    INNER JOIN cru_ps_all_user AS psAllUsr ON psAllUsr.cru_ps = ps.cru_ps_id
    )
    UNION ALL
    (
    SELECT proj.cru_name AS "Project Name",
           ps.cru_name AS "Permission Scheme",
    	   psGroup.cru_pid AS "Grantee",
           psGroup.cru_action_name AS "Permissions Granted"
    FROM   cru_project AS proj
    INNER JOIN cru_perm_scheme AS ps       ON proj.cru_permission_scheme = ps.cru_ps_id
    INNER JOIN cru_ps_group    AS psGroup ON psGroup.cru_ps = ps.cru_ps_id
    )
    UNION ALL
    (
    SELECT proj.cru_name AS "Project Name",
           ps.cru_name AS "Permission Scheme",
    	   psUser.cru_pid AS "Grantee",
           psUser.cru_action_name AS "Permissions Granted"
    FROM   cru_project AS proj
    INNER JOIN cru_perm_scheme AS ps     ON proj.cru_permission_scheme = ps.cru_ps_id
    INNER JOIN cru_ps_user     AS psUser ON psUser.cru_ps = ps.cru_ps_id
    )
    UNION ALL
    (
    SELECT proj.cru_name AS "Project Name",
           ps.cru_name AS "Permission Scheme",
           psRole.cru_pid AS "Grantee",
           psRole.cru_action_name AS "Permissions Granted"
    FROM   cru_project AS proj
    INNER JOIN cru_perm_scheme     AS ps     ON proj.cru_permission_scheme = ps.cru_ps_id
    INNER JOIN cru_ps_review_role  AS psRole ON psRole.cru_ps = ps.cru_ps_id
    )
    ORDER BY 1, 3
  • In a local instance that had only the Default Project, which used the agile permission scheme, the permission scheme was edited so as to grant Submit permission to an individual user whose username was "user1", and to grant Uncomplete permission to a specific group named "internal-group". This was the query output:

    Project Name     Permission Scheme  Grantee         Permissions Granted       
    ---------------  -----------------  --------------  ------------------------  
    Default Project  agile              All Logged In   action:viewReview         
    Default Project  agile              All Logged In   action:createReview       
    Default Project  agile              All Logged In   action:commentOnReview    
    Default Project  agile              Anonymous       action:viewReview         
    Default Project  agile              Author          action:approveReview      
    Default Project  agile              Author          action:rejectReview       
    Default Project  agile              Author          action:abandonReview      
    Default Project  agile              Author          action:recoverReview      
    Default Project  agile              Author          action:modifyReviewFiles  
    Default Project  agile              Author          action:deleteReview       
    Default Project  agile              Author          action:viewReview         
    Default Project  agile              Author          action:commentOnReview    
    Default Project  agile              Author          action:submitReview       
    Default Project  agile              Author          action:closeReview        
    Default Project  agile              Author          action:reopenReview       
    Default Project  agile              Creator         action:commentOnReview    
    Default Project  agile              Creator         action:submitReview       
    Default Project  agile              Creator         action:closeReview        
    Default Project  agile              Creator         action:reopenReview       
    Default Project  agile              Creator         action:approveReview      
    Default Project  agile              Creator         action:rejectReview       
    Default Project  agile              Creator         action:abandonReview      
    Default Project  agile              Creator         action:recoverReview      
    Default Project  agile              Creator         action:modifyReviewFiles  
    Default Project  agile              Creator         action:deleteReview       
    Default Project  agile              Creator         action:viewReview         
    Default Project  agile              Moderator       action:deleteReview       
    Default Project  agile              Moderator       action:viewReview         
    Default Project  agile              Moderator       action:commentOnReview    
    Default Project  agile              Moderator       action:submitReview       
    Default Project  agile              Moderator       action:closeReview        
    Default Project  agile              Moderator       action:reopenReview       
    Default Project  agile              Moderator       action:approveReview      
    Default Project  agile              Moderator       action:rejectReview       
    Default Project  agile              Moderator       action:abandonReview      
    Default Project  agile              Moderator       action:recoverReview      
    Default Project  agile              Moderator       action:modifyReviewFiles  
    Default Project  agile              Reviewer        action:closeReview        
    Default Project  agile              Reviewer        action:uncompleteReview   
    Default Project  agile              Reviewer        action:reopenReview       
    Default Project  agile              Reviewer        action:recoverReview      
    Default Project  agile              Reviewer        action:modifyReviewFiles  
    Default Project  agile              Reviewer        action:completeReview     
    Default Project  agile              Reviewer        action:commentOnReview    
    Default Project  agile              Reviewer        action:viewReview         
    Default Project  agile              internal-group  action:uncompleteReview   
    Default Project  agile              user1           action:submitReview       
DescriptionKnowledge base article compatible with Fisheye 4.8.6 and MySQL 5.7 / PostgreSQL 9.6 explaining how to retrieve a list of permissions set to specific Fisheye repositories and to Crucible projects directly from the database.
ProductFisheye, Crucible

Last modified on Nov 30, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.