ScriptRunner Database Picker field show projects current user has access to

[Robert Cook]

I am intending to use the "Database Picker" custom field offered by ScriptRunner in order to display the projects which the current user has permission to see.

I haven't found any solid answers about how I can modify the configuration script so far.

My "Retrieval/validation SQL":

select id, pname from project

  where id = cast(? as numeric)

My "Search SQL":

select id, pname from project

  where pname like concat(?, '%') 

My "Configuration Script":

import com.atlassian.jira.issue.Issue

import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters

getSearchSql = { String inputValue, Issue issue, String originalValue ->

    // return SqlWithParameters, that will control the search SQL..., eg:

    new SqlWithParameters("select * from project where name like concat(?, '%')", [inputValue])

[PD Sheehan · Accepted solution]

The permission model is quite complex. A user could have access to a project via multiple project roles or group and other configurations in the permissions scheme.

I would recommend you use the "custom picker" type of field instead.

This way you can just call "Projects.getAllProjects()" to get all the projects the current user has access to.

Or even the default Project Picker field type (available from the default customer field configuration page).