Mapping users from old to new LDAP (different usernames)

[Genia Photonics IT]

Hi everyone,

I'm wondering how I should approach the issue of having different usernames in the original and new LDAPs. We only have 10 users, so even a manual solution would probably do. Right now, if I tell users they can use the new LDAP, they'll login with the new names which will create new users and link their new content to a second user representing the same person. I want to avoid that!

We have an old LDAP which had names formatted "firstlast" and we are moving to a new model with usernames "flast". Both LDAPs are linked to JIRA right now and I can use either LDAP to authenticate. The old one "firstlast" LDAP is due to be disconnected soon, so I would like, before people start loging in and creating content with their new "flast" names, to map their current content to the new usernames.

How should I go about this? I didn't check if there was only simply a user table to ALTER or what. I want to get advice before messing with anything.

Thanks for the help!

[Maxime_Joanis · Accepted solution]

Well, it seems JIRA is using username strings as user keys... coupling JIRA with the authentication method. Hm.

Anyway... So I browsed the whole JIRA DB and replaced the old usernames with the new ones in tables which seemed to require it. I then had to reindex search indexes (otherwise the old usernames still had to be used in searches).

It's a bit tricky, as some tables clearly have username columns while some others have username references among other contents (like some configuration tables, JQL strings, etc.). Also, tables which index LDAP users don't need to be modified.

Looks like it worked so far.

I can't help but suggest that username != user (and to use numerical comparisons for such things).

For Confluence, it was much easier. Mostly user_mapping was to modify. This table, and a few others to remove traces from users created by the addition of the new user directory. I then updated the old usernames for the new ones to preserve their UUIDs. Works too!

Because I have a small number of users, it only took a few hours to update JIRA+Confluence and modify both databases. If someone wants to do the same and has a huge user base, I suggest to create some temporary databases to map new user names with old ones and update the database using nested queries in scripts instead of typing for days.

[Raju Adluru]

Hi Maxime we are also in same situation in JIRA and confluence, can you please send us the SQL which you used to map old users to new users our scenario is like this: In confluence - we have confluence internal directory, crowd internal directory and MS AD users from Crowd Internal direcotry have created pages and contents in Confluence. Now we want use Just AD, get rid of crowd internal users and Confl internal users, we want to map all those contents to AD users, can you please help us how we can resolve with your method. Same for JIRA also we tried to rename crowd internal user to match AD user, but in JIRA and Confl, user is shown as in active and not mapping to AD user, even though same email id!! Thanks alot Raju

[Maxime_Joanis]

Hello Raju, I have replied in a full answer message instead of packing it all in this comment. See (probably) below, the message with a big log chunk...

[Raju Adluru]

Hi Maxime, thank you so much for sharing this info, looks like that is for postgreDB, can i use same for MS SQL? i have around 120 users, so i need to add those statements ( replace newuser1,2,3), i will check and see, how it goes. thanks alot for the info Raju

[Maxime_Joanis]

Hey Raju, The database structure should be exactly the same. In MS SQL, you will have different commands to select the database, but the rest of the SQL statements should be the same. You will probably select the database with "use jiradb;" (or however you called it) instead of "\c jiradb", and you will use "SELECT * FROM information_schema.tables;" to list tables instead of "\dt".

[Raju Adluru]

Maxime Yes, thank you.

[Maxime_Joanis]

Hello Raju,

I'll reply here instead, as it's probably more suitable for a long post...

I never took the time to create a script for the task, as it was a one-off dreaded task. I went back to the server and saved the psql history to give you an idea of the task. I posted the history at the end of this message.

You could probably extract loops (or nested queries) from my experiments and build your own script if you wish. Because it was not the first time I changed ldap server or renamed user, my case was a little hard to script. I couldn't predict exactly which usernames I would find (which don't necessarily still exist in the current ldap server). Anyway, I just thought I'd browse the whole database and replace as required. It took maybe half a day to a full day, which isn't so bad for a manual DB fix. I had only about 20 usernames, but you might not be so lucky.

I cleaned the log a little bit to remove names... They were replaced by oldnameN (previous usernames), newnameN (new usernames), lastnameN, and firstnameN depending on what was stored in each columns/tables. You'll notice I left a lot of tables unchanged, either because these are features we didn't use yet, or there was no reference to the new or old usernames. It doesn't mean you won't have anything to change in these.

