Symptoms

One or more issues are not searchable. Trying to access any of them e.g. ABC-12345 via direct URL displays the message "Permission Violation":

Diagnosis

The user who tries to search for and access the issues already has Browse Projects permission. Granting him access to all available Security Levels of the project's issue security scheme doesn't seem to enable him to search for and access the issues.

Cause

For some unknown reason, the project is using an issue security scheme A, but the issues' security level is referring to a different issue security scheme B. The following SQL queries will help identify these data inconsistencies:

1. Find the issue security scheme being used by the project (replace ABC with the correct project key):

   SELECT 
       p.pname,
       na.sink_node_id,
       na.sink_node_entity,
       iss.name
   FROM 
       nodeassociation na 
       INNER JOIN project p ON na.source_node_id = p.id 
       INNER JOIN issuesecurityscheme iss ON na.sink_node_id = iss.id 
   WHERE 
       na.sink_node_entity = 'IssueSecurityScheme' 
       AND p.pkey = 'ABC';

   pname	sink_node_id	sink_node_entity	name
   ABC	12611	IssueSecurityScheme	ABC Issue Security Scheme

   Take note of the ID of the issue security scheme being used by the project (12611 in this case).

2. Find the security level associated with the affected issue (replace ABC and 12345 with the correct project key (pkey) and issue number (issuenum), respectively):

   SELECT DISTINCT 
       ss.scheme,
       ss.security,
       sl.name 
   FROM 
       schemeissuesecurities ss 
       INNER JOIN schemeissuesecuritylevels sl ON ss.security = sl.id
   	INNER JOIN jiraissue j ON ss.security = j.security 
       INNER JOIN project p ON j.project = p.id 
   WHERE 
       p.pkey = 'ABC' 
       AND j.issuenum = 12345;

   scheme	security	name
   12410	14415	Normal

   Here the issue is having security level 14415, named "Normal", but this security level belongs to issue security scheme ID 12410, which is not expected (not used by the project)

   If this is the case, then this article applies to the issue you are facing, so please refer to the Resolution below

Resolution

Make a full, proper backup of the database before attempting any Update queries.

1. Select all the issues from ABC which may be having the same problem (replace 12611 with the correct issue security scheme ID currently used by the project (discovered above) - and replace ABC with the correct project key):

   SELECT
       issuenum,
       security 
   FROM 
       jiraissue 
   WHERE 
       security NOT IN (
           SELECT
               security
           FROM
               schemeissuesecurities 
           WHERE 
               scheme = 12611
           ) 
       AND project = (
           SELECT 
               id
           FROM
               project
           WHERE
               pkey = 'ABC'
           );

2. Find the available security levels of the issue security scheme used by the project (replace 12611 with the correct issue security scheme ID):

   SELECT DISTINCT
       ss.scheme,
       ss.security,
       sl.name 
   FROM
       schemeissuesecurities ss
       INNER JOIN schemeissuesecuritylevels sl ON ss.security = sl.id
   WHERE 
       ss.scheme = 12611;

   scheme	security	name
   12611	15115	Admin

   Here, only one security level is available, whose ID is 15115 and name is Admin - there may be more in your case

3. Update all the affected issues returned by the first query (step 1) to use the security level to which users who are supposed to have access belong:

   UPDATE 
       jiraissue
   SET
       security = 15115
   WHERE 
       security NOT IN (
           SELECT 
               security
           FROM
               schemeissuesecurities 
           WHERE 
               scheme = 12611
           ) 
       AND project = (
           SELECT
               id
           FROM
               project
           WHERE 
               pkey = 'ABC'
           );

   The users belonging to the Admin security level should be able to access all issues now.