Refreshing the 2FA database
- Noreen Dertinger
Overview
This document describes the steps that needed to be done for the the 2FA refresh of Production to Acceptance in July 2023.
The purpose of the 2FA refresh was to make the data in 2FA acceptance be in sync with CAMIS acceptance.
see also:
Draft: CAMIS refresh - impacts / considerations re 2FA data
BEFORE the Refresh
Backup all tables
Backup the TWOFA tables prior to the refresh.
Create a script to restore the GLOBAL_USERID_LBL value after the refresh
GLOBAL_USERID_LBL in TWOFA.AC040_STAKEHOLDER has to be identical to TWOFA_USERID_LBL in CAMIS.AC008_APPLICATION_USER
If the values are not identical, they have to be updated by a dba on acceptance.
The following script identifies current development and CAMIS business support accounts with 2FA admin access on CAMIS.
The STAKEHOLDER_TYPE_CD = 1 for such accounts.
select NAME_LAST_NM, GLOBAL_USERID_LBL from AC040_STAKEHOLDER
where upper(NAME_LAST_NM) in ('ASGILL', 'AMIN', 'COLLINS', 'DERTINGER', 'KHAN', 'MAGHUR', 'NGUYEN', 'WANG', 'SHIRZAD', 'LE', 'BEDARD', 'DAVID-OMBIGA', 'KENNEDY', 'PEREIRA', 'ROY', 'SAMANT', 'ZOBARICH', 'BAPTISTE', 'DAWI', 'SHEIKHJ' )
and STAKEHOLDER_TYPE_CD = 1
and DATE_DELETED_DTE IS NULL
and ACCOUNT_LOCKED_IND = 0
order by NAME_LAST_NM asc;
The following script identifies current development and CAMIS business support accounts with access to eMER.
The STAKEHOLDER_TYPE_CD = 2 for such accounts.
select NAME_LAST_NM, GLOBAL_USERID_LBL from AC040_STAKEHOLDER
where upper(NAME_LAST_NM) in ('ASGILL', 'AMIN', 'COLLINS', 'DERTINGER', 'KHAN', 'MAGHUR', 'NGUYEN', 'WANG', 'SHIRZAD', 'LE', 'BEDARD', 'DAVID-OMBIGA', 'KENNEDY', 'PEREIRA', 'ROY', 'SAMANT', 'ZOBARICH', 'BAPTISTE', 'DAWI', 'SHEIKHJ' )
and STAKEHOLDER_TYPE_CD = 2
and DATE_DELETED_DTE IS NULL
and ACCOUNT_LOCKED_IND = 0
order by NAME_LAST_NM asc;
Prepare a script for the dba to update the accounts in the TWOFA database.
On development, developers can run a script to update the GLOBAL_USERID_LBL
If the script is being run on acceptance it has to be approved by the database management team and run by a dba as our accounts do not have sufficient privileges.
Script to update the GLOBAL_USERID_LBL column:
--The purpose of this script is to update the GLOBAL_USERID_LBL column to be in sync with CAMIS.AA008_APPLICATION_USER
--Last updated July 17, 2023 by Noreen Dertinger
--temporarily disable trigger TWOFA.AC040_BU_KEEP_GLOBAL_USER_ID
ALTER TRIGGER TWOFA.AC040_BU_KEEP_GLOBAL_USER_ID DISABLE;
--update Internal STAKEHOLDERS
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = 'uR2hE8cY9cX9' where STAKEHOLDER_ID = 5210; -- Hiren
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = 'REL410000002' where STAKEHOLDER_ID = 441; -- Daniel
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = 'J5MBODB0SAJM' where STAKEHOLDER_ID = 2491; -- Samia
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = 'sN2tN8vQ0mC9' where STAKEHOLDER_ID = 5110; -- Javad
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = 'A3YV9LU7K1R6' where STAKEHOLDER_ID = 4411; -- Noreen
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = '6Eu68YWgmpvu' where STAKEHOLDER_ID = 5190; -- Siamak
--update External STAKEHOLDERS
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = 'TFDN3DMNQ4P8' where STAKEHOLDER_ID = 5211; -- Hiren
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = 'V5P3KEKN5EEX' where STAKEHOLDER_ID = 4073; -- Noreen
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = 'EXVCL22ZZNUU' where STAKEHOLDER_ID = 3110; -- Michelle
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = '68H2227RXHSF' where STAKEHOLDER_ID = 3670; -- Phuong
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = 'L5AT7ATYSEAC' where STAKEHOLDER_ID = 4370; -- Siamak
update TWOFA.AC040_STAKEHOLDER set GLOBAL_USERID_LBL = '56T9Q5K26QQ2' where STAKEHOLDER_ID = 5111; -- TESTING CAME--re-enable trigger TWOFA.AC040_BU_KEEP_GLOBAL_USER_IDALTER TRIGGER TWOFA.AC040_BU_KEEP_GLOBAL_USER_ID ENABLE;
--commit the changes to the database
COMMIT;
Requesting the Refresh
Create a SM-GS ticket with the following
All of the 2FA schemas need to be refreshed when a refresh is done from production to acceptance or development.
The dba should export the following schemas on production and import them on the environment that is being refreshed:
TWOFA
TWOFA_EXTB
TWOFA_CAMIS
TWOFA_EMER
On completion of the refresh, the dba should:
recompile the application to resolve all invalid object and verify that there are no invalid objects remaining.
if they refreshed the the db links: verify that the db links point to the correct environment (acceptance or development). The db links are apparently not typically refreshed.
grants and permissions are in place for the environment that was refreshed,
work with development, if needed, to help resolve database issues arising from the refresh.
After the Refresh:
When the dba confirms that the refresh is completed development should:
Update AC001_APPLICATION
Insert accounts that were present on acceptance but not prod and are not updated in the script run by the DBAs to update the GLOBAL_USERID_LBL:
internal accounts with 2FA admin access and
accounts with access to eMER
Update AC001_APPLICATION (acceptance):
After an update of Acceptance from Production the following script needs to be run to set the correct URLs and API Key values for Acceptance. The script also re-inserts two rows that are on acceptance but not production.
Test that the AC001_APPLICATION values are correct on acceptance:
Restore accounts that were present in acceptance but not production:
1. Accounts with 2FA admin access
The script below was run in July 2023 to restore accounts with 2FA admin access that existed on acceptance prior to the refresh.
2. Accounts with eMER Access
3. Insert records into AC002_XREF_APPLICATION_USER to provide Admin access to eMER
Restore the records, in AC002_XREF_APPLICATION_USER that were present in acceptance prior to the refresh.
Verify that the existing records in have APPLICATION_CD =1 and ROLE_CD = 2
Note:
Changes were recently made to GC Key to add 2FA functionality to it. How those changes will impact 2FA and CAMIS is currently being analyzed. Affected documentation will be updated in Confluence after the scope of the change is known.
Documentation that could be impacted includes, but is not limited to:Account Access: CAME cannot access/login to eMER - preliminary troubleshooting for business
Account Access: CAME cannot access/login to eMER (developer troubleshooting)
Creating a CAME profile (step 3)
Creating a CAME associate profile (screenshot showing activation code and button for generating new activation code)
DevOps tasks that might be impacted
PBI 161551 Resetting the activation code should also clear the MBUN key
-> In relation to some instructions to press the “reset” button in the troubleshooting instructions.
GCKey reset script: the PBI was already completed but it may need to be updated:
PBI 185715 Create Script To Reset GCKey
->Does this script fix the GCKey so that a CAME does not possibly have to create a new GCKey after they did not log in for a long time.