Unable to delete some user access.

[ianliuwk1019]

The system seems to have trouble deleting some user access but some are fine.

---

For different scenarios:

• With unknown forest client number:

• With error response 422:

To Reproduce
Steps to reproduce the behavior:

1. Login into 'FAM-DEV'.
2. Select application 'Forest Operation Map' and on 'User' tab.
3. Find 'gatitebi' or 'IANLIU'(BCeID domain).
4. Try to delete user permission.

Expected behavior
Delete should be successful (for application admin at least)

Additional context
The log on user has these privileges:

[ianliuwk1019]

The 422 error scenario during removing user access:

2025-01-24 22:44:29 - 290 - api.app.routers.router_guards - DEBUG - Dependency 'get_target_user_from_id' called with request containing user_role_xref_id path param 174.
	
2025-01-24T22:44:29.124Z
2025-01-24 22:44:29 - 1846 - sqlalchemy.engine.Engine - INFO - SELECT app_fam.fam_user_role_xref.user_role_xref_id AS app_fam_fam_user_role_xref_user_role_xref_id, app_fam.fam_user_role_xref.user_id AS app_fam_fam_user_role_xref_user_id, app_fam.fam_user_role_xref.role_id AS app_fam_fam_user_role_xref_role_id, app_fam.fam_user_role_xref.create_user AS app_fam_fam_user_role_xref_create_user, app_fam.fam_user_role_xref.create_date AS app_fam_fam_user_role_xref_create_date, app_fam.fam_user_role_xref.update_user AS app_fam_fam_user_role_xref_update_user, app_fam.fam_user_role_xref.update_date AS app_fam_fam_user_role_xref_update_date, fam_forest_client_1.client_number_id AS fam_forest_client_1_client_number_id, fam_forest_client_1.forest_client_number AS fam_forest_client_1_forest_client_number, fam_forest_client_1.create_user AS fam_forest_client_1_create_user, fam_forest_client_1.create_date AS fam_forest_client_1_create_date, fam_forest_client_1.update_user AS fam_forest_client_1_update_user, fam_forest_client_1.update_date AS fam_forest_client_1_update_date, fam_role_1.role_id AS fam_role_1_role_id, fam_role_1.role_name AS fam_role_1_role_name, fam_role_1.role_purpose AS fam_role_1_role_purpose, fam_role_1.display_name AS fam_role_1_display_name, fam_role_1.application_id AS fam_role_1_application_id, fam_role_1.client_number_id AS fam_role_1_client_number_id, fam_role_1.create_user AS fam_role_1_create_user, fam_role_1.create_date AS fam_role_1_create_date, fam_role_1.parent_role_id AS fam_role_1_parent_role_id, fam_role_1.update_user AS fam_role_1_update_user, fam_role_1.update_date AS fam_role_1_update_date, fam_role_1.role_type_code AS fam_role_1_role_type_code, fam_user_type_code_1.user_type_code AS fam_user_type_code_1_user_type_code, fam_user_type_code_1.description AS fam_user_type_code_1_description, fam_user_type_code_1.effective_date AS fam_user_type_code_1_effective_date, fam_user_type_code_1.expiry_date AS fam_user_type_code_1_expiry_date, fam_user_type_code_1.update_date AS fam_user_type_code_1_update_date, fam_user_1.user_id AS fam_user_1_user_id, fam_user_1.user_type_code AS fam_user_1_user_type_code, fam_user_1.user_name AS fam_user_1_user_name, fam_user_1.create_user AS fam_user_1_create_user, fam_user_1.create_date AS fam_user_1_create_date, fam_user_1.user_guid AS fam_user_1_user_guid, fam_user_1.business_guid AS fam_user_1_business_guid, fam_user_1.cognito_user_id AS fam_user_1_cognito_user_id, fam_user_1.first_name AS fam_user_1_first_name, fam_user_1.last_name AS fam_user_1_last_name, fam_user_1.email AS fam_user_1_email, fam_user_1.update_user AS fam_user_1_update_user, fam_user_1.update_date AS fam_user_1_update_date 
	
