In this post, I would like to share a way of applying a security scheme to a JIRA project using SQL.
In many cases, you will not have to do that as JIRA administration console works perfectly fine in that respect. However, in my case, I needed to apply a security scheme to an existing project which had over 110,000 issues. I eventually managed to do that via JIRA administration console but in order to achieve that I had to add extra GB’s of RAM to my system (it may not be possible in many cases, especially if JIRA runs on 32-bit system) and the process itself was very time consuming, it took around 30+ hours. Application of a security scheme via SQL was a way quicker and completed in about 30 min.
Please note that this approach is not recommended by Atlassian (actually they do not recommend any direct database manipulations). Please make sure that you will take database backup before any SQL changes.
So let’s crack on. To associate a security scheme with a project, we need to perform the following steps:
- Shut down your JIRA system
- Find out project identifier
- Find out security scheme identifier
- Find out security level identifier
- Associate the project with the security scheme
- Associate the project issues with the security level.
- Start up your JIRA system
Shut down your JIRA system
It is recommended that JIRA is down during any database modifications, to add database changes as seamlessly as possible.
Find out project identifier
The project identifier can be taken from the
project table. We can use a project key (
FOO in my case) to get a project identifier:
SELECT id, pkey FROM project WHERE pkey = 'FOO'
And the results are like:
ID PKEY ------------------- ----------------- 11111 FOO
Find out security scheme identifier
The security scheme details are kept in
issuesecurityscheme table. Let’s find out the scheme identifier by its name (Test Security Scheme in my case):
SELECT id, name FROM issuesecurityscheme WHERE name='Test Security Scheme'
The output will show the identifier of the scheme we are looking for:
ID NAME ------------ --------------------- 10160 Test Security Scheme
Find out security level identifier
The security scheme level identifier details are kept in
schemeissuesecuritylevels table. Let’s find out all security levels in a given security scheme.
SELECT * FROM schemeissuesecuritylevels WHERE scheme = 10160
The output will have all security levels (two in my case) e.g.
ID NAME DESCRIPTION SCHEME ------- --------------- ---------------------------------- -------- 10490 Managers Only Managers can see the ticket 10160 10491 Users & Owner Users & Owner can see the ticket 10160
We would like to apply Users & Owner security level to all our existing issues so we will use
10491 security level identifier.
Associate the project with the security scheme
The project to security scheme association is held in
nodeassociation table. The following SQL command should do the trick of associating my project to the Test Security Scheme:
INSERT INTO nodeassociation (SOURCE_NODE_ID, SOURCE_NODE_ENTITY, SINK_NODE_ID, SINK_NODE_ENTITY, ASSOCIATION_TYPE, SEQUENCE) VALUES (11111, 'Project', 10160, 'IssueSecurityScheme', 'ProjectScheme', NULL)
Associate the project issues with the security level
Now we need to update
jiraissue table and assign security level identifier (
10491 in our case) to all issues in a given project. We can do that using the following SQL command:
UPDATE jiraissue SET security = 10491 WHERE project = 11111
Start-up JIRA and you should have a security scheme applied to your project.
This solution works fine with JIRA 6. I expect it to work with earlier version too; however, I have not tested that with other JIRA releases. And as mentioned above, make sure that you take database backup before any SQL changes to restore your system in case of any issues.