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

関連コンテンツ

  • 関連コンテンツがありません

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

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.

*Fisheye および Crucible は除く

注意:

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.

目的

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.

ソリューション

  • 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

    メモ:  

    • 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
説明 Knowledge 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.
製品Fisheye, Crucible

最終更新日 2021 年 11 月 30 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する

関連コンテンツ

  • 関連コンテンツがありません
Powered by Confluence and Scroll Viewport.