How to edit AD imported user which is deleted in AD?

[Jacek Nowak]

Hello everyone!

Let me explain the situation. We identify users in our company using a code in format:
ABC1234. Sometimes (rarely but still...) it may be that we have to change that code. In that case completely new account is created. It has the same name and surname and in some cases the same email.

In Jira we are using incoming mail handlers in order to create tickets from email. Here is the problem... After we create new user in AD with the same email it's imported to Jira of course. Then old user is deactivated in AD and finally after 1 month it is deleted completely. We assume that deleted user did some work inside Jira, e.g. reported or was assigned to a task. Jira keeps both accounts and they have exactly the same email. For some reason email handler checks the status of the oldest user (which is inactive in Jira) and throws an error in logs that ticket could not be created because the user has no access to Jira.

I've already found some questions on the community on that topic. They all propose changing an email of inactive user as a solution. I'm okay with that but I can't do anything with this user because he is imported from active directory. I've tried AD resynchronization in Jira, changing an email in database but with no success.

This is my situation right now. Two users, both imported from AD.

First user (was created earlier, inactive):
Username: ABC1234
Full name: John Doe, (john.doe@company.com) [X]
Email: john.doe@company.com

Second user (new user created recently, active):
Username: CBA4321
Full name: John Doe, (john.doe@company.com)
Email: john.doe@company.com

First user is no longer present in Active Directory, he was deleted. However his account in Jira has some ticket "connections". He wrote some comments and created a few issues.

[Andy Heinzer · Accepted solution]

Hi Jacu,

I see that you are looking to change the email address of an AD/LDAP user in Jira.  However even direct SQL changes to that record appear to fail this purpose.  I think I can shed some more light on the problem here.

Active Directory may have called that user object 'deleted' however this status is somewhat misleading because the object technically still exists Active Directory, but the user cannot utilize any of the services Active Directory previous granted to them.  Moreover, when Jira is syncing to this directly, it still can see that deleted object and sync data from it.  Which is why after you try to manually change the email address in the database, Jira appears to be changing that email address back to what it was.  This detail is noted in the Username section of the documentation in Connecting to an LDAP directory.

I found a pair of interesting guides in regards to recovering that object in Active Directory:

• A Step-By-Step Guide to Restore Deleted Objects in Active Directory
• How to restore deleted user accounts and their group memberships in Active Directory

You, as a Jira Admin, might not be the user that can follow these steps, so you might actually need to get your AD admin to try these steps.   If the AD admin could temporarily restore that object in AD, then change the email address to some different value like 'username@example.com'.  Jira could then sync that change for that account.  Which would then allow you to have the new account be the only one with the desired email address in Jira.

That is probably the method that makes the most sense and requires the least amount of hacking into Jira.  That said, I can see another alternative method where you might be able to adjust this in Jira's SQL database.

Jira has a table in the database called app_user.  This table has existed since Jira 6.0.  It specifically exists to keep track of user account renames in Jira. This table only has two columns: user_key and lower_user_name.  When an account is created in Jira for the first time, each account is generated a unique user_key value in that table.  Right now the default behavior (In Jira 8.4 and below) has been to use the lower_user_name value at the time this account is created to be that user_key, and if that value is already taken, append a #1 to that name (or increment that value, #2, #3, etc).  Jira does not expect to change the user_key value ever, even if the username changes.   The value in lower_user_name in that table can change though, and it will change anytime the user account is renamed.  It will then be expected to match the cwd_user table and the lower_user_name value in that table.

With that info in mind, you could make an adjustment to the app_user table here instead.  There is some risk in doing this, especially in regards to the historic account actions.  Many functions in Jira record the user_key of an action instead of the user_name, so to make a change to the user_key field does have the potential to alter the historic account actions.   So if you go this route, definitely create a back of your SQL database first, and I would also suggest testing this change in a staging environment before trying it in a production site.  It might be a viable work-around, but being there exists potential to alter historic info in Jira, perhaps the first option would be better to approach instead.

Let me know if you have any concerns about either option here.

Andy

[Jacek Nowak]

Hello Andy!

Thank you for detailed explanation.

I've been checking sources you provided regarding AD user restoration. Unfortunately all of them assume that user was deleted recently. All I can say is that this deleted user last login in Jira was over a year ago. I have some permissions to manipulate AD objects and according to these tutorials Recycle Bin might not be enabled. I don't see any folder with deleted items.

From my perspective user doesn't exist in AD anymore and if I make brand new Jira instance and import users he won't be on the list anymore. I don't know if my assumption is correct but I think that the only reason he's present as inactive is the fact that some objects (as issues and comments) inside this instance depend on this user. That's why I don't understand why this kind of dummy user is not editable.

Of course I'm gonna verify my statement regarding user import. As soon as I can I will prepare clear Jira instance on my testing environment and then I will turn on LDAP in order to check which users are going to be imported.

[Andy Heinzer]

Hi Jacek,

Creating a new user directory in Jira won't necessarily relieve you of this un-editable user account.  This account is currently just a placeholder.  If the account was truly deleted within Jira, it would cause problems in regards to seeing the historical actions of that account.  For examples, issue edits, issue creation, comments etc, these actions all tie back to the account's user_key in Jira.  Without a value for that account, Jira won't be able to tell who did what and when.  There is more info on the differences between deactivating and removing an account in Create, edit, or remove a user.

If you can't undelete that account, try this instead:

1. In LDAP/AD: create a new user account with the same username as the previously deleted account in your example ABC1234
2. Give that account some bogus email address such as text@example.com (I like the example.com domain because it's IANA reserved domain name)
3. Get Jira to sync with LDAP once more, to bring that user account in.
4. Once you can confirm that the user account email address in Jira has changed in that LDAP directory to use that bogus email address, you can then deactivate the user account in LDAP (such as disable the account, or delete if you wish)

This way you can affect change on that previous account as it sits in Jira.

Please let me know if you have any questions about this.

Andy

[Jacek Nowak]

I am aware of everything you described and for that reason since the beginning I tried to change that email instead of removing entire user account ;)

I will try this in the nearest future as your latest solution sounds very reasonable. Thank you for this one.