Create 2FA admin account for CAMIS

2FA account creation so that the CAMIS administrator can assign eMER status to CAME.
This assumes that 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;

Create 2FA admin account for CAMIS

0 Comments