Versions Compared

Old Version 32

changes.mady.by.user Noreen Dertinger

Saved on

compared with

New Version Current

changes.mady.by.user Noreen Dertinger

Saved on

Key

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

...

To add 2FA Admin to an existing profile.

Use the connection corresponding to development, acceptance or production as follows:

For development: connect using TWOFA@TTSXD18

For acceptance connect using TWOFA@TTSXA18

For production: Connect using TWOFA@TTSXP18

  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 the CAMIS environment, using CAMIS_DATA_ADMIN@CAMISPADMIN, to the corresponding to where the twofa updates are being made.

    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;

  5. APPLICATION_CD to use in the script provided in this step:

    Dev: 4
    Acc and Prod: 1

    Create a record in TWOFA.AC002_XREF_APPLICATION_USER with the following standard values

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

Example : of adding 2FA admin access to a production account.
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);

...

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

For Development, the APPLICATION_CD =4; for Acceptance and Production use APPLICATION_CD =1

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. For Acc and Prod the APPLICATION_CD = 1
        and au.ROLE_CD = 2        -- Admin
WHERE s.STAKEHOLDER_TYPE_CD = 1   -- Internal user
;

...

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

c) verify that the folder “TC_Directory” is present. If the TC_Directory folder is missing, users with Admin access will not be able to generate new activation codes or add internal CAME users in CAMIS via the
ADMIN → User Admin menu.

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

...