Identifying the difference between restricted and unrestricted comments in the Jira Database

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Summary

In the Jira Database, restricted comments have the 'rolelevel' set to the ID of the group they are restricted to. 

Below we will demonstrate how to identify these issues. 


Diagnosis

We spun up a test instance and created two comments:

  • One Comment that is unrestricted
  • One comment that is restricted to Administrators

Next, we did a simple query to find out the difference between the two comments:

select * from jiraaction;

Results:

idissueidauthoractiontypeactionlevelrolelevelactionbodycreatedupdateauthorupdatedactionnum
1000010000JIRAUSER10000comment(null)(null)This comment is available to everyone2021-01-08 11:57:06JIRAUSER100002021-01-08 11:57:06(null)
1000110000JIRAUSER10000comment(null)10002This is a restricted comment restricted to administrators2021-01-08 11:57:26JIRAUSER100002021-01-08 11:57:26(null)


 From the results, it appears that:

  • Unrestricted comments have 'rolelevel' set to (null)
  • Restricted comments have the 'role level' set to the ID of the role(In this case Administrators)

Tying this together and modifying the query from How to find all the comments related to one user in the database:

SELECT P.pkey||'-'||JI.issuenum||Ja.rolelevel as issuekey, JA.actionbody, JA.rolelevel
FROM jiraaction JA
JOIN jiraissue JI ON JA.issueid = JI.id
JOIN project P ON JI.project = P.id
JOIN app_user U ON U.user_key = JA.author
WHERE JA.actiontype = 'comment'
and rolelevel is null;

Results:

issuekeyactionbodyrolelevel
(null)This comment is available to everyone(null)


Solution

Restricted comments have the 'role level' set to the ID of the role(In this case Administrators).

Unrestricted comments have the 'rolelevel' as null.


Last modified on Feb 23, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.