Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Reverted from v. 5

Info

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

Developers should not be added as a CAME via CAMIS Admin on Production because developers should not be listed in the CAME drop-down.

...

.

...

...

Insert into CAMIS.YA096_PERSON (PERSON_ID, LAST_NAME_NM, FIRST_NAME_NM, EMAIL_TXT, LANGUAGE_CORRESPONDENCE_CD, LANGUAGE_SERVICE_CD, EXTERNAL_PERSON_IND, DATE_CREATED_DTE, DATE_DELETED_DTE, DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID) Values (558724, 'AAACTestAcct200', 'Noreen', 'noreen.dertinger@tc.gc.ca', 'B', 'B', 1, TO_DATE('2023/07/26 9:43:59 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/26 9:45:47 AM', 'YYYY/MM/DD HH:MI:SS AM'), SYSDATE, 557838); Insert into CAMIS.YA001_MEDICAL_EXAMINER (MEDICAL_EXAMINER_PERSON_ID, APPOINTMENT_START_DTE, APPOINTMENT_END_DTE, COMMENT_TXT, HOME_ADDRESS_PRIVATE_IND, MAILING_ADDRESS_PRIVATE_IND, EXAMINER_STATUS_CD, EMAIL_PRIVATE_IND, SEARCH_IND, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE, DATE_DELETED_DTE, USER_LAST_UPDATE_ID) Values (558724, TO_DATE('2023/07/19', 'YYYY/MM/DD'), TO_DATE('2027/07/19', 'YYYY/MM/DD'), 'Test account - noreen', 'Y', 'Y', '1', 'N', 'Y', TO_DATE('2023/07/26 9:43:59 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/26 9:45:47 AM', 'YYYY/MM/DD HH:MI:SS AM'), SYSDATE, 557838); Insert into CAMIS.YA006_PERSON_TELEPHONE (TELEPHONE_ID, PERSON_ID, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE, DATE_DELETED_DTE
Code Block
Code Block
You will require a unique stakeholder id in the table AC040 for an external account.
Get next value from sequence: SELECT AC040_STAKEHOLDER_ID_SEQ.nextval FROM dual; 
**This will be used as the STAKEHOLDER_ID for the external (CAME account)

You will also require a 12 character password which is separate from the password for a CAMIS internal
account. Note: GLOBAL_USERID_LBL is just a random string of 12 alphanumeric characters.
You can use sites like StrongPasswordGenerator (Get a Safe Password Now) - Privacy Canada  
to generate the string

Replace the values in the example with those pertaining to the new CAME account for developers.
Example: Phuong stakeholder id = 3670, Mbun is [yourMBUN], GLOBAL_USERID_LBL = 1VBEZE4QB4HZ, Activation_CODE_TXT: 1CPQKPBFSS3C8JSA

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
  VALUES (1661003670, 5587242, TO_DATE('2023/07/26 9:46:15 AM'Phuong', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/26 9:46:15 AM', 'YYYY/MM/DD HH:MI:SS AM'), SYSDATE, 557838Nguyen', 1, '1VBEZE4QB4HZ', 1);

INSERT INTO Insert into CAMIS.UA031_MEDICAL_EXAMINER_OFFICE
   (MEDICAL_EXAMINER_PERSONTWOFA.AC002_XREF_APPLICATION_USER (STAKEHOLDER_ID, MEDICAL_OFFICE_ADDRESS_IDAPPLICATION_CD, CAMIS_REGIONROLE_CD, DATESTAKEHOLDER_CREATED_DTE, DATE_LAST_UPDATE_DTE, DATE_DELETED_DTE,
    ID, USER_LAST_UPDATE_ID)
VALUES Values
   (5587243670, 3465911, '3642', TO_DATE('2023/07/26 9:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/26 9:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'),
    SYSDATE, 557838)1, 1, 1);

Insert into CAMISTWOFA.UA041AC044_MEDICALEXTERNAL_OFFICE_TELSTAKEHOLDER
   (MEDICALSTAKEHOLDER_EXAMINER_PERSON_ID, MEDICALSTAKEHOLDER_OFFICETYPE_ADDRESS_IDCD, TELEPHONEIDENTITY_ISSUER_IDTXT, PRIVATEIDENTITY_MBUN_INDNUM, DATEACTIVATION_CREATEDCODE_DTETXT,
    DATE_LAST_UPDATEACTIVATED_DTE, DATE_DELETED_DTE, USER_LAST_UPDATE_ID)
 Values
   (558724, 346591, 166099, 'N', TO_DATE('2023/07/26 9:45:02 AM', 'YYYY/MM/DD HH:MI:SS AM'),
    TO_DATE('2023/07/26 9:45:02 AM', 'YYYY/MM/DD HH:MI:SS AM'), SYSDATE, 557838);

Insert into CAMIS.UA045_STAMP
   (STAMP_NUMBER_TXT, MEDICAL_EXAMINER_PERSON_ID, MEDICAL_OFFICE_ADDRESS_ID, STAMP_ASSIGNED_DTE, STAMP_RETURN_DTETELEPHONE_NUMBER_NUM, STAKEHOLDER_CREATED_ID, DATE_CREATED_DTE, DATE_DELETED_DTE,
    DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)

Values
   ('A2165', 5587243670, 346591, TO_DATE('2023/07/01'2', 'YYYY/MM/DD'), SYSDATE, TO_DATE('2023/07/26 9:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'), SYSDATE,https%3A%2F%2Fclegc-gckey.gc.ca', '[yourMBUN]', '1CPQKPBFSS3C8JSA',
    SYSDATE, TO_DATE('2023/07/26 9:45:01 AM'3435480275', 'YYYY/MM/DD HH:MI:SS AM'), 5578381, SYSDATE, SYSDATE, 1);
    

-- Note: the value for TWOFA_USERID_LBL has to match the GLOBAL_USERID_LBL in the corresponding TWOFA database. 
-- refer to the Confluence document "Refreshing the 2FA database"-- Refreshing the 2FA database 

Insert into CAMIS.AA008_APPLICATION_USER
   (PERSON_ID, CAMIS_REGION_CD, USER_STATUS_CD, TWOFA_USERID_LBL, DATE_CREATED_DTE,
    DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
 Values
   (558724, '431', '1', 'UKTTCV8HZVF6', TO_DATE('2023/07/26 9:44:00 AM', 'YYYY/MM/DD HH:MI:SS AM'),
    TO_DATE('2023/07/26 9:46:24 AM', 'YYYY/MM/DD HH:MI:SS AM'), 557838);

COMMIT;

...

Code Block
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 (1447, 2, 'Noreen', 'AAACTestAcct200', 1, 'UKTTCV8HZVF6', 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
(1447, '2', '', '', 'y52cA9lR76J6',
SYSDATE, '6132958253', 1, SYSDATE, SYSDATE, 1);

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

...

if you don't have a GCKey for eMER Production already, you will need to sign up for one.

Find [yourMBUN] by go to this link https://gart.tc.gc.ca/secure/piu/saml

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