Restore the Missing Object Schema Viewers role in Assets after JSM upgrade
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
The Object Schema Viewers role is missing from existing Assets object schemas after upgrading Jira Service Management Data Center. This prevents the Assets Confluence Macro from displaying data for those schemas.
Environment
Jira Service Management Data Center versions 10.5.x and later, including 11.x versions.
Diagnosis
Run the following SQL query against your Jira database to confirm which schemas are missing the Object Schema Viewers role (TYPE 8):
SELECT
os."ID" AS schema_id,
os."NAME" AS schema_name,
r."TYPE",
r."NAME" AS role_name
FROM "AO_8542F1_IFJ_ROLE" r
JOIN "AO_8542F1_IFJ_OBJ_SCHEMA" os ON r."OBJECT_SCHEMA_ID" = os."ID"
ORDER BY os."ID", r."TYPE";Review the output Schemas that show only TYPE 2 (Managers), 3 (Users), and 4 (Developers), but omit TYPE 8 (Object Schema Viewers), which are affected.
Cause
The Assets Viewers role (introduced in JSM 10.5) is not automatically backfilled into existing object schemas during an upgrade.
Solution
Insert missing roles into the database
Before proceeding, perform a full backup of your Jira database.
Run the following SQL statement to insert the missing role for all schemas where it is currently absent:
INSERT INTO "AO_8542F1_IFJ_ROLE" ("NAME", "TYPE", "OBJECT_SCHEMA_ID")
SELECT 'Object Schema Viewers', 8, os."ID"
FROM "AO_8542F1_IFJ_OBJ_SCHEMA" os
WHERE os."ID" NOT IN (
SELECT r."OBJECT_SCHEMA_ID"
FROM "AO_8542F1_IFJ_ROLE" r
WHERE r."TYPE" = 8
);Re-start Jira
Verify the insertion by running the following query:
SELECT os."ID", os."NAME", r."TYPE", r."NAME" AS role_name
FROM "AO_8542F1_IFJ_ROLE" r
JOIN "AO_8542F1_IFJ_OBJ_SCHEMA" os ON r."OBJECT_SCHEMA_ID" = os."ID"
WHERE r."TYPE" = 8
ORDER BY os."ID";You should now see TYPE 8 rows for all schemas.
Once the role is present:
Go to Assets → affected schema → Configure → Roles
Assign the appropriate users or groups to the Object Schema Viewers role
Test the Assets Confluence Macro; it should now be able to display data for those schemas
Was this helpful?