Create 2FA admin account for CAMIS

2FA admin account creation so that the CAMIS administrator can activate/modify a CAME’s eMER account.

This assumes that a record in CAMIS has already been created. If you can log into CAMIS on the target environment (development, acceptance or production) without seeing an error in the login page then you have a CAMIS profile.

If not, the required record can be created by:

Logging into CAMIS
Selecting User Admin from the menu
Adding the person via the “TC” Directory.

Note: adding the “Administrator” privilege to the new CAME profile is insufficient for creating new CAME profiles or generating/re-generating activation codes for existing CAME profiles.

A 2FA admin account also has to be created.
The following sections explain:

Adding a 2FA admin account to an existing CAMIS Profile.
Checking what 2FA Admin profiles exist already.
Troubleshooting your existing CAMIS profile.

To add 2FA Admin to an existing profile.

Connect using TWOFA@TTSXP18

Get next value from sequence: SELECT AC040_STAKEHOLDER_ID_SEQ.nextval FROM dual;
**This will be used as the STAKEHOLDER_ID

Create a record in the TWOFA.AC040_STAKEHOLDER table
**Note: GLOBAL_USERID_LBL is just a random string of 12 alphanumeric characters. You can use sites like https://privacycanada.net/strong-password-generator/ to generate the string

INSERT INTO TWOFA.AC040_STAKEHOLDER (STAKEHOLDER_ID, STAKEHOLDER_TYPE_CD, NAME_FIRST_NM, NAME_LAST_NM, STAKEHOLDER_CREATED_ID, GLOBAL_USERID_LBL, USER_LAST_UPDATE_ID)
VALUES (:stakeholder_id, 1, 'First Name', 'Last Name', 1, :12_chars_alphanumeric, 1);

Create a record in TWOFA.AC043_INTERNAL_STAKEHOLDER

INSERT INTO TWOFA.AC043_INTERNAL_STAKEHOLDER
VALUES (:stakeholder_id, 1, :12_chars_alphanumeric, 1, :tc_user_id, SYSDATE, NULL, SYSDATE, 1);

Connect using CAMIS_DATA_ADMIN@CAMISP
Copy the string from TWOFA.AC040_STAKEHOLDER.GLOBAL_USERID_LBL into the CAMIS database CAMIS.AA008_APPLICATION_USER.TWOFA_USERID_LBL for the newly created TWOFA account
```
UPDATE CAMIS.AA008_APPLICATION_USER 
SET TWOFA_USERID_LBL = :12_chars_alphanumeric
WHERE TC_USER_ID = :tc_user_id;
```

Create a record in TWOFA.AC002_XREF_APPLICATION_USER

INSERT INTO TWOFA.AC002_XREF_APPLICATION_USER (STAKEHOLDER_ID, APPLICATION_CD, ROLE_CD, STAKEHOLDER_CREATED_ID, USER_LAST_UPDATE_ID)
VALUES (:stakeholder_id, 4, 2, 1, 1);

APPLICATION_CD

Dev: 4
Acc: 1

Example:
User: Michelle Le (LEMI)

--TWOFA@TTSXP18
SELECT AC040_STAKEHOLDER_ID_SEQ.nextval FROM dual;

INSERT INTO TWOFA.AC040_STAKEHOLDER (STAKEHOLDER_ID, STAKEHOLDER_TYPE_CD, NAME_FIRST_NM, NAME_LAST_NM, STAKEHOLDER_CREATED_ID, GLOBAL_USERID_LBL, USER_LAST_UPDATE_ID)
VALUES (2491, 1, 'Michelle', 'Le', 1, 'J5MBODB0SAJM', 1);

INSERT INTO TWOFA.AC043_INTERNAL_STAKEHOLDER
VALUES (2491, 1, 'J5MBODB0SAJM', 1, 'LEMI', SYSDATE, NULL, SYSDATE, 1);

--CAMIS_DATA_ADMIN@CAMISP
UPDATE CAMIS.AA008_APPLICATION_USER 
SET TWOFA_USERID_LBL = 'J5MBODB0SAJM' 
WHERE TC_USER_ID = 'LEMI';