2025-01-24 22:44:29 - 1846 - sqlalchemy.engine.Engine - INFO - SELECT app_fam.fam_user_role_xref.user_role_xref_id AS app_fam_fam_user_role_xref_user_role_xref_id, app_fam.fam_user_role_xref.user_id AS app_fam_fam_user_role_xref_user_id, app_fam.fam_user_role_xref.role_id AS app_fam_fam_user_role_xref_role_id, app_fam.fam_user_role_xref.create_user AS app_fam_fam_user_role_xref_create_user, app_fam.fam_user_role_xref.create_date AS app_fam_fam_user_role_xref_create_date, app_fam.fam_user_role_xref.update_user AS app_fam_fam_user_role_xref_update_user, app_fam.fam_user_role_xref.update_date AS app_fam_fam_user_role_xref_update_date, fam_forest_client_1.client_number_id AS fam_forest_client_1_client_number_id, fam_forest_client_1.forest_client_number AS fam_forest_client_1_forest_client_number, fam_forest_client_1.create_user AS fam_forest_client_1_create_user, fam_forest_client_1.create_date AS fam_forest_client_1_create_date, fam_forest_client_1.update_user AS fam_forest_client_1_update_user, fam_forest_client_1.update_date AS fam_forest_client_1_update_date, fam_role_1.role_id AS fam_role_1_role_id, fam_role_1.role_name AS fam_role_1_role_name, fam_role_1.role_purpose AS fam_role_1_role_purpose, fam_role_1.display_name AS fam_role_1_display_name, fam_role_1.application_id AS fam_role_1_application_id, fam_role_1.client_number_id AS fam_role_1_client_number_id, fam_role_1.create_user AS fam_role_1_create_user, fam_role_1.create_date AS fam_role_1_create_date, fam_role_1.parent_role_id AS fam_role_1_parent_role_id, fam_role_1.update_user AS fam_role_1_update_user, fam_role_1.update_date AS fam_role_1_update_date, fam_role_1.role_type_code AS fam_role_1_role_type_code, fam_user_type_code_1.user_type_code AS fam_user_type_code_1_user_type_code, fam_user_type_code_1.description AS fam_user_type_code_1_description, fam_user_type_code_1.effective_date AS fam_user_type_code_1_effective_date, fam_user_type_code_1.expiry_date AS fam_user_type_code_1_expiry_date, fam_user_type_code_1.update_date AS fam_user_type_code_1_update_date, fam_user_1.user_id AS fam_user_1_user_id, fam_user_1.user_type_code AS fam_user_1_user_type_code, fam_user_1.user_name AS fam_user_1_user_name, fam_user_1.create_user AS fam_user_1_create_user, fam_user_1.create_date AS fam_user_1_create_date, fam_user_1.user_guid AS fam_user_1_user_guid, fam_user_1.business_guid AS fam_user_1_business_guid, fam_user_1.cognito_user_id AS fam_user_1_cognito_user_id, fam_user_1.first_name AS fam_user_1_first_name, fam_user_1.last_name AS fam_user_1_last_name, fam_user_1.email AS fam_user_1_email, fam_user_1.update_user AS fam_user_1_update_user, fam_user_1.update_date AS fam_user_1_update_date
	
2025-01-24T22:44:29.124Z
FROM app_fam.fam_user_role_xref LEFT OUTER JOIN app_fam.fam_role AS fam_role_1 ON fam_role_1.role_id = app_fam.fam_user_role_xref.role_id LEFT OUTER JOIN app_fam.fam_forest_client AS fam_forest_client_1 ON fam_forest_client_1.client_number_id = fam_role_1.client_number_id LEFT OUTER JOIN app_fam.fam_user AS fam_user_1 ON fam_user_1.user_id = app_fam.fam_user_role_xref.user_id LEFT OUTER JOIN app_fam.fam_user_type_code AS fam_user_type_code_1 ON fam_user_type_code_1.user_type_code = fam_user_1.user_type_code 
	
FROM app_fam.fam_user_role_xref LEFT OUTER JOIN app_fam.fam_role AS fam_role_1 ON fam_role_1.role_id = app_fam.fam_user_role_xref.role_id LEFT OUTER JOIN app_fam.fam_forest_client AS fam_forest_client_1 ON fam_forest_client_1.client_number_id = fam_role_1.client_number_id LEFT OUTER JOIN app_fam.fam_user AS fam_user_1 ON fam_user_1.user_id = app_fam.fam_user_role_xref.user_id LEFT OUTER JOIN app_fam.fam_user_type_code AS fam_user_type_code_1 ON fam_user_type_code_1.user_type_code = fam_user_1.user_type_code
	
