How do I change a username prior to Confluence 5.3

A username is the name used to log into Confluence, eg. jsmith.

Prior to Confluence 5.3, there was no straightforward method for changing a username and its associated content, to that of another user. The only practicable method currently available is to execute direct SQL queries on your database. There is a feature request to facilitate this process via a web interface and you can vote for it to improve its chances of being implemented. Be aware, however, that no matter what method you use to change usernames in Confluence, there is no support provided for this process. The instructions below provide suggested guidelines on how to change a username via SQL queries, although this may vary depending on your database.

Instructions For Changing Usernames

This document is for use with 3.5 or later, through 5.1.x. If using an earlier version, please see the 3.4 version of the page.

The following SQL commands are only tested for MySQL and PostgreSQL Databases. If you have any other database please contact your DBA to determine the equivalent queries.

Usernames can only be changed through direct update to the Confluence database.

  1. If you have a database administrator, request that they approve the database-related steps described below
  2. If you are using JIRA user management, Reverting from Crowd or JIRA to Internal User Management
  3. Backup Confluence
  4. If you are using MySQL, make sure you are not running in safe updates mode:

    set sql_safe_updates=0;
    
  5. Create a usermigration table:

    create table usermigration
    (
    oldusername varchar(255),
    newusername varchar(255)
    )
    
  6. Usernames that will be changed must be placed in the usermigration table with their current and planned usernames:

    insert into usermigration (oldusername, newusername)
    values ('oldusername', 'newusername');
    
  7. Run the following SQL commands:
    1. If you have command line access to your database, download the scripts for PostgreSQL or MySQL then run them against your database:

      PostgreSQL

      $ psql -f PostgreSQLChangeUsernames.sql your_database_name
      

      MySQL

      $ mysql your_database_name < MySQLChangeUsernames.sql
      
    2. Otherwise, run the following:
      1. If your DB administration tool does not support multiple SQL queries, these must be entered individually:

        PostgreSQL

        update attachments
        set creator = newusername from usermigration u
        where creator = u.oldusername;
        
        update attachments
        set lastmodifier = newusername from usermigration u
        where lastmodifier = u.oldusername;
        
        update content
        set creator = newusername from usermigration u
        where creator = u.oldusername;
        
        update content
        set lastmodifier = newusername from usermigration u
        where lastmodifier = u.oldusername;
        
        update content
        set username = newusername from usermigration u
        where username = u.oldusername;
        
        update content_label
        set owner = newusername from usermigration u
        where owner = u.oldusername;
        
        update content_perm
        set creator = newusername from usermigration u
        where creator = u.oldusername;
        
        update content_perm
        set lastmodifier = newusername from usermigration u
        where lastmodifier = u.oldusername;
        
        update content_perm
        set username = newusername from usermigration u
        where username = u.oldusername;
        
        update cwd_user
        set lower_user_name = lower(newusername) from usermigration u
        where lower_user_name = lower(u.oldusername);
        
        update cwd_user
        set user_name = newusername from usermigration u
        where user_name = u.oldusername;
        
        update extrnlnks
        set creator = newusername from usermigration u
        where creator = u.oldusername;
        
        update extrnlnks
        set lastmodifier = newusername from usermigration u
        where lastmodifier = u.oldusername;
        
        update follow_connections
        set followee = newusername from usermigration u
        where followee = u.oldusername;
        
        update follow_connections
        set follower = newusername from usermigration u
        where follower = u.oldusername;
        
        update label
        set owner = newusername from usermigration u
        where owner = u.oldusername;
        
        update links
        set creator = newusername from usermigration u
        where creator = u.oldusername;
        
        update links
        set lastmodifier = newusername from usermigration u
        where lastmodifier = u.oldusername;
        
        update notifications
        set creator = newusername from usermigration u
        where creator = u.oldusername;
        
        update notifications
        set lastmodifier = newusername from usermigration u
        where lastmodifier = u.oldusername;
        
        update notifications
        set username = newusername from usermigration u
        where username = u.oldusername;
        
        update pagetemplates
        set creator = newusername from usermigration u
        where creator = u.oldusername;
        
        update pagetemplates
        set lastmodifier = newusername from usermigration u
        where lastmodifier = u.oldusername;
        
        update remembermetoken
        set username = newusername from usermigration u
        where username = u.oldusername;
        
        update spacegroups
        set creator = newusername from usermigration u
        where creator = u.oldusername;
        
        update spacegroups
        set lastmodifier = newusername from usermigration u
        where lastmodifier = u.oldusername;
        
        update spacepermissions
        set creator = newusername from usermigration u
        where creator = u.oldusername;
        
        update spacepermissions
        set lastmodifier = newusername from usermigration u
        where lastmodifier = u.oldusername;
        
        update spacepermissions
        set permusername = newusername from usermigration u
        where permusername = u.oldusername;
        
        update spaces
        set creator = newusername from usermigration u
        where creator = u.oldusername;
        
        update spaces
        set lastmodifier = newusername from usermigration u
        where lastmodifier = u.oldusername;
        
        update trackbacklinks
        set creator = newusername from usermigration u
        where creator = u.oldusername;
        
        update trackbacklinks
        set lastmodifier = newusername from usermigration u
        where lastmodifier = u.oldusername;
        

        MySQL

        update ATTACHMENTS a, usermigration u
        set a.creator = u.newusername
        where a.creator = u.oldusername;
        
        update ATTACHMENTS a, usermigration u
        set a.lastmodifier = u.newusername
        where a.lastmodifier = u.oldusername;
        
        update CONTENT a, usermigration u
        set a.creator = u.newusername
        where a.creator = u.oldusername;
        
        update CONTENT a, usermigration u
        set a.lastmodifier = u.newusername
        where a.lastmodifier = u.oldusername;
        
        update CONTENT a, usermigration u
        set a.username = u.newusername
        where a.username = u.oldusername;
        
        update CONTENT_LABEL a, usermigration u
        set a.owner = u.newusername
        where a.owner = u.oldusername;
        
        update CONTENT_PERM a, usermigration u
        set a.creator = u.newusername
        where a.creator = u.oldusername;
        
        update CONTENT_PERM a, usermigration u
        set a.lastmodifier = u.newusername
        where a.lastmodifier = u.oldusername;
        
        update CONTENT_PERM a, usermigration u
        set a.username = u.newusername
        where a.username = u.oldusername;
        
        update CWD_USER a, usermigration u
        set a.lower_user_name = LOWER(u.newusername)
        where a.lower_user_name = LOWER(u.oldusername);
        
        update CWD_USER a, usermigration u
        set a.user_name = u.newusername
        where a.user_name = u.oldusername;
        
        update EXTRNLNKS a, usermigration u
        set a.creator = u.newusername
        where a.creator = u.oldusername;
        
        update EXTRNLNKS a, usermigration u
        set a.lastmodifier = u.newusername
        where a.lastmodifier = u.oldusername;
        
        update FOLLOW_CONNECTIONS a, usermigration u
        set a.followee = u.newusername
        where a.followee = u.oldusername;
        
        update FOLLOW_CONNECTIONS a, usermigration u
        set a.follower = u.newusername
        where a.follower = u.oldusername;
        
        update LABEL a, usermigration u
        set a.owner = u.newusername
        where a.owner = u.oldusername;
        
        update LINKS a, usermigration u
        set a.creator = u.newusername
        where a.creator = u.oldusername;
        
        update LINKS a, usermigration u
        set a.lastmodifier = u.newusername
        where a.lastmodifier = u.oldusername;
        
        update NOTIFICATIONS a, usermigration u
        set a.creator = u.newusername
        where a.creator = u.oldusername;
        
        update NOTIFICATIONS a, usermigration u
        set a.lastmodifier = u.newusername
        where a.lastmodifier = u.oldusername;
        
        update NOTIFICATIONS a, usermigration u
        set a.username = u.newusername
        where a.username = u.oldusername;
        
        update PAGETEMPLATES a, usermigration u
        set a.creator = u.newusername
        where a.creator = u.oldusername;
        
        update PAGETEMPLATES a, usermigration u
        set a.lastmodifier = u.newusername
        where a.lastmodifier = u.oldusername;
        
        update REMEMBERMETOKEN a, usermigration u
        set a.username = u.newusername
        where a.username = u.oldusername;
        
        update SPACEGROUPS a, usermigration u
        set a.creator = u.newusername
        where a.creator = u.oldusername;
        
        update SPACEGROUPS a, usermigration u
        set a.lastmodifier = u.newusername
        where a.lastmodifier = u.oldusername;
        
        update SPACEPERMISSIONS a, usermigration u
        set a.creator = u.newusername
        where a.creator = u.oldusername;
        
        update SPACEPERMISSIONS a, usermigration u
        set a.lastmodifier = u.newusername
        where a.lastmodifier = u.oldusername;
        
        update SPACEPERMISSIONS a, usermigration u
        set a.permusername = u.newusername
        where a.permusername = u.oldusername;
        
        update SPACES a, usermigration u
        set a.creator = u.newusername
        where a.creator = u.oldusername;
        
        update SPACES a, usermigration u
        set a.lastmodifier = u.newusername
        where a.lastmodifier = u.oldusername;
        
        update TRACKBACKLINKS a, usermigration u
        set a.creator = u.newusername
        where a.creator = u.oldusername;
        
        update TRACKBACKLINKS a, usermigration u
        set a.lastmodifier = u.newusername
        where a.lastmodifier = u.oldusername;
        
        
      2. Reassign user preferences in the OS_PROPERTYENTRY table. Usernames in the OS_PROPERTYENTRY table need to be prefixed with 'CWD_'.

        PostgreSQL

        update os_propertyentry
        set entity_name = 'CWD_' || newusername from usermigration u
        where entity_name = 'CWD_' || u.oldusername;
        

        MySQL

        update OS_PROPERTYENTRY a, usermigration u
        set a.entity_name = concat('CWD_', u.newusername)
        where a.entity_name = concat('CWD_', u.oldusername);
        
      3. Reassign personal spaces and settings associated with the old username to the new username. The tilda (~) is required as it is prepended to the space key of all personal spaces:

        PostgreSQL

        update spaces
        set spacekey = '~' || newusername from usermigration u
        where spacekey = '~' || u.oldusername;
         
        update bandana
        set bandanacontext = '~' || newusername from usermigration u
        where bandanacontext = '~' || u.oldusername;
        

        MySQL

        update SPACES a, usermigration u
        set a.spacekey = concat('~', u.newusername)
        where a.spacekey = concat('~', u.oldusername);
        
        update BANDANA a, usermigration u
        set a.bandanacontext = concat('~', u.newusername)
        where a.bandanacontext = concat('~', u.oldusername);
        
  8. Each username is associated with a full name. For example, username 'jsmith' may have a full name of 'John M Smith'. If this fullname needs to be changed, modify the first_name, lower_first_name, last_name and lower_last_name in the cwd_user table. Ensure the lower_ columns are merely copies of their normal counterparts but with all letters in lower case. Then modify the display_name and lower_display_name columns so that they are the first_name and last_name columns or the lower_first_name and lower_last_name columns put together but separated by a space.

Rebuild the Indexes

After all the updates, it's necessary to Rebuild the Indexes from Scratch

All old usernames in Confluence should now be replaced with the new usernames from the usermigration table.

Last modified on Oct 9, 2015

Was this helpful?

Yes
No
Provide feedback about this article

Not finding the help you need?

Ask the community

Powered by Confluence and Scroll Viewport.