Current Assignee in Issue Security Level fired high numbers of SQL queries of different project to database

Steps to Reproduce

1. Create a issue security schemes with 3 security level.
2. Each security level, include Current Assignee.
3. Create 2 projects and associated with the issue security scheme
4. Go to logging and profiling, turn on SQL DEBUG and dump log.
5. Browse any project available.
6. There should be 6 records of such queries in the SQL log.

   2014-05-05 21:14:16,292 http-bio-9617-exec-7 jira 1274x243x1 1bj5x68 /browse/TEST 1ms "SELECT COUNT(I.ID) FROM public.jiraissue I WHERE I.PROJECT='16201' AND I.ASSIGNEE='jira'"
   2014-05-05 21:14:16,292 http-bio-9617-exec-7 jira 1274x243x1 1bj5x68 /browse/TEST call stack
   	...
   	at com.atlassian.jira.project.DefaultProjectManager.isAssignee(DefaultProjectManager.java:628)
   	at com.atlassian.jira.project.DefaultProjectManager.isAssignee(DefaultProjectManager.java:635)
   	at com.atlassian.jira.project.CachingProjectManager.isAssignee(CachingProjectManager.java:254)
   	at com.atlassian.jira.security.type.CurrentAssignee.hasProjectPermission(CurrentAssignee.java:88)
   	at com.atlassian.jira.security.type.AbstractIssueFieldSecurityType.hasPermission(AbstractIssueFieldSecurityType.java:228)
   	at com.atlassian.jira.issue.search.parameters.lucene.DefaultPermissionQueryFactory.userHasPermissionForProjectAndSecurityType(DefaultPermissionQueryFactory.java:227)
   	at com.atlassian.jira.issue.search.parameters.lucene.DefaultPermissionQueryFactory.collectSecurityLevelTerms(DefaultPermissionQueryFactory.java:196)
   	at com.atlassian.jira.issue.search.parameters.lucene.DefaultPermissionQueryFactory.getQuery(DefaultPermissionQueryFactory.java:92)
   	at com.atlassian.jira.issue.search.parameters.lucene.PermissionsFilterGeneratorImpl.getQuery(PermissionsFilterGeneratorImpl.java:38)
   	at com.atlassian.jira.issue.search.providers.LuceneSearchProvider.getPermissionsFilter(LuceneSearchProvider.java:522)
   	at com.atlassian.jira.issue.search.providers.LuceneSearchProvider.getHitCount(LuceneSearchProvider.java:244)
   	at com.atlassian.jira.issue.search.providers.LuceneSearchProvider.searchCount(LuceneSearchProvider.java:116)
   	at com.atlassian.jira.plugin.projectpanel.fragment.impl.AbstractFragment.getIssueCountInSearch(AbstractFragment.java:94)
   	at com.atlassian.jira.plugin.projectpanel.fragment.impl.CreatedVsResolvedFragment.showFragment(CreatedVsResolvedFragment.java:60)
   	at com.atlassian.jira.plugin.browsepanel.AbstractFragmentBasedTabPanel.appendFragmentIfVisible(AbstractFragmentBasedTabPanel.java:192)
   	at com.atlassian.jira.plugin.browsepanel.AbstractFragmentBasedTabPanel.createColumnContent(AbstractFragmentBasedTabPanel.java:176)
   	at com.atlassian.jira.plugin.browsepanel.AbstractFragmentBasedTabPanel.createContent(AbstractFragmentBasedTabPanel.java:116)
   	at com.atlassian.jira.plugin.browsepanel.AbstractFragmentBasedTabPanel.getHtml(AbstractFragmentBasedTabPanel.java:42)
   	at com.atlassian.jira.web.action.browser.BrowseProject.getTabHtml(BrowseProject.java:224)
   	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   	at java.lang.reflect.Method.invoke(Method.java:606)
   	at webwork.util.InjectionUtils$DefaultInjectionImpl.invoke(InjectionUtils.java:70)
   	at webwork.util.InjectionUtils.invoke(InjectionUtils.java:56)
   	at webwork.util.ValueStack.findValue(ValueStack.java:417)
   	at webwork.util.ValueStack.findValue(ValueStack.java:216)
   	at webwork.view.taglib.WebWorkBodyTagSupport.findValue(WebWorkBodyTagSupport.java:62)
   	at webwork.view.taglib.BasicPropertyTag.doStartTag(BasicPropertyTag.java:54)
   	at org.apache.jsp.secure.views.browser.browseproject_jsp._jspx_meth_ww_005fproperty_005f19(browseproject_jsp.java:2604)
   	at org.apache.jsp.secure.views.browser.browseproject_jsp._jspx_meth_ui_005fparam_005f23(browseproject_jsp.java:2572)
   	at org.apache.jsp.secure.views.browser.browseproject_jsp._jspx_meth_ui_005fsoy_005f9(browseproject_jsp.java:2440)
   	at org.apache.jsp.secure.views.browser.browseproject_jsp._jspx_meth_ui_005fparam_005f19(browseproject_jsp.java:2024)
   	at org.apache.jsp.secure.views.browser.browseproject_jsp._jspx_meth_ui_005fsoy_005f7(browseproject_jsp.java:1980)
   	at org.apache.jsp.secure.views.browser.browseproject_jsp._jspService(browseproject_jsp.java:161)
   	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
   	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
   	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
   	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
   	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
   	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
   	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
   	...
   

Expected Behaviour:

The permission checking should involve the project that browse by user but not all the projects configured with such security level.

Actual Behaviour/How many SQL Queries could be fired in just one Browse Project action:

• Each Current Assignee in Security Level fired one SQL SELECT query.
• So 1 issue security scheme with average 3 security levels using Current Assignee will generate 3 queries for each associated projects with the schemes.
• So if JIRA has 5 security schemes with following setup, it would generate 15 queries(5 * 3).
• So if customer has 200 projects attached to such issue security schemes, it means that it would generate 3000 SQL Queries(200 x 15) in just one action!

Notes:

• This happens when you browse ANY project and these queries will fired to check the permission. For example, if a user browse a project WITHOUT associated to such issue security scheme, this SQL queries will still continue to fire to the database.
• This can generate performance impact as there is JIRA instance experienced a maximum of 18 million such queries executed in the database due to high number of projects(eg. 200 projects) configured with issue security scheme. This could potentially overloaded the database server with 3 CPUs of 100% performance especially there are concurrent usage by the users.
• It seems generated as well when picking project one by one from issue navigator and accessing issue from issue navigator.