Account Access: CAME cannot access/login to eMER (developer troubleshooting)
- Noreen Dertinger
This article contains the scripts for checking a CAME’s account only. This process is not valid for checking a a CAME ASSOCIATE’s account.
Requirements for developer troubleshooting the issue:
1. setup connections to CAMIS and TWOFA in a database tool (i.e. TOAD, SQL Developer, etc.).
Access to Database Passwords
2a. Developer needs to have access to CAMIS as an internal user with the following roles:
System Administrator
eMER - CAME/Associate User Administrator
eMER Triage
2b. The following steps need to be done for the developer’s account:
Create 2FA admin account for CAMIS
Troubleshooting the access issue:
Before starting: make sure you have the CAME’s STAMP number. |
|---|
Run the attached script “CAMIS_CHECK_CAME_ACCESS.sql” to check the data in CAMIS. It retrieves the data that we have to check in CAMIS using a single query.
--This script checks the data related to a CAME's account.
--This script is not valide for checking the data for a CAME ASSOCIATE Admin
--If the values return do not match the expected values it will be necessary to update the data - instructions are provided as part of the CAME ACCESS support documentation
select UA045_STAMP.STAMP_NUMBER_TXT,
UA045_STAMP.STAMP_RETURN_DTE, --if the STAMP_RETURN_DTE is in the past, update it to be blank
YA096_PERSON.PERSON_ID, YA096_PERSON.LAST_NAME_NM, YA096_PERSON.FIRST_NAME_NM,
AA008_APPLICATION_USER.USER_STATUS_CD, -- expected value is 1,
AA011_EXTERNAL_ACCESS.DATE_DELETED_DTE, --expected value is blank. That is, there should not be a date here.
AA011_EXTERNAL_ACCESS.ACTIVATION_KEY_STATUS_CD, -- expected value is 20,
AA011_EXTERNAL_ACCESS.LAST_LOGIN_DTE, -- if CAME has not logged in for a long time, they may need to create a new gc key, --
AA008_APPLICATION_USER.TWOFA_USERID_LBL --IMPORTANT: copy the TWOFA_USERID_LBL to notepad as you will need it when checking the CAME's data in the TWOFA database
from (((YA096_PERSON
inner join AA008_APPLICATION_USER on YA096_PERSON.PERSON_ID = AA008_APPLICATION_USER.PERSON_ID)
inner join AA011_EXTERNAL_ACCESS on YA096_PERSON.PERSON_ID = AA011_EXTERNAL_ACCESS.EXTERNAL_APPLICATION_USER_ID )
inner join UA045_STAMP on YA096_PERSON.PERSON_ID = UA045_STAMP.MEDICAL_EXAMINER_PERSON_ID )
where UA045_STAMP.STAMP_NUMBER_TXT in 'stamp_number_txt' --replace the text stamp_number_txt with the stamp number provided in the ticket**. For example ('A2260') or ('A2260', 'A1111')
-- ** The leading letter of the STAMP_NUMBER_TXT is case sensitive - has to be upper case.
The expected output of running the script is as shown below:
STAMP_RETURN_DTE if the STAMP_RETURN_DTE is in the past, update it to be blank (source table is AA045_STAMP)
USER_STATUS_CD should be 1 - 1 = Active. (source table is AA008_APPLICATION_USER)
DATE_DELETED_DTE should be Blank/NULL/empty (source table is AA008_APPLICATION_USER)
ACTIVATION_KEY_STATUS_CD should be 20. (source table is AA011_EXTERNAL_ACCESS)
LAST_LOGIN_DTE if CAME has not logged in for a long time, they may need to create a new gc key
(source table is AA011_EXTERNAL_ACCESS)
TWOFA_USERID_LBL - IMPORTANT Copy this value to notepad. You will need it for querying the 2FA table. (source table is AA011_EXTERNAL_ACCESS)
UPDATE SCRIPTS to correct data in CAMIS for CAMES ONLY
does not apply to CAME Associate Admins.
Run the following scripts if needed:
If STAMP_RETURN_DATE is NOT NULL
update UA045_STAMP
set STAMP_RETURN_DTE=''
where STAMP_NUMBER_TXT='stamp_number_txt'; –- replace stamp_number_txt with the STAMP_NUMBER_TXT that was retrieved by the single combined query.
If USER_STATUS_ID is not 1 (ACTIVE)
update AA008_APPLICATION_USER
set USER_STATUS_CD=1
where PERSON_ID = 'application_user_person_id'; –- replace person_id with the PERSON_ID that was retrieved by the single combined query.
IF DATE DELETED DTE is not NULL (this is for CAME only as there is no DATA in AA0011_EXTERNAL_ACCESS for CAME ASSOCIATES)
IF ACTIVATION_KEY_STATUS_CD is not 20 (this is for CAME only as there is no DATA in AA0011_EXTERNAL_ACCESS for CAME ASSOCIATES)
Checking the TWOFA Data for the account:
connect to the TWOFA database and run the script below.
Run the following scripts if needed:
TWOFA_check_ACCESS_CAME”. It retrieves the data that we have to check in TWOFA using a single query.
Important: you will need the TWOFA_USERID_LBL obtained from the script run in step 2.
An example of the expected output of running the script is shown below.
If the data is not correct in CAMIS you will need to run the scripts from the section “UPDATE” Scripts for correcting the CAMIS DATA” and “UPDATE” Scripts for correcting TWOFA DATA”.
Run the relevant scripts from step 2 and 3 again to confirm that the data is correctly updated.
Ask requester to get activation code from CAMIS and send to CAME, have CAME confirm that they can login.
UPDATE SCRIPTS to correct data in TWOFA for CAMEs (does not apply to CAME Admins)
IF DATE DELETED DTE is not NULL in AC040_STAKEHOLDER:
IF ACCOUNT LOCKED IN IS NOT 0 AC040_STAKEHOLDER:
IF DATE ACTIVATED DTE is not NULL in AC044_EXTERNAL_STAKEHOLDER:
IF DATE EXPIRED DTE is not NULL in AC044_EXTERNAL_STAKEHOLDER:
AC044_EXTERNAL_STAKEHOLDER.ACTIVATION_CODE
This is a visual comparison. Compare the value returned by the twofa query that is provided with the activation code in CAMIS.
AC044_EXTERNAL_STAKEHOLDER.TELEPHONE_NUMBER_NUM
This is a manual check.
AC044_EXTERNAL_STAKEHOLDER.IDENTITY_MBUN_NUM