Versions Compared

Old Version 28

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.

...

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.

The ability to create new CAME profiles or generating/re-generating activating CAME profiles should usually only be provided to internal TC users (development and business client (example, Samia Dawi, users authorized by the business client. In these cases, A 2FA admin account also with “role code 2” has to be created.

The following sections explain:

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

  2. Checking what 2FA Admin profiles exist already.

  3. Troubleshooting your existing CAMIS profile.

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

APPLICATION_CD

Dev: 4
Acc: 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

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


...