How to update project permission automacitally? Edited

Hi Support,

I use the following SQL to get the jira project's last updated time as following:

SELECT DISTINCT i.PROJECT, MAX(i.UPDATED) as "Last Updated", p.pname
FROM jiraissue i
INNER JOIN project p
ON p.ID = i.PROJECT
GROUP BY i.PROJECT, p.pname
ORDER BY MAX(i.UPDATED) ASC, i.PROJECT, p.pname

I want to set a scripts to update those projects(that are not updated before one year) to read-only permission,Do you have any suggestions? Thank you!

 

2 answers

3 votes
Alexey Matveev Community Champion Jan 10, 2018

Hello,

Do not update Jira database. Use Jira Api to do that. From your post I could not figure out what exactly you want to update. Could you give more details?

I do not update the DB, I just use those SQL to get project last update time and then I want to change the original permission Scheme to Read-only permission,is it possible to do it use api ?How to use it ?thank you!

Alexey Matveev Community Champion Jan 10, 2018

You mean that you do not want let anybody to change a permission scheme? What do you mean by original permission scheme?

1.For example,the attachment is SQL's result

2.Those project's Permissions all set to Standard_Permission_Scheme,I want to use api or CLI plugins to bulk update project(Example:project A,B,C) permission to Readonly_Permission_Scheme

How could I do it  use api ?

Could you please give me some suggestions ?

Thank you123.PNG

Original project permission( Standard_Permission_Scheme) is Administrators,Developers and Testers has all has other permission,but Readonly permission(Readonly_Permission_Scheme) is just has browse project permission

So the question is: How can the Permission Scheme set on a project be changed programmatically? Is that correct?

Alexey Matveev Community Champion Jan 11, 2018

You would need to iterate over all projects you need and then execute something like this

Project = ComponentAccessor.getProjectManager().getProjectByCurrentKey("projectKey")

String permissionScheme = "Readonly_Permission_Scheme"

ComponentAccessor.getPermissionSchemeManager().addSchemeToProject(project, ComponentAccessor.getPermissionSchemeManager().getSchemeObject(permissionScheme))

 Kindly try first in a test environment. Do not do it in production. I did not try the code.

YES!You are Right!I just want to ask: How can the Permission Scheme set on a project be changed programmatically?

I also want to set it to crontab job,is it possible to do it ?

Project = ComponentAccessor.getProjectManager().getProjectByCurrentKey("projectKey")

String permissionScheme = "Readonly_Permission_Scheme"

ComponentAccessor.getPermissionSchemeManager().addSchemeToProject(project, ComponentAccessor.getPermissionSchemeManager().getSchemeObject(permissionScheme))

But the above code can't get the last updated project's time stamp,could it be integrate into SQL ?  

I like the purpose of this question (archiving old projects automatically after some months from last update on an issue) as well as Alexey's invaluable input.

But How can I do it through api  programmatically?Do you have any ideas about it ?

Alexey Matveev Community Champion Jan 11, 2018

Do you have ScriptRunner?

Alexey Matveev Community Champion Jan 11, 2018

Ok, I could write such a script using Jira Api (just select issues and find max date in a stream) but it would take a bit more time  than I can afford right now :) You can execute your sql query to find this information. You can find an example on how to execute an SQL query in Scriptrunner here:

https://scriptrunner.adaptavist.com/latest/jira/recipes/misc/connecting-to-databases.html#_querying_the_current_jira_database

Then you can write a service also with Scriptrunner and execute your script on schedule.

https://scriptrunner.adaptavist.com/4.3.0/jira/services.html

Hi Alexey, I will tried the above Scriptrunner methods,but I am a beginner in JIRA Scriptrunner ,How long will it be release this function(one month?)? I can wait for the important code,or could you please give me more direction about it?

Thanks for your great support in advance.

Alexey Matveev Community Champion Jan 11, 2018

Do you mean the function to define last updated date? I do not work for Scriptrunner. That is why I do not know. But you can do everything right now yourself. Try to make a script. If you have any problems then post the problem here.

I means is it can only through Script runner to do it ?

I will tried first but not sure it is work.

So I will also wait if you have another time to develop this function.Thank you in advance.

1.Iterate all jira project and pick the project that hasn't updated before one year.

2.Write a script to change the above project's permission scheme.

3.Write a service to schedule the jobs.

 

Thank You!

Alexey Matveev Community Champion Jan 11, 2018

Not only Scriptrunner, I guess. But I just know how to do it in Scriptrunner or develop a custom plugin. Ok. If I have time I will write the script.

Hi Support,

We hope these function will be release for 2018 Q1. It is very important for us,by the way,I will try to develop it ,If I have any problems I will post to it,Thanks a lot.

Hi Support,

If we write a groovy scripts and want to put it into services,How could we use the scriptrunner's service function?

I test the script in Script Console it works,but when I put it to services,it didn't work.

I follow the SOP as below steps:

https://scriptrunner.adaptavist.com/latest/jira/services.html

Alexey Matveev Community Champion Jan 28, 2018

Could you show the script and kindly have a look at the errors in the log file.

Hi Support,

we run it manually is ok, but set it to service has problem.

Could you please help to solve this problem.

Thank you!

0129_new.PNG 

Alexey Matveev Community Champion Jan 28, 2018

You would need to give more details about the proble. The error, which you get, would enough

There are no error occurred.So my main questions is how to set groovy scripts into service by use script runner.

Thank you!

Hi Support,

error message as below:

WARN anonymous    set_readonly_services [c.a.jira.ofbiz.ConnectionPoolHealthSqlInterceptor] Dangerous use of multiple connections: replaced => count=0; marks=[-1-3]; pool=0/20

How could I fix this problem?

By the way,any suggestion value about 'pool-max-size' ?We use default value:20 but found this error occurred.

Thank you!

Hi Support,

Could you please give me some suggestions?

Thank you!

Please respond. This is urgent

Alexey Matveev Community Champion Feb 12, 2018

You can find how to create service with ScriptRunner here:

https://scriptrunner.adaptavist.com/5.3.1/jira/services.html

About the warning you can read more here:

https://jira.atlassian.com/browse/JRASERVER-45794

Basically it means that your sql query performs too long and Jira warns you about it. It is not a error, it is a warning.

Hi Support,

So this warning can be ignore?Or any suggestion value about 'pool-max-size' ?We use default value:20

Thank you!

Alexey Matveev Community Champion Feb 13, 2018

The problem is not about poos-max-size. The problem is about your SQL queries which execute too long. If you can not optimize your SQL queries, then you can ignore it. But it could be a peformance problem later.

Hi Support,

How could I tuning the SQL statement ?

Please advice in advance,thank you!

2 votes
Daniel Wester Community Champion Jan 13, 2018

Take a look at Jira’s rest api in particular the update project end point. https://docs.atlassian.com/software/jira/docs/api/REST/7.6.1/#api/2/project-updateProject

That will let you change the permission scheme for a project. 

Hi Support,

If you have anything new to release, please notify me as soon as possible.

Thank you!

Thank you for the link!

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Jira

Looking for anyone who made the switch to Data Center

The Jira Marketing team is putting together an ebook on migrating to Data Center. We're looking for pro tips on how you staffed your project team and organized your Proof of Concept. Share yo...

33 views 0 2
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you