All users must use Lan Id as username

[ddelvecchio]

Make sure all users in database are using their lan ID for their username

[cseppan]

Investigate issues involved with changing usernames

[cseppan]

Most of the EMF database tables that point to a user use a foreign key to the users.id field. However, there are three tables with foreign keys referencing the users.username field:

• dataset_access_logs.username
• dataset.creator
• status.username

If we try to directly update a username, we'll run into the foreign key constraints on these tables. Instead, we can use a temporary user account to reassign the values.

Here's an example of how to update a username from "abc" to "abclastname":

1) Create a temporary, non-login user (username = "tmp_user")

insert into users (username, password, name, affiliation, phone, email, is_account_disabled) values ('tmp_user', '', '', '', '', '', true);

2) Reassign the data table values to the temporary user

update dataset_access_logs set username = 'tmp_user' where username = 'abc';
update datasets set creator = 'tmp_user' where creator = 'abc';
update status set username = 'tmp_user' where username = 'abc';

3) Update the actual user's username

update users set username = 'abclastname' where username = 'abc';

4) Reassign the data table values to the actual user

update dataset_access_logs set username = 'abclastname' where username = 'tmp_user';
update datasets set creator = 'abclastname' where creator = 'tmp_user';
update status set username = 'abclastname' where username = 'tmp_user';

5) Repeat for additional users, if needed

6) Delete the temporary user

delete from users where username = 'tmp_user';

[eyth]

Thanks Catherine - this looks doable...

On Mon, Apr 15, 2024 at 1:17 PM Catherine Seppanen @.***> wrote:

Most of the EMF database tables that point to a user use a foreign key to the users.id field. However, there are three tables with foreign keys referencing the users.username field:

• dataset_access_logs.username
• dataset.creator
• status.username

If we try to directly update a username, we'll run into the foreign key constraints on these tables. Instead, we can use a temporary user account to reassign the values.

Here's an example of how to update a username from "abc" to "abclastname":

1. Create a temporary, non-login user (username = "tmp_user")

insert into users (username, password, name, affiliation, phone, email, is_account_disabled) values ('tmp_user', '', '', '', '', '', true);

2. Reassign the data table values to the temporary user

update dataset_access_logs set username = 'tmp_user' where username = 'abc';update datasets set creator = 'tmp_user' where creator = 'abc';update status set username = 'tmp_user' where username = 'abc';

3. Update the actual user's username

update users set username = 'abclastname' where username = 'abc';

4. Reassign the data table values to the actual user

update dataset_access_logs set username = 'abclastname' where username = 'tmp_user';update datasets set creator = 'abclastname' where creator = 'tmp_user';update status set username = 'abclastname' where username = 'tmp_user';

5.

Repeat for additional users, if needed 6.

Delete the temporary user

delete from users where username = 'tmp_user';

— Reply to this email directly, view it on GitHub https://github.com/USEPA/emf/issues/137#issuecomment-2057431834, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB26PS3ZNWU6WQJRJOWRCSTY5QDRLAVCNFSM6AAAAAA2NL43DWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANJXGQZTCOBTGQ . You are receiving this because you are subscribed to this thread.Message ID: @.***>