Altering user accounts in the database directly

[David Owen]

Hi

I have a question about user account management that I hoped somebody might be able to help with.

We've been using XNAT at <a href="https://www.cardiff.ac.uk/cardiff-university-brain-research-imaging-centre">CUBRIC</a> since 2016 with great success.

We're looking to tidy up our user accounts where unwanted duplicates exist. I've looked through all of the previous discussions on this topic for help, but can't seem to overcome a particular problem. That is, after altering a user via SQL, its project memberships seem to break and can't be fixed. This is despite the fact that no changes are made to any xdat_user_id values.

We have two particular user accounts that we're trying to alter. User "waans" is to be renamed to "waans_old" and user "waans_01" is to then be renamed to "waans". The waans_old user is the one that is to be effectively deleted (but not actually deleted of course).

This is how the user accounts are represented in the xdat_user, xhbm_xdat_user_auth, and xdat_user_groupid tables.

xdat_user
=========
|login   |enabled|verified|user_info|xdat_user_id|
|--------|-------|--------|---------|------------|
|waans   |0      |0       |145      |145         |
|waans_01|1      |1       |146      |146         |

xhbm_xdat_user_auth
===================
|id |disabled               |enabled|auth_method|auth_method_id|auth_user|xdat_username|
|---|-----------------------|-------|-----------|--------------|---------|-------------|
|145|1970-01-01 01:00:00.000|true   |localdb    |              |waans    |waans        |
|146|1970-01-01 01:00:00.000|true   |ldap       |ldap1         |waans    |waans_01     |

xdat_user_groupid
=================
|groupid            |user_groupid_info|xdat_user_groupid_id|groups_groupid_xdat_user_xdat_user_id|
|-------------------|-----------------|--------------------|-------------------------------------|
|441_project_member |1,418            |1,418               |146                                  |
|416_project_member |1,417            |1,417               |146                                  |

The following statements are used to make the desired changes (only attempted in our development environment so far):

1) xdat_user: rename waans to waans_old
UPDATE xdat_user SET login = 'waans_old', email = '' WHERE login = 'waans';
2) xdat_user: rename waans_01 to waans
UPDATE xdat_user SET login = 'waans' WHERE login = 'waans_01';
3) xhbm_xdat_user_auth: id 146 - change xdat_username to waans
UPDATE xhbm_xdat_user_auth SET xdat_username = 'waans' WHERE id = 146;
4) xhbm_xdat_user_auth: id 145 - change auth_user and xdat_username to waans_old
UPDATE xhbm_xdat_user_auth SET xdat_username = 'waans_old', auth_user = 'waans_old', enabled = FALSE, disabled = NOW() WHERE id = 145;

However, user with ID 146 (now waans) now appears corrupted. Its project memberships are no longer listed when clicking on the user in XNAT (Administer -> Users). This seems odd because the ID (146) hasn't changed so references across the three above tables should be maintained. Trying to re-add the user's project memberships in XNAT causes the error "An error occurred adding user role. [object Object]" to appear. I've also tried restarting Tomcat but that doesn't have an effect.

Is anybody able to advise, please, on how (or why) the user account becomes corrupted in this case (and therefore how we can complete the user renaming).

We currently use XNAT version 1.8.6.1 (build: 17), PostgreSQL 12.2, Tomcat 9.0.65, Debian 10 (buster). We deploy using this Docker file: https://github.com/NrgXnat/xnat-docker-compose/blob/master/xnat/Dockerfile

Regards

David