How to Get a List of Projects that has Anonymous Access in Jira from Database

Still need help?

The Atlassian Community is here for you.

Ask the community

The information in this page relates to SQL queries for reporting purpose. Atlassian Support cannot guarantee to provide any support for the steps described on this page as using SQL for business intelligence is beyond the scope of Atlassian Support Offerings. Please be aware that this material is provided for your information only and that you use it at your own risk. Please try the workaround/resolution on your staging/development/testing Jira instance database before performing any changes on production Jira instance database.

Summary

The purpose of this article is to show how to retrieve a list of projects that have Anonymous Access, which means Browse Project permission is granted to Anyone. There are multiple tables involved in the SQL queries to get a meaning return result. 

Workaround

  • Log in to Jira database.

  • Run the following SQL command :

    SELECT p.id, p.pname, ps.name FROM project p
    INNER JOIN nodeassociation na ON
    p.id = na.source_node_id
    INNER JOIN schemepermissions sp ON
    na.sink_node_id = sp.scheme
    INNER JOIN permissionscheme ps ON
    na.sink_node_id = ps.id
    WHERE na.source_node_entity = 'Project'
    AND na.sink_node_entity = 'PermissionScheme'
    AND sp.permission_key='BROWSE_PROJECTS'
    AND sp.perm_type='group' 
    AND sp.perm_parameter is null

    (info) This SQL is only tested on PostgreSQL. For other database, the SQL may need to be altered to suit the syntax.

  • The result would be, for example :

       id  | pname |                     name
    -------+-------+------------------------------------------------------
     10200 | MOGA1 | Default Permission Scheme
     10000 | TEST  | Default Permission Scheme
     10101 |  SD2  | Jira Service Management Permission Scheme for Project SDT

Last modified on Nov 23, 2020

Was this helpful?

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