2025-01-24T22:44:29.124Z
WHERE app_fam.fam_user_role_xref.user_role_xref_id = %(user_role_xref_id_1)s
	
WHERE app_fam.fam_user_role_xref.user_role_xref_id = %(user_role_xref_id_1)s
	
2025-01-24T22:44:29.124Z
2025-01-24 22:44:29 - 1846 - sqlalchemy.engine.Engine - INFO - [generated in 0.00024s] {'user_role_xref_id_1': '174'}
	
2025-01-24 22:44:29 - 1846 - sqlalchemy.engine.Engine - INFO - [generated in 0.00024s] {'user_role_xref_id_1': '174'}
	
2025-01-24T22:44:29.143Z
2025-01-24 22:44:29 - 41 - api.app.database - WARNING - DB session exception: 1 validation error for TargetUserSchema
	
2025-01-24 22:44:29 - 41 - api.app.database - WARNING - DB session exception: 1 validation error for TargetUserSchema
	
2025-01-24T22:44:29.143Z
	
user_guid
	

• Cause: When retrieving the target user, the TargetUserSchema "user_guid" should not be a "None".

The backend schema implentation should be correct after verifying user (user access) creation in database. The user (if new) when first time is inserted into database it should already have "user_guid" value. There are several places in backend inserting a user:

• When user login that triggers Cognito post-update (our auth_function/lambda_function).
• When regular user access is added, delegated-admin user privilege is added, application admin user privilege is added.
  All of above logic all insert "user_guid" value.
  So it looks like in this scenario, the user is a "legacy" record (past loosely logic that does not require user_guid when inserting a user).
• Questions are, how many this type of user records exist? How do we deal with them (if still users that are valid and should exist for the system)?

---

Collecting such **empty user_guid** records found:

DEV:

TEST:

PROD:

[ianliuwk1019]

The unknown forest client number scenario:

2025-01-25T00:26:03.483Z
2025-01-25 00:26:03 - 544 - urllib3.connectionpool - DEBUG - https://nr-forest-client-api-test.api.gov.bc.ca:443 "GET /api/clients/search?page=0&size=50&id=12345678 HTTP/1.1" 200 None
	
2025-01-25 00:26:03 - 544 - urllib3.connectionpool - DEBUG - https://nr-forest-client-api-test.api.gov.bc.ca:443 "GET /api/clients/search?page=0&size=50&id=12345678 HTTP/1.1" 200 None
	
2025-01-25T00:26:03.484Z
2025-01-25 00:26:03 - 78 - api.app.integration.forest_client_integration - DEBUG - FC API result: []. Took: 0.220124 seconds
	
2025-01-25 00:26:03 - 78 - api.app.integration.forest_client_integration - DEBUG - FC API result: []. Took: 0.220124 seconds
	
2025-01-25T00:26:03.484Z
2025-01-25 00:26:03 - 159 - api.app.crud.services.permission_audit_service - DEBUG - Revoke user permission encountered problem.Unknown forest client number 12345678 for scoped permission FOM_SUBMITTER_12345678.
	
2025-01-25 00:26:03 - 159 - api.app.crud.services.permission_audit_service - DEBUG - Revoke user permission encountered problem.Unknown forest client number 12345678 for scoped permission FOM_SUBMITTER_12345678.
	
2025-01-25T00:26:03.484Z
{ 'eventTime': '2025-01-25 00:26:03', 'logger': 'api.app.utils.audit_util', 'level': 'INFO', 'eventMessage': {"auditEventTypeCode": "REMOVE_USER_ROLE_ACCESS", "auditEventResultCode": "FAIL", "applicationId": 2, "applicationName": "FOM_DEV", "applicationEnv": "DEV", "roleId": 74, "roleName": "FOM_SUBMITTER_12345678", "roleType": "C", "forestClientNumbers": [], "targetUser": {"userGuid": "7CEE19E97218419FADF740DBCD2B8265", "userType": "B", "idpUserName": "gatitebi", "cognitoUsername": null}, "requestingUser": {"userGuid": "E72A12C916A44A9581CF39E5DCDFFAE7", "userType": "I", "idpUserName": "IANLIU", "cognitoUsername": "test-idir_e72a12c916a44a9581cf39e5dcdffae7@idir"}, "requestIP": "165.225.210.217", "exception": {"exceptionType": "HTTPException", "statusCode": 500, "details": {"code": "unknown_state", "description": "Revoke user permission encountered problem.Unknown forest client number 12345678 for scoped permission FOM_SUBMITTER_12345678."}}} }
	
