Versions Compared

Old Version 24

changes.mady.by.user Noreen Dertinger

Saved on

compared with

New Version 25

changes.mady.by.user Le, Michelle

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Info

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:1.

  1. Logging into CAMIS

2.

  1. Selecting User Admin from the menu

3.

  1. Adding the person via the “TC” Directory.

Note

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:

...

  1. Adding a 2FA admin account to an existing CAMIS Profile.

...

  1. Checking what 2FA Admin profiles exist already.

...

  1. Troubleshooting your existing CAMIS profile.

To add 2FA Admin to an existing profile.

...

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

  2. 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

    Code Block
    languagesql
    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);

  3. Create a record in TWOFA.AC043_INTERNAL_STAKEHOLDER

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

  4. Connect using CAMIS_DATA_ADMIN@CAMISP

  5. 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

    Code Block
    languagesql
    UPDATE CAMIS.AA008_APPLICATION_USER 
SET TWOFA_USERID_LBL = :12_chars_alphanumeric
WHERE TC_USER_ID = :tc_user_id;

  6. Create a record in TWOFA.AC002_XREF_APPLICATION_USER

    Code Block
    languagesql
    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);
Info

APPLICATION_CD

Dev: 4
Acc: 1

...

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

Code Block
languagesql
-- 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)

Code Block
languagesql
--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:

Code Block
languagesql
-- 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:

...