Duplicate Permission entries prevent updates to Global Permissions in Confluence Data Center

Summary

When attempting to modify Global Permissions in Confluence Data Center, users may experience an issue where the interface does not reflect the expected changes. Specifically, attempts to revoke or modify a permission for a group fail to take effect. This is commonly observed with the "Can use" permission but can occur with any permission type due to duplicate entries in the database.

Environment

• Confluence Data Center

• The issue occurs in Confluence versions 8.5.6 and later.

  Note:This issue may also occur in earlier versions, though it has not been tested.

• Any supported database (e.g., MySQL, PostgreSQL, Oracle, Microsoft SQL Server)

Diagnosis

1. Inspect the database table SPACEPERMISSIONS to check for duplicate entries:

   • Use the following SQL query to identify duplicates for any permission, such as the "Can use" permission:

     1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 WITH duplicates AS (     SELECT         PERMID,         PERMTYPE,         PERMGROUPNAME,         SPACEID,         ROW_NUMBER() OVER (PARTITION BY PERMGROUPNAME, SPACEID ORDER BY PERMID DESC) AS row_num     FROM         SPACEPERMISSIONS     WHERE         PERMTYPE = 'USECONFLUENCE' ) SELECT * FROM duplicates WHERE row_num > 1;

   • Adjust the PERMTYPE filter in the query for other permissions as needed.

2. If the query returns results, it indicates that duplicate entries exist for the affected group(s) in the database.

3. Attempt to modify the permission (e.g., "Can use") for the group via the Global Permissions screen in the Confluence UI. Note that each save action removes only one duplicate entry, and the issue persists until all duplicates are removed.

Cause

The issue arises due to duplicate entries for the specified permission in the SPACEPERMISSIONS table. These duplicates prevent the proper application of changes to Global Permissions through the Confluence UI.

It is unclear what caused the duplicate values in the SPACEPERMISSIONS table.

Solution

1. Shutdown Confluence.

2. Backup the SPACEPERMISSIONS table to preserve its current state:

   1 2 CREATE TABLE SPACEPERMISSIONS_BACKUP AS SELECT * FROM SPACEPERMISSIONS;

3. Remove duplicate entries using the following SQL query (MySQL):

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 WITH duplicates AS (     SELECT         PERMID,         ROW_NUMBER() OVER (PARTITION BY PERMGROUPNAME, SPACEID ORDER BY PERMID DESC) AS row_num     FROM         SPACEPERMISSIONS     WHERE         PERMTYPE = 'USECONFLUENCE' ) DELETE FROM SPACEPERMISSIONS WHERE PERMID IN (     SELECT PERMID     FROM duplicates     WHERE row_num > 1 );

   - Adjust the PERMTYPE filter in the query for other permissions as needed.

   - Note: The SQL queries provided are for MySQL. If you are using a different database, the syntax may need to be adjusted accordingly.

4. Restart Confluence.

5. Reattempt to edit the Global Permissions for the affected group. Verify that the changes are now successfully applied.

Additional Notes

• Ensure that no manual modifications are made to the database without proper testing and backups.

• If the issue persists after following these steps, consider reaching out to Atlassian Support for further assistance.

• There is another issue involving corrupted space permissions with null spaceid values appearing in the Global Permissions page. For more details, see this KB Article.