{ 'eventTime': '2025-01-25 00:26:03', 'logger': 'api.app.utils.audit_util', 'level': 'INFO', 'eventMessage': {"auditEventTypeCode": "REMOVE_USER_ROLE_ACCESS", "auditEventResultCode": "FAIL", "applicationId": 2, "applicationName": "FOM_DEV", "applicationEnv": "DEV", "roleId": 74, "roleName": "FOM_SUBMITTER_12345678", "roleType": "C", "forestClientNumbers": [], "targetUser": {"userGuid": "7CEE19E97218419FADF740DBCD2B8265", "userType": "B", "idpUserName": "gatitebi", "cognitoUsername": null}, "requestingUser": {"userGuid": "E72A12C916A44A9581CF39E5DCDFFAE7", "userType": "I", "idpUserName": "IANLIU", "cognitoUsername": "test-idir_e72a12c916a44a9581cf39e5dcdffae7@idir"}, "requestIP": "165.225.210.217", "exception": {"exceptionType": "HTTPException", "statusCode": 500, "details": {"code": "unknown_state", "description": "Revoke user permission encountered problem.Unknown forest client number 12345678 for scoped permission FOM_SUBMITTER_12345678."}}} }
	
2025-01-25T00:26:03.484Z
2025-01-25 00:26:03 - 41 - api.app.database - WARNING - DB session exception: 500: {'code': 'unknown_state', 'description': 'Revoke user permission encountered problem.Unknown forest client number 12345678 for scoped permission FOM_SUBMITTER_12345678.'}
	
2025-01-25 00:26:03 - 41 - api.app.database - WARNING - DB session exception: 500: {'code': 'unknown_state', 'description': 'Revoke user permission encountered problem.Unknown forest client number 12345678 for scoped permission FOM_SUBMITTER_12345678.'}
	
2025-01-25T00:26:03.484Z
2025-01-25 00:26:03 - 2704 - sqlalchemy.engine.Engine - INFO - ROLLBACK

• Cause: The forest client number is not a valid number for the scoped user role. It causes issue for saving user-role revoke history into audit table because the history needs to know what was the forest client number and forest client name (by search the forest client number) for the revoked history.

---

For this scenario, I think we can just delete the (user/role assignment) records with non-existing forest_client_number 12345678. These records were pre-existing before FAM has forest_client_number search check and they were just testing data.
Deletion script:

WITH non_existing_fc_user_role_records as (
    select user_role_xref_id from app_fam.fam_user_role_xref ux 
    join app_fam.fam_role fr on ux.role_id = fr.role_id 
    join app_fam.fam_forest_client fc on fr.client_number_id = fc.client_number_id
    where fc.forest_client_number = '12345678'
)
delete from app_fam.fam_user_role_xref where user_role_xref_id in (select * from non_existing_fc_user_role_records);

DEV (user/role record deleted):

TEST (does not have such record):

PROD (1 user/role record deleted):

[ianliuwk1019]

Luckily for 422 error (empty user_guid) case in PROD all BCeID type users (with empty user_guid) are all non-existing users (I searched for all of them) except one, but that one is FOM's testing account in PROD('FOM_TESTBCEID4') so we can remove all associated BCeID user role permissions (fam_user_role_xref).

In this ticket, I used db script to now clean up all BCeID users' user role permissions for the ones associated with the empty user_guid in DEV/TEST/PROD environments and also remove these users from fam_user table.

However, for the IDIR type users who are having empty user_guid, some are valid users (after search) and are associated with user/role permission assignments. So at this time I will leave it and will create a separate ticket to discuss options and resolve these IDIR type users with empty user_guid.
@OlgaLiber2 @basilv

[OlgaLiber2]

Sounds good. Thanks Ian! Great investigative work. @ianliuwk1019

[ianliuwk1019]

Link to new ticket #1742

[ianliuwk1019]

FYI, I was looking at some settings in FAM repo and see this for Github "Projects":

@basilv @OlgaLiber2
It is interesting our repo is public and the "Project" can be set as "private"; so our attached information were safe (still better to be aware and cautious for privacy and security).
But, not sure where it can be set, maybe @gormless87 knows.