--TWOFA@TTSXP18
INSERT INTO TWOFA.AC002_XREF_APPLICATION_USER (STAKEHOLDER_ID, APPLICATION_CD, ROLE_CD, STAKEHOLDER_CREATED_ID, USER_LAST_UPDATE_ID)
VALUES (2491, 1, 2, 1, 1);

To find all users who have CAMIS 2FA Admin accounts:

Run the query below using the TWOFA connection for the environment that you would like to check:

-- Find all users who have CAMIS 2FA admin accounts
SELECT s.STAKEHOLDER_ID, ins.TC_USERID_LBL, s.NAME_FIRST_NM, s.NAME_LAST_NM, s.ACCOUNT_LOCKED_IND, s.GLOBAL_USERID_LBL, ins.CERTIFICATE_DN_NM
FROM AC040_STAKEHOLDER s
    JOIN AC043_INTERNAL_STAKEHOLDER ins ON s.STAKEHOLDER_ID = ins.STAKEHOLDER_ID
        and TC_USERID_LBL is not null
    JOIN AC002_XREF_APPLICATION_USER au ON au.STAKEHOLDER_ID = s.STAKEHOLDER_ID
        and au.APPLICATION_CD = 4 -- eMER DEV
        and au.ROLE_CD = 2        -- Admin
WHERE s.STAKEHOLDER_TYPE_CD = 1   -- Internal user
;

Select statements to verify data:

SELECT * FROM TWOFA.AC040_STAKEHOLDER WHERE GLOBAL_USERID_LBL = 'J5MBODB0SAJM';
SELECT * FROM TWOFA.AC040_STAKEHOLDER ORDER BY STAKEHOLDER_ID DESC;
SELECT * FROM TWOFA.AC043_INTERNAL_STAKEHOLDER ORDER BY STAKEHOLDER_ID DESC;
SELECT * FROM TWOFA.AC002_XREF_APPLICATION_USER ORDER BY STAKEHOLDER_ID DESC;

SELECT * FROM CAMIS.AA008_APPLICATION_USER WHERE TC_USER_ID IN ('DAWIS');

Problems with 2FA Admin Account:

Re-enabling an existing CAMIS Account that was deleted

If your account was added for access to CAMIS, and the error below is displayed, your CAMIS account may have been. The following error in CAMIS after logging in via Entrust. The same error is also displayed if you have not been given access to CAMIS.

To restore access:

Go to table AA008_APPLICATION_USER

Retrieve the affected user record by TC_USER_ID. → TC_USER_ID is the same as the account’s network login id.

Make sure that DATE_DELETED_DTE is NULL.

Take note of your PERSON_ID (you will need this for the remaining steps).

Go to table AA005_USER_ROLE

retrieve the record by using the PERSON_ID obtained in step 1.

Make sure that DATE_DELETED_DTE is NULL for records that have set to deleted (or recently set to deleted).

Go to table YA096_PERSON

retrieve the record by using the PERSON_ID obtained in step 1.

Make sure that DATE_DELETED_DTE is NULL for records that have set to deleted.

2. Unable to activate new CAME accounts or generate new activation codes

If you cannot activate a CAME account or generate a new activation code:
a) verify that you have a 2FA admin account as explained in this article.

b) the following script can be used to check your 2FA admin account.

select 
AC040_STAKEHOLDER.STAKEHOLDER_ID AS "AC040_STAKEHOLDER.STAKEHOLDER_ID", 
AC040_STAKEHOLDER.STAKEHOLDER_TYPE_CD AS "AC040_STAKEHOLDER.AC040_STAKEHOLDER.STAKEHOLDER_TYPE_CD",  -- should be 1.  
AC040_STAKEHOLDER.NAME_FIRST_NM AS "AC040_STAKEHOLDER.NAME_FIRST_NM", 
AC040_STAKEHOLDER.NAME_LAST_NM AS "AC040_STAKEHOLDER.NAME_LAST_NM", 
AC040_STAKEHOLDER.ACCOUNT_LOCKED_IND AS "AC040_STAKEHOLDER.ACCOUNT_LOCKED_IND",  --should be 0
AC040_STAKEHOLDER.GLOBAL_USERID_LBL AS "AC040_STAKEHOLDER.GLOBAL_USER_ID_LBL",   
AC040_STAKEHOLDER.DATE_DELETED_DTE AS "AC040_STAKEHOLDER.DATE_DELETED_DTE",      --should be NULL/empty

