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.

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://passwordsgenerator.net/ 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

-- 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
;

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);

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');

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;