<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=344430429281371&amp;ev=PageView&amp;noscript=1">

Renaming Users: How We Almost Broke Confluence and Jira

Posted by Foogie Sim on February 13, 2019

We recently decided to streamline all our logins across our internal ServiceRocket systems (which include Confluence and Jira instances), to use a Single Sign On (SSO) service called Okta (in case you haven't heard, Okta is one of the major Identity Providers in the SSO sphere). Part of the requirement for us to do that is to change our login format from usernames (which we have been using for the past decade or so), to email address (appending @servicerocket.com):

Screen Shot 2019-02-12 at 18.50.32

Screen Shot 2019-02-12 at 18.51.24

 

With Confluence and Jira's built in capability to rename users automatically (as long as their external_id remains the same - explained below), this should be a pretty straightforward change. This is helped by the fact that all our users are managed by Crowd, thus allowing us to simply rename the users within Crowd, which should hopefully trigger the rename in all connected systems, see: Screen Shot 2019-02-12 at 18.55.25

 
What happens in the DB in these systems:

Before:

cwd_user table

lower_user_name
external_id
fooguan.sim abcd-1234

app_user table (JIRA)

user_key
lower_user_name
fooguan.sim fooguan.sim

user_mapping table (Confluence)

user_key
lower_username
4028808a65e7e00f0165e7fe518c0005 fooguan.sim

 

After:

Confluence and Jira detects that the external_id already exists in the cwd_user table, but with a different username. This triggers the user rename procedure, resulting in the results below:

cwd_user table

lower_user_name
external_id
fooguan.sim@servicerocket.com abcd-1234

app_user table (JIRA)

user_key
lower_user_name
fooguan.sim fooguan.sim@servicerocket.com

user_mapping table (Confluence)

user_key
lower_username
4028808a65e7e00f0165e7fe518c0005 fooguan.sim@servicerocket.com

 

The key point to understand here is that the user_key in both Confluence and Jira databases need to stay the same for the user before and after the rename. Only the user_name, lower_user_name and lower_username is allowed to change!


This is important, because the user_key is the primary key used to link Content and Jira issues to the actual users.

See:

Jira

jiraissues table

SUMMARY
REPORTER
ASSIGNEE
Test Issue 123 fooguan.sim fooguan.sim

app_user table (JIRA)

user_key
lower_user_name
fooguan.sim fooguan.sim@servicerocket.com

 

Confluence

CONTENT table

TITLE
CREATOR
LASTMODIFIER
This is a page 4028808a65e7e00f0165e7fe518c0005 4028808a65e7e00f0165e7fe518c0005

user_mapping table (Confluence)

user_key
lower_username
4028808a65e7e00f0165e7fe518c0005 fooguan.sim@servicerocket.com

 As long as the user_key is tied to the correct entries in the CONTENT, jiraissues (and a bunch of other tables, too many to list here), then the renaming operation is a success and users will maintain their content ownership.

 

But...Something Went Wrong

Before we performed the change, we decided to run the following 2 queries in Confluence and JIRA DB respectively:

 Screen Shot 2019-02-12 at 19.00.18

 As some of you may have guessed, these queries should not return any results at all. But sadly, they do, and quite a bunch of rows too.

Why shouldn't these queries return any results? One reason is that since we are changing the usernames from a format like "fooguan.sim" to "fooguan.sim@servicerocket.com", the rename will fail if it detects that the username that is being renamed to, already exists in the DB. Example:

Before:

cwd_user table

lower_user_name
external_id
fooguan.sim@servicerocket.com abcd-1234

app_user table (JIRA)

user_key
lower_user_name
fooguan.sim fooguan.sim
fooguan.sim@servicerocket.com fooguan.sim@servicerocket.com

user_mapping table (Confluence)

user_key
lower_username
4028808a65e7e00f0165e7fe518c0005 fooguan.sim
4028808a65e7e00f0165e7f312318995 fooguan.sim@servicerocket.com

 

After:

(Rename fails, data in these tables remain the same, except for the cwd_user table)

cwd_user table

lower_user_name
external_id
fooguan.sim@servicerocket.com abcd-1234

app_user table (JIRA)

user_key
lower_user_name
fooguan.sim fooguan.sim
fooguan.sim@servicerocket.com fooguan.sim@servicerocket.com

user_mapping table (Confluence)

user_key
lower_username
4028808a65e7e00f0165e7fe518c0005 fooguan.sim
4028808a65e7e00f0165e7f312318995 fooguan.sim@servicerocket.com

 

You may argue that since the username is renamed in cwd_user, it should then work, shouldn't it? Well, the user is able to login with the new email address format, but sadly, the user will not be linked to his/her old content. Content that the user previously authored will be marked as authored by an Unknown User, and similarly, issues assigned to this user will be marked as assigned to an Unknown User

This is because of the mismatch in the user_key:

Jira

jiraissues table

SUMMARY
REPORTER
ASSIGNEE
Test Issue 123 fooguan.sim fooguan.sim

app_user table (JIRA)

user_key
lower_user_name
fooguan.sim fooguan.sim
fooguan.sim@servicerocket.com fooguan.sim@servicerocket.com

Screen Shot 2019-02-12 at 19.02.51 

Confluence

CONTENT table

TITLE
CREATOR
LASTMODIFIER
This is a page 4028808a65e7e00f0165e7fe518c0005 4028808a65e7e00f0165e7fe518c0005

user_mapping table (Confluence)

user_key
lower_username
4028808a65e7e00f0165e7fe518c0005 fooguan.sim
4028808a65e7e00f0165e7f312318995 fooguan.sim@servicerocket.com

Screen Shot 2019-02-12 at 19.03.36

 

 

How Should You Fix This?

Alternative 1: Fix Before Rename

Alternative 1 is probably the best alternative, which is to make sure these users with @servicerocket.com (the new username) has no content tied to them, and remove them from the system before the rename. This will ensure a clean and safe rename but note that from our experience, deleting from the UI may not remove the app_user and user_mapping entries, so Alternative 2 then becomes more viable. 

Alternative 2: Fix After the Rename

Alternative 2 is what we call the swap method. Swap the user_key and username pairing between the broken user and the proper user.

The concept is simple: whatever queries you run, make sure that you end up with an entry that has the new username (in this case: fooguan.sim@servicerocket.com) as its lower_username but with the old user_key. In this case it would look like below:

cwd_user table

lower_user_name
external_id
fooguan.sim@servicerocket.com abcd-1234

app_user table (JIRA)

user_key
lower_user_name
fooguan.sim fooguan.sim@servicerocket.com

user_mapping table (Confluence)

user_key
lower_username
4028808a65e7e00f0165e7fe518c0005 fooguan.sim@servicerocket.com

For our case, we simply ran the following (example only, and works well on our instance, but may not work on others. Do not run this on your own instance unless you have already consulted us!):

Jira

UPDATE app_user SET lower_user_name = 'fooguan.sim@servicerocket.com.old' WHERE user_key = 'fooguan.sim@servicerocket.com';
UPDATE app_user SET lower_user_name = 'fooguan.sim@servicerocket.com' WHERE user_key = 'fooguan.sim';
UPDATE app_user SET lower_user_name = 'fooguan.sim' WHERE user_key = 'fooguan.sim@servicerocket.com';

Confluence

UPDATE user_mapping SET lower_username = 'fooguan.sim@servicerocket.com.old', username = 'fooguan.sim@servicerocket.com.old' WHERE lower_username = 'fooguan.sim@servicerocket.com';
UPDATE user_mapping SET lower_username = 'fooguan.sim@servicerocket.com', username = 'fooguan.sim@servicerocket.com' WHERE lower_username = 'fooguan.sim';
UPDATE user_mapping SET lower_username = 'fooguan.sim', username = 'fooguan.sim' WHERE lower_username = 'fooguan.sim@servicerocket.com.old';

 

Topics: Jira, Confluence

Subscribe To
Our Newsletter

Interested in writing for the Software Adoption Blog?

We love connecting with software leaders and writers who can help us fulfill our mission to create entertaining AND educational resources that people can put to use.

Find Out How ➝

Recent Posts

Posts by Topic

see all