Question on SQL Scripts for Permission Scheme

[HaRsHaS' V]

I am trying to write a script to have Permission scheme and related details showing up like Permission type, who has been given permission and all.

select NAME, perm_parameter,perm_type, Permission_Key
FROM schemepermissions SP
INNER JOIN permissionscheme PS ON SP.scheme = PS.id

The script to a certain extent given the desired result. However, in the perm_parameter table, there are few Ids included, I understand that the original table contains them. However, how do I replace them and I need the exact permission parameter that suits them.

Please advice

Note @Sebastian Krzewiński

Tagging you here for visibility. Thanks!

[Moosh]

Hello, it's not the perfect answer for your need, bute theses 2 statements I just have write, works for me.

Perhaps it can be an inspiration

List of scheme , right, role  

SELECT
schemepermissions.scheme, 
 permissionscheme.id, 
permissionscheme.name,
 permissionscheme.description,
schemepermissions.permission,
schemepermissions.perm_type,
schemepermissions.perm_parameter,
schemepermissions.permission_key  right,
projectrole.name role
FROM
jira8.schemepermissions
LEFT JOIN jira8.permissionscheme
ON schemepermissions.scheme = permissionscheme.id
INNER JOIN jira8.projectrole
ON schemepermissions.perm_type = 'projectrole' and 
schemepermissions.perm_parameter = projectrole.id

ORDER BY schemepermissions.permission_key,role,permissionscheme.name
;

SAME but  role grouped on one line

SELECT
schemepermissions.permission_key droit,
permissionscheme.name,
LISTAGG(projectrole.name, ', ') WITHIN GROUP( ORDER BY projectrole.name ) "rôles"
FROM
jira8.schemepermissions
LEFT JOIN jira8.permissionscheme
ON schemepermissions.scheme = permissionscheme.id
LEFT JOIN jira8.projectrole
ON schemepermissions.perm_parameter = projectrole.id
WHERE schemepermissions.perm_type = 'projectrole'
GROUP BY
schemepermissions.permission_key,
permissionscheme.name
Order by
schemepermissions.permission_key,
permissionscheme.name
;