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