SQL: How to query JIRA's permission schemes

[Sameera Shaakunthala [inactive]]

JIRA's SCHEMEPERMISSIONS table consists of the following columns.

ID, SCHEME, PERMISSION, PERM_TYPE, PERM_PARAMETER

To where should I join PERMISSION column?

I couldn't find any table containing the set of permission types.

Thanks!

--

Shaakunthala

[Daniel Luevano · Accepted solution]

You can use this query (PostgreSQL):

SELECT PS.name AS PermissionSchemeName,SP.perm_parameter AS GroupName, SP.permission_key AS Permission

FROM schemepermissions SP

INNER JOIN permissionscheme PS ON SP.scheme = PS.id

ORDER BY 1, 2, 3;

[Cody Crandall]

This should be the top answer. Thanks Daniel!

[Leigh Simpson]

These are hardcoded constants within the JIRA codebase (see https://docs.atlassian.com/jira/latest/com/atlassian/jira/permission/Permission.html for Javadoc details of the `Permission` ENUM). There is a full list here: https://docs.atlassian.com/jira/latest/constant-values.html#com.atlassian.jira.security.Permissions.ADMINISTER

[AhmadDanial]

Hey there, Sameera.

Perhaps you can check on the following documentation. You might be able to find what you are looking for:

https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA

Warm regards,

Danial

[Sameera Shaakunthala [inactive]]

Looking at the following Javadoc, I think this is not something we can query using SQL.

http://docs.atlassian.com/jira/latest/com/atlassian/jira/security/Permissions.html

[Sameera Shaakunthala [inactive]]

I forgot to add a justification to what I said above.

According to the Javadoc provided, all the permission items are actually integer constants. They seem to be hardcoded into JIRA so it is not possible to retrieve that information using SQL.