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

Summary

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

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 ( 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:

  1 2 3 4 5 6 7 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:

  MySQL query

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ( 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

  PostgreSQL query

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ( 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:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 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