Changing User Names in Confluence

• 14 September 2010
• atlassian,
• confluence

At the office we use Atlassian Confluence as our internal Wiki system. I do like it, but it has some idiosyncrasies. Similar to JIRA there is no internal way to change user names. Here is the SQL needed to update Confuence.

This is pretty clean SQL because you really only need to update the first two lines.

SET @oldusername = "OLD\_USER\_NAME";
SET @newusername = "NEW\_USER\_NAME";
SET @tildedoldusername = CONCAT('~', @oldusername);
SET @tildednewusername = CONCAT('~', @newusername);
SET @locoldusername = CONCAT('LOC\_', @oldusername);
SET @locnewusername = CONCAT('LOC\_', @newusername);
-- Attachments update ATTACHMENTS set creator = @newusername where creator = @oldusername;
update ATTACHMENTS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- Bandana update BANDANA set bandanacontext = @newusername where bandanacontext = @oldusername;
-- Content update CONTENT set creator = @newusername where creator = @oldusername;
update CONTENT set lastmodifier = @newusername where lastmodifier = @oldusername;
update CONTENT set username = @newusername where username = @oldusername;
update CONTENT set draftspacekey = @tildednewusername where draftspacekey = @tildeoldusername;
-- content\_label update CONTENT\_LABEL set owner = @newusername where owner = @oldusername;
-- update CONTENT\_LABEL set spacekey = @tildednewusername where owner = @tildedoldusername;
-- content\_perl update CONTENT\_PERM set creator = @newusername where creator = @oldusername;
update CONTENT\_PERM set lastmodifier = @newusername where lastmodifier = @oldusername;
update CONTENT\_PERM set username = @newusername where username = @oldusername;
-- contentlock update CONTENTLOCK set creator = @newusername where creator = @oldusername;
update CONTENTLOCK set lastmodifier = @newusername where lastmodifier = @oldusername;
-- decorator update DECORATOR set SPACEKEY = @tildednewusername where SPACEKEY = @tildedoldusername;
-- extrnlnks update EXTRNLNKS set creator = @newusername where creator = @oldusername;
update EXTRNLNKS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- label update LABEL set owner = @newusername where owner = @oldusername;
-- links update LINKS set creator = @newusername where creator = @oldusername;
update LINKS set lastmodifier = @newusername where lastmodifier = @oldusername;
update LINKS set destspacekey = @tildednewusername where destspacekey = @tildedoldusername;
update LINKS set destpagetitle = @tildednewusername where destpagetitle = @tildedoldusername;
-- notifications update NOTIFICATIONS set creator = @newusername where creator = @oldusername;
update NOTIFICATIONS set lastmodifier = @newusername where lastmodifier = @oldusername;
update NOTIFICATIONS set username = @newusername where username = @oldusername;
-- os\_propertyEntry update OS\_PROPERTYENTRY set entity\_name = @locnewusername where entity\_name = @locoldusername;
update OS\_PROPERTYENTRY set string\_val = @tildednewusername where entity\_name = @tildedoldusername;
-- pagetemplates update PAGETEMPLATES set creator = @newusername where creator = @oldusername;
update PAGETEMPLATES set lastmodifier = @newusername where lastmodifier = @oldusername;
-- spacegrouppermissions update SPACEGROUPPERMISSIONS set permusername = @newusername where permusername = @oldusername;
-- spacegroups update SPACEGROUPS set creator = @newusername where creator = @oldusername;
update SPACEGROUPS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- spacepermissions update SPACEPERMISSIONS set creator = @newusername where creator = @oldusername;
update SPACEPERMISSIONS set lastmodifier = @newusername where lastmodifier = @oldusername;
update SPACEPERMISSIONS set permusername = @newusername where permusername = @oldusername;
-- spaces update SPACES set creator = @newusername where creator = @oldusername;
update SPACES set lastmodifier = @newusername where lastmodifier = @oldusername;
update SPACES set spacekey = @tildednewusername where lastmodifier = @tildedoldusername;
-- trackbacklinks update TRACKBACKLINKS set creator = @newusername where creator = @oldusername;
update TRACKBACKLINKS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- os\_user and users update os\_user set username = @newusername where username = @oldusername;
update users set name = @newusername where name = @oldusername;

I used this code on about 50 users recently and did not have any problems.

Here are the steps I did:

• I took the template above and made copies for each user that needed to be changed. Each was edited as needed.
• I shutdown Confluence
• I used the mysql command line utility to connect to my confluence database
• I loaded each SQL file by hand, which will update the database
• I relocated the cache which is in the data directory and called index.
• I restarted confluence and rebuilt the cache

Everything went pretty well. After I did the user migration, I then integrated LDAP to our AD and every user now has one less password to remember.

Hope this helps someone.

• ← Previous
  Merging PDFs
• Next →
  Garage Storage - Part 1