How to merge two users' content ownership and permissions in Confluence

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.

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:


OldNew
Usernamejohnsmith@example.comjs1234@example.com
Unique identifier (objectGUID)123456a555666b


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_user table: user_name and lower_user_name columns
  • user_mapping table: username and lower_username columns
  • spaces table: 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.

(warning) 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');

If 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:

For Oracle Database..
update spacepermissions a set a.permusername = ( select b.new_user_key from tmp_user_mapping_migration b where a.permusername = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.permusername = b.old_user_key and a.permusername is not null);
update spacepermissions a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update spacepermissions a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
update spaces a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update spaces a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
update content_label a set a.owner = ( select b.new_user_key from tmp_user_mapping_migration b where a.owner = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.owner = b.old_user_key and a.owner is not null);
update pagetemplates a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update pagetemplates a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
update label a set a.owner = ( select b.new_user_key from tmp_user_mapping_migration b where a.owner = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.owner = b.old_user_key and a.owner is not null);
-- START: For Confluence versions below 7.0, execute the following for the extrnlnks table:
update extrnlnks a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update extrnlnks a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
-- END
update user_relation a set a.sourceuser = ( select b.new_user_key from tmp_user_mapping_migration b where a.sourceuser = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.sourceuser = b.old_user_key and a.sourceuser is not null);
update user_relation a set a.targetuser = ( select b.new_user_key from tmp_user_mapping_migration b where a.targetuser = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.targetuser = b.old_user_key and a.targetuser is not null);
update user_relation a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update user_relation a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
update usercontent_relation a set a.sourceuser = ( select b.new_user_key from tmp_user_mapping_migration b where a.sourceuser = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.sourceuser = b.old_user_key and a.sourceuser is not null);
update usercontent_relation a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update usercontent_relation a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
update content_relation a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update content_relation a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
update links a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update links a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
update likes a set a.username = ( select b.new_user_key from tmp_user_mapping_migration b where a.username = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.username = b.old_user_key and a.username is not null);
-- START: For Confluence versions below 7.0, execute the following for the trackbacklinks table:
update trackbacklinks a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update trackbacklinks a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
-- END
update content a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update content a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
update content a set a.username = ( select b.new_user_key from tmp_user_mapping_migration b where a.username = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.username = b.old_user_key and a.username is not null);
update notifications a set a.username = ( select b.new_user_key from tmp_user_mapping_migration b where a.username = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.username = b.old_user_key and a.username is not null);
update notifications a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update notifications a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
update content_perm a set a.username = ( select b.new_user_key from tmp_user_mapping_migration b where a.username = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.username = b.old_user_key and a.username is not null);
update content_perm a set a.creator = ( select b.new_user_key from tmp_user_mapping_migration b where a.creator = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.creator = b.old_user_key and a.creator is not null);
update content_perm a set a.lastmodifier = ( select b.new_user_key from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.lastmodifier = b.old_user_key and a.lastmodifier is not null);
update follow_connections a set a.follower = ( select b.new_user_key from tmp_user_mapping_migration b where a.follower = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.follower = b.old_user_key and a.follower is not null);
update follow_connections a set a.followee = ( select b.new_user_key from tmp_user_mapping_migration b where a.followee = b.old_user_key) where exists ( select 1 from tmp_user_mapping_migration b where a.followee = b.old_user_key and a.followee is not null);
For Other Database...
update spacepermissions set permusername = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where spacepermissions.permusername = tmp_user_mapping_migration.old_user_key;
update spacepermissions set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where	spacepermissions.creator = tmp_user_mapping_migration.old_user_key;
update spacepermissions set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where spacepermissions.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update spaces set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where spaces.creator = tmp_user_mapping_migration.old_user_key; 		
update spaces set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where spaces.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update content_label set owner = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_label.owner = tmp_user_mapping_migration.old_user_key; 		
update pagetemplates set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where pagetemplates.creator = tmp_user_mapping_migration.old_user_key; 		
update pagetemplates set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where pagetemplates.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update logininfo set username = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where logininfo.username = tmp_user_mapping_migration.old_user_key; 		
update label set owner = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where label.owner = tmp_user_mapping_migration.old_user_key; 		
-- START: For Confluence versions below 7.0, execute the following for the extrnlnks table:
update extrnlnks set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where extrnlnks.creator = tmp_user_mapping_migration.old_user_key; 		
update extrnlnks set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where extrnlnks.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
-- END
update user_relation set sourceuser = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where user_relation.sourceuser = tmp_user_mapping_migration.old_user_key; 		
update user_relation set targetuser = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where user_relation.targetuser = tmp_user_mapping_migration.old_user_key; 		
update user_relation set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where user_relation.creator = tmp_user_mapping_migration.old_user_key; 		
update user_relation set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where user_relation.lastmodifier = tmp_user_mapping_migration.old_user_key;
update usercontent_relation set sourceuser = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where usercontent_relation.sourceuser = tmp_user_mapping_migration.old_user_key; 		
update usercontent_relation set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where usercontent_relation.creator = tmp_user_mapping_migration.old_user_key; 		
update usercontent_relation set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where usercontent_relation.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update content_relation set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_relation.creator = tmp_user_mapping_migration.old_user_key; 		
update content_relation set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_relation.lastmodifier = tmp_user_mapping_migration.old_user_key; 	
update links set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where links.creator = tmp_user_mapping_migration.old_user_key; 		
update links set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where links.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update likes set username = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where likes.username = tmp_user_mapping_migration.old_user_key; 		
-- START: For Confluence versions below 7.0, execute the following for the trackbacklinks table:
update trackbacklinks set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where trackbacklinks.creator = tmp_user_mapping_migration.old_user_key; 		
update trackbacklinks set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where trackbacklinks.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
-- END
update content set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content.creator = tmp_user_mapping_migration.old_user_key; 		
update content set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update content set username = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content.username = tmp_user_mapping_migration.old_user_key; 		
update notifications set username = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where notifications.username = tmp_user_mapping_migration.old_user_key; 		
update notifications set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where notifications.creator = tmp_user_mapping_migration.old_user_key; 		
update notifications set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where notifications.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update content_perm set username = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_perm.username = tmp_user_mapping_migration.old_user_key; 		
update content_perm set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_perm.creator = tmp_user_mapping_migration.old_user_key; 		
update content_perm set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_perm.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update follow_connections set follower = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where follow_connections.follower = tmp_user_mapping_migration.old_user_key; 		
update follow_connections set followee = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where follow_connections.followee = tmp_user_mapping_migration.old_user_key; 	

If you have Team Calendar on your Confluence, you would need to update the following tables.

Table NameTable Column
AO_950DC3_TC_EVENTSORGANISER
AO_950DC3_TC_EVENTS_INVITEESINVITEE_ID
AO_950DC3_TC_REMINDER_SETTINGSLAST_MODIFIER
AO_950DC3_TC_SUBCALS_PRIV_USRUSER_KEY
AO_950DC3_TC_REMINDER_USERSUSER_KEY


Remove one of the duplicates:

delete from user_mapping where user_key = tmp_user_mapping_migration.old_user_key;

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;

(info) 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 7: Start Confluence

Start Confluence to see the changes take effect.

(warning) 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.

We recommend leaving the temp tables in place, as they may be useful in the future. 


Last modified on Sep 21, 2021

Was this helpful?

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