It really wasn't so hard to do. Start by showing the content of each table. You get a good idea of the column types by their names alone. In case of doubts, select all values and see if there are usernames in there. It's not because a few rows have nothing in that column that the whole column is empty. Also, it's not because you see an unrelated datatype in one row, that there will not be a username somewhere on another row. Better make good use of judgement to guess if it's probable or not that a username may be found in one column or not.

In any case, do your best and it's always time to go fix an entry later when you see that something's wrong.

Don't forget to rebuild jira/confluence indexes, or it's going to fail miserably.

Sorry for not having anything cleaner to offer you!

Maxime

\c jiradb
\dt
select * from AO_21D670_WHITELIST_RULES;
select * from app_user;
select * from audit_changed_value;
select * from audit_item;
select * from audit_log;
select * from avatar;
select * from avatar;
update avatar set owner = 'mjoanis' where owner = 'maximejoanis';
select * from avatar;
select * from avatar;
\dt
select * from worklog;
\dt
select * from changegroup;
select * from changeitem;
select count(*) from changeitem;
\dt
select * from clustermessage;
select * from clusternode;
select * from columnlayout;
select * from columnlayoutitem;
select * from component;
select * from configurationcontext;
select * from customfield;
select * from customfieldoption;
select * from customfieldvalue;
select * from cwd_application;
select * from cwd_application_address ;
\dt
select * from cwd_directory;
select * from cwd_directory_attribute;
select * from cwd_directory_operation ;
\dt
select * from cwd_group ;
\dt
select * from cwd_group_attributes ;
select * from cwd_membership ;
select * from cwd_user;
select * from cwd_user where user_name = 'mjoanis';
select * from cwd_user_attributes ;
select * from draftworkflowscheme;
select * from draftworkflowschemeentity ;
select * from entity_property;
select * from entity_property_index_document;
select * from external_entities ;
select * from external_entities where name = 'mjoanis';
select * from externalgadget ;
select * from favouriteassociations ;
update favouriteassociations set username = 'mjoanis' where username = 'maximejoanis';
select * from favouriteassociations ;
update favouriteassociations set username = 'newuser0' where username = 'olduser0';
update favouriteassociations set username = 'newuser1' where username = 'olduser1';
select * from favouriteassociations ;
select * from feature ;
\dt
select * from fieldconfigscheme;
select * from fieldconfigschemeissuetype;
select * from fieldconfiguration ;
select * from fieldlayout;
select * from fieldlayoutitem;
select * from fieldlayoutscheme;
select * from fieldlayoutschemeassociation;
select * from fieldlayoutschemeentity;
select * from fieldscreen;
select * from fieldscreenlayoutitem;
select * from fieldscreenscheme;
select * from fieldscreenschemeitem;
select * from fieldscreentab;
select * from fileattachment ;
update fileattachment set author = 'newuser0' where author = 'olduser0';
update fileattachment set author = 'newuser1' where author = 'olduser1';
update fileattachment set author = 'mjoanis' where author = 'maximejoanis';
update fileattachment set author = 'newuser2' where author = 'olduser2';
select * from fileattachment ;
select * from filtersubscription ;
select * from gadgetuserpreference ;
select * from genericconfiguration ;
select * from groupbase ;
select * from issuelink;
select * from issuelinktype;
select * from issuelinktypescreenscheme;
select * from issuetypescreenscheme;
select * from issuelinktypescreenscheme;
select * from issuetypescreenschemeentity ;
\dt
select * from jiraaction;
select distinct author from jiraaction ;
update jiraaction set author = 'newuser3' where author = 'olduser3';
update jiraaction set author = 'newuser1' where author = 'olduser1';
update jiraaction set author = 'newuser0' where author = 'olduser0';
update jiraaction set author = 'mjoanis' where author = 'maximejoanis';
update jiraaction set author = 'newuser2' where author = 'olduser2';
select distinct updateauthor from jiraaction ;
update jiraaction set updateauthor = 'newuser3' where updateauthor = 'olduser3';
update jiraaction set updateauthor = 'newuser1' where updateauthor = 'olduser1';
update jiraaction set updateauthor = 'newuser0' where updateauthor = 'olduser0';
update jiraaction set updateauthor = 'mjoanis' where updateauthor = 'maximejoanis';
update jiraaction set updateauthor = 'newuser2' where updateauthor = 'olduser2';
select * from jiradraftworkflows;
select * from jiraeventtype ;
select count(*) from jiraissue ;
select * from jiraissue;
select distinct reporter from jiraissue ;
update jiraissue set reporter = 'newuser4' where reporter = 'olduser4';
update jiraissue set assignee = 'newuser4' where assignee = 'olduser4';
update jiraissue set creator = 'newuser4' where creator = 'olduser4';
update jiraissue set reporter = 'newuser3' where reporter = 'olduser3';
update jiraissue set assignee = 'newuser3' where assignee = 'olduser3';
update jiraissue set creator = 'newuser3' where creator = 'olduser3';
update jiraissue set reporter = 'newuser1' where reporter = 'olduser1';
update jiraissue set assignee = 'newuser1' where assignee = 'olduser1';
update jiraissue set creator = 'newuser1' where creator = 'olduser1';
update jiraissue set reporter = 'newuser5' where reporter = 'olduser5';
update jiraissue set assignee = 'newuser5' where assignee = 'olduser5';
update jiraissue set creator = 'newuser5' where creator = 'olduser5';
update jiraissue set reporter = 'newuser0' where reporter = 'olduser0';
update jiraissue set assignee = 'newuser0' where assignee = 'olduser0';
update jiraissue set creator = 'newuser0' where creator = 'olduser0';
update jiraissue set reporter = 'mjoanis' where reporter = 'maximejoanis';
update jiraissue set assignee = 'mjoanis' where assignee = 'maximejoanis';
update jiraissue set creator = 'mjoanis' where creator = 'firstname2';
update jiraissue set reporter = 'newuser2' where reporter = 'olduser2';
update jiraissue set assignee = 'newuser2' where assignee = 'olduser2';
update jiraissue set creator = 'newuser2' where creator = 'olduser2';
update jiraissue set reporter = 'newuser6' where reporter = 'olduser6';
update jiraissue set assignee = 'newuser6' where assignee = 'olduser6';
update jiraissue set creator = 'newuser6' where creator = 'olduser6';
select distinct reporter from jiraissue ;
select distinct creator from jiraissue ;
select distinct assignee from jiraissue ;
update jiraissue set assignee = 'newuser7' where assignee = 'olduser7';
update jiraissue set assignee = 'newuser8' where assignee = 'olduser8';
select distinct assignee from jiraissue ;
select * from jiraperms;
select * from jiraworkflows ;
select * from label;
select * from listenerconfig ;
select * from mailserver ;
select * from managedconfigurationitem ;
select * from membershipbase ;
select * from moved_issue_key;
select count(*) from moved_issue_key;
select * from nodeassociation ;
select * from nodeindexcounter ;
select * from notification;
select * from notificationinstance;
select * from notificationscheme;
select * from oauthconsumer;
select * from oauthconsumertoken ;
select * from oauthspconsumer ;
select * from oauthsptoken ;
select * from optionconfiguration ;
select * from os_currentstep;
select * from os_currentstep_prev;
select * from os_historystep;
select * from os_historystep_prev;
select * from os_wfentry ;
select * from permissionscheme l;
select * from permissionscheme ;
select * from pluginstate ;
\dt
select * from pluginversion ;
select * from portalpage ;
update portalpage set username = 'mjoanis' where username = 'maximejoanis';
update portalpage set username = 'newuser0' where username = 'olduser0';
select * from portletconfiguration ;
select * from priority ;
select * from project ;
select distinct lead from project;
update project set lead = 'newuser4' where lead = 'olduser4';
update project set lead = 'newuser6' where lead = 'olduser6';
update project set lead = 'newuser3' where lead = 'olduser3';
update project set lead = 'newuser1' where lead = 'olduser1';
update project set lead = 'mjoanis' where lead = 'maximejoanis';
select * from project_key;
select * from projectcategory ;
select * from projectrole ;
select * from projectroleactor ;
select * from projectversion ;
select * from propertydata;
select * from propertydate;
select * from propertydecimal;
select * from propertyentry;
select * from propertynumber;
select * from propertystring;
select * from propertystring;
select * from propertystring where propertyvalue ~ 'maximejoanis';
select * from propertyentry;
select * from propertyentry where id = 21304;
select * from propertyentry where id = 21306;
select * from propertytext ;
select count(*) from propertytext ;
select * from propertytext where propertyvalue ~ 'joanis' ;
select * from qrtz_calendars ;
select * from qrtz_cron_triggers ;
select * from qrtz_job_details ;
select * from qrtz_job_listeners ;
select * from qrtz_simple_triggers;
select * from qrtz_trigger_listeners;
select * from qrtz_triggers;
select * from remembermetoken ;
select * from remotelink ;
select * from replicatedindexoperation;
select * from resolution ;
\dt
select * from schemeissuesecurities ;
select * from schemeissuesecuritylevels ;
select * from schemepermissions ;
select * from searchrequest ;
udpate searchrequest set authorname = 'mjoanis' where authorname = 'maximejoanis';
update searchrequest set authorname = 'mjoanis' where authorname = 'maximejoanis';
update searchrequest set authorname = 'newuser1' where authorname = 'olduser1';
update searchrequest set username = 'mjoanis' where username = 'maximejoanis';
update searchrequest set username = 'newuser1' where username = 'olduser1';
select * from searchrequest ;
update searchrequest set reqcontent = 'assignee = mjoanis AND status = Resolved' where id = 10001;
select * from searchrequest ;
update searchrequest set reqcontent = 'assignee = newuser2 AND status in (Open, \"In Progress\", Reopened, Resolved)' where id = 10300;
update searchrequest set reqcontent = 'assignee = newuser2 AND status in (Open, "In Progress", Reopened, Resolved)' where id = 10300;
select * from searchrequest ;
select * from sequence_value_item ;
select * from serviceconfig ;
select * from sharepermissions;
select * from trackback_ping ;
select * from trustedapp ;
select * from updatedhistory;
select * from upgradehistory ;
select * from upgradeversionhistory ;
select * from userassociation ;
select distinct source_name from userassociation ;
update userassociation set source_name = 'newuser4' where source_name ~ 'firstname4';
update userassociation set source_name = 'newuser3' where source_name ~ 'firstname3';
update userassociation set source_name = 'newuser1' where source_name ~ 'lastname1';
update userassociation set source_name = 'newuser0' where source_name ~ 'lastname0';
update userassociation set source_name = 'mjoanis' where source_name ~ 'joanis';
update userassociation set source_name = 'newuser2' where source_name ~ 'lastname2';
select * from userbase ;
select * from userhistoryitem ;
select distinct username from userhistoryitem ;
update userhistoryitem set username = 'mjoanis' where username ~ 'joanis';
update userhistoryitem set username = 'mjoanis' where username = 'maximejoanis';
select * from userpickerfilter;
select * from userpickerfiltergroup ;
select * from userpickerfilterrole ;
select * from versioncontrol ;
select * from votehistory ;
select * from workflowscheme;
select * from workflowschemeentity ;
select * from worklog ;
select distinct author from worklog;
update worklog set author = 'newuser0' where author ~ 'lastname0';
update worklog set author = 'newuser1' where author ~ 'lastname1';
update worklog set author = 'mjoanis' where author ~ 'joanis';
select distinct updateauthor from worklog;
update worklog set updateauthor = 'mjoanis' where updateauthor ~ 'joanis';
update worklog set updateauthor = 'newuser1' where updateauthor ~ 'lastname1';
update worklog set updateauthor = 'newuser0' where updateauthor ~ 'lastname0';
select * from avatar ;
select distinct owner from avatar;
update avatar set owner = 'newuser1' where owner ~ 'lastname1';
update avatar set owner = 'newuser1' where owner = 'olduser1';
update avatar set owner = 'newuser1' where owner = 'olduser1';
update avatar set owner = 'newuser1' where owner ~ 'lastname1';
update avatar set owner = 'newuser3' where owner ~ 'lastname3';
update avatar set owner = 'newuser0' where owner ~ 'lastname0';
select * from changegroup ;
select distinct author from changegroup ;
update changegroup set author = 'newuser3' where author ~ 'lastname3';
update changegroup set author = 'newuser1' where author ~ 'lastname1';
update changegroup set author = 'newuser0' where author ~ 'lastname0';
update changegroup set author = 'mjoanis' where author ~ 'joanis';
update changegroup set author = 'newuser2' where author ~ 'lastname2';
select * from changeitem ;
select distinct oldvalue from changeitem ;
update changeitem set oldvalue = 'newuser7' where oldvalue = 'olduser7';
update changeitem set oldvalue = 'newuser6' where oldvalue = 'olduser6';
update changeitem set oldvalue = 'newuser4' where oldvalue = 'olduser4';
update changeitem set oldvalue = 'newuser7' where oldvalue = 'olduser7_2';
update changeitem set oldvalue = 'newuser8' where oldvalue = 'olduser8';
update changeitem set oldvalue = 'newuser1' where oldvalue = 'olduser1';
update changeitem set oldvalue = 'newuser2' where oldvalue = 'olduser2';
update changeitem set oldvalue = 'mjoanis' where oldvalue = 'maximejoanis';
update changeitem set oldvalue = 'newuser0' where oldvalue = 'olduser0';
select distinct newvalue from changeitem ;
update changeitem set newvalue = 'newuser0' where newvalue = 'olduser0';
update changeitem set newvalue = 'mjoanis' where newvalue = 'maximejoanis';
update changeitem set newvalue = 'newuser2' where newvalue = 'olduser2';
update changeitem set newvalue = 'newuser1' where newvalue = 'olduser1';
update changeitem set newvalue = 'newuser8' where newvalue = 'olduser8';
update changeitem set newvalue = 'newuser7' where newvalue = 'olduser7_2';
update changeitem set newvalue = 'newuser4' where newvalue = 'olduser4';
update changeitem set newvalue = 'newuser6' where newvalue = 'olduser6';
update changeitem set newvalue = 'newuser7' where newvalue = 'olduser7';
\q
\c jiradb
select * from jiraissue;
\q
\dt
\c jiradb
\dt
select * from AO_E8B6CC_COMMIT;
\q
\c jiradb
select distict assignee from jiraissue;
select distinct assignee from jiraissue;
\dt
select * from AO_E8B6CC_COMMIT;
\q
\c jiradb
\dt
select * from cwd_directory;
select * from cwd_directory_attribute ;
select * from cwd_directory_attribute where attribute_name ~ 'object';
select * from cwd_directory_attribute where attribute_name ~ 'filter';
select * from cwd_directory_attribute where attribute_name ~ 'rdn';
select * from cwd_directory_attribute where attribute_name ~ 'first';
select * from cwd_directory_attribute where attribute_name ~ 'last';
select * from cwd_directory_attribute where attribute_name ~ 'display';
select * from cwd_directory_attribute where attribute_name ~ 'email';
select * from cwd_directory_attribute where attribute_name ~ 'pass';
select * from cwd_directory_attribute where attribute_name ~ 'unique';
select * from cwd_directory_attribute where attribute_name ~ 'i';
select * from cwd_directory_attribute where attribute_name ~ 'id';
select * from cwd_directory_attribute where attribute_name ~ 'group';
select * from cwd_directory_attribute where attribute_name ~ 'memb';
\c confluence
\dt
select * from users;
select * from user_mapping ;
select * from attachmentdata ;
select * from attachments;
select * from bandana ;
select count(*) from bandana ;
select * from bandana ;
select banadanaid from bandana where bandanacontext ~ 'joanis' or banadakey ~ 'joanis' or bandanavalue ~ 'joanis';
select bandanaid from bandana where bandanacontext ~ 'joanis' or bandanakey ~ 'joanis' or bandanavalue ~ 'joanis';
select * from bodycontent ;
select count(*) from bodycontent ;
select * from clustersafety ;
select * from confancestors ;
select * from confversion ;
select * from content;
select * from content_label ;
select * from content_perm;
select * from content_perm_set;
select * from contentproperties ;
select * from cwd_app_dir_group_mapping ;
select * from cwd_app_dir_mapping ;
select * from cwd_app_dir_operation ;
select * from cwd_application ;
select * from cwd_application_address ;
select * from cwd_application_attribute ;
select * from cwd_directory ;
select * from cwd_directory_attribute ;
select * from cwd_directory_operation ;
select * from cwd_group ;
select * from cwd_group_attribute ;
select * from cwd_membership ;
select * from cwd_user ;
select * from cwd_user_attribute ;
select * from cwd_user_credential_record ;
select * from decorator ;
select * from external_entities ;
select * from external_members ;
select * from extrnlnks ;
select * from follow_connections ;
select * from groups ;
select * from hibernate_unique_key ;
select * from imagedetails ;
select * from indexqueueentries ;
select * from keystore ;
select * from label ;
select * from likes ;
select * from links ;
select * from local_members ;
select * from logininfo ;
select * from notifications ;
select * from os_group ;
select * from os_propertyentry ;
select * from os_user ;
select * from os_user_group ;
select * from pagetemplates ;
select * from plugindata ;
select * from remembermetoken ;
select * from spacegrouppermissions ;
select * from spacegroups ;
select * from spacepermissions ;
select * from spaces ;
select * from trackbacklinks ;
select * from trustedapp;
select * from trustedapprestriction ;
select * from user_mapping;
delete from user_mapping where username = 'newuser5';
select * from content where user_key = 000000004492dccf014492ed71b10030;
select * from content where user_key = '000000004492dccf014492ed71b10030';
select * from content where username = '000000004492dccf014492ed71b10030';
delete from content where contentid = 7012382;
delete from user_mapping where username = 'newuser5';
delete from user_mapping where username = 'newuser6';
delete from content where username = (select user_key from user_mapping where username = 'newuser6');
delete from user_mapping where username = 'newuser6';
delete from content where username = (select user_key from user_mapping where username = 'mjoanis');
delete from user_mapping where username = 'mjoanis';
select * from logininfo;
delete from logininfo where username = '000000004492dccf014492ed71b50034';
delete from user_mapping where username = 'mjoanis';
delete from content where username = (select user_key from user_mapping where username = 'newuser4');
delete from user_mapping where username = 'newuser4';
delete from content where username = (select user_key from user_mapping where username = 'newuser10');
delete from user_mapping where username = 'newuser10';
delete from content where username = (select user_key from user_mapping where username = 'newuser11');
delete from user_mapping where username = 'newuser11';
delete from content where username = (select user_key from user_mapping where username = 'newuser8');
delete from user_mapping where username = 'newuser8';
delete from content where username = (select user_key from user_mapping where username = 'newuser1');
delete from user_mapping where username = 'newuser1';
delete from content where username = (select user_key from user_mapping where username = 'newuser12');
delete from user_mapping where username = 'newuser12';
delete from content where username = (select user_key from user_mapping where username = 'newuser13');
delete from user_mapping where username = 'newuser13';
delete from content where username = (select user_key from user_mapping where username = 'newuser2');
delete from user_mapping where username = 'newuser2';
delete from content where username = (select user_key from user_mapping where username = 'newuser14');
delete from user_mapping where username = 'newuser14';
delete from content where username = (select user_key from user_mapping where username = 'newuser0');
delete from user_mapping where username = 'newuser0';
delete from content where username = (select user_key from user_mapping where username = 'newuser15');
delete from user_mapping where username = 'newuser15';
delete from content where username = (select user_key from user_mapping where username = 'newuser3');
delete from user_mapping where username = 'newuser3';
delete from content where username = (select user_key from user_mapping where username = 'newuser16');
delete from user_mapping where username = 'newuser16';
delete from content where username = (select user_key from user_mapping where username = 'newuser17');
delete from user_mapping where username = 'newuser17';
delete from content where username = (select user_key from user_mapping where username = 'newuser18');
delete from user_mapping where username = 'newuser18';
delete from content where username = (select user_key from user_mapping where username = 'newuser7');
delete from user_mapping where username = 'newuser7';
delete from content where username = (select user_key from user_mapping where username = 'newuser19');
delete from user_mapping where username = 'newuser19';
select * from user_mapping;
update user_mapping set username = 'newuser5', lower_username = 'newuser5' where username = 'olduser5';
select * from user_mapping ;
update user_mapping set username = 'newuser6', lower_username = 'newuser6' where username = 'olduser6';
update user_mapping set username = 'mjoanis', lower_username = 'mjoanis' where username = 'maximejoanis';
update user_mapping set username = 'newuser4', lower_username = 'newuser4' where username = 'olduser4';
update user_mapping set username = 'newuser10', lower_username = 'newuser10' where username = 'olduser10';
update user_mapping set username = 'newuser11', lower_username = 'newuser11' where username = 'olduser11';
update user_mapping set username = 'newuser8', lower_username = 'newuser8' where username ~ 'lastname8';
update user_mapping set username = 'newuser1', lower_username = 'newuser1' where username ~ 'olduser1';
update user_mapping set username = 'newuser12', lower_username = 'newuser12' where username ~ 'lastname12';
update user_mapping set username = 'newuser13', lower_username = 'newuser13' where username ~ 'lastname13';
update user_mapping set username = 'newuser2', lower_username = 'newuser2' where username ~ 'lastname2';
update user_mapping set username = 'newuser14', lower_username = 'newuser14' where username ~ 'olduser14';
update user_mapping set username = 'newuser0', lower_username = 'newuser0' where username ~ 'lastname0';
update user_mapping set username = 'newuser15', lower_username = 'newuser15' where username ~ 'lastname15';
update user_mapping set username = 'newuser3', lower_username = 'newuser3' where username ~ 'lastname3';
update user_mapping set username = 'newuser16', lower_username = 'newuser16' where username ~ 'lastname16';
update user_mapping set username = 'newuser17', lower_username = 'newuser17' where username ~ 'lastname17';
update user_mapping set username = 'newuser18', lower_username = 'newuser18' where username ~ 'lastname18';
update user_mapping set username = 'newuser7', lower_username = 'newuser7' where username ~ 'lastname7';
delete from user_mapping where username = 'newuser7';
update user_mapping set username = 'newuser7', lower_username = 'newuser7' where username ~ 'lastname7';
select * from user_mapping ;
delete from user_mapping where username = 'olduser7_2';
delete from content where username = (select user_key from user_mapping where username = 'olduser7_2');
delete from user_mapping where username = 'olduser7_2';
update user_mapping set username = 'newuser7', lower_username = 'newuser7' where username ~ 'lastname7';
select * from user_mapping ;
update user_mapping set username = 'newuser19', lower_username = 'newuser19' where username ~ 'lastname19';
select * from user_mapping ;
select * from user_mapping where username != lower_username;
select * from user_mapping where username = lower_username;
\q
\s postgres-history.txt

