How To - Transfer Content Ownership in between Users

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.

Important

Atlassian official recommendation is to rename users in LDAP/Directory as needed instead of creating new ones once possible. This workaround is suggested to those who can't follow the recommendation by any given reasons. It is also important to highlight that database manipulation should be thoroughly tested in non-production prior being applied in production and that Support won't be able to help in case anything goes wrong unless a database backup has been taken to restore the application to its original state.

Problem

Let's say that for any given reasons, an user received a new username to access Confluence. However, this very specific user created tons of contents on Confluence using his old account. Confluence does not have an embedded feature to transfer content ownership from one user to another, which means that this user will lose everything created with the old user upon such scenario.

Important

This KB is meant to workaround the above problem when the new user has been created in the same directory as the old user. If we are talking about changing LDAP directories or moving users to a new LDAP directory and transferring content to them, this is not the Knowledge Base article that you are looking for. Please, check How to merge two users' content ownership and permissions in Confluence instead.

Feature Request

Confluence doesn't have any available features to help with this specific scenario. If you feel like Confluence should have it, please, vote and subscribe to the Feature Request below to increase its visibility:

Workaround

  1. Ensure that both usernames exists in Confluence and that they are part of the same directory.

    select * from cwd_user where user_name in ('olduser','newuser') and directory_id = '98305';
  2. If they are not, proceed with using How to merge two users' content ownership and permissions in Confluence instead of this kb.
  3. If they are, proceed with the steps below.
  4. Back-up your database.
  5. Shut down Confluence.
  6. Open your Database Management System and create a temporary table called tmp_usermigration to store information about the users that we are going to modify.

    CREATE TABLE tmp_usermigration
    (
    oldusername varchar(255),
    newusername varchar(255)
    );
  7. Insert user information on it, please, replace the variables below accordingly:

    INSERT INTO tmp_usermigration (oldusername, newusername) 
    VALUES ('usernameOfOldUser', 'usernameOfNewUser');
  8. Create a temporary table called tmp_user_mapping_migration to store information about the user key of the users that we are going to modify:

    CREATE TABLE tmp_user_mapping_migration
    (
    old_user_key varchar(255),
    new_user_key varchar(255)
    );
  9. Pick the user key from the old username and from the new username - Please, be careful, this is the most important of the steps, since every content is related to the user key of an user:

    select user_key, lower_username from user_mapping where lower_username in 
    	(select oldusername from tmp_usermigration)
        OR lower_username in
        (select newusername from tmp_usermigration);
  10. Take notes of the values and replace them accordingly in the insert query below - First is the old username key value and second is the new username key value:

    INSERT INTO tmp_user_mapping_migration (old_user_key, new_user_key)
    VALUES
     ('40288bf465e825770165fd31c7060000', '40288bf465e825770165fd3236980001');
  11. Now, execute the following queries to replace contents accordingly - They will pick up contents created by the old username and assign it to the new username:

    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);
    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);
    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);
    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);
    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 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;
    
    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;
    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;
    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;
    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;
  12. Last query will replace personal spaces accordingly:

    UPDATE spaces SET spacekey = '~' || newusername, lowerspacekey = '~' || newusername FROM tmp_usermigration u WHERE lowerspacekey = '~' || u.oldusername;
  13. Now, bring Confluence back-up.
  14. Rebuild your Search Index by accessing <Your_Base_URL>/admin/search-indexes.action and clicking on Rebuild.
  15. With that completed, clear up the application caches by accessing <Your_Base_URL>/admin/cache/showStatistics.action, scrolling to the bottom of the page and clicking on Flush All.
  16. Rebuild your Ancestors Table.
  17. Once all of this is completed, you may disable/delete the Old Username from Confluence UI, since contents will be already tied to the New Username.


Last modified on Sep 14, 2020

Was this helpful?

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