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

Currently, there is 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

The following SQL commands are only tested for MYSQL and POSTGRES 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, Revert from JIRA To Internal User Management

  3. Backup Confluence

  4. Creating a usermigration table: 
    create table usermigration
(
oldusername varchar(255),
newusername varchar(255)
)
  5. 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');
  6. 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 lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update content
set creator = newusername from usermigration u
where creator = u.oldusername;

update content
set username = newusername from usermigration u
where username = 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 follower = newusername from usermigration u
where follower = u.oldusername;

update follow_connections
set followee = newusername from usermigration u
where followee = u.oldusername;

update label
set owner = newusername from usermigration u
where owner = u.oldusername;

update content_label
set owner = newusername from usermigration u
where owner = u.oldusername;

update links
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update links
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 creator = newusername from usermigration u
where creator = u.oldusername;

update pagetemplates
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update pagetemplates
set creator = newusername from usermigration u
where creator = 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 spacepermissions
set permusername = newusername from usermigration u
where permusername = 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 contentlock
set creator = newusername from usermigration u
where creator = u.oldusername;

update contentlock
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update os_user
set username = newusername from usermigration u
where username = 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.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.username = u.newusername
where a.username = 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.follower = u.newusername
where a.follower = u.oldusername;

update FOLLOW_CONNECTIONS a, usermigration u
set a.followee = u.newusername
where a.followee = u.oldusername;

update LABEL a, usermigration u
set a.owner = u.newusername
where a.owner = u.oldusername;

update CONTENT_LABEL a, usermigration u
set a.owner = u.newusername
where a.owner = u.oldusername;

update LINKS a, usermigration u
set a.lastmodifier = u.newusername
where a.lastmodifier = u.oldusername;

update LINKS 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.creator = u.newusername
where a.creator = u.oldusername;

update PAGETEMPLATES a, usermigration u
set a.lastmodifier = u.newusername
where a.lastmodifier = u.oldusername;

update PAGETEMPLATES a, usermigration u
set a.creator = u.newusername
where a.creator = 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 SPACEPERMISSIONS a, usermigration u
set a.permusername = u.newusername
where a.permusername = 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 CONTENTLOCK a, usermigration u
set a.creator = u.newusername
where a.creator = u.oldusername;

update CONTENTLOCK a, usermigration u
set a.lastmodifier = u.newusername
where a.lastmodifier = u.oldusername;

update os_user a, usermigration u
set a.username = u.newusername
where a.username = 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;

Your existing user preferences (eg. avatar, editing preferences, etc.) will be lost if you run the script below. To make sure these are retained, you need to update them in the OS_PROPERTYENTRY table. These properties will be in the format of LOC_username, in the entity_name column. You must change this column to use the new username.

  1. If using Confluence 2.1 or newer, run the following command:

    PostgreSQL

    update users
set name = newusername from usermigration u
where name = u.oldusername;

    MySQL

    update users a, usermigration u
set a.name = u.newusername
where a.name = u.oldusername;
  1. Reassign personal spaces and settings associated with the old username to the new username.

    PostgreSQL

    update content_label
set owner = newusername from usermigration u
where owner = u.oldusername;

    MySQL

    update CONTENT_LABEL a, usermigration u
set a.owner = u.newusername
where a.owner = u.oldusername;

    For the two queries below, the tilda (~) is required as it is prepended to the space key of all personal spaces:

    PostgreSQL

    update spaces
set spacekey = '~newusername'
where spacekey = '~oldusername';

update bandana
set bandanacontext = '~newusername'
where bandanacontext = '~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);
  1. 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 fullname in the users or os_user table. 

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.

関連トピック