Quick update of security scheme in JIRA

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.

Warning! 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:

1. Shut down your JIRA system
2. Find out project identifier
3. Find out security scheme identifier
4. Find out security level identifier
5. Associate the project with the security scheme
6. Associate the project issues with the security level.
7. Start up your JIRA system

It is recommended that JIRA is down during any database modifications, to add database changes as seamlessly as possible.

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

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

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.

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)

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.