[Maxime_Joanis]

Well, it seems JIRA is using username strings as user keys... coupling JIRA with the authentication method. Hm.

Anyway... So I browsed the whole JIRA DB and replaced the old usernames with the new ones in tables which seemed to require it. I then had to reindex search indexes (otherwise the old usernames still had to be used in searches).

It's a bit tricky, as some tables clearly have username columns while some others have username references among other contents (like some configuration tables, JQL strings, etc.). Also, tables which index LDAP users don't need to be modified.

Looks like it worked so far.

I can't help but suggest that username != user (and to use numerical comparisons for such things).

For Confluence, it was much easier. Mostly user_mapping was to modify. This table, and a few others to remove traces from users created by the addition of the new user directory. I then updated the old usernames for the new ones to preserve their UUIDs. Works too!

[Maxime_Joanis]

I have just updated to 6.2. I have looked into the user renaming feature... It seems this feature is only available for the internal directory which I don't use. I have a Microsoft Active Directory and an Apple Open Directory. We are about to disconnect the Apple one to keep the new Microsoft one. I need to remap users which were in the Apple directory to users which are in the Microsoft directory. All I need to do is to replace Apple directory usernames by Microsoft directory usernames. So, it's probably just updating the username for each user from his Apple directory one to his new Microsoft directory one (assuming JIRA doesn't use the username string as the user key, otherwise that's going to be more complicated). Do I need to modify the DB tables directly? Anyone has any indications for this?

[Genia Photonics IT]

Only one user was logged in so far (me), and I didn't create new content with it. I have synchronized the new LDAP though. I don't know if that might cause problem. I guess the best plan right now could be to upgrade JIRA and look at that UUID linking feature you've mentioned.

Thanks for your reply!

For the record, I'm currently running JIRA 5.0.4

[Zul NS _Atlassian_]

The latest JIRA version has the feature to rename username from LDAP based on its UUID. But it seems that it may be to late to try the feature out.

I would suggest a manual way since the users are already logged in using their new username. Which is to bulk move all the old usernames' assigned or reported ticket. I'm afraid, the user would loose some of the data such as saved filters.

I'm not sure for Confluence though.