Identifying the difference between restricted and unrestricted comments in the Jira Database
Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.
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.
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;
|10000||10000||JIRAUSER10000||comment||(null)||(null)||This comment is available to everyone||2021-01-08 11:57:06||JIRAUSER10000||2021-01-08 11:57:06||(null)|
|10001||10000||JIRAUSER10000||comment||(null)||10002||This is a restricted comment restricted to administrators||2021-01-08 11:57:26||JIRAUSER10000||2021-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;
|(null)||This comment is available to everyone||(null)|
Restricted comments have the 'role level' set to the ID of the role(In this case Administrators).
Unrestricted comments have the 'rolelevel' as null.