How to merge two users' content ownership and permissions in Confluence
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Where a workaround involves running SQL scripts directly on your database, we strongly recommend you do this first on a non-production, staging or test environment before running the scripts on your production database. We also strongly recommend you take a backup of your data before making any modifications via SQL scripts.
Problem
When migrating users from one LDAP / Active Directory to another (such as during a company merger or acquisition), content ownership will not be migrated to the new usernames if the new unique identifiers do not match the existing unique identifiers. When the unique identifier for each user changes across directories, Confluence has no idea how to map the old usernames to the new usernames.
Disclaimer
Atlassian support does not recommend following these steps unless absolutely necessary (i.e. the second directory has already been added and users have content under both usernames), as they require a lot of manual database manipulation with a lot of opportunity for error. As such, Atlassian Support does not provide support for this, and you accept the risk in executing these commands.
Instead, we recommend renaming the users to their new usernames in the existing LDAP/AD directory, performing a directory sync in Confluence, then adding the second directory to Confluence. This will effectively preserve content ownership and permissions for each user in the new LDAP/AD directory.
Diagnosis
These steps are intended to fix Confluence content ownership after an environmental change to LDAP, wherein the original directory is no longer used and the new directory will be used going forward. This only applies when migrating from one external directory to another, where the usernames change and both directories have already been added to Confluence (meaning users have created content under both usernames).
For example:
| Old | New | |
|---|---|---|
| Username | johnsmith@example.com | js1234@example.com |
| Unique identifier (objectGUID) | 123456a | 555666b |
The Unique Identifier is defined in this setting (objectGUID is the attribute in this example):
This does not apply if the existing directory is modified and the usernames are updated, keeping the same unique identifiers. That type of change does not require these steps and after the next directory sync Confluence will see the updated usernames, assuming they still have the original unique guid/identifier.
Feature request
If you would like a function in Confluence that will enable you to merge users' content ownership, please vote on this feature request: CONFSERVER-11990 - Getting issue details... STATUS
Resolution
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
When renaming users, there are 5 fields in 3 tables that need to be updated. You will also need a temp table to map the old usernames to the new usernames.
cwd_usertable: user_name and lower_user_name columnsuser_mappingtable: username and lower_username columnsspacestable: spacekey columns (for personal spaces of the users that will be renamed)
Please Ensure that all "lower" username columns are indeed all lower-case entries when entering the new usernames.
The queries below are written for PostgreSQL and may need syntax changes for other database flavors.
Step 1: Stop Confluence
As we are manipulating data, the instance must be stopped before proceeding. This will prevent any new records from being created or modified during the update process below.
Step 2: Create a temp table to map old and new user names
CREATE TABLE tmp_usermigration
(
oldusername varchar(255),
newusername varchar(255)
);
Load the tmp_usermigration table with old and new username info. This is a manual process which can be done a few different ways as long as you end up with the resulting 2 columns.
Here the one approach to inserting records:
INSERT INTO tmp_usermigration (oldusername, newusername)
VALUES ('<oldusername>', '<newusername>');
Multiple records at once:
INSERT INTO tmp_usermigration (oldusername, newusername)
VALUES
('<oldusername1>', '<newusername1>'),
('<oldusername2>', '<newusername2>'),
('<oldusername3>', '<newusername3>'),
...
('<oldusernameN>', '<newusernameN>');
Example:
INSERT INTO tmp_usermigration (oldusername, newusername) VALUES ('johnsmith', 'js1234'), ('janedoe', 'jd5432');
Step 3: Create a temp table to map old and new user_key
Create a temporary table that manually stores the user_keys for oldusername and newusername:
CREATE TABLE tmp_user_mapping_migration
(
old_user_key varchar(255),
new_user_key varchar(255)
);
Obtain the old & new user keys:
select user_key, lower_username from user_mapping
You will need to find a way to manually keep track of which usernames are old vs new here, and their keys.
For duplicate users (i.e. have an old and new username), insert their old_user_key and new_user_key:
INSERT INTO tmp_user_mapping_migration (old_user_key, new_user_key)
VALUES
('8a7f80856438dc8d01643d79e8230007', '8a7f80856438dc8d01643d797d1a0006'),
('8a7f80856438d7af01643dcf6787000f', '402848b9644306620164430830490000');
Please continue with either Option-A or Option-B based on the scenario you are working on.
Option-A. If the content has been created by both (old and new) accounts:
Run the following queries to leave all the content associated to one of the user accounts.
If you have Team Calendar on your Confluence, you would need to update the following tables.
| Table Name | Table Column |
|---|---|
| AO_950DC3_TC_EVENTS | ORGANISER |
| AO_950DC3_TC_EVENTS_INVITEES | INVITEE_ID |
| AO_950DC3_TC_REMINDER_SETTINGS | LAST_MODIFIER |
| AO_950DC3_TC_SUBCALS_PRIV_USR | USER_KEY |
| AO_950DC3_TC_REMINDER_USERS | USER_KEY |
Remove one of the duplicates:
delete from user_mapping where user_key = tmp_user_mapping_migration.old_user_key;
Option-B. If the new account did not create any content:
Drop the user_mapping table constraints:
ALTER TABLE user_mapping DROP PRIMARY KEY;
ALTER TABLE user_mapping DROP CONSTRAINT unq_lwr_username;
Update the username associated to the old_user_key to the new username:
UPDATE user_mapping SET username = newusername, lower_username = newusername FROM tmp_usermigration u WHERE username = u.oldusername;
Then, delete the unused new_user_key :
delete from user_mapping where user_key = 'new_user_key';
After completing the above for all affected users, add the constraints to the user_mapping table that we have removed:
ALTER TABLE user_mapping ADD CONSTRAINT user_mapping_pkey PRIMARY KEY (user_key);
ALTER TABLE user_mapping ADD CONSTRAINT unq_lwr_username UNIQUE (lower_username);
Step 4: Update cwd_user table
UPDATE cwd_user SET user_name = newusername, lower_user_name = newusername FROM tmp_usermigration u WHERE user_name = u.oldusername;
Step 5: Update spaces table
UPDATE spaces SET spacekey = '~' || newusername, lowerspacekey = '~' || newusername FROM tmp_usermigration u WHERE lowerspacekey = '~' || u.oldusername;
The spacekey includes a tilde '~' character at the beginning of all personal space names. This will only update spacekeys for users whom have already created a personal space.
Step 6: Start Confluence
Start Confluence to see the changes take effect.
Note: It is recommended to rebuild the content index and the ancestors table, to ensure all indexes include current information and parent/child page permissions are current.
- How To Rebuild The Content Indexes From Scratch On Confluence Server
- How To Rebuild The Content Indexes From Scratch On Confluence Data Center
- Rebuilding The Ancestor Table
We recommend leaving the temp tables in place, as they may be useful in the future.