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

  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