AC043_INTERNAL_STAKEHOLDER.CERTIFICATE_DN_NM AS "AC043_INTERNAL_STAKEHOLDER.CERTIFICATE_DN_NM", --should be the same as the GLOBAL_USER_ID_LBL in AC040_STAKEHOLDER
AC043_INTERNAL_STAKEHOLDER.TC_USERID_LBL AS "AC043_INTERNAL_STAKEHOLDER.TC_USERID_LBL", --user's network id 
AC043_INTERNAL_STAKEHOLDER.DATE_DELETED_DTE AS "AC043_INTERNAL_STAKEHOLDER.DATE_DELETED_DTE",  --ahould be NULL/empty

AC002_XREF_APPLICATION_USER.APPLICATION_CD AS "AC002_XREF_APPLICATION_USER.APPLICATION_CD", --should be 1.
AC002_XREF_APPLICATION_USER.ROLE_CD AS "AC002_XREF_APPLICATION_USER.ROLE_CD", --ahould be 2.
AC002_XREF_APPLICATION_USER.DATE_EXPIRY_DTE AS "AC002_XREF_APPLICATION_USER.DATE_EXPIRY_DTE", --should be NULL/empty 
AC002_XREF_APPLICATION_USER.DATE_DELETED_DTE AS "AC002_XREF_APPLICATION_USER.DATE_DELETED_DTE" --should be NULL/empty 

from 
(((AC040_STAKEHOLDER
inner join AC043_INTERNAL_STAKEHOLDER on AC040_STAKEHOLDER.STAKEHOLDER_ID = AC043_INTERNAL_STAKEHOLDER.STAKEHOLDER_ID)
inner join AC002_XREF_APPLICATION_USER on AC040_STAKEHOLDER.STAKEHOLDER_ID = AC002_XREF_APPLICATION_USER.STAKEHOLDER_ID))
where AC040_STAKEHOLDER.STAKEHOLDER_ID = <stakeholder_id>; -- replace <stakeholder_id> with your stakeholder_id from AC040

Set up 2FA PROD account by directing inserting GCkey MBun info (this below script is provided by Michelle Le) → if we want the access to eMER prod as CAMIS admin

Example: Phuong stakeholder id = 3670, Mbun is [yourMBUN], GLOBAL_USERID_LBL = 1VBEZE4QB4HZ, Activation_CODE_TXT: 1CPQKPBFSS3C8JSA

Find [yourMBUN] by go to this link https://gart.tc.gc.ca/secure/piu/saml
INSERT INTO TWOFA.AC040_STAKEHOLDER (STAKEHOLDER_ID, STAKEHOLDER_TYPE_CD, NAME_FIRST_NM, NAME_LAST_NM, STAKEHOLDER_CREATED_ID, GLOBAL_USERID_LBL, USER_LAST_UPDATE_ID)
VALUES (3670, 2, 'Phuong', 'Nguyen', 1, '1VBEZE4QB4HZ', 1);

INSERT INTO TWOFA.AC002_XREF_APPLICATION_USER (STAKEHOLDER_ID, APPLICATION_CD, ROLE_CD, STAKEHOLDER_CREATED_ID, USER_LAST_UPDATE_ID)
VALUES (3670, 1, 1, 1, 1);

Insert into TWOFA.AC044_EXTERNAL_STAKEHOLDER
   (STAKEHOLDER_ID, STAKEHOLDER_TYPE_CD, IDENTITY_ISSUER_TXT, IDENTITY_MBUN_NUM, ACTIVATION_CODE_TXT,
    DATE_ACTIVATED_DTE, TELEPHONE_NUMBER_NUM, STAKEHOLDER_CREATED_ID, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
Values
   (3670, '2', 'https%3A%2F%2Fclegc-gckey.gc.ca', '[yourMBUN]', '1CPQKPBFSS3C8JSA',
    SYSDATE, '3435480275', 1, SYSDATE, SYSDATE, 1);

UPDATE TWOFA.AC044_EXTERNAL_STAKEHOLDER SET IDENTITY_MBUN_NUM = '[yourMBUN] WHERE STAKEHOLDER_ID = 3670;