How do I change a username prior to Confluence 5.3

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

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

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.