Query to get the list of unmapped permission schemes,notification schemes and issuetype schemes

jayasingh January 7, 2014

Hi All,

Can any one please share the query to fetch the unmapped permission schemes,notification schemes and permission schemes.

Thanks & Regards,

Jayasingh

6 answers

0 votes
artafon June 23, 2016

Hi All!

Please, and what about  the query to fetch the unmapped issuetype schemes?

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 23, 2016

It's a negative question - you can't look for what isn't there.  You will need to read for all the issuetype schemes, then subtract the used ones from it.

Or, look in the UI, it's far easier, and you can use it to delete the unused ones (which you must not do in SQL)

artafon June 23, 2016

Nic, Hi!

I meant that I need to get list of projects that use default issuetype scheme, But this case I decided:

select p.id, p.pname from project p
where p.id NOT IN (select cc.project from configurationcontext cc where cc.customfield='issuetype' and cc.project is not NULL)

then, I need bulk change default issue type scheme on projects. How to do this, I don't know

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 23, 2016

There's no bulk-change for this stuff, you need to work through it one at a time.  It's important that you do it this way as changing schemes may result in needs to update projects and it will need to ask you questions.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 23, 2016

You'll still need to answer the migration questions manually.

artafon June 23, 2016

sorry, didn't understood smile

artafon June 23, 2016

I resolved with: https://bobswift.atlassian.net/wiki/display/JCLIP and 

jira --action updateProject --project "zjiracli" --issueTypeScheme "10000"

0 votes
Abinaya TDK January 26, 2014

Hi Nic,

I have developed query to fetch groupname which is unamapped with permission schemes.

Query:

SELECTdistinct group_name FROM CWD_GROUP
where group_name not in
(SELECT distinct perm_parameter FROM schemepermissions where perm_type='group');

here, schemepermissions and cwd_group are the two tables.In the schemepermission table we have perm_parameter field which shows the groupnames which are mapped with any permission schemes.In CWD_GROUP table we can see groupnames whichever we created even if it is not mapped with any permission schemes.So by using these both table we found a query to fetch the groupnames which is not mapped with any permission schemes.

So this is the another way to fetch groupnames which is not mapped with any permission schemes:):)

Thank you so much for guiding me to find the unmapped groups.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 27, 2014

So, you got the list of groups, then subtracted the groups in the permission schemes. Glad you got there in the end.

However, what about roles, group fields, filters, gadgets, notifications, workflows etc? I do hope you're not just planning to remove groups based on permissions, because there's LOTS of other places they're potentially used.

Abinaya TDK January 27, 2014

Yes we are considering all those.

Anyway thank you so much for your help in finding the table:)

Shradha Singh June 15, 2015

Hello Abinaya Can you help me in figuring out a sql query to fetch all permission schemes where 'Delete issue' permission is not set to empty? Thanks Shradha

0 votes
Abinaya TDK January 26, 2014

Hi Nic,

I have developed query to fetch groupname which is unamapped with permission schemes.

Query:

SELECTdistinct group_name FROM CWD_GROUP
where group_name not in
(SELECT distinct perm_parameter FROM schemepermissions where perm_type='group');

here, schemepermissions and cwd_group are the two tables.In the schemepermission table we have perm_parameter field which shows the groupnames which are mapped with any permission schemes.In CWD_GROUP table we can see groupnames whichever we created even if it is not mapped with any permission schemes.So by using these both table we found a query to fetch the groupnames which is not mapped with any permission schemes.

So this is the another way to fetch groupnames which is not mapped with any permission schemes:):)

Thank you so much for guiding me to find the unmapped groups.

0 votes
Abinaya TDK January 21, 2014

I am having 10999 groups. How i can manually find the groups which is not mapped with any permission scheme??

Please help me to find some way to cleanup the Groups which are not mapped with any permission scheme.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 21, 2014

Please, read the responses you are being given.

Asking the same question over and over without bothering to take account of what you are being told, and not adding any new information is not going to get you anywhere.

Abinaya TDK January 21, 2014

hello Nic,

I am not asking the same question again and again.. We need a sql query to delete all the groups which is not mapped with any permission scheme.

I am having a single question .. there is no relation between CWD_GROUP table and Node association table.. then how we will be able to make a query to fetch unused groups?.

For that you have answered "to get the full list of all groups, then the groups used in whereever you are looking and then subtract the list of used groups from the full list. The remainder will be the unused groups".

Its take huge time to delete.. so i have asked any other way is there to handle...

Any way thanks for your prompt response.

But for sure once i find solution will share the same:)

Thank you so much..

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 21, 2014

I'm afraid you have asked this over and over, in several places, and until today, you do not appear to have read any of the responses. Instead, you've posted the same question phrased in a slightly different way, either here, or on another thread.

Going back to the question, the answer has not changed. You need to get the list of all groups and subtract the used groups from it.

You have not yet really decided what a "used" group is yet either. Is it really just "used in permission scheme"? That is simple (and you've been pointed at SQL that can help you with it, albeit not the exact answer - we tend to ask you to think for yourself as you'll learn more if than if you just parrotted our SQL) - read the table schemepermissions for lines with perm_type of "group" and it will name the groups.

Or do you need to consider the other places a group can be used? Most of these *might* be indirectly used to grant access as well. Project roles, group custom fields, security schemes, gadgets, filters and workflows. I can't tell you whether you need to look for these, you need to analyse your system to work it out.

Abinaya TDK January 26, 2014

Hi Nic,

Developed query to fetch groups which is not mapped with permission schemes.

the query is,


SELECTdistinct group_name FROM CWD_GROUP
where group_name not in
(SELECT distinct perm_parameter FROM schemepermissions where perm_type='group');

Thanks for your support in finding schemepermissions teble:):)

0 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 21, 2014

You can't look for a negative.

You need to get the full list of all groups, then the groups used in whereever you are looking and then subtract the list of used groups from the full list. The remainder will be the unused groups.

Note that you also need to consider group-picker fields, filters and gadgets if you want to delete groups safely.

0 votes
Abinaya TDK January 19, 2014

please tell me the query to find unmapped groups from the project in Jira?????

Suggest an answer

Log in or Sign up to answer